Subject: shared buffers

From: Greg Rychlewski <[email-masked]>
Date: Mon, 29 Mar 2021 11:27:21 -0400

Hi,

Will every page touched during a table or index scan, even if it's not
going to be used in the final result, be loaded into shared buffers?

i.e. if you need to evaluate a filter condition, will it load that page
into shared buffers and then evaluate it from there?

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 29 Mar 2021 19:09:02 +0200

On Mon, 2021-03-29 at 11:27 -0400, Greg Rychlewski wrote:
> Will every page touched during a table or index scan, even if it's
>  not going to be used in the final result, be loaded into shared buffers? 
> 

Even if a value does not appear in a query result, the page containing it
has to be read, if the value is used for calculating the query result.

All pages read are loaded into shared buffers.  So yes, they will be loaded.

Note that there is an optimization for big sequential scans:  if the table
scanned is bigger than a quarter of shared buffers, PostgreSQL will use a
small ring buffer to read the table.  This prevents a large sequential scan
from blowing out your cache, since it uses the same buffers to scan
the whole table.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

---

From: Greg Rychlewski <[email-masked]>
Date: Mon, 29 Mar 2021 14:04:29 -0400

Oh that's really interesting about the ring buffer. So if you're doing an
update/delete/insert that requires the ring buffer, does that mean the
backend itself will write to disk instead of the checkpoint process?

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 30 Mar 2021 13:14:55 +0200

> backend itself will write to disk instead of the

That is only used for reading.

All reading and writing is *always* done through shared buffers.

Yours,
Laurenz Albe