From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mayrom Rabinovich <mayromrabinovich@gmail.com>
cc: pgsql-hackers@postgresql.org
Subject: Re: Planing edge case for sorts with limit on non null column
In-reply-to: <CAH-Ro_22oaP1B8oYsaiukY3QzC3yDueB-rGGG_Rtb-QtG+hb+w@mail.gmail.com>
References: <CAH-Ro_22oaP1B8oYsaiukY3QzC3yDueB-rGGG_Rtb-QtG+hb+w@mail.gmail.com>
Comments: In-reply-to Mayrom Rabinovich <mayromrabinovich@gmail.com>
	message dated "Thu, 05 Feb 2026 13:23:51 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1615617.1770306397.1@sss.pgh.pa.us>
Date: Thu, 05 Feb 2026 10:46:37 -0500
Message-ID: <1615618.1770306397@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1615618.1770306397%40sss.pgh.pa.us>
Precedence: bulk

Mayrom Rabinovich <mayromrabinovich@gmail.com> writes:
> -- same deal query by the reverse order of the index, but also specify the
> wrong null order
> -- from my understanding this should not matter because we don't have any
> nulls on the table
> -- due to the constraint.

No, that is not taken into account.  The planner's notion of a
concrete sort order always includes a nulls first/last flag, and this
index doesn't match what the query asks for.  If you want this query
to use an index you'll need to make an index that puts nulls at the
other end (either ASC NULLS FIRST or DESC NULLS LAST will do).

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.  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?

			regards, tom lane