pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Rowley <drowley@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Have the planner account for the Memoize cache key memory
Date: Mon, 20 Mar 2023 03:26:38 +0000
Message-ID: <E1pe6AQ-004Pr8-BY@gemulon.postgresql.org> (raw)

Have the planner account for the Memoize cache key memory

The Memoize executor node stores the cache key values along with the
tuple(s) which were found in the outer node which match each key value,
however, when the planner tried to estimate how many entries could be
stored in the cache, it didn't take into account that the cache key must
also be stored.  In many cases, this won't make a large difference as the
key is likely small in comparison to the tuple(s) being stored, however,
it's not impossible to craft cases where the key could take more memory
than the tuple(s) stored for it.

Here we adjust the planner so it takes into account the estimated amount
of memory to store the cache key.  Effectively, this change will reduce
the estimated cache hit ratio when it's thought that not all items will
fit in the cache, thus Memoize will become more expensive in such cases.

The executor already takes into account the memory consumed by the cache
key, so here we only need to adjust the planner.

Discussion: https://postgr.es/m/CAApHDvqGErGuyBfQvBQrTCHDbzLTqoiW=_G9sOzeFxWEc_7auA@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/785f7095767e148995d276fe52f6b1e1af76e726

Modified Files
--------------
src/backend/optimizer/path/costsize.c | 104 +++++++++++++++++++---------------
1 file changed, 58 insertions(+), 46 deletions(-)



Message-ID: <E1pe6AQ-004Pr8-BY@gemulon.postgresql.org>
Permalink:  ../E1pe6AQ-004Pr8-BY@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1pe6AQ-004Pr8-BY@gemulon.postgresql.org

 · 

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: pgsql-committers@postgresql.org
  Cc: drowley@postgresql.org, pgsql-committers@lists.postgresql.org
  Subject: Re: pgsql: Have the planner account for the Memoize cache key memory
  In-Reply-To: <E1pe6AQ-004Pr8-BY@gemulon.postgresql.org>

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

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