Re: very slow query

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote: 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".

Actually all issue is with sms_new table which is having 120 M rows.
an planer is ignoring to use index on delivery id.
EXPLAIN ANALYSE too take long time to show output :(

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)

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

---------------------------------
Now you can chat without downloading messenger. Click here to know how.