Difference between idx_tup_read and idx_tup_fetch on Postgres

Question

On Postgres 8.4 when you do:

select * from pg_stat_all_indexes where relname = 'table_name';

It returns the fields idx_tup_read and idx_tup_fetch, what is the difference?

Answer 1

When looking at the source code of the view, then you'll see that idx_tup_read is the result of calling pg_stat_get_tuples_returned() and idx_tup_fetch is the result of calling pg_stat_get_tuples_fetched()

The manual describes the two functions as follows:

pg_stat_get_tuples_returned(oid)

Number of rows read by sequential scans when argument is a table, or number of index entries returned when argument is an index

pg_stat_get_tuples_fetched(oid)

Number of table rows fetched by bitmap scans when argument is a table, or table rows fetched by simple index scans using the index when argument is an index

Answer 2

From postgresql docs,

idx_tup_read is number of index entries returned by scans on this index
idx_tup_fetch is number of live table rows fetched by simple index scans using this index

so, the reads are when index gives back position of the row required and fetches are when the index gives back the table rows themselves.

Answer 3

The official documentation page says that the difference between them appears:

1. when the index is involved in a bitmap index scan
2. if any dead or not-yet-committed rows are fetched using the index
3. if any heap fetches are avoided by means of an index-only scan
4. when the index is accessed by the optimizer's checks

In all these cases idx_tup_read becomes greater than idx_tup_fetch.