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
