Re: EXPLAIN BUFFERS: dirtied

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