我已经阅读了文档和以前的 stackoverflow 问题,但没有发现那些有用的问题。Postgres 版本 10.6
即使对于仅索引扫描,idx_tup_fetch 的值也在增加。所以我无法理解 idx_tup_read 和 idx_tup_fetch 之间的区别。
最初的理解是:idx_tup_read:从索引中读取的匹配记录。
idx_tup_fetch:记录在索引上匹配后从表中获取的实时行。所以我的理解是,只扫描索引,因为没有从表中获取数据,所以这不应该增加。
但是当我进行测试时,这种理解是不正确的。
postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
16413 | 16418 | public | test_monika | test_monika_rank | 4 | 2 | 2
(1 row)
postgres=> select * from test_monika where rank=100;
id | name | rank
----+------+------
95 | | 100
(1 row)
postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
16413 | 16418 | public | test_monika | test_monika_rank | 5 | 3 | 3
(1 row)
postgres=> select rank from test_monika where rank=100;
rank
------
100
(1 row)
postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
16413 | 16418 | public | test_monika | test_monika_rank | 6 | 4 | 4
(1 row)
postgres=> select id from test_monika where rank=100;
id
----
95
(1 row)
postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
16413 | 16418 | public | test_monika | test_monika_rank | 7 | 5 | 5
(1 row)
postgres=> select 1 from test_monika where rank=100;
?column?
----------
1
(1 row)
postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-------------+------------------+----------+--------------+---------------
16413 | 16418 | public | test_monika | test_monika_rank | 8 | 6 | 6
(1 row)
idx_tup_read
每当读取索引条目时都会增加。idx_tup_fetch
每当索引扫描获取表条目时都会增加。如果您的仅索引扫描增加
idx_tup_fetch
,则它并不是真正的仅索引扫描。PostgreSQL 无论如何都必须获取表行,以检查它们是否可见,这是未存储在索引中的信息。EXPLAIN (ANALYZE)
您可以在输出中将其视为“提取的堆元组” 。解决方案是
VACUUM
表格,以便更新可见性地图。