Re: Planing edge case for sorts with limit on non null column

Thanks for the quick response,

On Thu, Feb 5, 2026 at 5:46 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Also, the question could be turned around: if you know
> that the table contains no nulls, why are you going out of your way to
> specify the "wrong" null order?

That query was generated by an ORM, and I didn't want to create a new
index on my table just for that query because of the overhead
associated with it.
So I ended up patching the ORM library I used in order to drop the
null ordering if the column is non null. But still, that caught me off
guard. I was expecting Postgres to build a better plan for the query.

Here is a simple example that shows how I stumbled into that edge case:
```
-- Create a table to query using created_at as a pagination cursor
CREATE TABLE d (i INT PRIMARY KEY, created_at TIMESTAMP NOT NULL DEFAULT NOW());
CREATE INDEX a ON d (created_at);

-- Get the next 10 records using the "a" index, this select is called
repeatedly with decreasing created_at value based on the smallest
value returned by the previous query.
-- this query is generated by my ORM and the ORM was programmed to
always return nulls last when working with pagination, so it builds a
query similar to this one:
SELECT * FROM d WHERE created_at < $0 ORDER BY created_at DESC NULLS
LAST LIMIT 10;
```

> I'm not really excited about poking holes in the PathKey concept to
> make this work the way you want. I think the odds of introducing bugs
> would be high.

Do you have anything in mind that would be acceptable or safe?
Unless you feel like the risk outweighs the benefit here, I do think
that this edge case could catch other people off guard, especially
users that interact with the database using some sort of ORM.

Thanks again,
Mayrom Rabinovich