Explain with buffer counts

These examples should have proven to you that looking at the counts of blocks hit and read is valuable for determining whether a query plan is really being executed correctly or not. PostgreSQL 9.0 adds a feature to make this easier than before. Instead of looking at the pg_stat* data as done in the previous example, you can request a count of buffers accessed directly when running EXPLAIN:

EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT count(*) FROM t WHERE v=5;
QUERY PLAN                                                     
----------
Aggregate  (cost=332.92..332.93 rows=1 width=0) (actual time=39.132..39.134 rows=1 loops=1)
   Buffers: shared hit=46 read=23
   ->  Index Scan using i on t  (cost=0.00..308.21 rows=9883 width=0) (actual time=0.069..21.843 rows=9993 loops=1)
         Index Cond: (v = 5)
         Buffers: shared hit=46 read=23

To see how well this works, try running that query again after dropping the index that's selective on v:

DROP INDEX...