query not using index for descending records?
От | email lists |
---|---|
Тема | query not using index for descending records? |
Дата | |
Msg-id | 035C9F7CE28601428BBB5B051C9F77F2017898@orion обсуждение исходный текст |
Ответы |
Re: query not using index for descending records?
|
Список | 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_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
В списке pgsql-sql по дате отправления: