[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