Is it possible to get a "pages touched" statistic from PostgreSQL?

Question

With PostgreSQL one can measure disk reads and cache hits. Is it possible to have a clearer statistic of how many index pages were "touched" to run a query? If so, how?

Answer 1

You can analyze your query with EXPLAIN (ANALYZE, BUFFERS) (BUFFERS being the essential option to see details about memory utilization). A "buffer" in RAM is the equivalent of a "block" on disk, 8 kb on most installations.

Example:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM tbl WHERE tbl_id = 123;

With a big table tbl that's not cached, yet (not recently queried), and a btree index on tbl_id, you'll see something like:

Index Scan using tbl_pkey on tbl  (cost=0.43..5.45 rows=1 width=86) (actual time=61.360..61.363 rows=1 loops=1)
  Index Cond: (tbl_id = 12345)
  Buffers: shared read=4

Meaning that 4 data pages have been read from disk. That includes both index and table pages. For an index scan reading a single row from a big table, that's typically 3 blocks from the index and one from the table.
(For a small table you'd see shared read=2 - only 1 index page for a shallow btree index.)

If you repeat the same, you typically see the same, executed much faster and one difference:

  Buffers: shared hit=4

Meaning that 4 data pages have been read from cache, cached due to the first invocation.

The manual explains:

BUFFERS

Include information on buffer usage. Specifically, include the number of shared blocks hit, read, dirtied, and written, the number of local blocks hit, read, dirtied, and written, and the number of temp blocks read and written. A hit means that a read was avoided because the block was found already in cache when needed. Shared blocks contain data from regular tables and indexes; local blocks contain data from temporary tables and indexes; while temp blocks contain short-term working data used in sorts, hashes, Materialize plan nodes, and similar cases. The number of blocks dirtied indicates the number of previously unmodified blocks that were changed by this query; while the number of blocks written indicates the number of previously-dirtied blocks evicted from cache by this backend during query processing. The number of blocks shown for an upper-level node includes those used by all its child nodes. In text format, only non-zero values are printed. This parameter may only be used when ANALYZE is also enabled. It defaults to FALSE.

Related:

• "Recheck Cond:" line in query plans with a bitmap index scan