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 vers=
ions.
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) m=
atching 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 d=
one 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 r=
eturns results in no time as expected but as soon as I go higher in the lim=
it to a few thousands then the index on Order_Items.Order_id is no longer u=
sed - 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=
=3D co.id
Regards,
Nenea Nelu.
________________________________
Attention:
The information contained in this message and or attachments is intended on=
ly for the person or entity to which it is addressed and may contain confid=
ential and/or privileged material. Any review, retransmission, disseminatio=
n or other use of, or taking of any action in reliance upon, this informati=
on by persons or entities other than the intended recipient is prohibited. =
If you received this in error, please contact the sender and delete the mat=
erial from any system and destroy any copies.