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.

> HashAggregate  (cost=5895532.37..5895534.35 rows=158 width=32)
>    ->  Hash Join  (cost=215823.74..5895449.38 rows=5533 width=32)
>          Hash Cond: ("outer".deliveryid = "inner".deliveryid)
>          ->  Seq Scan on sms_new  (cost=0.00..5038183.09 rows=128277444 width=8)
>                Filter: ((otid)::text !~~ 'ERROR%'::text)
>          ->  Hash  (cost=215823.35..215823.35 rows=158 width=32)
>                ->  Bitmap Heap Scan on delivery  (cost=2178.24..215823.35 rows=158 width=32)
>                      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)
>                            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))           

-- 
Alvaro Herrera                         http://www.flickr.com/photos/alvherre/
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiándose", y el computador dirá "especifique el tipo de ciervo"
(Jason Alexander)