Re: very slow query

On Dec 12, 2007 5:44 AM, Ashish Karalkar <ashish_postgre(at)yahoo(dot)co(dot)in> wrote:
> 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

An index on deliveryid IS being used. Says so right there in your
plan. Says it's expecting to get back exactly one row from it. Now,
sms_new is being seq scanned. While it might have 120M rows,
according to this plan, the query planner thinks it has 384 rows. So,
it's likely you aren't analyzing that table often enough. Just
guessing.

What's the query and table structure look like?