Re: PostgreSQL Query Speed Issues

Hi all,

On 02/21/2013 03:55 PM, Joseph Pravato wrote:
> Do you have an recommendations that are optimized for database with
> 600,000 user records and tables with up to 2,000,000 records?

Joey and I are both working on this and it's related to an issue I
posted a few weeks ago about a "hang". Since then I learned that a
better problem description is "a query that takes longer to complete
then anyone can tolerate". :)

Here is our postgresql.conf for version 9.2.1 running on Centos6/64:

listen_addresses = '*'
max_connections = 200
shared_buffers = 2GB
effective_cache_size = 1024MB
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_timezone = 'US/Central'
datestyle = 'iso, mdy'
timezone = 'US/Central'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

The database is running in a Xen VM with 12GB of ram, 4 virtual CPU's
and fast, dedicated physical disks rather then shared network storage.
The problem queries take one core to saturation and keep it there with
very little disk I/O.

Any suggestions or insights would be greatly appreciated.

Thanks,

-Tom