Thread: shared buffers
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?
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? > > i.e. if you need to evaluate a filter condition, will it load that page > into shared buffers and then evaluate it from there? 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
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?
On Mon, Mar 29, 2021 at 1:09 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
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?
>
> i.e. if you need to evaluate a filter condition, will it load that page
> into shared buffers and then evaluate it from there?
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
On Mon, 2021-03-29 at 14:04 -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? > > > > > > i.e. if you need to evaluate a filter condition, will it load that page > > > into shared buffers and then evaluate it from there? > > > > 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. > > 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? That is only used for reading. All reading and writing is *always* done through shared buffers. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com