Search Postgresql Archives

Why does pg_statio_user_tables report heap_blks_hit after index only scan?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hey pgsql-general,

while debugging some performance issues, I've noticed that index only scan (with number of heap fetches reported as 0) can still cause heap_blks_hit as reported by pg_statio_user_tables to increment (in my case by 1).

For example, on 14.4:

  create table test (a int);
  CREATE TABLE
  insert into test (a) select i from generate_series(1, 100000) i;
  INSERT 0 100000
  create index ix_test on test (a);
  CREATE INDEX
  vacuum analyze test;
  VACUUM
  select pg_stat_reset();
  pg_stat_reset
  ---------------

  (1 row)

  select pg_sleep(1);
  pg_sleep
  ----------

  (1 row)

  Expanded display is on.
  select * from pg_statio_user_tables;
  -[ RECORD 1 ]---+-------
  relid           | 16384
  schemaname      | public
  relname         | test
  heap_blks_read  | 447
  heap_blks_hit   | 102657
  idx_blks_read   | 1
  idx_blks_hit    | 0
  toast_blks_read |
  toast_blks_hit  |
  tidx_blks_read  |
  tidx_blks_hit   |

  Expanded display is off.
  explain analyze
  select a from test where a = 1;
                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
  Index Only Scan using ix_test on test  (cost=0.29..4.31 rows=1 width=4) (actual time=0.043..0.045 rows=1 loops=1)
    Index Cond: (a = 1)
    Heap Fetches: 0
  Planning Time: 0.146 ms
  Execution Time: 0.063 ms
  (5 rows)

  select pg_sleep(1);
  pg_sleep
  ----------

  (1 row)

  Expanded display is on.
  select * from pg_statio_user_tables;
  -[ RECORD 1 ]---+-------
  relid           | 16384
  schemaname      | public
  relname         | test
  heap_blks_read  | 447
  heap_blks_hit   | 102658
  idx_blks_read   | 3
  idx_blks_hit    | 1
  toast_blks_read |
  toast_blks_hit  |
  tidx_blks_read  |
  tidx_blks_hit   |

That is heap_blks_hit incremented by one after index only scan that supposedly did not fetch anything from the table.

Am I right assuming this is due to some non data related read, like checking visibility map, and this will always happen? Or maybe my methodology or other assumptions are wrong?

Thanks for your feedback.

PS: I'm doing those pg_sleeps because I've noticed those are (usually) needed for stats to refresh. Also, not sure why first pg_reset is not clearing pg_statio table (as this works for me in many different contexts)... Apparently I'm not fully understanding when those stats are affected and maybe this is the reason I've trouble interpreting this increment?








[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux