Skip site navigation (1) Skip section navigation (2)

Re: Postgres not using indexes

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 (view raw or flat)
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.
>

In response to

Responses

pgsql-bugs by date

Next:From: Lawrence CohanDate: 2011-03-30 16:13:42
Subject: Re: Postgres not using indexes
Previous:From: Nathan M. DavalosDate: 2011-03-30 16:04:41
Subject: Re: Postgres not using indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group