What are the difference between tup_fetched and tup_returned in PostgreSQL pg_stat_database view?

Question

Suppose there is a table t1 with 10 data. If I count like

select count(a) from t1

then how tup_fetched and tup_returned will count those information in pg_stat_database view;

As per my understanding tup_returned means it return total 10 record.

and tup_fetched means it fetched 1 record from those returned value which is count=10 rows

But at pg_stat_database details I found when I create database or create any table those value increased and also for table data search those column value increased. Anyone can clear it to me why it is behave like this?

Answer 1

The difference used to be easier to figure out in past versions as it was described in function you could call. Per Postgres 9.1 docs Monitoring-stats:

pg_stat_get_tuples_returned(oid) bigint
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) bigint
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

Now a days you have to go into the source ~/src/include/pgstat.h:

PgStat_TableCounts

Note: for a table, tuples_returned is the number of tuples successfully fetched by heap_getnext, while tuples_fetched is the number of tuples successfully fetched by heap_fetch under the control of bitmap indexscans. For an index, tuples_returned is the number of index entries returned by the index AM, while tuples_fetched is the number of tuples successfully fetched by heap_fetch under the control of simple indexscans for this index.

The descriptions refer to tables/indexes as that is the base unit the data is collected for. This information is then aggregated into the database wide statistics.