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.davalos@sharedmarketing.com]
Sent: March-30-11 12:05 PM
To: Lawrence Cohan; pgsql-bugs@postgresql.org
Subject: RE: [BUGS] Postgres not using indexes

I force postgresql to use indexes instead of sequential scans by setting en=
able_seqscan =3D off in postgresql.conf and it helps in a lot of cases. Pro=
bably not the best practice, but it does improve a lot of the queries we wi=
ll execute on a regular basis. It forces the planner to prefer indexes. I'v=
e also noticed that limit behavior which is sort of puzzling to me.


From: pgsql-bugs-owner@postgresql.org [mailto:pgsql-bugs-owner@postgresql.o=
rg] On Behalf Of Lawrence Cohan
Sent: Wednesday, March 30, 2011 10:01 AM
To: pgsql-bugs@postgresql.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 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.

________________________________
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.