public inbox for [email protected]help / color / mirror / Atom feed
shared buffers 4+ messages / 2 participants [nested] [flat]
* shared buffers @ 2021-03-29 15:27 Greg Rychlewski <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Greg Rychlewski @ 2021-03-29 15:27 UTC (permalink / raw) To: pgsql-novice 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? ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: shared buffers @ 2021-03-29 17:09 Laurenz Albe <[email protected]> parent: Greg Rychlewski <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Laurenz Albe @ 2021-03-29 17:09 UTC (permalink / raw) To: Greg Rychlewski <[email protected]>; pgsql-novice 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: shared buffers @ 2021-03-29 18:04 Greg Rychlewski <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Greg Rychlewski @ 2021-03-29 18:04 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: pgsql-novice 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 <[email protected]> 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 > > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: shared buffers @ 2021-03-30 11:14 Laurenz Albe <[email protected]> parent: Greg Rychlewski <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Laurenz Albe @ 2021-03-30 11:14 UTC (permalink / raw) To: Greg Rychlewski <[email protected]>; +Cc: pgsql-novice 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-03-30 11:14 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-03-29 15:27 shared buffers Greg Rychlewski <[email protected]> 2021-03-29 17:09 ` Laurenz Albe <[email protected]> 2021-03-29 18:04 ` Greg Rychlewski <[email protected]> 2021-03-30 11:14 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox