Re: very slow query

"A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> wrote: am Wed, dem 12.12.2007, um 11:44:58 +0000 mailte Ashish Karalkar folgendes:
> Hi,
> I am having PostgreSQL 8.2.4 on Suse 10.3
>
> Server is not using the index insted it chooses to take seq scan path. table is
> having @ 120 million rows
>
> here is the output from planner:
> Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106)
> -> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106)
> Filter: (mobile = 919820920858::bigint)
> -> Index Scan using deliveryid_pkey on delivery (cost=0.00..6.02 rows=1
> width=8)
> Index Cond: ("outer".deliveryid = delivery.deliveryid)
> Filter: ((createddate > '2007-12-11 00:00:00'::timestamp without time
> zone) AND (createddate < '2007-12-11 21:00:00'::timestamp without time zone)
> AND ((keyword)::text = 'CRI'::text))
>
> column deliveryid has an index over it in sms_new table.
>
> can anybody please guide to force the usage of index

It's using an index on "deliveryid":
Index Scan using deliveryid_pkey

It is using index on deliveryid of table delivery but not of sms_new which is having 120 million rows.

deliveryid is also present in sms_new having index on it. instead of using index on sms_new its doing seq scan. I want to force index scan on sms_new

Do you have an index on "mobile"? Can you show us the table definition
and the output from EXPLAIN ANALYSE?

no index on mobile

output of EXPLAIn ANALYSE taking very long time.

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 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

---------------------------------
Did you know? You can CHAT without downloading messenger. Click here