From: | digoal(at)126(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14163: Index only scan Buffer read enlarged when the data is random |
Date: | 2016-05-28 16:09:41 |
Message-ID: | 20160528160941.19432.17678@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: 14163
Logged by: Zhou Digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: 9.6beta1
Operating system: CentOS 6.x x64
Description:
postgres=# create table tbl7(id int primary key, info text);
CREATE TABLE
postgres=# insert into tbl7 select trunc(random()*10000000),
md5(random()::text) from generate_series(1,5000000) on conflict on
constraint tbl7_pkey do nothing;
INSERT 0 3934548
postgres=# vacuum analyze tbl7;
VACUUM
postgres=# set enable_seqscan=off;
SET
postgres=# explain (analyze,buffers,costs,timing,verbose) select id from
tbl7;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using tbl7_pkey on public.tbl7 (cost=0.43..73176.63
rows=3934547 width=4) (actual time=0.047..969.841 rows=3934548 loops=1)
Output: id
Heap Fetches: 0
Buffers: shared hit=41790
Planning time: 0.055 ms
Execution time: 1686.971 ms
(6 rows)
postgres=# select relpages from pg_class where relname='tbl7_pkey';
relpages
----------
14158
(1 row)
when i cluster it or load it to another table ordered.
the seq scan only scan 10755 pages.
it's a bug or we can optimized it?
postgres=# create table tbl8(id int primary key, info text);
CREATE TABLE
postgres=# insert into tbl8 select * from tbl7 order by id;
INSERT 0 3934548
postgres=# vacuum analyze tbl8;
VACUUM
postgres=# select relpages from pg_class where relname='tbl8_pkey';
relpages
----------
10791
(1 row)
postgres=# explain (analyze,buffers,costs,timing,verbose) select id from
tbl8;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using tbl8_pkey on public.tbl8 (cost=0.43..69809.63
rows=3934547 width=4) (actual time=0.026..921.137 rows=3934548 loops=1)
Output: id
Heap Fetches: 0
Buffers: shared hit=10755
Planning time: 0.111 ms
Execution time: 1626.908 ms
(6 rows)
best regards ,
digoal
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-05-28 16:52:03 | Re: ram consumption |
Previous Message | 德哥 | 2016-05-28 15:59:00 | Re: BUG #14161: reverse index scan cann't use double link? |