BUG #13437: Seq Scan increases heap_blks_read instead of heap_blks_hit when no disk is read

From: polpot78(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13437: Seq Scan increases heap_blks_read instead of heap_blks_hit when no disk is read
Date: 2015-06-12 13:21:33
Message-ID: 20150612132133.32368.65305@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: 13437
Logged by: Kjetil Nygård
Email address: polpot78(at)gmail(dot)com
PostgreSQL version: 9.4.3
Operating system: CentOS 6.6
Description:

When a seq scan is performed, but no real disk reads is performed, then
postgreSQL still reports that disk-reads are performed. iostat reports that
no Blocks are read.

TEST
Machine: 1gb memory (Only PostgreSQL)
Table size: 200mb

TEST TABLES
drop table test;
create table test as (select generate_series(1, 5000000) as id );
alter table test add column tekst varchar(255);
update test set tekst = 'teksten ' || id;
alter table test add constraint pk_test primary key (id);

TEST FROM BASH:
iostat -md /dev/vda
sudo -u postgres psql response <<EOF
select pg_stat_reset();select relname, round (100.0*heap_blks_hit / (0.0
+heap_blks_hit + heap_blks_read), 2)||'%', heap_blks_read, heap_blks_hit
from pg_statio_user_tables where (heap_blks_hit + heap_blks_read) > 0;
select sum(length(tekst)) from test;
select relname, round (100.0*heap_blks_hit / (0.0 +heap_blks_hit +
heap_blks_read), 2)||'%', heap_blks_read, heap_blks_hit from
pg_statio_user_tables where (heap_blks_hit + heap_blks_read) > 0;
EOF
iostat -md /dev/vda

OUTPUT FROM TEST:
### iostat -md
Linux 2.6.32-504.16.2.el6.x86_64 (cu-kny-nrpe1.eiendomsinfo.no) 12. juni
2015 _x86_64_ (1 CPU)

Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 36,09 1,78 2,42 10364 14094

### Reset pg_Stats
pg_stat_reset
---------------

(1 row)

### Empty pg_statio_user_tables
relname | ?column? | heap_blks_read | heap_blks_hit
---------+----------+----------------+---------------
(0 rows)

### Query output
sum
----------
58888896
(1 row)

### Bad stats!
relname | ?column? | heap_blks_read | heap_blks_hit
---------+----------+----------------+---------------
test | 63.01% | 9423 | 16054
(1 row)

### Final iostat, shows no data read
Linux 2.6.32-504.16.2.el6.x86_64 (cu-kny-nrpe1.eiendomsinfo.no) 12. juni
2015 _x86_64_ (1 CPU)

Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
vda 36,04 1,78 2,42 10364 14094

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2015-06-12 13:24:11 Re: BUG #13437: Seq Scan increases heap_blks_read instead of heap_blks_hit when no disk is read
Previous Message Michael Meskes 2015-06-12 13:06:35 Re: Lack of Sanity Checking in file 'misc.c' for PostgreSQL 9.4.x