Re: Planner ignoring to use INDEX SCAN

Richard Huxton <dev(at)archonet(dot)com> 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:
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)
-> Bitmap Heap Scan on delivery (cost=2218.02..218057.87 rows=174 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..2218.02 rows=207004 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))

Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

---------------------------------
Get the freedom to save as many mails as you wish. Click here to know how.