Re: Postgres not using indexes

Thanks for the tip however No 1 is that we can't do that in the production environment due to impact and No 2 that I tried that and is still not using an index on the large table but seq scan.

From: Nathan M. Davalos [mailto:n(dot)davalos(at)sharedmarketing(dot)com]
Sent: March-30-11 12:05 PM
To: Lawrence Cohan; pgsql-bugs(at)postgresql(dot)org
Subject: RE: [BUGS] Postgres not using indexes

I force postgresql to use indexes instead of sequential scans by setting enable_seqscan = off in postgresql.conf and it helps in a lot of cases. Probably not the best practice, but it does improve a lot of the queries we will execute on a regular basis. It forces the planner to prefer indexes. I've also noticed that limit behavior which is sort of puzzling to me.

From: pgsql-bugs-owner(at)postgresql(dot)org [mailto:pgsql-bugs-owner(at)postgresql(dot)org] On Behalf Of Lawrence Cohan
Sent: Wednesday, March 30, 2011 10:01 AM
To: pgsql-bugs(at)postgresql(dot)org
Subject: [BUGS] Postgres not using indexes

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

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.