Re: Planner ignoring to use INDEX SCAN

Ashish Karalkar wrote:
> Thanks Richard for your replay,
>
> here is the output..
>
> Richard Huxton <dev(at)archonet(dot)com> wrote: Ashish Karalkar wrote:
>> Richard Huxton wrote: Ashish Karalkar wrote:
>>> query which was taking seconds on the join of these two table
>>> suddenly started taking 20/25 min
>> Show the EXPLAIN ANALYSE of your problem query and someone will be able
>> to tell you why.
>>
>> Here is the output from explain analyse:
>
> Actually, this is the output from EXPLAIN not EXPLAIN ANALYSE. It
> doesn't show what actually happened, just what the planner thought was
> going to happen.
>
> Its from EXPLAIN ANALYSE

No it's not, because that shows an extra set of figures. If you see
below there is a cost for each stage and an actual time too.

EXPLAIN ANALYSE SELECT d.id, l.name FROM items.documents d JOIN
lookups.document_class l ON d.class=l.id;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.16..14.00 rows=162 width=19) (actual
time=0.100..0.392 rows=162 loops=1)
Hash Cond: ((d.class)::text = (l.id)::text)
-> Seq Scan on documents d (cost=0.00..10.62 rows=162 width=9)
(actual time=0.024..0.121 rows=162 loops=1)
-> Hash (cost=1.07..1.07 rows=7 width=20) (actual
time=0.029..0.029 rows=8 loops=1)
-> Seq Scan on document_class l (cost=0.00..1.07 rows=7
width=20) (actual time=0.008..0.015 rows=8 loops=1)
Total runtime: 0.506 ms
(6 rows)

> Are the row-estimates roughly accurate?
>
> Yes Row count of sms_new is approx. same

OK, that's good.

>> table structures are more or less same with delivery being parent and sms_new being child having index on deliveryid in both tables.
>>
>> HashAggregate (cost=6153350.21..6153352.38 rows=174 width=32)
>> -> Hash Join (cost=218058.30..6153259.97 rows=6016 width=32)
>> Hash Cond: ("outer".deliveryid = "inner".deliveryid)
>> -> Seq Scan on sms_new (cost=0.00..5240444.80 rows=138939341 width=8)
>> Filter: ((otid)::text !~~ 'ERROR%'::text)
>> -> Hash (cost=218057.87..218057.87 rows=174 width=32)
>
> Well, it knows that it's going to be expensive (cost=5240444.80). Since
> it thinks you'll only get 174 rows from the other side and 6016
> matching, I can't see how an index could be calculated as more expensive.
>
> Try issuing ENABLE seq_scan=off and re-running the EXPLAIN, let's see
> what cost that comes up with.
>
> here is explain out put after setting enable_seqscan=off
>
> HashAggregate (cost=27729224.21..27729226.21 rows=160 width=32)
> -> Nested Loop (cost=2534.67..27729143.31 rows=5393 width=32)
> -> Bitmap Heap Scan on delivery (cost=2094.41..216143.78 rows=160 width=32)

> -> Bitmap Heap Scan on sms_new (cost=440.26..171369.61 rows=46931 width=8)

Well, the estimated cost for this one is up to 27 million from the
previous 6 million. It's doing two bitmap scans and then the nested loop
which is what's pushing the cost up.

Can you post the query too?

--
Richard Huxton
Archonet Ltd