I force postgresql to use indexes instead of sequential scans by setting
enable_seqscan =3D off in postgresql.conf and it helps in a lot of cases.
Probably not the best practice, but it does improve a lot of the queries
we will execute on a regular basis. It forces the planner to prefer
indexes. I've also noticed that limit behavior which is sort of puzzling
to me.
=20
=20
From: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Lawrence Cohan
Sent: Wednesday, March 30, 2011 10:01 AM
To: pgsql-bugs@postgresql.org
Subject: [BUGS] Postgres not using indexes
=20
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.
=20
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
=20
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?
=20
select oi.id from order_items oi INNER JOIN client_orders co ON
oi.order_id =3D co.id
=20
Regards,
Nenea Nelu.
=20
=20
________________________________
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.