Re: [BUGS] BUG #14899: not null constraint cann't improve the planner

On 11 November 2017 at 21:34, <digoal(at)126(dot)com> wrote:
> this is the test case, cc table have an constraint not null.
> but it cann't improve the planer's plan, in fact planer can use index
> direct to get the needed tuple.
>
> create table cc(id int not null);
> insert into cc select generate_series(1,1000000);
> create index idx_cc on cc (id asc nulls first);
>
> postgres=# explain (analyze,verbose,timing,costs,buffers) select * from cc
> order by id limit 1;

[ Bad Plan ]

> postgres=# explain (analyze,verbose,timing,costs,buffers) select * from cc
> order by id nulls first limit 1;

[ Good Plan ]

Hi Zhou,

It may seem non-difficult to have the query planner understand that
the index satisfies the Sort here when the column is defined as NOT
NULL. However, the complications around this are around cached plans.
If the NOT NULL is dropped, the cached plan must be invalidated. We've
only got the infrastructure to invalidate cached plans which depend on
a constraint, the problem is that NOT NULLs are not really defined as
a constraint in PostgreSQL. It's simply just a property of
pg_attribute. There have been previous discussions about moving these
into pg_constraint, I just don't recall the exact reason why it's not
been done yet.

From the archives, it looks like the latest attempt at this is at [1],
although it seems to have died because Alvaro didn't quite like the
way something was done in the patch and the author didn't put anything
forward to resolve that.

I agree that it would be nice to see this type of plan improve.
However, this is not a bug. It's simply a missed opportunity.

[1] https://www.postgresql.org/message-id/flat/AANLkTi%3Dk1AARugC%2BSv3XPGf1n97f9HS1y9S7W33f%3Dyye%40mail(dot)gmail(dot)com#AANLkTi=k1AARugC+Sv3XPGf1n97f9HS1y9S7W33f=yye(at)mail(dot)gmail(dot)com

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services