Indexing example walkthrough

Discussion of indexes and query plans can quickly reach an overwhelming level of theory. Instead, this section will lead you through running various queries, with and without useful indexes, and showing how the query execution changes.

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