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
Date: Thu, 23 May 2024 14:26:10 -0400
Message-ID: <375311.1716488770@sss.pgh.pa.us> (raw)
In-Reply-To: <18477-55fd294f03d66632@postgresql.org>
References: <18477-55fd294f03d66632@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, alexander.berezin3000@gmail.com, 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: <375311.1716488770@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox