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 ...
