From: email lists <lists@darrenmackay.com>
To: pgsql-sql@postgresql.org
Subject: query not using index for descending records?
Date: Thu, 29 Jan 2004 22:18:08 +1000
Message-ID: <035C9F7CE28601428BBB5B051C9F77F2017898@orion> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@darrenmackay.com
Subject: Re: query not using index for descending records?
In-Reply-To: <035C9F7CE28601428BBB5B051C9F77F2017898@orion>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox