Re: Postgres not using indexes

Please see updated attachment that includes the tables involved in the simple query below and all their indexes. We believe that the performance issue is due to the query not using any index but doing seq scans instead and this is very little related to the knowledge from the link you posted below. As you can see we picked a simple query with INNER JOIN between two indexed tables where postgres 8.3 and 9.0 decides to not use existing indexes for whatever reason.

select oi.id from order_items oi inner join clients_orders co on oi.order_id = co.id;

Lawrence Cohan.

-----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.