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_idon 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
