8

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?

10

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

| improve this answer | |
  • 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. – dezso 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 at 14:09

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.