Обсуждение: [BUGS] BUG #14899: not null constraint cann't improve the planner

The following bug has been logged on the website:

Bug reference:      14899
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 10.1
Operating system:   centos 7.4 x64
Description:

HI, this is the test case, cc table  have an constraint not null. but it cann't improve the planer's plan, in fact
planercan 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;                                                                QUERY PLAN
                                     
 

---------------------------------------------------------------------------------------------------------------------------------------------Limit
(cost=27969.43..27969.43 rows=1 width=4) (actual 
time=263.972..263.972 rows=1 loops=1)  Output: id  Buffers: shared hit=7160  ->  Sort  (cost=27969.43..30469.43
rows=1000000width=4) (actual 
time=263.970..263.970 rows=1 loops=1)        Output: id        Sort Key: cc.id        Sort Method: top-N heapsort
Memory:25kB        Buffers: shared hit=7160        ->  Bitmap Heap Scan on public.cc  (cost=8544.42..22969.42 
rows=1000000 width=4) (actual time=29.927..148.733 rows=1000000 loops=1)              Output: id              Heap
Blocks:exact=4425              Buffers: shared hit=7160              ->  Bitmap Index Scan on idx_cc
(cost=0.00..8294.42
rows=1000000 width=0) (actual time=29.380..29.380 rows=1000000 loops=1)                    Buffers: shared
hit=2735Planningtime: 0.098 msExecution time: 264.009 ms
 
(16 rows)



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

---------------------------------------------------------------------------------------------------------------------------------------Limit
(cost=0.42..0.45 rows=1 width=4) (actual time=0.053..0.053 rows=1 
loops=1)  Output: id  Buffers: shared hit=4  ->  Index Only Scan using idx_cc on public.cc  (cost=0.42..22719.62
rows=1000000 width=4) (actual time=0.052..0.052 rows=1 loops=1)        Output: id        Heap Fetches: 1
Buffers:shared hit=4Planning time: 0.137 msExecution time: 0.072 ms
 
(9 rows)
```


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 11 November 2017 at 21:34,  <digoal@126.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.gmail.com#AANLkTi=k1AARugC+Sv3XPGf1n97f9HS1y9S7W33f=yye@mail.gmail.com

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