From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Alexander Alexander <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: Wed, 05 Jun 2024 23:48:09 -0400
Message-ID: <951329.1717645689@sss.pgh.pa.us> (raw)
In-Reply-To: <CAApHDvoUQqKy5qoXBBL3NReEiigWJDh54EQuyT8Ha4rO=htOjA@mail.gmail.com>
References: <CA+AiShyGQoZfS6NvCfZ6xz1kTm_Sei3DtreXkNhRr5yd8LacVQ@mail.gmail.com>
<202405241003.uxtpwvtwcods@alvherre.pgsql>
<CAApHDvoUQqKy5qoXBBL3NReEiigWJDh54EQuyT8Ha4rO=htOjA@mail.gmail.com>
David Rowley <dgrowleyml@gmail.com> writes:
> On Fri, 24 May 2024 at 22:03, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>> But the NOT NULL constraint could be dropped at any minute, so the
>> system needs to know where NULLs would go if that were to happen.
> In my understanding, the planner will hold a lock that will prevent a
> concurrent session from doing ALTER TABLE ... DROP NOT NULL, so if the
> planner were to do an optimisation such as this, I think it should be
> safe. Can you explain where the hazard is?
This has nothing to do with the planner. The question Alvaro
responded to is whether the DDL definition of the index establishes
which end nulls should go at. Which it does, independently of whether
the table actually contains any nulls.
Yes, we could possibly set things up so that an index with nulls last
is considered to match a query that specifies NULLS FIRST if we know
that the column is not-nullable. But I refuse to believe that this
would be a good use of either development effort or planner cycles.
AFAICS the problem is purely self-inflicted damage: why is the user
specifying NULLS FIRST if he knows the column is not-null?
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, dgrowleyml@gmail.com, alvherre@alvh.no-ip.org, 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: <951329.1717645689@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