12

I am running EXPLAIN (ANALYZE, BUFFERS) SELECT ... in my Postgres 9.3 server. I end up seeing something like Buffers: shared hit=166416 dirtied=2 in the output.
From the documentation, "dirtied" indicates:

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.

This sounds to me like the process of marking a block dirty should only happen when updating data though. My query is a SELECT, however, and only reads data. I would imagine it would only report hits or reads. I am obviously mistaken. What exactly is happening in this situation, though?

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
D-Rock
  • 275
  • 2
  • 7

1 Answers1

14

This has a simple reason.

In PostgreSQL a row has to go through a visibility check. On the first read, the system checks if a row can be seen by everybody. If it is, it will be "frozen". This is where the writes come from. Similarly, VACUUM also sets bits.

There is a detailed explanation: http://www.cybertec.at/speeding-up-things-with-hint-bits/.

András Váczi
  • 31,778
  • 13
  • 102
  • 151