From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
        Alexander Alexander <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: 
 <CAApHDvoUQqKy5qoXBBL3NReEiigWJDh54EQuyT8Ha4rO=htOjA@mail.gmail.com>
References: 
 <CA+AiShyGQoZfS6NvCfZ6xz1kTm_Sei3DtreXkNhRr5yd8LacVQ@mail.gmail.com>
 <202405241003.uxtpwvtwcods@alvherre.pgsql>
 <CAApHDvoUQqKy5qoXBBL3NReEiigWJDh54EQuyT8Ha4rO=htOjA@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Thu, 06 Jun 2024 15:29:33 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <951328.1717645689.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 05 Jun 2024 23:48:09 -0400
Message-ID: <951329.1717645689@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/951329.1717645689%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Fri, 24 May 2024 at 22:03, Alvaro Herrera <alvherre@alvh.no-ip.org> w=
rote:
>> 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