From: Tom Lane <tgl@sss.pgh.pa.us>
To: alexander.berezin3000@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18477: A specific SQL query with "ORDER BY ... NULLS FIRST"
 is performing poorly if an ordering column is n
In-reply-to: <18477-55fd294f03d66632@postgresql.org>
References: <18477-55fd294f03d66632@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Thu, 23 May 2024 17:34:12 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <375310.1716488770.1@sss.pgh.pa.us>
Date: Thu, 23 May 2024 14:26:10 -0400
Message-ID: <375311.1716488770@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/375311.1716488770%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> A specific SQL query with "ORDER BY ... NULLS FIRST" is performing poorly if
> an ordering column is not nullable.

The reason it's performing poorly is that
	ORDER BY updated_at NULLS FIRST
is not compatible with the sort order of your index (which is,
by default, NULLS LAST).  So the query has to be done with an
explicit sort, which requires reading the whole table.

I know you are going to say that it shouldn't matter as long as the
column is marked NOT NULL, but too bad: it does.  This is not a bug,
and it's not something we're likely to expend a great deal of sweat
on improving.  If you know the column is null-free, why are you
writing NULLS FIRST?  If you have a good reason to write NULLS FIRST,
why not declare the index to match?

			regards, tom lane