11

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?

1 Answer 1

13

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/.

3
  • 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. Commented Oct 27, 2014 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. Commented Oct 27, 2014 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. Commented Jan 13, 2020 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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