From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Lawrence Cohan <LCohan(at)web(dot)com> |
Cc: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: Postgres not using indexes |
Date: | 2011-03-30 16:07:38 |
Message-ID: | AANLkTin4YBeRku4kY3HeRf+QWHJezri3RHA-QBBwRkVw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Lawrence Cohan | 2011-03-30 16:13:42 | Re: Postgres not using indexes |
Previous Message | Nathan M. Davalos | 2011-03-30 16:04:41 | Re: Postgres not using indexes |