Measuring query disk and index block statistics

The best way to really understand how indexes work to save on the number of disk reads is to show how many blocks were actually used to satisfy that query. The following view merges together the two main sources for relevant table statistics, pg_stat_user_tables and pg_statio_user_tables:

CREATE OR REPLACE VIEW table_stats AS
SELECT
  stat.relname AS relname, 
  seq_scan, seq_tup_read, idx_scan, idx_tup_fetch,
  heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit    
FROM
  pg_stat_user_tables stat
  RIGHT JOIN pg_statio_user_tables statio 
    ON stat.relid=statio.relid;

For the examples coming up, the following snippet of code is used after each statement (with t being the only table they use) to show the buffer usage count statistics that follow the query:

SELECT pg_sleep(1);
\pset x on
SELECT * FROM table_stats WHERE relname='t';
SELECT pg_stat_reset();
\pset x off

The idea is that if you reset all the database...