Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.3\))
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost
 adjustment
From: Daniel Gustafsson <daniel@yesql.se>
In-Reply-To: <CAApHDvrBctjUFjY77BFd_MqPD+yf4nOZSKzbdzP70XntkknY8A@mail.gmail.com>
Date: Thu, 6 Jul 2023 09:56:18 +0200
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 David Rowley <dgrowleyml@gmail.com>
Content-Transfer-Encoding: quoted-printable
Message-Id: <51A15CD7-E31B-483D-B911-D0EB9F5FF952@yesql.se>
References: <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.com>
 <CAApHDvrBctjUFjY77BFd_MqPD+yf4nOZSKzbdzP70XntkknY8A@mail.gmail.com>
To: Lukas Fittl <lukas@fittl.com>
Archived-At: <https://www.postgresql.org/message-id/51A15CD7-E31B-483D-B911-D0EB9F5FF952%40yesql.se>
Precedence: bulk

> On 7 Mar 2023, at 10:51, David Rowley <dgrowleyml@gmail.com> wrote:
>=20
> On Sun, 5 Mar 2023 at 13:21, Lukas Fittl <lukas@fittl.com> wrote:
>> Alternatively (or in addition) we could consider showing the =
"ndistinct" value that is calculated in cost_memoize_rescan - since =
that's the most significant contributor to the cache hit ratio (and you =
can influence that directly by improving the ndistinct statistics).
>=20
> I think the ndistinct estimate plus the est_entries together would be
> useful. I think showing just the hit ratio number might often just
> raise too many questions about how that's calculated. To calculate the
> hit ratio we need to estimate the number of entries that can be kept
> in the cache at once and also the number of input rows and the number
> of distinct values.  We can see the input rows by looking at the outer
> side of the join in EXPLAIN, but we've no idea about the ndistinct or
> how many items the planner thought could be kept in the cache at once.
>=20
> The plan node already has est_entries, so it should just be a matter
> of storing the ndistinct estimate in the Path and putting it into the
> Plan node so the executor has access to it during EXPLAIN.

Lukas: do you have an updated patch for this commitfest to address =
David's
comments?

--
Daniel Gustafsson