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?

Why are you specifying NULLS LAST at all if you know that your DATE column is NOT NULL? — Nick
– Nick, Commented Nov 1, 2016 at 17:56
Because I noted that the index scan was using the condition DATE IS NOT NULL so I put the NULLS LAST by thinking that postgre was checking that condition because when ordering in descending order the first element was considered as a posible null — Rafael
– Rafael, Commented Nov 1, 2016 at 18:24
Please edit your question and add the the execution plan generated using explain (analyze, verbose). Formatted text please, no screen shots — user330315
– user330315, Commented Nov 1, 2016 at 18:36

Laurenz Albe · Accepted Answer · 2016-11-06 04:40:25Z

4

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.

answered Nov 6, 2016 at 4:40

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bjorsig · Accepted Answer · 2022-03-03 14:34:57Z

3

I came across this question when I was searching for an answer to a very similar problem. I found the answer in this article in the PostgreSQL documentation. https://www.postgresql.org/docs/current/indexes-ordering.html

So in short: Limit N is an order by function. When there is an index matching the order by clause of the query it can use it, otherwise not. So by specifying "null last" you are matching the order by of the query with the order of the index making it usable. Otherwise the server cannot be certain it is fetching the correct N records.

edited Mar 3, 2022 at 14:34

user330315

answered Mar 3, 2022 at 13:35

bjorsig

1,1172 gold badges7 silver badges21 bronze badges

Comments

wrattlesnake_wrangler · Accepted Answer · 2019-07-18 03:13:52Z

1

Because you're doing a "SELECT ALL ROWS" with that query. Postgres doesn't bother using an index.

I would bet that if you added a "WHERE date = ?" condition, the index would be used.

answered Jul 18, 2019 at 3:13

wrattlesnake_wrangler

214 bronze badges

1 Comment

Surya Over a year ago

Nope even if we add where date = ?, it is not using the index

Xoel · Accepted Answer · 2024-09-18 11:24:21Z

0

Because when you do select * from "Track" order by "Date" desc limit 1, what Postgres is actually doing is select * from "Track" order by "Date" desc NULLS FIRST limit 1

And there's no index for that, because you specified NULLS LAST when you created the index.

For your query to use the index, you'd have to remove the NULLS LAST part, or create another index with NULLS FIRST or with nothing, as NULLS FIRST is the default. Like this:

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

or just

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

answered Sep 18, 2024 at 11:24

Xoel

3936 silver badges18 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related