MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: query not using index for descending records?
Date: Thu, 29 Jan 2004 22:18:08 +1000
Content-class: urn:content-classes:message
Message-ID: <035C9F7CE28601428BBB5B051C9F77F2017898@orion>
Thread-Topic: query not using index for descending records?
Thread-Index: AcODcHOY6tfixTn+QQ+mYkt5tzbVLxi7maEQ
From: "email lists" <lists@darrenmackay.com>
To: <pgsql-sql@postgresql.org>

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=3D0.00..2.31 rows=3D20 width=3D12)
   ->  Index Scan using idx_trafficlogs_datetime_id on trafficlogs
(cost=3D0.00..1057.89 rows=3D9172 width=3D12)
(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=3D926.38..926.43 rows=3D20 width=3D12)
   ->  Sort  (cost=3D926.38..949.31 rows=3D9172 width=3D12)
         Sort Key: datetime, id
         ->  Seq Scan on trafficlogs  (cost=3D0.00..322.72 rows=3D9172
width=3D12)


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