Thread: 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

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

Thanks!