Search Postgresql Archives

Re: very slow query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





"A. Kretschmer" <andreas.kretschmer@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly


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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux