Re: Planner ignoring to use INDEX SCAN

[Ashish Karalkar <ashish_postgre@xxxxxxxxxxx>]

Thanks Richard for your replay,

here is the output..

Richard Huxton <dev@xxxxxxxxxxxx> 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


Are the row-estimates roughly accurate?

Yes Row count of sms_new is approx. same

> 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)
               Recheck Cond: ((createddate >= '2007-12-10 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 00:00:00'::timestamp without time zone))
               Filter: ((taskid = 14267) AND (((remoteip)::text = '192.168.3.26'::text) OR ((remoteip)::text = '202.162.231.230'::text) OR ((remoteip)::text = '202.162.231.2'::text) OR ((remoteip)::text = '192.168.4.3'::text) OR ((remoteip)::text = '192.168.3.3'::text) OR ((remoteip)::text = '202.162.231.105'::text) OR ((remoteip)::text = '202.162.231.5'::text) OR ((remoteip)::text = '202.162.231.1'::text) OR ((remoteip)::text = '192.168.4.6'::text) OR ((remoteip)::text = '192.168.3.6'::text) OR ((remoteip)::text = '202.162.231.107'::text) OR ((remoteip)::text = '202.162.231.7'::text)))
               ->  Bitmap Index Scan on createddate_idx  (cost=0.00..2094.41 rows=197068 width=0)
                     Index Cond: ((createddate >= '2007-12-10 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 00:00:00'::timestamp without time zone))
         ->  Bitmap Heap Scan on sms_new  (cost=440.26..171369.61 rows=46931 width=8)
               Recheck Cond: (sms_new.deliveryid = "outer".deliveryid)
               Filter: ((otid)::text !~~ 'ERROR%'::text)
               ->  Bitmap Index Scan on sms_new_deliveryid_idx  (cost=0.00..440.26 rows=46931 width=0)
                     Index Cond: (sms_new.deliveryid = "outer".deliveryid)




Oh, and I take it sms_new is recently vacuumed and analysed?

yes it is vacuumed and analysed



--
Richard Huxton
Archonet Ltd

---

 Share files, take polls, and discuss your passions - all under one roof.  Click here.