public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Tim Schwenke <[email protected]>
To: pgsql-novice <[email protected]>
Subject: Re: Does PostgreSQL cache all columns of a table after SELECT?
Date: Mon, 05 Jun 2023 15:58:38 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Mon, 2023-06-05 at 14:15 +0200, Tim Schwenke wrote:
> Hello,
> 
> I have the following table with the following columns:
> 
> large_a: text (few dozen characters up to a few hundred)
> large_b: text (few dozen characters up to a few hundred)
> 
> The table has several million rows. The DB is running on a large machine.
> 
> I perform the following query:
> 
> select large_a from table;
> 
> The first query takes a few minutes. Afterwards I see that the cache in memory has grown. Next query only takes a few seconds.
> 
> What I want to know:
> 
> Does the cache also contain large_b? Or is only large_a cached? Assumption is that memory is large enough to fit everything.

It depends.

If "large_a" and "large_b" are TOASTed, then they will only be cached if the column
is explicitly selected.

For shorter attributes, the whole row is in one piece, and all columns will get cached.

However, for sequential scans that are larger than a quarter of shared buffers,
PostgreSQL uses a ring buffer to avoid blowing out the cache.  In that case, most
data won't be cached for the next query.

Yours,
Laurenz Albe

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Does PostgreSQL cache all columns of a table after SELECT?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox