10

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
  • 175,982
  • 27
  • 439
  • 600
D-Rock
  • 255
  • 2
  • 7

1 Answers1

12

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,278
  • 13
  • 101
  • 147
Hans-Jürgen Schönig
  • 1,190
  • 6
  • 8
  • 1
    Your blog is a source of really valuable information. At the same time, please add the main points of your post there to this answer, so that it complies with the rules here. – András Váczi Oct 27 '14 at 14:56
  • the thing is: in postgresql a row has to go through a visibility check. on 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. btw, vacuum also sets bits. it is a similar issue. – Hans-Jürgen Schönig Oct 27 '14 at 15:15
  • I have a complaint. You certainly meant "hint bits" here. "Frozen" is something quite different in PostgreSQL, and tuples are never frozen during a SELECT. – Laurenz Albe Jan 13 '20 at 14:09