query not using index for descending records?

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

Responses

Browse pgsql-sql by date

  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