Re: BUG #18477: A specific SQL query with "ORDER BY ... NULLS FIRST" is performing poorly if an ordering column is n

On Fri, 24 May 2024 at 22:03, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> On 2024-May-24, Alexander Alexander wrote:
>
> > Additionally, as you mentioned, the default index is created with NULLS
> > LAST, but in this case, the column is non-nullable, making NULLS LAST
> > unnecessary as well.
>
> 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?

In the EXECUTE of a prepared statement case, DROP NOT NULL should
cause a relcache invalidation that should be noticed during
AcquireExecutorLocks() which should result in a re-plan. In the
re-plan, the optimisation will not be enabled.

Isn't the argument you're making here just the same as in [1] which
Tom explained was safe in [2]?

I think this concern may have come from our inability to allow to
allow functional dependency detection of columns that are dependant on
a UNIQUE + NOT NULL constraint. e.g.

CREATE TABLE t (a INT NOT NULL UNIQUE, b INT NOT NULL);
CREATE VIEW v_t AS SELECT a,b FROM t GROUP BY a;

The same works ok if you swap "UNIQUE" for "PRIMARY KEY" as PKs make
the columns non-nullable.

For UNIQUE constraints, we cannot allow the view to be created because
we have no way to add a dependency to block the NOT NULL from being
dropped which would invalidate the view.

(Thanks for your work on getting us closer to allowing that. I hope
you get more time to work on that for v18)

David

[1] https://postgr.es/m/202401231915.uwk6zrqbdvsu@alvherre.pgsql
[2] https://postgr.es/m/4071562.1706038734@sss.pgh.pa.us