Re: very slow query

"A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> wrote: am Wed, dem 12.12.2007, um 12:44:09 +0000 mailte Ashish Karalkar folgendes:
> > Do you have an index on "mobile"? Can you show us the table definition
> > and the output from EXPLAIN ANALYSE?
> >
> > no index on mobile
>
> Seq Scan on sms_new ... Filter: (mobile = 919820920858::bigint)
> (cost=0.00..5027902.00
>
>
> Can you see the problem?
>
> So i will have to create index on mobile is taht so?

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

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))

any suggestion on this

With Regards
Ashish...

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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