Hello 2011/3/30 Lawrence Cohan <lco...@web.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. > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
