From: | "email lists" <lists(at)darrenmackay(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | query not using index for descending records? |
Date: | 2004-01-29 12:18:08 |
Message-ID: | 035C9F7CE28601428BBB5B051C9F77F2017898@orion |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi All,
I have this table;
id - Serial
datetime - timestamp without timezone
with the index as
index idx_trafficlogs_datetime_id on trafficlogs using btree
(datetime,id);
When performing the following query:
explain select datetime,id from trafficlogs order by datetime,id limit
20;
QUERY PLAN
------------------------------------------------------------------------
--------------------------------------------
Limit (cost=0.00..2.31 rows=20 width=12)
-> Index Scan using idx_trafficlogs_datetime_id on trafficlogs
(cost=0.00..1057.89 rows=9172 width=12)
(2 rows)
however, I am wanting to return the last 20 records. I have been using:
explain select datetime,id from trafficlogs order by datetime,id desc
limit 20;
QUERY PLAN
------------------------------------------------------------------------
---------
Limit (cost=926.38..926.43 rows=20 width=12)
-> Sort (cost=926.38..949.31 rows=9172 width=12)
Sort Key: datetime, id
-> Seq Scan on trafficlogs (cost=0.00..322.72 rows=9172
width=12)
as you can see, a sequential scan is performed.
How do I get pg to use an index scan for this query. The table in a
production environment grows by approx 150,000 records daily, hence long
term performance is a major factor here - for each additional day of
data, the above query takes an additional 6-8 secs to run.
Tia,
Darren
From | Date | Subject | |
---|---|---|---|
Next Message | Kumar | 2004-01-29 12:22:35 | Re: SQL Query for Top Down fetching of childs |
Previous Message | MUKTA | 2004-01-29 12:02:10 |