Indexed ORDER BY with LIMIT 1

Question

I am trying to extract the very last row in a table. I have a simple created_at that is indexed. When I request ORDER BY created_at DESC LIMIT 1 , it takes a lot longer than I think it should (about 50 ms on my machine in 36k lines).

EXPLAIN-ing claims to use reverse index scanning, but I confirmed that changing the index (created_at DESC) does not change the cost in the query planner for simple index scanning.

How can I optimize this use case?

Running postgresql 9.2.4 .

Edit:

 # EXPLAIN SELECT * FROM articles ORDER BY created_at DESC LIMIT 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..0.58 rows=1 width=1752) -> Index Scan Backward using index_articles_on_created_at on articles (cost=0.00..20667.37 rows=35696 width=1752) (2 rows) 

Answer 1

Assuming we are dealing with a large table , a partial index may help:

 CREATE INDEX tbl_created_recently_idx ON tbl (created_at DESC) WHERE created_at > '2013-09-15 0:0'::timestamp; 

As you already found out: downward or upward hardly matters here. Postgres can scan backward at almost the same speed (exceptions apply to multiple index columns).

Request to use this index:

 SELECT * FROM tbl WHERE created_at > '2013-09-15 0:0'::timestamp -- matches index ORDER BY created_at DESC LIMIT 1; 

Here you need to make the index much smaller , so it will need to be cached and maintained.

• You need to select a timestamp that is guaranteed to be less than the last.
• You need to recreate the index from time to time to disable old data.
• The condition must be IMMUTABLE .

Thus, the one-time effect worsens over time. a specific problem is a hard-coded condition:

 WHERE created_at > '2013-09-15 0:0'::timestamp 

Automate

You may occasionally update the index and your queries manually. Or you automate it using a function like this:

 CREATE OR REPLACE FUNCTION f_min_ts() RETURNS timestamp LANGUAGE sql IMMUTABLE AS $$SELECT '2013-09-15 0:0'::timestamp$$ 

Index

 CREATE INDEX tbl_created_recently_idx ON tbl (created_at DESC); WHERE created_at > f_min_ts(); 

Query:

 SELECT * FROM tbl WHERE created_at > f_min_ts() ORDER BY created_at DESC LIMIT 1; 

Automation of rest using a cron job or a trigger-based event. Your inquiries may remain the same. But you need to recreate all indexes using this function after changing it. Just uncheck and create each.

First..

... check if you really click on the neck of the bottle with this.

Try a simple DROP index ... ; CREATE index ... operation DROP index ... ; CREATE index ... DROP index ... ; CREATE index ... Then your index could be bloated. Your automatic shutdown settings may be disabled.

Or try VACUUM FULL ANALYZE to get the whole table plus indexes in their original state and check again.

Other options include the usual general performance tuning and coverage indexes , depending on what you actually retrieve from the table.