Re: Postgres not using indexes

Please see results attached before and after the vacuum and note they are taken from version 9.0
As regular maintenance we reindex/vacuum/analyze entire database once a week and run ANALYZE against it every few hours.

Lawrence Cohan.

-----Original Message-----
From: Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)com]
Sent: March-30-11 12:08 PM
To: Lawrence Cohan
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] Postgres not using indexes

Hello

2011/3/30 Lawrence Cohan <LCohan(at)web(dot)com>:
> We have a huge performance issues in Postgres that surfaced due to existing
> indexes not being used like in the example below in both 8.35 and 9.0
> versions.
>
>
>
> Client_Orders table with and int ID as PK which is the order_id and indexed
> – about 155,000 rows
>
> Order_Items table with and int ID primary key and INDEX on Order_id (int)
> matching the ID in the above client_orders table. – about 33 million rows
>
>
>
> A query like below takes almost ten minutes to complete however the result
> set is 33768 rows and Total query runtime: 427539 ms.!!! This is due to the
> fact that the index on Order_Items it is NOT used and a sequence scan is
> done instead but this is obviously not acceptable from performance point of
> view. If I add a LIMIT 1000 for instance then the index is used and query
> returns results in no time as expected but as soon as I go higher in the
> limit to a few thousands then the index on Order_Items.Order_id is no longer
> used – why??? Is there any way to force Postgres to use the existing indexes
> instead of table seq scan which is deadly?
>
>
>
> select oi.id from order_items oi INNER JOIN client_orders co ON oi.order_id
> = co.id
>
>

Do you do a ANALYZE and VACUUM. Can you send a result of EXPLAIN
ANALYZE SELECT ...

Please, do ANALYZE and VACUUM first.

regards

Pavel Stehule

>
> Regards,
>
> Nenea Nelu.
>
>
>
> ________________________________
> 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.
>

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.