Re: very slow query

Ashish Karalkar wrote:

> Thanks for your answer actually that was the part of full query here is the actuall plan

I think you are confusing this for "here is a completely different plan
for a completely different query that has nothing to do whatsoever to
what I was asking before".

On this plan it looks like you need an index on otid. Or maybe not.
It's hard to tell with only an EXPLAIN.

Here is the OUPTPUT from EXPLAIN ANALYSE

 HashAggregate  (cost=5893012.31..5893014.28 rows=158 width=32) (actual time=829.511..829.511 rows=0 loops=1)
   ->  Hash Join  (cost=215823.74..5892929.49 rows=5521 width=32) (actual time=829.502..829.502 rows=0 loops=1)
         Hash Cond: ("outer".deliveryid = "inner".deliveryid)
         ->  Seq Scan on sms_new  (cost=0.00..5036990.11 rows=128012086 width=8) (actual time=8.620..8.620 rows=1 loops=1)
               Filter: ((otid)::text !~~ 'ERROR%'::text)
         ->  Hash  (cost=215823.35..215823.35 rows=158 width=32) (actual time=820.865..820.865 rows=0 loops=1)
               ->  Bitmap Heap Scan on delivery  (cost=2178.24..215823.35 rows=158 width=32) (actual time=820.857..820.857 rows=0 loops=1)
                     Recheck Cond: ((createddate >= '2007-12-11 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 23:59:59'::timestamp without time zone))
                     Filter: ((taskid = 1024) 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..2178.24 rows=195039 width=0) (actual time=264.982..264.982 rows=208124 loops=1)
                           Index Cond: ((createddate >= '2007-12-11 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 23:59:59'::timestamp without time zone))
 Total runtime: 829.864 ms
(12 rows)


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster