BUG #14163: Index only scan Buffer read enlarged when the data is random

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

Browse pgsql-bugs by date

  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?