2

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?

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:

  • Thanks!! But isn't EXPLAIN a guess i.e. isn't there a possibility that when the actual execution happens, the real result is different? If this is so, how can I measure the 'actual' reads and hits? – audlift-bit Apr 16 '17 at 9:29
  • 1
    EXPLAIN is based on the execution plan. EXPLAIN (ANALYZE) actually executes the SELECT, and gives an answer based on the real execution. See EXPLAIN ANALYZE – joanolo Apr 16 '17 at 18:08
  • When I run a query with EXPLAIN ANALYZE, (planning time+ execution time) is smaller than total query runtime. Where does this difference come from? – audlift-bit Apr 17 '17 at 0:01
  • @audlift-bit; Define "total execution time". For one, EXPLAIN ANALYZE incurs some overhead for timing, obviously. EXPLAIN (ANALYZE, BUFFERS) even a bit more. On the other hand, there is also the time for data transfer / network latency (which can easily be the lion's share, depending on your connection and query) and time your client uses to process and display results. You need to look closely what you measure exactly. Measurements you get from EXPLAIN ANALYZE only reflect the share of the RDBMS, not network or client. – Erwin Brandstetter Apr 17 '17 at 0:09
  • 1
    @audlift-bit: as said before the "total execution time" reported by your SQL client includes the time to transfer the data over the network. The time reported by explain (analyze) reports the time it took to run the query on the server without sending data to the client – a_horse_with_no_name Apr 17 '17 at 8:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.