Re: Postgres not using indexes

Thank you for all your suggestions and I hope the "set enable_seqscan =3D o=
ff;" will work for the time being until we can make PG config changes and m=
ore testing in the near future. We expect indeed much better performance wi=
th index being used on the 33+million rows table vs seq scan and I will pos=
t back real time results as soon as I can get them done in production serve=
rs.

Regards,
Lawrence Cohan.
-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: April-01-11 10:38 AM
To: Greg Stark
Cc: pgsql-bugs@postgresql.org; Lawrence Cohan
Subject: Re: Postgres not using indexes

Greg Stark <gsstark@mit.edu> wrote:
> On Thu, Mar 31, 2011 at 11:33 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> Greg Stark <gsstark@mit.edu> wrote:
>>
>>> your query does require reading all the data.
>>
>> Huh?  It requires reading all the data from at least *one* of the
>> tables.
>
> The query he posted a plan for was:
>
> EXPLAIN ANALYZE select oi.id from order_items oi inner join
> clients_orders co on oi.order_id =3D co.id;
>
> And the plan for it looks like it's optimal to me:
>
> 'Hash Join  (cost=3D780.55..1908023.16 rows=3D1027457 width=3D4) (actual
> time=3D63.506..85607.003 rows=3D33768 loops=3D1)'
> '  Hash Cond: (oi.order_id =3D co.id)'
> '  ->  Seq Scan on order_items oi  (cost=3D0.00..1558536.52
> rows=3D33843152 width=3D8) (actual time=3D0.005..69718.563 rows=3D33909137
> loops=3D1)'
> '  ->  Hash  (cost=3D480.80..480.80 rows=3D23980 width=3D4) (actual
> time=3D13.072..13.072 rows=3D23980 loops=3D1)'
> '        Buckets: 4096  Batches: 1  Memory Usage: 844kB'
> '        ->  Seq Scan on clients_orders co  (cost=3D0.00..480.80
> rows=3D23980 width=3D4) (actual time=3D0.006..6.570 rows=3D23980 loops=3D=
1)'
> 'Total runtime: 85613.391 ms'

It may or may not be optimal, but the assertion that all 33.9
*million* order_items rows must be read to pick out the needed 33.8
*thousand* is just plain incorrect.  Personally, I won't be shocked
if using the index to cut the tuples accessed by three orders of
magnitude is faster.

-Kevin

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, disseminati=
on or other use of, or taking of any action in reliance upon, this informat=
ion by persons or entities other than the intended recipient is prohibited.=
 If you received this in error, please contact the sender and delete the ma=
terial from any system and destroy any copies.