Why is Postgres not using my index on a simple ORDER BY LIMIT 1?

Question

I have a large table (about 11,000,000 rows) and I need to find the first item given a sorting condition.

Note that column Date does NOT accept nulls

Why isn't Postgres using the index:

CREATE INDEX track_ix_date
  ON "Track"
  USING btree
  ("Date" DESC NULLS LAST);

On this simple query:

select * from "Track" order by "Date" desc limit 1

But it does use it on this other query:

select * from "Track" order by "Date" desc nulls last limit 1

The second query is in fact much more faster that the first query.

I have read the indexes and ORDER BY documentation and says that in the special case of an ORDER BY with a LIMIT clause is much more efficient to use the index instead of scanning the table, because the sorting would need to scan the full table just to get a single item

Shouldn't Postgres detect that nulls last / first doesn't matter since the column doesn't accept nulls and just use the fastest method?

Answer 1

There is always a tradeoff, because making the optimizer smarter also means making the optimizer slower, which hurts everybody.

Currently, it isn't smart enough, so you'll have to change the index definition or the query to get it to work.

It might be worth asking for such an improvement on the pgsql-hackers mailing list or write a patch for it yourself and submit it there.