From: | digoal(at)126(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Cc: | digoal(at)126(dot)com |
Subject: | BUG #14899: not null constraint cann't improve the planner |
Date: | 2017-11-11 08:34:15 |
Message-ID: | 20171111083415.31513.29268@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14899
Logged by: Zhou Digoal
Email address: digoal(at)126(dot)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 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;
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=1000000 width=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=2735
Planning time: 0.098 ms
Execution 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=4
Planning time: 0.137 ms
Execution time: 0.072 ms
(9 rows)
```
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2017-11-11 08:39:11 | Re: BUG #14897: Segfault on statitics SQL request |
Previous Message | Andres Freund | 2017-11-10 23:23:04 | Re: BUG #14897: Segfault on statitics SQL request |