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

The following bug has been logged on the website:

Bug reference:      13437
Logged by:          Kjetil Nygård
Email address:      polpot78@gmail.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

On 2015-06-12 13:21:33 +0000, polpot78@gmail.com wrote:
> 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.

Note that postgres' reports everything as a disk read that goes to the
OS. Which then does it's own level of caching.

Greetings,

Andres Freund