Re: Postgres not using indexes

I think you are right (my bad) and please see the results below plus a little bit more info about the environment and sorry I missed that before. I've been told the server was tuned to the best for what we need and looks like we will need to change at least the two values below and maybe play with work_mem to see if it solves our issues.
The only issue is that we are running a 24/7 web site against the db and if we need to restart PG for the changes to take place we will need to wait for a downtime before any changes can be made.

'shared_buffers';'500MB' - shared_buffers should be 10% to 25% of available RAM -> change it to 2GB
'effective_cache_size';'2GB' - effective_cache_size should be 75% of available RAM -> change it to 10GB
'work_mem';'1MB' - increase it to 8MB, 32MB, 256MB, 1GB and check if better results.

PostgreSQL 9.0.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
2 x Intel(R) Xeon(R) CPU E5345 @ 2.33GHz
4 x 4GB = 16GB RAM

--query results below:
'version';'PostgreSQL 9.0.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit'
'archive_command';'cp %p /pglog/wal_export/%f'
'archive_mode';'on'
'archive_timeout';'3min'
'autovacuum_analyze_threshold';'1000'
'autovacuum_vacuum_threshold';'1000'
'bytea_output';'escape'
'checkpoint_segments';'64'
'checkpoint_warning';'1min'
'client_encoding';'UNICODE'
'effective_cache_size';'2GB'
'escape_string_warning';'off'
'lc_collate';'en_US.UTF-8'
'lc_ctype';'en_US.UTF-8'
'listen_addresses';'xxx.xxx.xxx.xxx'
'log_autovacuum_min_duration';'2s'
'log_checkpoints';'on'
'log_destination';'syslog'
'log_line_prefix';'user=%u,db=%d '
'log_min_duration_statement';'1s'
'maintenance_work_mem';'256MB'
'max_connections';'1200'
'max_stack_depth';'2MB'
'port';'5432'
'server_encoding';'UTF8'
'shared_buffers';'500MB'
'syslog_facility';'local0'
'syslog_ident';'postgres'
'TimeZone';'Canada/Eastern'
'vacuum_cost_delay';'10ms'
'wal_buffers';'4MB'
'wal_level';'hot_standby'

-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Sent: March-30-11 1:33 PM
To: pgsql-bugs(at)postgresql(dot)org; Lawrence Cohan
Subject: RE: [BUGS] Postgres not using indexes

Lawrence Cohan <LCohan(at)web(dot)com> wrote:
> From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]

>> [configuration advice]

>> If, after reading the above-cited page and tuning your server you
>> still have performance problems, pick one query to work on first,
>> and follow the step outlined here:
>>
>> http://wiki.postgresql.org/wiki/SlowQueryQuestions

> We thank you for the links that have a lots of info and please
> note that we tuned our servers as recommended by Enterprise DB
> experts while they were in house for our hardware/software
> migrations and the setting you mentioned are in place already.

Then the next step would be to provide enough information on one of
the slow queries for people to be able to offer useful advice. Your
other post showed the query and the EXPLAIN ANALYZE output, but the
other information listed in the above-cited page is useful when
trying to understand a problem. I'm particularly curious about the
data types of the id columns and the specifics of the index
definitions.

-Kevin

Attention:
The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies.