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@postgresql.org so that your message can get through to the mailing list cleanly