1

I have gone through documentation and previous stackoverflow questions but not findings those helpful. Postgres version 10.6

Even for index only scans values of idx_tup_fetch is increasing. So I am not able to understand difference between idx_tup_read and idx_tup_fetch.

Initially understanding was: idx_tup_read: matching records read from index.

idx_tup_fetch: live rows fetched from table after records are matched on index. So my understanding is that for index only scan since no data is fetched from table so this should not increment.

But when I ran the tests then this understanding is not true.

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)

3
  • It seems to me impossible to try to answer your question without having the actual execution plans of your queries. Could you post the results of EXPLAIN ANALYZE of your queries? – Michal T Jan 4 at 6:20
  • @MichalT: question is not about explain analyze or plan but about diff in idx_tup_read and idx_tup_fetch – user3143770 Jan 6 at 5:29
  • Well, I think you mistakenly consider the index is used when in fact it isn't. The only way to clearly see if the planner really uses the index is by looking at the explain analyze output. Only then we can help you with the interpretation of idx_tup_fetch and idx_tup_read. – Michal T Jan 6 at 7:14
1

idx_tup_read is increased whenever an index entry is read.

idx_tup_fetch is increased whenever an index scan fetches a table entry.

If your index-only scan increases idx_tup_fetch, it is not truly index-only. PostgreSQL had to fetch the table rows anyway just to check if they are visible or not, an information that is not stored in the index.

You can see that as "heap tuples fetched" in EXPLAIN (ANALYZE) output.

The solution is to VACUUM the table so that the visibility map gets updated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.