EXPLAIN BUFFERS: dirtied
Hi,
In EXPLAIN (ANALYZE, BUFFERS) for a SELECT query, I see the following
statistics under an Index Scan node:
Buffers: shared hit=8357288 read=6165444 dirtied=44820 written=5590
As far as I understand, that's the statistics for accesses to shared
buffers during the query:
- hit = required page was already in shared buffers
- read = required page was not in shared buffers, and was loaded from
disk (from filesystem cache)
- written = while loading the required page, there was no free space for
it in shared buffers, so some other dirty page was evicted from shared
buffers and was written to disk (to filesystem cache), to free some
space and to load the required page
But what is "dirtied" statistics? When a SELECT query could make pages
dirty?
Regards,
Vitaliy
Vitaliy Garnashevich <vgarnashevich@gmail.com> writes:
But what is "dirtied" statistics? When a SELECT query could make pages
dirty?
Setting hint bits on recently-committed rows.
regards, tom lane
I've read this article: https://wiki.postgresql.org/wiki/Hint_Bits
It says:
A plain SELECT, count(*), or VACUUM on the entire table will check
every tuple for visibility and set its hint bits.
Suppose, a new page was created using many INSERTs, and then was written
to disk during a checkpoint. There were no SELECTs or VACUUM on the page
or table yet. Will the following SELECT of one tuple from the page
update hint bits for ALL tuples on the page? Is that correct?
When a page is initially created and then is being written to disk
during a checkpoint, does checkpoint writer update the hint bits before
writing the page, or the following SELECT/VACUUM will have to do that
(possibly loading/updating/writing the page again)?
Regards,
Vitaliy
Show quoted text
On 2018-01-29 20:38, Tom Lane wrote:
Vitaliy Garnashevich <vgarnashevich@gmail.com> writes:
But what is "dirtied" statistics? When a SELECT query could make pages
dirty?Setting hint bits on recently-committed rows.
regards, tom lane
On 01/29/2018 08:21 PM, Vitaliy Garnashevich wrote:
I've read this article: https://wiki.postgresql.org/wiki/Hint_Bits
It says:
A plain SELECT, count(*), or VACUUM on the entire table will check
every tuple for visibility and set its hint bits.Suppose, a new page was created using many INSERTs, and then was written
to disk during a checkpoint. There were no SELECTs or VACUUM on the page
or table yet. Will the following SELECT of one tuple from the page
update hint bits for ALL tuples on the page? Is that correct?
Possibly, if there are no old transactions running.
When a page is initially created and then is being written to disk
during a checkpoint, does checkpoint writer update the hint bits before
writing the page, or the following SELECT/VACUUM will have to do that
(possibly loading/updating/writing the page again)?
Checkpoint only deals with 8kB chunks of data. Hint bits are not set on
a page, but on individual items (rows), so it's not up to the checkpoint
process to tweak that - that's up to queries accessing the data.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services