From: David Rowley <drowley@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Display Memoize planner estimates in EXPLAIN
Date: Tue, 29 Jul 2025 03:18:25 +0000
Message-ID: <E1ugarA-001F32-2M@gemulon.postgresql.org> (raw)
Display Memoize planner estimates in EXPLAIN
There've been a few complaints that it can be overly difficult to figure
out why the planner picked a Memoize plan. To help address that, here we
adjust the EXPLAIN output to display the following additional details:
1) The estimated number of cache entries that can be stored at once
2) The estimated number of unique lookup keys that we expect to see
3) The number of lookups we expect
4) The estimated hit ratio
Technically #4 can be calculated using #1, #2 and #3, but it's not a
particularly obvious calculation, so we opt to display it explicitly.
The original patch by Lukas Fittl only displayed the hit ratio, but
there was a fear that might lead to more questions about how that was
calculated. The idea with displaying all 4 is to be transparent which
may allow queries to be tuned more easily. For example, if #2 isn't
correct then maybe extended statistics or a manual n_distinct estimate can
be used to help fix poor plan choices.
Author: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Author: Lukas Fittl <lukas@fittl.com>
Reviewed-by: David Rowley <dgrowleyml@gmail.com>
Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Reviewed-by: Robert Haas <robertmhaas@gmail.com>
Discussion: https://postgr.es/m/CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g%40mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/4bc62b86849065939a6b85273fece6b92d6e97bf
Modified Files
--------------
src/backend/commands/explain.c | 21 ++++++++++++++++++++-
src/backend/optimizer/path/costsize.c | 18 ++++++++++++------
src/backend/optimizer/plan/createplan.c | 15 ++++++++++++---
src/backend/optimizer/util/pathnode.c | 11 ++++++++---
src/include/nodes/pathnodes.h | 4 +++-
src/include/nodes/plannodes.h | 10 ++++++++++
src/include/optimizer/pathnode.h | 2 +-
7 files changed, 66 insertions(+), 15 deletions(-)
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: Display Memoize planner estimates in EXPLAIN
In-Reply-To: <E1ugarA-001F32-2M@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