public inbox for [email protected]
help / color / mirror / Atom feedRe: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
49+ messages / 14 participants
[nested] [flat]
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-13 06:50 newtglobal postgresql_contributors <[email protected]>
1 sibling, 0 replies; 49+ messages in thread
From: newtglobal postgresql_contributors @ 2025-03-13 06:50 UTC (permalink / raw)
To: [email protected]; +Cc: Jian He <[email protected]>
The following review has been posted through the commitfest application:
make installcheck-world: not tested
Implements feature: not tested
Spec compliant: not tested
Documentation: not tested
Hi,
Tested the latest patch that allows direct `COPY` operations on Materialized Views, removing the need for `COPY (SELECT ...)`. This enhancement reduces query overhead, improving performance by **4–5%**.
Example:
Previous approach:
COPY (SELECT * FROM staff_summary) TO STDOUT WITH CSV HEADER;
Optimized approach:
COPY staff_summary TO STDOUT WITH CSV HEADER;
Performance tests were conducted using a Materialized View containing around 80,000 records, confirming that the new approach is faster and more efficient for exporting data.
Regards,
Newt Global PostgreSQL Contributors
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-26 19:04 Kirill Reshke <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: Kirill Reshke @ 2025-03-26 19:04 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Tue, 28 Jan 2025 at 07:49, jian he <[email protected]> wrote:
>
> On Mon, Jan 6, 2025 at 5:34 PM jian he <[email protected]> wrote:
> >
> > hi.
> >
> > about this issue,
> > last email in 2012 (https://postgr.es/m/[email protected])
> > """
> > Even if it happens to be trivial in the current patch, it's an added
> > functional requirement that we might later regret having cavalierly
> > signed up for. And, as noted upthread, relations that support only
> > one direction of COPY don't exist at the moment; that would be adding
> > an asymmetry that we might later regret, too.
> >
> > regards, tom lane
> > """
> >
> > but now we have numerous COPY options that work solely in a single
> > direction of COPY.
> > I think now we can make some kind of relation (pg_class.relkind) that
> > only works in one direction of COPY.
>
> hi.
> patch attached.
> also cc to Tom,
> since at that time, you are against the idea of ``COPY matview TO``.
Hi! With this patch it is possible to COPY matview TO, but not regular
view, which is surprising. Let's fix that?
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-29 06:46 jian he <[email protected]>
parent: Kirill Reshke <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: jian he @ 2025-03-29 06:46 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Thu, Mar 27, 2025 at 3:04 AM Kirill Reshke <[email protected]> wrote:
> >
> > hi.
> > patch attached.
> > also cc to Tom,
> > since at that time, you are against the idea of ``COPY matview TO``.
>
> Hi! With this patch it is possible to COPY matview TO, but not regular
> view, which is surprising. Let's fix that?
create view v1 as select 1;
copy v1 to stdout;
if you specifying table name, not query, then
{
cstate = BeginCopyTo(pstate, rel, query, relid,
stmt->filename, stmt->is_program,
NULL, stmt->attlist, stmt->options);
*processed = DoCopyTo(cstate); /* copy from database to file *
}
will use {table_beginscan, table_scan_getnextslot, table_endscan}
to output the data.
but views don't have storage, table_beginscan mechanism won't work.
so i don't think this is possible for view.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-29 12:38 Kirill Reshke <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: Kirill Reshke @ 2025-03-29 12:38 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Sat, 29 Mar 2025 at 09:47, jian he <[email protected]> wrote:
>
> will use {table_beginscan, table_scan_getnextslot, table_endscan}
> to output the data.
> but views don't have storage, table_beginscan mechanism won't work.
>
> so i don't think this is possible for view.
Well... So you are saying that let us have inconsistent features
because of how things are implemented in core... I don't sure I'm
buying that, but whatever, let's hear some other voices from the
community. My argument is that while we are working on it, perhaps we
should revise certain implementation specifics along the way. However,
this is merely my opinion on the matter.
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-29 14:40 David G. Johnston <[email protected]>
parent: Kirill Reshke <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: David G. Johnston @ 2025-03-29 14:40 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: jian he <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Saturday, March 29, 2025, Kirill Reshke <[email protected]> wrote:
> On Sat, 29 Mar 2025 at 09:47, jian he <[email protected]> wrote:
> >
> > will use {table_beginscan, table_scan_getnextslot, table_endscan}
> > to output the data.
> > but views don't have storage, table_beginscan mechanism won't work.
> >
> > so i don't think this is possible for view.
>
> Well... So you are saying that let us have inconsistent features
> because of how things are implemented in core... I don't sure I'm
> buying that, but whatever, let's hear some other voices from the
> community. My argument is that while we are working on it, perhaps we
> should revise certain implementation specifics along the way. However,
> this is merely my opinion on the matter.
>
At present copy {table} to only exists to support pg_dump. It is not
marketed as a general purpose export facility. While copy {relation} from
is a general purpose importer. Do we want to turn copy to into a general
purpose exporter and start accepting patches to export foreign tables, deal
with partitioned tables, views of both kinds? Given we already accept
things in copy from that copy to cannot produce the symmetry argument seems
flawed.
I’m on board with making copy {relation} to a general purpose export
facility and allowing for incremental implementations as people wish to
spend time developing them. Consistency should not prevent progress here.
On the topic of copy {matview} from, why not permit it? In particular,
with dump/restore we could dump the materialized view and restore it, which
seems like a win in terms of time spent restoring. That wouldn’t be this
patch.
David J.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-29 16:06 Andrew Dunstan <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: Andrew Dunstan @ 2025-03-29 16:06 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; Kirill Reshke <[email protected]>; +Cc: jian he <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On 2025-03-29 Sa 10:40 AM, David G. Johnston wrote:
> On Saturday, March 29, 2025, Kirill Reshke <[email protected]> wrote:
>
> On Sat, 29 Mar 2025 at 09:47, jian he
> <[email protected]> wrote:
> >
> > will use {table_beginscan, table_scan_getnextslot, table_endscan}
> > to output the data.
> > but views don't have storage, table_beginscan mechanism won't work.
> >
> > so i don't think this is possible for view.
>
> Well... So you are saying that let us have inconsistent features
> because of how things are implemented in core... I don't sure I'm
> buying that, but whatever, let's hear some other voices from the
> community. My argument is that while we are working on it, perhaps we
> should revise certain implementation specifics along the way. However,
> this is merely my opinion on the matter.
>
>
> At present copy {table} to only exists to support pg_dump. It is not
> marketed as a general purpose export facility.
*ahem*
What is your evidence for that proposition? If this were true we would
not support CSV mode, which pg_dump does not use. It might have
limitations, but its use goes far beyond just pg_dump, both in theory
and practice.
cheers
andew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-29 16:17 David G. Johnston <[email protected]>
parent: Andrew Dunstan <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: David G. Johnston @ 2025-03-29 16:17 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: Kirill Reshke <[email protected]>; jian he <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Sat, Mar 29, 2025 at 9:06 AM Andrew Dunstan <[email protected]> wrote:
>
> On 2025-03-29 Sa 10:40 AM, David G. Johnston wrote:
>
> On Saturday, March 29, 2025, Kirill Reshke <[email protected]> wrote:
>
>> On Sat, 29 Mar 2025 at 09:47, jian he <[email protected]>
>> wrote:
>> >
>> > will use {table_beginscan, table_scan_getnextslot, table_endscan}
>> > to output the data.
>> > but views don't have storage, table_beginscan mechanism won't work.
>> >
>> > so i don't think this is possible for view.
>>
>> Well... So you are saying that let us have inconsistent features
>> because of how things are implemented in core... I don't sure I'm
>> buying that, but whatever, let's hear some other voices from the
>> community. My argument is that while we are working on it, perhaps we
>> should revise certain implementation specifics along the way. However,
>> this is merely my opinion on the matter.
>>
>
> At present copy {table} to only exists to support pg_dump. It is not
> marketed as a general purpose export facility.
>
>
> *ahem*
>
>
> What is your evidence for that proposition? If this were true we would not
> support CSV mode, which pg_dump does not use. It might have limitations,
> but its use goes far beyond just pg_dump, both in theory and practice.
>
>
>
>
"copy {subquery} to" is a general-purpose exporter that makes use of those
additional features. Sure, they also work for the narrowed case of "copy
{relation/table} to" but I make my claim on the very fact that {relation}
cannot be stuff like foreign tables or partitioned tables, which pg_dump
has no need to target.
David J.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-29 18:56 Andrew Dunstan <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: Andrew Dunstan @ 2025-03-29 18:56 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Kirill Reshke <[email protected]>; jian he <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On 2025-03-29 Sa 12:17 PM, David G. Johnston wrote:
> On Sat, Mar 29, 2025 at 9:06 AM Andrew Dunstan <[email protected]>
> wrote:
>
>
> On 2025-03-29 Sa 10:40 AM, David G. Johnston wrote:
>> On Saturday, March 29, 2025, Kirill Reshke
>> <[email protected]> wrote:
>>
>> On Sat, 29 Mar 2025 at 09:47, jian he
>> <[email protected]> wrote:
>> >
>> > will use {table_beginscan, table_scan_getnextslot,
>> table_endscan}
>> > to output the data.
>> > but views don't have storage, table_beginscan mechanism
>> won't work.
>> >
>> > so i don't think this is possible for view.
>>
>> Well... So you are saying that let us have inconsistent features
>> because of how things are implemented in core... I don't sure I'm
>> buying that, but whatever, let's hear some other voices from the
>> community. My argument is that while we are working on it,
>> perhaps we
>> should revise certain implementation specifics along the way.
>> However,
>> this is merely my opinion on the matter.
>>
>>
>> At present copy {table} to only exists to support pg_dump. It
>> is not marketed as a general purpose export facility.
>
>
> *ahem*
>
>
> What is your evidence for that proposition? If this were true we
> would not support CSV mode, which pg_dump does not use. It might
> have limitations, but its use goes far beyond just pg_dump, both
> in theory and practice.
>
>
>
>
> "copy {subquery} to" is a general-purpose exporter that makes use of
> those additional features. Sure, they also work for the narrowed case
> of "copy {relation/table} to" but I make my claim on the very fact
> that {relation} cannot be stuff like foreign tables or partitioned
> tables, which pg_dump has no need to target.
>
>
I don't believe that the premise supports the conclusion.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-29 18:58 David G. Johnston <[email protected]>
parent: Andrew Dunstan <[email protected]>
0 siblings, 2 replies; 49+ messages in thread
From: David G. Johnston @ 2025-03-29 18:58 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: Kirill Reshke <[email protected]>; jian he <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Sat, Mar 29, 2025 at 11:56 AM Andrew Dunstan <[email protected]> wrote:
> I don't believe that the premise supports the conclusion.
>
>
> Regardless, I do support this patch and probably any similar ones proposed
in the future. Do you have an opinion on that?
David J.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-29 19:27 Kirill Reshke <[email protected]>
parent: David G. Johnston <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: Kirill Reshke @ 2025-03-29 19:27 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; jian he <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Sat, 29 Mar 2025 at 19:59, David G. Johnston
<[email protected]> wrote:
> Regardless, I do support this patch and probably any similar ones proposed in the future. Do you have an opinion on that?
>
> David J.
>
I do also support what this patch aims to do, how do you like v1?
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-29 19:49 David G. Johnston <[email protected]>
parent: Kirill Reshke <[email protected]>
0 siblings, 0 replies; 49+ messages in thread
From: David G. Johnston @ 2025-03-29 19:49 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; jian he <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Sat, Mar 29, 2025 at 12:27 PM Kirill Reshke <[email protected]>
wrote:
> On Sat, 29 Mar 2025 at 19:59, David G. Johnston
> <[email protected]> wrote:
> > Regardless, I do support this patch and probably any similar ones
> proposed in the future. Do you have an opinion on that?
> >
>
> I do also support what this patch aims to do, how do you like v1?
>
>
Seems reasonable, but I don't have enough experience with the codebase in
that area to submit a code review or be aware of any unexpected
side-effects or deficiencies.
David J.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-29 20:01 Andrew Dunstan <[email protected]>
parent: David G. Johnston <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: Andrew Dunstan @ 2025-03-29 20:01 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Kirill Reshke <[email protected]>; jian he <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On 2025-03-29 Sa 2:58 PM, David G. Johnston wrote:
> On Sat, Mar 29, 2025 at 11:56 AM Andrew Dunstan <[email protected]>
> wrote:
>
> I don't believe that the premise supports the conclusion.
>
>
> Regardless, I do support this patch and probably any similar ones
> proposed in the future. Do you have an opinion on that?
>
>
In principle I think it would be good to have COPY materialized_view TO ...
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-03-31 15:26 Fujii Masao <[email protected]>
parent: Andrew Dunstan <[email protected]>
0 siblings, 2 replies; 49+ messages in thread
From: Fujii Masao @ 2025-03-31 15:26 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; David G. Johnston <[email protected]>; +Cc: Kirill Reshke <[email protected]>; jian he <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On 2025/03/30 5:01, Andrew Dunstan wrote:
>
> On 2025-03-29 Sa 2:58 PM, David G. Johnston wrote:
>> On Sat, Mar 29, 2025 at 11:56 AM Andrew Dunstan <[email protected]> wrote:
>>
>> I don't believe that the premise supports the conclusion.
>>
>>
>> Regardless, I do support this patch and probably any similar ones proposed in the future. Do you have an opinion on that?
>>
>>
>
> In principle I think it would be good to have COPY materialized_view TO ...
I haven't found any reasons to object to this patch for now,
so I have no objections to this change.
Regarding the patch, here are some review comments:
+ errmsg("cannot copy from materialized view when the materialized view is not populated"),
How about including the object name for consistency with
other error messages in BeginCopyTo(), like this?
errmsg("cannot copy from unpopulated materialized view \"%s\"",
RelationGetRelationName(rel)),
+ errhint("Use the REFRESH MATERIALIZED VIEW command populate the materialized view first."));
There seems to be a missing "to" just after "command".
Should it be "Use the REFRESH MATERIALIZED VIEW command to
populate the materialized view first."? Or we could simplify
the hint to match what SELECT on an unpopulated materialized
view logs: "Use the REFRESH MATERIALIZED VIEW command.".
The copy.sgml documentation should clarify that COPY TO can
be used with a materialized view only if it is populated.
Wouldn't it be beneficial to add a regression test to check
whether COPY matview TO works as expected?
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-04-01 03:50 David G. Johnston <[email protected]>
parent: Fujii Masao <[email protected]>
1 sibling, 0 replies; 49+ messages in thread
From: David G. Johnston @ 2025-04-01 03:50 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Fujii Masao <[email protected]>; Andrew Dunstan <[email protected]>; Kirill Reshke <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Mon, Mar 31, 2025 at 8:13 PM jian he <[email protected]> wrote:
> On Mon, Mar 31, 2025 at 11:27 PM Fujii Masao
>
> >
> > The copy.sgml documentation should clarify that COPY TO can
> > be used with a materialized view only if it is populated.
> >
> "COPY TO can be used only with plain tables, not views, and does not
> copy rows from child tables or child partitions"
> i changed it to
> "COPY TO can be used with plain tables and materialized views, not
> regular views, and does not copy rows from child tables or child
> partitions"
>
> Another alternative wording I came up with:
> "COPY TO can only be used with plain tables and materialized views,
> not regular views. It also does not copy rows from child tables or
> child partitions."
>
>
Those don't address the "populated" aspect of the materialized view.
I'm unclear ATM (can check later if needed) if populated means "non-empty"
or simply "had refresh executed at least once on it". i.e., if the refresh
produces zero rows stored in the MV is it still populated? I'm guessing
yes; and this only pertains to "WITH NO DATA", which itself already calls
out that "...and cannot be queried until RMV is used". I find it of
marginal usefulness to bring that distinction over to COPY TO absent people
showing up confused about the error message, which likely will be quite
rare. That said I'd probably settle with:
COPY TO can only be used with plain tables and populated
materialized views. It does not copy rows from child tables
or child partitions (i.e., copy table to copies the same rows as
select * from only table). The syntax COPY (select * from table) TO ...
can be used to dump all of the rows in an inheritance hierarchy,
partitioned table, or foreign table; as well as ordinary view results.
Curious about sequences; no way to name an index here.
I'm second-guessing why "composite type" shows up in the glossary under
"Relation"...though that originally came up IIRC discussing namespaces.
David J.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-04-01 09:45 vignesh C <[email protected]>
parent: Fujii Masao <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: vignesh C @ 2025-04-01 09:45 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Fujii Masao <[email protected]>; Andrew Dunstan <[email protected]>; David G. Johnston <[email protected]>; Kirill Reshke <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Tue, 1 Apr 2025 at 08:43, jian he <[email protected]> wrote:
>
> On Mon, Mar 31, 2025 at 11:27 PM Fujii Masao
> <[email protected]> wrote:
> >
> > Regarding the patch, here are some review comments:
> >
> > + errmsg("cannot copy from materialized view when the materialized view is not populated"),
> >
> > How about including the object name for consistency with
> > other error messages in BeginCopyTo(), like this?
> >
> > errmsg("cannot copy from unpopulated materialized view \"%s\"",
> > RelationGetRelationName(rel)),
> >
> >
> > + errhint("Use the REFRESH MATERIALIZED VIEW command populate the materialized view first."));
> >
> > There seems to be a missing "to" just after "command".
> > Should it be "Use the REFRESH MATERIALIZED VIEW command to
> > populate the materialized view first."? Or we could simplify
> > the hint to match what SELECT on an unpopulated materialized
> > view logs: "Use the REFRESH MATERIALIZED VIEW command.".
> >
> based on your suggestion, i changed it to:
>
> if (!RelationIsPopulated(rel))
> ereport(ERROR,
> errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> errmsg("cannot copy from unpopulated
> materialized view \"%s\"",
> RelationGetRelationName(rel)),
> errhint("Use the REFRESH MATERIALIZED VIEW
> command to populate the materialized view first."));
>
>
> >
> > The copy.sgml documentation should clarify that COPY TO can
> > be used with a materialized view only if it is populated.
> >
> "COPY TO can be used only with plain tables, not views, and does not
> copy rows from child tables or child partitions"
> i changed it to
> "COPY TO can be used with plain tables and materialized views, not
> regular views, and does not copy rows from child tables or child
> partitions"
>
> Another alternative wording I came up with:
> "COPY TO can only be used with plain tables and materialized views,
> not regular views. It also does not copy rows from child tables or
> child partitions."
One thing I noticed was that if the materialized view is not refreshed
user will get stale data:
postgres=# create table t1(c1 int);
CREATE TABLE
postgres=# create materialized view mv2 as select * from t1;
SELECT 0
postgres=# insert into t1 values(10);
INSERT 0 1
postgres=# select * from t1;
c1
----
10
(1 row)
-- Before refresh the data will not be selected
postgres=# copy mv2 to stdout with (header);
c1
-- After refresh the data will be available
postgres=# refresh materialized view mv2;
REFRESH MATERIALIZED VIEW
postgres=# copy mv2 to stdout with (header);
c1
10
Should we document this?
The following can be changed to keep it consistent:
+copy matview1(id) TO stdout with (header);
+copy matview2 TO stdout with (header);
To:
COPY matview1(id) TO stdout with (header);
COPY matview2 TO stdout with (header);
Regards,
Vignesh
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-04-01 10:18 Kirill Reshke <[email protected]>
parent: vignesh C <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: Kirill Reshke @ 2025-04-01 10:18 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: jian he <[email protected]>; Fujii Masao <[email protected]>; Andrew Dunstan <[email protected]>; David G. Johnston <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Tue, 1 Apr 2025, 11:45 vignesh C, <[email protected]> wrote:
>
> One thing I noticed was that if the materialized view is not refreshed
> user will get stale data
>
> Should we document this?
>
Does this patch alter thus behaviour? User will get stale data even on
HEAD, why should we take a care within this thread?
>
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-04-01 13:52 vignesh C <[email protected]>
parent: Kirill Reshke <[email protected]>
0 siblings, 2 replies; 49+ messages in thread
From: vignesh C @ 2025-04-01 13:52 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: jian he <[email protected]>; Fujii Masao <[email protected]>; Andrew Dunstan <[email protected]>; David G. Johnston <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Tue, 1 Apr 2025 at 15:49, Kirill Reshke <[email protected]> wrote:
>
> On Tue, 1 Apr 2025, 11:45 vignesh C, <[email protected]> wrote:
>>
>>
>> One thing I noticed was that if the materialized view is not refreshed
>> user will get stale data
>>
>> Should we document this?
>
> Does this patch alter thus behaviour? User will get stale data even on HEAD, why should we take a care within this thread?
We are not changing the existing behavior. However, since copying data
from large tables can take a significant amount of time, would it be
helpful to add a cautionary note advising users to refresh the
materialized view before running copy command to avoid stale data?
This could prevent users from realizing the issue only after running
the copy operation, which would then require them to run it again. If
you think this is already obvious, then the note may not be necessary.
Regards,
Vignesh
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-04-01 14:14 Kirill Reshke <[email protected]>
parent: vignesh C <[email protected]>
1 sibling, 0 replies; 49+ messages in thread
From: Kirill Reshke @ 2025-04-01 14:14 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: jian he <[email protected]>; Fujii Masao <[email protected]>; Andrew Dunstan <[email protected]>; David G. Johnston <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Tue, 1 Apr 2025 at 15:52, vignesh C <[email protected]> wrote:
>
> On Tue, 1 Apr 2025 at 15:49, Kirill Reshke <[email protected]> wrote:
> >
> > On Tue, 1 Apr 2025, 11:45 vignesh C, <[email protected]> wrote:
> >>
> >>
> >> One thing I noticed was that if the materialized view is not refreshed
> >> user will get stale data
> >>
> >> Should we document this?
> >
> > Does this patch alter thus behaviour? User will get stale data even on HEAD, why should we take a care within this thread?
>
> We are not changing the existing behavior. However, since copying data
> from large tables can take a significant amount of time, would it be
> helpful to add a cautionary note advising users to refresh the
> materialized view before running copy command to avoid stale data?
> This could prevent users from realizing the issue only after running
> the copy operation, which would then require them to run it again.
Yes, agree, +1 on that.
> If
> you think this is already obvious, then the note may not be necessary.
I don't think this is already obvious, but my objection is that we
should maybe discuss this as a separate issue (in a separate patch).
Looks like fixing this together with code commit is too much at once.
I prefer a one-commit-for-one-purpose style.
> Regards,
> Vignesh
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
@ 2025-04-01 14:27 David G. Johnston <[email protected]>
parent: vignesh C <[email protected]>
1 sibling, 0 replies; 49+ messages in thread
From: David G. Johnston @ 2025-04-01 14:27 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Kirill Reshke <[email protected]>; jian he <[email protected]>; Fujii Masao <[email protected]>; Andrew Dunstan <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]>
On Tue, Apr 1, 2025 at 6:52 AM vignesh C <[email protected]> wrote:
>
> We are not changing the existing behavior. However, since copying data
> from large tables can take a significant amount of time, would it be
> helpful to add a cautionary note advising users to refresh the
> materialized view before running copy command to avoid stale data?
>
>
No, for the same reason the caveat about WITH NO DATA need not be mentioned
either. These are the inherent properties/trade-offs of using a
materialized view versus a normal view. They are discussed when talking
about the creation and usage of materialized views specifically. Features
that interact with materialized views consistent with these two documented
properties do not need to point out that fact. i.e., the existing
non-documenting of those two points in SELECT is appropriate, and should be
emulated in COPY. The output doesn't change if you write "copy table"
instead of "copy (select * from table)" so nothing needs to be pointed out.
David J.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-01 21:06 David Rowley <[email protected]>
0 siblings, 2 replies; 49+ messages in thread
From: David Rowley @ 2025-04-01 21:06 UTC (permalink / raw)
To: Ilia Evdokimov <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On Tue, 1 Apr 2025 at 20:52, Ilia Evdokimov
<[email protected]> wrote:
> With the feature freeze coming up soon, I’d like to ask: do we plan to
> include this patch in v18?
I don't think there's a clear enough consensus yet on what EXPLAIN
should display. We'd need that beforehand. There are now less than 7
days to get that, so it might be unrealistic.
I tried to move things along to address Tom's concern about not
wanting to show this in EXPLAIN's standard output. I suggested in [1]
that we could use EXPLAIN ANALYZE, but nobody commented on that.
EXPLAIN ANALYZE is much more verbose than EXPLAIN already, and if we
put these in EXPLAIN ANALYZE then the viewer can more easily compare
planned vs actual. I had mentioned that Hash Join does something like
this for buckets.
David
[1] https://postgr.es/m/CAApHDvqPkWmz1Lq23c9CD+mAW=hgPrD289tC30f3H1f6Ng59+g@mail.gmail.com
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-02 09:35 Ilia Evdokimov <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: Ilia Evdokimov @ 2025-04-02 09:35 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On 02.04.2025 00:06, David Rowley wrote:
> I tried to move things along to address Tom's concern about not
> wanting to show this in EXPLAIN's standard output. I suggested in [1]
> that we could use EXPLAIN ANALYZE, but nobody commented on that.
> EXPLAIN ANALYZE is much more verbose than EXPLAIN already, and if we
> put these in EXPLAIN ANALYZE then the viewer can more easily compare
> planned vs actual. I had mentioned that Hash Join does something like
> this for buckets.
>
> David
>
> [1]https://postgr.es/m/CAApHDvqPkWmz1Lq23c9CD+mAW=hgPrD289tC30f3H1f6Ng59+g@mail.gmail.com
Apologies for not addressing your earlier suggestion properly. After
reconsidering, I agree that showing ndistinct and est_entries in EXPLAIN
ANALYZE when there are actual cache misses is the best approach. This is
typically when users notice performance regressions and start
investigating the cause, so surfacing planner expectations at that point
can be the most useful. I attached v6 patch with changes.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
Attachments:
[text/x-patch] v6-0001-Display-estimated-distinct-keys-and-cache-capacity.patch (5.7K, ../../[email protected]/3-v6-0001-Display-estimated-distinct-keys-and-cache-capacity.patch)
download | inline diff:
From 36ed866e3d28a709fd1133a89ecdc7a2654a657f Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email protected]>
Date: Wed, 2 Apr 2025 10:49:27 +0300
Subject: [PATCH v6] Display estimated distinct keys and cache capacity in
EXPLAIN ANALYZE for Memoize when cache misses occur
---
src/backend/commands/explain.c | 13 +++++++++++++
src/backend/optimizer/path/costsize.c | 3 +++
src/backend/optimizer/plan/createplan.c | 10 +++++++---
src/backend/optimizer/util/pathnode.c | 3 +++
src/include/nodes/pathnodes.h | 2 ++
src/include/nodes/plannodes.h | 6 ++++++
6 files changed, 34 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index ef8aa489af8..f21de58e0a0 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3635,6 +3635,19 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
if (mstate->stats.cache_misses > 0)
{
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str, "Estimated Capacity: %u Estimated Distinct Lookup Keys: %0.0f\n",
+ ((Memoize *) plan)->est_entries,
+ ((Memoize *) plan)->est_unique_keys);
+ }
+ else
+ {
+ ExplainPropertyUInteger("Estimated Capacity", "", ((Memoize *) plan)->est_entries, es);
+ ExplainPropertyFloat("Estimated Distinct Lookup Keys", "", ((Memoize *) plan)->est_unique_keys, 0, es);
+ }
+
/*
* mem_peak is only set when we freed memory, so we must use mem_used
* when mem_peak is 0.
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f6f77b8fe19..e3abf9ccc26 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2604,6 +2604,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
mpath->est_entries = Min(Min(ndistinct, est_cache_entries),
PG_UINT32_MAX);
+ /* Remember ndistinct for a potential EXPLAIN later */
+ mpath->est_unique_keys = ndistinct;
+
/*
* When the number of distinct parameter values is above the amount we can
* store in the cache, then we'll have to evict some entries from the
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 75e2b0b9036..38882501484 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -284,7 +284,8 @@ static Material *make_material(Plan *lefttree);
static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids);
+ uint32 est_entries, double est_unique_keys,
+ Bitmapset *keyparamids);
static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1703,7 +1704,8 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
plan = make_memoize(subplan, operators, collations, param_exprs,
best_path->singlerow, best_path->binary_mode,
- best_path->est_entries, keyparamids);
+ best_path->est_entries, best_path->est_unique_keys,
+ keyparamids);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6636,7 +6638,8 @@ materialize_finished_plan(Plan *subplan)
static Memoize *
make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
List *param_exprs, bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids)
+ uint32 est_entries, double est_unique_keys,
+ Bitmapset *keyparamids)
{
Memoize *node = makeNode(Memoize);
Plan *plan = &node->plan;
@@ -6654,6 +6657,7 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
node->binary_mode = binary_mode;
node->est_entries = est_entries;
node->keyparamids = keyparamids;
+ node->est_unique_keys = est_unique_keys;
return node;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 93e73cb44db..1fbcda99067 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1701,6 +1701,9 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
Assert(enable_memoize);
pathnode->path.disabled_nodes = subpath->disabled_nodes;
+ /* Estimated number of distinct memoization keys, computed using estimate_num_groups() */
+ pathnode->est_unique_keys = 0;
+
/*
* Add a small additional charge for caching the first entry. All the
* harder calculations for rescans are performed in cost_memoize_rescan().
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index c24a1fc8514..0946ccea994 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2040,6 +2040,8 @@ typedef struct MemoizePath
uint32 est_entries; /* The maximum number of entries that the
* planner expects will fit in the cache, or 0
* if unknown */
+ double est_unique_keys; /* Estimated number of distinct memoization keys,
+ * used for cache size evaluation. Kept for EXPLAIN */
} MemoizePath;
/*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 658d76225e4..3d9d3a1159d 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1063,6 +1063,12 @@ typedef struct Memoize
/* paramids from param_exprs */
Bitmapset *keyparamids;
+
+ /*
+ * Estimated number of distinct memoization keys,
+ * used for cache size evaluation. Kept for EXPLAIN
+ */
+ double est_unique_keys;
} Memoize;
/* ----------------
--
2.34.1
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-14 09:02 Ilia Evdokimov <[email protected]>
parent: Ilia Evdokimov <[email protected]>
0 siblings, 0 replies; 49+ messages in thread
From: Ilia Evdokimov @ 2025-04-14 09:02 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
As there seems to be general support for the idea behind this patch, and
no one has raised strong objections recently, but also no clear
consensus on the exact final shape of the output has been reached yet -
and I understand other priorities might be taking precedence right now -
I’ve registered the patch in the upcoming CommitFest 2025-07 to ensure
it stays in the review process.
https://commitfest.postgresql.org/patch/5697/
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-14 16:31 Robert Haas <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 2 replies; 49+ messages in thread
From: Robert Haas @ 2025-04-14 16:31 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Ilia Evdokimov <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On Tue, Apr 1, 2025 at 5:07 PM David Rowley <[email protected]> wrote:
> I tried to move things along to address Tom's concern about not
> wanting to show this in EXPLAIN's standard output. I suggested in [1]
> that we could use EXPLAIN ANALYZE, but nobody commented on that.
> EXPLAIN ANALYZE is much more verbose than EXPLAIN already, and if we
> put these in EXPLAIN ANALYZE then the viewer can more easily compare
> planned vs actual. I had mentioned that Hash Join does something like
> this for buckets.
I don't think we should use ANALYZE for this because, IME, that should
just be about whether the query gets executed. Since this looks like
information that is available at plan time, I think it should be
displayed all the time or made contingent on VERBOSE. It would be sad
if you had to run the query to get information that was available
without needing to run the query. Personally, I think we can probably
just display it all the time. I mean, the typical plan is not going to
contain enough Memoize nodes that a little extra chatter for each one
impedes readability significantly.
As far as what to display, I have sympathy with Lukas's initial
complaint that it was hard to understand the costing of Memoize. I
haven't faced this exact problem, I think because Memoize isn't that
often used in plans I have seen, but I've faced a lot of similar
problems where you have to try to work backwards painfully to figure
out the chain of events that led to the value you're actually seeing,
and I'm +1 for efforts to make it more clear.
Having looked at v6, I think it would help, at least if the reader is
sufficiently knowledgeable. From the values displayed, it looks like
someone could reconstruct the evict_ratio value in
cost_memoize_rescan(), and if they know the loop count, also the
hit_ratio. But that seems hard: if you weren't reading the code, how
would you know how to do it? Even if you are reading the code, are you
sure you'd reconstruct it correctly? I wonder why we think it's better
to display this than a more "cooked" number like the estimated hit
ratio that was proposed in v1?
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-14 19:49 Andrei Lepikhov <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 0 replies; 49+ messages in thread
From: Andrei Lepikhov @ 2025-04-14 19:49 UTC (permalink / raw)
To: Robert Haas <[email protected]>; David Rowley <[email protected]>; +Cc: Ilia Evdokimov <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>
On 4/14/25 18:31, Robert Haas wrote:
> On Tue, Apr 1, 2025 at 5:07 PM David Rowley <[email protected]> wrote:
>> I tried to move things along to address Tom's concern about not
>> wanting to show this in EXPLAIN's standard output. I suggested in [1]
>> that we could use EXPLAIN ANALYZE, but nobody commented on that.
>> EXPLAIN ANALYZE is much more verbose than EXPLAIN already, and if we
>> put these in EXPLAIN ANALYZE then the viewer can more easily compare
>> planned vs actual. I had mentioned that Hash Join does something like
>> this for buckets.
> Having looked at v6, I think it would help, at least if the reader is
> sufficiently knowledgeable. From the values displayed, it looks like
> someone could reconstruct the evict_ratio value in
> cost_memoize_rescan(), and if they know the loop count, also the
> hit_ratio. But that seems hard: if you weren't reading the code, how
> would you know how to do it? Even if you are reading the code, are you
> sure you'd reconstruct it correctly? I wonder why we think it's better
> to display this than a more "cooked" number like the estimated hit
> ratio that was proposed in v1?
+1.
My reasons: a) it more "physical"; b) We don't need to look for the
corresponding NestLoop JOIN node - my explans usually contain 150+ lines
and it is hard to get into the analysis something out of current screen.
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-14 20:53 David Rowley <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: David Rowley @ 2025-04-14 20:53 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Ilia Evdokimov <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On Tue, 15 Apr 2025 at 04:31, Robert Haas <[email protected]> wrote:
> I don't think we should use ANALYZE for this because, IME, that should
> just be about whether the query gets executed. Since this looks like
> information that is available at plan time, I think it should be
> displayed all the time or made contingent on VERBOSE. It would be sad
> if you had to run the query to get information that was available
> without needing to run the query. Personally, I think we can probably
> just display it all the time. I mean, the typical plan is not going to
> contain enough Memoize nodes that a little extra chatter for each one
> impedes readability significantly.
You might be right there. I just offered that as a possible solution
to the concern of bloating the EXPLAIN output.
> Having looked at v6, I think it would help, at least if the reader is
> sufficiently knowledgeable. From the values displayed, it looks like
> someone could reconstruct the evict_ratio value in
> cost_memoize_rescan(), and if they know the loop count, also the
> hit_ratio. But that seems hard: if you weren't reading the code, how
> would you know how to do it? Even if you are reading the code, are you
> sure you'd reconstruct it correctly? I wonder why we think it's better
> to display this than a more "cooked" number like the estimated hit
> ratio that was proposed in v1?
I think the problem with only showing the estimated hit ratio is that
it's difficult to then know what you might change to improve that in
cases where you might have other join types disabled and are
experimenting to figure out why Nested Loop / Memoize isn't being
chosen. If you see the estimated capacity isn't big enough to store
all the rows for all distinct keys then you know you can increase
work_mem to assist. If the Estimated Unique Keys is too high, then you
might be able to adjust some ndistinct estimates or increase stats
targets somewhere to improve that. If we only showed the estimated
hit ratio, then you wouldn't know where to start to get the planner to
pick the Memoize plan.
As I mentioned in [1], I wouldn't object to having the estimated hit
ratio shown in addition to the components that are used to calculate
it. Per [2], Andrei didn't seem to like the idea due to the
duplication. FWIW, I do agree with you that the hit ratio isn't
exactly easy to calculate on the fly when looking at the input numbers
that are used to calculate it. I just wasn't 100% certain that it
would be needed in its calculated form. Maybe it depends on the reason
you're looking at EXPLAIN. Maybe the reason I mentioned above for
looking at EXPLAIN is just one of many and I'm not thinking hard
enough to consider the other ones.
If we can't get consensus for everything people want to add at once
then maybe the patch could be broken into two, with 0001 being pretty
much the v4 patch and then have 0002 add the Estimated Hit Ratio.
Having the physical patches and being able to try it out or view the
regression test changes might lower the bar on people chipping in with
their views.
David
[1] https://postgr.es/m/CAApHDvoE5S5FkvEq+N3-J9LfaVUpWLOnczOYAOEvBMCY20=pdg@mail.gmail.com
[2] https://postgr.es/m/[email protected]
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-14 23:14 Ilia Evdokimov <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: Ilia Evdokimov @ 2025-04-14 23:14 UTC (permalink / raw)
To: David Rowley <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On 14.04.2025 23:53, David Rowley wrote:
> If we can't get consensus for everything people want to add at once
> then maybe the patch could be broken into two, with 0001 being pretty
> much the v4 patch and then have 0002 add the Estimated Hit Ratio.
> Having the physical patches and being able to try it out or view the
> regression test changes might lower the bar on people chipping in with
> their views.
Agreed - I’ll split the patch into two: one adds Estimated Capacity
and Estimated Distinct Keys, and the second adds Estimated Hit Ratio.
I’ll also add regression test differences to make it easier to evaluate
the usefulness of each part.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
Attachments:
[text/x-patch] v7-0001-Show-ndistinct-and-est_entries-in-EXPLAIN-for-Mem.patch (32.8K, ../../[email protected]/3-v7-0001-Show-ndistinct-and-est_entries-in-EXPLAIN-for-Mem.patch)
download | inline diff:
From 99a23dfae4415a3eb88c3847deefae3c2103077d Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email protected]>
Date: Tue, 15 Apr 2025 00:47:26 +0300
Subject: [PATCH v7 1/2] Show ndistinct and est_entries in EXPLAIN for Memoize
---
src/backend/commands/explain.c | 13 ++++
src/backend/optimizer/path/costsize.c | 3 +
src/backend/optimizer/plan/createplan.c | 10 ++-
src/backend/optimizer/util/pathnode.c | 3 +
src/include/nodes/pathnodes.h | 2 +
src/include/nodes/plannodes.h | 6 ++
src/test/regress/expected/create_index.out | 3 +-
src/test/regress/expected/join.out | 64 +++++++++++---------
src/test/regress/expected/memoize.out | 54 +++++++++++------
src/test/regress/expected/partition_join.out | 18 ++++--
src/test/regress/expected/subselect.out | 10 +--
11 files changed, 126 insertions(+), 60 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 786ee865f14..0a4ce5ee7d7 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3628,6 +3628,19 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
ExplainPropertyText("Cache Key", keystr.data, es);
ExplainPropertyText("Cache Mode", mstate->binary_mode ? "binary" : "logical", es);
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str, "Estimated Capacity: %u Estimated Distinct Lookup Keys: %0.0f\n",
+ ((Memoize *) plan)->est_entries,
+ ((Memoize *) plan)->est_unique_keys);
+ }
+ else
+ {
+ ExplainPropertyUInteger("Estimated Capacity", "", ((Memoize *) plan)->est_entries, es);
+ ExplainPropertyFloat("Estimated Distinct Lookup Keys", "", ((Memoize *) plan)->est_unique_keys, 0, es);
+ }
+
pfree(keystr.data);
if (!es->analyze)
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 60b0fcfb6be..f72319d903c 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2604,6 +2604,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
mpath->est_entries = Min(Min(ndistinct, est_cache_entries),
PG_UINT32_MAX);
+ /* Remember ndistinct for a potential EXPLAIN later */
+ mpath->est_unique_keys = ndistinct;
+
/*
* When the number of distinct parameter values is above the amount we can
* store in the cache, then we'll have to evict some entries from the
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index a8f22a8c154..a1456c9014d 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -284,7 +284,8 @@ static Material *make_material(Plan *lefttree);
static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids);
+ uint32 est_entries, Bitmapset *keyparamids,
+ double est_unique_keys);
static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1703,7 +1704,8 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
plan = make_memoize(subplan, operators, collations, param_exprs,
best_path->singlerow, best_path->binary_mode,
- best_path->est_entries, keyparamids);
+ best_path->est_entries, keyparamids,
+ best_path->est_unique_keys);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6636,7 +6638,8 @@ materialize_finished_plan(Plan *subplan)
static Memoize *
make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
List *param_exprs, bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids)
+ uint32 est_entries, Bitmapset *keyparamids,
+ double est_unique_keys)
{
Memoize *node = makeNode(Memoize);
Plan *plan = &node->plan;
@@ -6654,6 +6657,7 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
node->binary_mode = binary_mode;
node->est_entries = est_entries;
node->keyparamids = keyparamids;
+ node->est_unique_keys = est_unique_keys;
return node;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 93e73cb44db..1fbcda99067 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1701,6 +1701,9 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
Assert(enable_memoize);
pathnode->path.disabled_nodes = subpath->disabled_nodes;
+ /* Estimated number of distinct memoization keys, computed using estimate_num_groups() */
+ pathnode->est_unique_keys = 0;
+
/*
* Add a small additional charge for caching the first entry. All the
* harder calculations for rescans are performed in cost_memoize_rescan().
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index bb678bdcdcd..07d97dc0b5b 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2138,6 +2138,8 @@ typedef struct MemoizePath
uint32 est_entries; /* The maximum number of entries that the
* planner expects will fit in the cache, or 0
* if unknown */
+ double est_unique_keys; /* Estimated number of distinct memoization keys,
+ * used for cache size evaluation. Kept for EXPLAIN */
} MemoizePath;
/*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 658d76225e4..3d9d3a1159d 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1063,6 +1063,12 @@ typedef struct Memoize
/* paramids from param_exprs */
Bitmapset *keyparamids;
+
+ /*
+ * Estimated number of distinct memoization keys,
+ * used for cache size evaluation. Kept for EXPLAIN
+ */
+ double est_unique_keys;
} Memoize;
/* ----------------
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 9ade7b835e6..826018a8a9f 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2245,10 +2245,11 @@ SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON
-> Memoize
Cache Key: tenk1.hundred
Cache Mode: logical
+ Estimated Capacity: 100 Estimated Distinct Lookup Keys: 100
-> Index Scan using tenk2_hundred on tenk2
Index Cond: (hundred = tenk1.hundred)
Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2))
-(10 rows)
+(11 rows)
--
-- Check behavior with duplicate index column contents
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 14da5708451..eca6ce2c6f0 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2729,8 +2729,8 @@ select * from onek t1
left join lateral
(select * from onek t3 where t3.two = t2.two offset 0) s
on t2.unique1 = 1;
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Nested Loop Left Join
-> Seq Scan on onek t1
-> Materialize
@@ -2740,9 +2740,10 @@ select * from onek t1
-> Memoize
Cache Key: t2.two
Cache Mode: binary
+ Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
-> Seq Scan on onek t3
Filter: (two = t2.two)
-(11 rows)
+(12 rows)
--
-- check a case where we formerly got confused by conflicting sort orders
@@ -5128,8 +5129,8 @@ select * from
on i8.q2 = 123,
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
where t1.f1 = ss.f1;
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Nested Loop
Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
Join Filter: (t1.f1 = t2.f1)
@@ -5146,11 +5147,12 @@ where t1.f1 = ss.f1;
Output: (i8.q1), t2.f1
Cache Key: i8.q1
Cache Mode: binary
+ Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
-> Limit
Output: (i8.q1), t2.f1
-> Seq Scan on public.text_tbl t2
Output: i8.q1, t2.f1
-(20 rows)
+(21 rows)
select * from
text_tbl t1
@@ -5171,8 +5173,8 @@ select * from
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1,
lateral (select ss1.* from text_tbl t3 limit 1) as ss2
where t1.f1 = ss2.f1;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Nested Loop
Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1, ((i8.q1)), (t2.f1)
Join Filter: (t1.f1 = (t2.f1))
@@ -5191,6 +5193,7 @@ where t1.f1 = ss2.f1;
Output: (i8.q1), t2.f1
Cache Key: i8.q1
Cache Mode: binary
+ Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
-> Limit
Output: (i8.q1), t2.f1
-> Seq Scan on public.text_tbl t2
@@ -5199,11 +5202,12 @@ where t1.f1 = ss2.f1;
Output: ((i8.q1)), (t2.f1)
Cache Key: (i8.q1), t2.f1
Cache Mode: binary
+ Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
-> Limit
Output: ((i8.q1)), (t2.f1)
-> Seq Scan on public.text_tbl t3
Output: (i8.q1), t2.f1
-(30 rows)
+(32 rows)
select * from
text_tbl t1
@@ -5225,8 +5229,8 @@ select 1 from
left join text_tbl as tt4 on (tt3.f1 = tt4.f1),
lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1
where tt1.f1 = ss1.c0;
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Nested Loop
Output: 1
-> Nested Loop Left Join
@@ -5252,6 +5256,7 @@ where tt1.f1 = ss1.c0;
Output: ss1.c0
Cache Key: tt4.f1
Cache Mode: binary
+ Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
-> Subquery Scan on ss1
Output: ss1.c0
Filter: (ss1.c0 = 'foo'::text)
@@ -5259,7 +5264,7 @@ where tt1.f1 = ss1.c0;
Output: (tt4.f1)
-> Seq Scan on public.text_tbl tt5
Output: tt4.f1
-(32 rows)
+(33 rows)
select 1 from
text_tbl as tt1
@@ -6462,17 +6467,18 @@ select * from sj t1
join lateral
(select * from sj tablesample system(t1.b)) s
on t1.a = s.a;
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Nested Loop
-> Seq Scan on sj t1
-> Memoize
Cache Key: t1.a, t1.b
Cache Mode: binary
+ Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
-> Sample Scan on sj
Sampling: system (t1.b)
Filter: (t1.a = a)
-(8 rows)
+(9 rows)
-- Ensure that SJE does not form a self-referential lateral dependency
explain (costs off)
@@ -7614,43 +7620,46 @@ select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a, lateral generate_series(1,two) g;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Aggregate
-> Nested Loop
-> Seq Scan on tenk1 a
-> Memoize
Cache Key: a.two
Cache Mode: binary
+ Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
-> Function Scan on generate_series g
-(7 rows)
+(8 rows)
explain (costs off)
select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Aggregate
-> Nested Loop
-> Seq Scan on tenk1 a
-> Memoize
Cache Key: a.two
Cache Mode: binary
+ Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
-> Function Scan on generate_series g
-(7 rows)
+(8 rows)
-- don't need the explicit LATERAL keyword for functions
explain (costs off)
select count(*) from tenk1 a, generate_series(1,two) g;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Aggregate
-> Nested Loop
-> Seq Scan on tenk1 a
-> Memoize
Cache Key: a.two
Cache Mode: binary
+ Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
-> Function Scan on generate_series g
-(7 rows)
+(8 rows)
-- lateral with UNION ALL subselect
explain (costs off)
@@ -7702,8 +7711,8 @@ select count(*) from tenk1 a,
explain (costs off)
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
- QUERY PLAN
-------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Aggregate
-> Nested Loop
-> Nested Loop
@@ -7712,9 +7721,10 @@ explain (costs off)
-> Memoize
Cache Key: "*VALUES*".column1
Cache Mode: logical
+ Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
-> Index Only Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 = "*VALUES*".column1)
-(10 rows)
+(11 rows)
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 38dfaf021c9..39c76aaa1a4 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -46,12 +46,13 @@ WHERE t2.unique1 < 1000;', false);
-> Memoize (actual rows=1.00 loops=N)
Cache Key: t2.twenty
Cache Mode: logical
+ Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N)
Index Cond: (unique1 = t2.twenty)
Heap Fetches: N
Index Searches: N
-(13 rows)
+(14 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
@@ -78,12 +79,13 @@ WHERE t1.unique1 < 1000;', false);
-> Memoize (actual rows=1.00 loops=N)
Cache Key: t1.twenty
Cache Mode: binary
+ Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1.00 loops=N)
Index Cond: (unique1 = t1.twenty)
Heap Fetches: N
Index Searches: N
-(13 rows)
+(14 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
@@ -113,6 +115,7 @@ WHERE t1.unique1 < 10;', false);
-> Memoize (actual rows=2.00 loops=N)
Cache Key: t1.two
Cache Mode: binary
+ Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
Hits: 8 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Subquery Scan on t2 (actual rows=2.00 loops=N)
Filter: (t1.two = t2.two)
@@ -120,7 +123,7 @@ WHERE t1.unique1 < 10;', false);
-> Index Scan using tenk1_unique1 on tenk1 t2_1 (actual rows=4.00 loops=N)
Index Cond: (unique1 < 4)
Index Searches: N
-(15 rows)
+(16 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t2.t1two) FROM tenk1 t1 LEFT JOIN
@@ -149,13 +152,14 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
-> Memoize (actual rows=1.00 loops=N)
Cache Key: (t1.two + 1)
Cache Mode: binary
+ Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
Hits: 998 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1.00 loops=N)
Filter: ((t1.two + 1) = unique1)
Rows Removed by Filter: 9999
Heap Fetches: N
Index Searches: N
-(14 rows)
+(15 rows)
-- And check we get the expected results.
SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
@@ -182,11 +186,12 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
-> Memoize (actual rows=1.00 loops=N)
Cache Key: t1.two, t1.twenty
Cache Mode: binary
+ Estimated Capacity: 40 Estimated Distinct Lookup Keys: 40
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Seq Scan on tenk1 t2 (actual rows=1.00 loops=N)
Filter: ((t1.twenty = unique1) AND (t1.two = two))
Rows Removed by Filter: 9999
-(12 rows)
+(13 rows)
-- And check we get the expected results.
SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
@@ -220,13 +225,14 @@ ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false);
-> Memoize (actual rows=2.00 loops=N)
Cache Key: t1.x, (t1.t)::numeric
Cache Mode: logical
+ Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20
Hits: 20 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using expr_key_idx_x_t on expr_key t2 (actual rows=2.00 loops=N)
Index Cond: (x = (t1.t)::numeric)
Filter: (t1.x = (t)::numeric)
Heap Fetches: N
Index Searches: N
-(11 rows)
+(12 rows)
DROP TABLE expr_key;
-- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions
@@ -249,12 +255,13 @@ WHERE t2.unique1 < 1200;', true);
-> Memoize (actual rows=1.00 loops=N)
Cache Key: t2.thousand
Cache Mode: logical
+ Estimated Capacity: 655 Estimated Distinct Lookup Keys: 721
Hits: N Misses: N Evictions: N Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N)
Index Cond: (unique1 = t2.thousand)
Heap Fetches: N
Index Searches: N
-(13 rows)
+(14 rows)
CREATE TABLE flt (f float);
CREATE INDEX flt_f_idx ON flt (f);
@@ -273,12 +280,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false);
-> Memoize (actual rows=2.00 loops=N)
Cache Key: f1.f
Cache Mode: logical
+ Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
Hits: 1 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2.00 loops=N)
Index Cond: (f = f1.f)
Heap Fetches: N
Index Searches: N
-(12 rows)
+(13 rows)
-- Ensure memoize operates in binary mode
SELECT explain_memoize('
@@ -292,12 +300,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false);
-> Memoize (actual rows=2.00 loops=N)
Cache Key: f1.f
Cache Mode: binary
+ Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
Hits: 0 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2.00 loops=N)
Index Cond: (f <= f1.f)
Heap Fetches: N
Index Searches: N
-(12 rows)
+(13 rows)
DROP TABLE flt;
-- Exercise Memoize in binary mode with a large fixed width type and a
@@ -320,11 +329,12 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false);
-> Memoize (actual rows=4.00 loops=N)
Cache Key: s1.n
Cache Mode: binary
+ Estimated Capacity: 3 Estimated Distinct Lookup Keys: 3
Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Scan using strtest_n_idx on strtest s2 (actual rows=4.00 loops=N)
Index Cond: (n <= s1.n)
Index Searches: N
-(10 rows)
+(11 rows)
-- Ensure we get 3 hits and 3 misses
SELECT explain_memoize('
@@ -337,11 +347,12 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
-> Memoize (actual rows=4.00 loops=N)
Cache Key: s1.t
Cache Mode: binary
+ Estimated Capacity: 4 Estimated Distinct Lookup Keys: 4
Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Scan using strtest_t_idx on strtest s2 (actual rows=4.00 loops=N)
Index Cond: (t <= s1.t)
Index Searches: N
-(10 rows)
+(11 rows)
DROP TABLE strtest;
-- Ensure memoize works with partitionwise join
@@ -366,6 +377,7 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
-> Memoize (actual rows=4.00 loops=N)
Cache Key: t1_1.a
Cache Mode: logical
+ Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using iprt_p1_a on prt_p1 t2_1 (actual rows=4.00 loops=N)
Index Cond: (a = t1_1.a)
@@ -378,12 +390,13 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
-> Memoize (actual rows=4.00 loops=N)
Cache Key: t1_2.a
Cache Mode: logical
+ Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using iprt_p2_a on prt_p2 t2_2 (actual rows=4.00 loops=N)
Index Cond: (a = t1_2.a)
Heap Fetches: N
Index Searches: N
-(25 rows)
+(27 rows)
-- Ensure memoize works with parameterized union-all Append path
SET enable_partitionwise_join TO off;
@@ -400,6 +413,7 @@ ON t1.a = t2.a;', false);
-> Memoize (actual rows=4.00 loops=N)
Cache Key: t1.a
Cache Mode: logical
+ Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Append (actual rows=4.00 loops=N)
-> Index Only Scan using iprt_p1_a on prt_p1 (actual rows=4.00 loops=N)
@@ -410,7 +424,7 @@ ON t1.a = t2.a;', false);
Index Cond: (a = t1.a)
Heap Fetches: N
Index Searches: N
-(17 rows)
+(18 rows)
DROP TABLE prt;
RESET enable_partitionwise_join;
@@ -424,8 +438,8 @@ WHERE unique1 < 3
SELECT 1 FROM tenk1 t1
INNER JOIN tenk1 t2 ON t1.unique1 = t2.hundred
WHERE t0.ten = t1.twenty AND t0.two <> t2.four OFFSET 0);
- QUERY PLAN
-----------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 t0
Index Cond: (unique1 < 3)
Filter: EXISTS(SubPlan 1)
@@ -436,10 +450,11 @@ WHERE unique1 < 3
-> Memoize
Cache Key: t2.hundred
Cache Mode: logical
+ Estimated Capacity: 100 Estimated Distinct Lookup Keys: 100
-> Index Scan using tenk1_unique1 on tenk1 t1
Index Cond: (unique1 = t2.hundred)
Filter: (t0.ten = twenty)
-(13 rows)
+(14 rows)
-- Ensure the above query returns the correct result
SELECT unique1 FROM tenk1 t0
@@ -469,8 +484,8 @@ EXPLAIN (COSTS OFF)
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2
WHERE t1.unique1 < 1000;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
@@ -483,9 +498,10 @@ WHERE t1.unique1 < 1000;
-> Memoize
Cache Key: t1.twenty
Cache Mode: logical
+ Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20
-> Index Only Scan using tenk1_unique1 on tenk1 t2
Index Cond: (unique1 = t1.twenty)
-(14 rows)
+(15 rows)
-- And ensure the parallel plan gives us the correct results.
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 6101c8c7cf1..d3c37ffe22b 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -5289,8 +5289,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1;
-- Increase number of tuples requested and an IndexScan will be chosen
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Limit
-> Append
-> Nested Loop
@@ -5298,6 +5298,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-> Memoize
Cache Key: p1_1.c
Cache Mode: logical
+ Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
-> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
Index Cond: (c = p1_1.c)
-> Nested Loop
@@ -5305,6 +5306,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-> Memoize
Cache Key: p1_2.c
Cache Mode: logical
+ Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
-> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
Index Cond: (c = p1_2.c)
-> Nested Loop
@@ -5312,9 +5314,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-> Memoize
Cache Key: p1_3.c
Cache Mode: logical
+ Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
-> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
Index Cond: (c = p1_3.c)
-(23 rows)
+(26 rows)
-- If almost all the data should be fetched - prefer SeqScan
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1000;
@@ -5343,8 +5346,8 @@ SET max_parallel_workers_per_gather = 1;
SET debug_parallel_query = on;
-- Partial paths should also be smart enough to employ limits
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Gather
Workers Planned: 1
Single Copy: true
@@ -5355,6 +5358,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-> Memoize
Cache Key: p1_1.c
Cache Mode: logical
+ Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
-> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
Index Cond: (c = p1_1.c)
-> Nested Loop
@@ -5362,6 +5366,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-> Memoize
Cache Key: p1_2.c
Cache Mode: logical
+ Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
-> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
Index Cond: (c = p1_2.c)
-> Nested Loop
@@ -5369,9 +5374,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-> Memoize
Cache Key: p1_3.c
Cache Mode: logical
+ Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
-> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
Index Cond: (c = p1_3.c)
-(26 rows)
+(29 rows)
RESET debug_parallel_query;
-- Remove indexes from the partitioned table and its partitions
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 40d8056fcea..f5575c7d332 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1316,8 +1316,8 @@ select sum(o.four), sum(ss.a) from
select * from x
) ss
where o.ten = 1;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Aggregate
-> Nested Loop
-> Seq Scan on onek o
@@ -1325,13 +1325,14 @@ where o.ten = 1;
-> Memoize
Cache Key: o.four
Cache Mode: binary
+ Estimated Capacity: 4 Estimated Distinct Lookup Keys: 4
-> CTE Scan on x
CTE x
-> Recursive Union
-> Result
-> WorkTable Scan on x x_1
Filter: (a < 10)
-(13 rows)
+(14 rows)
select sum(o.four), sum(ss.a) from
onek o cross join lateral (
@@ -2642,6 +2643,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
-> Memoize
Cache Key: b.hundred, b.odd
Cache Mode: binary
+ Estimated Capacity: 1000 Estimated Distinct Lookup Keys: 1000
-> Subquery Scan on "ANY_subquery"
Filter: (b.hundred = "ANY_subquery".min)
-> Result
@@ -2650,7 +2652,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
-> Index Scan using tenk2_hundred on tenk2 c
Index Cond: (hundred IS NOT NULL)
Filter: (odd = b.odd)
-(16 rows)
+(17 rows)
--
-- Test VALUES to ARRAY (VtA) transformation
--
2.34.1
[text/x-patch] v7-0002-Add-Estimated-Hit-Ratio-for-Memoize-plan-nodes-in.patch (26.1K, ../../[email protected]/4-v7-0002-Add-Estimated-Hit-Ratio-for-Memoize-plan-nodes-in.patch)
download | inline diff:
From b2dc58e271075909d5136b09259f2f6b83d4d112 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email protected]>
Date: Tue, 15 Apr 2025 01:53:27 +0300
Subject: [PATCH v7 2/2] Add Estimated Hit Ratio for Memoize plan nodes in
EXPLAIN
---
src/backend/commands/explain.c | 4 +-
src/backend/optimizer/path/costsize.c | 3 ++
src/backend/optimizer/plan/createplan.c | 7 +--
src/backend/optimizer/util/pathnode.c | 5 +++
src/include/nodes/pathnodes.h | 1 +
src/include/nodes/plannodes.h | 3 ++
src/test/regress/expected/create_index.out | 3 +-
src/test/regress/expected/join.out | 28 ++++++++----
src/test/regress/expected/memoize.out | 46 +++++++++++++-------
src/test/regress/expected/partition_join.out | 10 ++++-
src/test/regress/expected/subselect.out | 6 ++-
11 files changed, 83 insertions(+), 33 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 0a4ce5ee7d7..d8526b7d06d 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3634,11 +3634,13 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
appendStringInfo(es->str, "Estimated Capacity: %u Estimated Distinct Lookup Keys: %0.0f\n",
((Memoize *) plan)->est_entries,
((Memoize *) plan)->est_unique_keys);
- }
+ ExplainIndentText(es);
+ appendStringInfo(es->str, "Estimated Hit Ratio: %0.2f\n", ((Memoize *) plan)->hit_ratio); }
else
{
ExplainPropertyUInteger("Estimated Capacity", "", ((Memoize *) plan)->est_entries, es);
ExplainPropertyFloat("Estimated Distinct Lookup Keys", "", ((Memoize *) plan)->est_unique_keys, 0, es);
+ ExplainPropertyFloat("Estimated Hit Ratio", "", ((Memoize *) plan)->hit_ratio, 2, es);
}
pfree(keystr.data);
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f72319d903c..3e99214501b 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2624,6 +2624,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
hit_ratio = ((calls - ndistinct) / calls) *
(est_cache_entries / Max(ndistinct, est_cache_entries));
+ /* Remember cache hit ratio for a potential EXPLAIN later */
+ mpath->hit_ratio = hit_ratio;
+
Assert(hit_ratio >= 0 && hit_ratio <= 1.0);
/*
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index a1456c9014d..ccb880158fe 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -285,7 +285,7 @@ static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
uint32 est_entries, Bitmapset *keyparamids,
- double est_unique_keys);
+ double est_unique_keys, double hit_ratio);
static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1705,7 +1705,7 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
plan = make_memoize(subplan, operators, collations, param_exprs,
best_path->singlerow, best_path->binary_mode,
best_path->est_entries, keyparamids,
- best_path->est_unique_keys);
+ best_path->est_unique_keys, best_path->hit_ratio);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6639,7 +6639,7 @@ static Memoize *
make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
List *param_exprs, bool singlerow, bool binary_mode,
uint32 est_entries, Bitmapset *keyparamids,
- double est_unique_keys)
+ double est_unique_keys, double hit_ratio)
{
Memoize *node = makeNode(Memoize);
Plan *plan = &node->plan;
@@ -6658,6 +6658,7 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
node->est_entries = est_entries;
node->keyparamids = keyparamids;
node->est_unique_keys = est_unique_keys;
+ node->hit_ratio = hit_ratio;
return node;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 1fbcda99067..5971f67b77a 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1704,6 +1704,11 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
/* Estimated number of distinct memoization keys, computed using estimate_num_groups() */
pathnode->est_unique_keys = 0;
+ /*
+ * The estimated cache hit ratio will calculated later by cost_memoize_rescan()
+ */
+ pathnode->hit_ratio = 0;
+
/*
* Add a small additional charge for caching the first entry. All the
* harder calculations for rescans are performed in cost_memoize_rescan().
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 07d97dc0b5b..9bfc35cd4f6 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2140,6 +2140,7 @@ typedef struct MemoizePath
* if unknown */
double est_unique_keys; /* Estimated number of distinct memoization keys,
* used for cache size evaluation. Kept for EXPLAIN */
+ double hit_ratio; /* Estimated cache hit ratio, kept for EXPLAIN */
} MemoizePath;
/*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 3d9d3a1159d..a7d350ce9ca 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1069,6 +1069,9 @@ typedef struct Memoize
* used for cache size evaluation. Kept for EXPLAIN
*/
double est_unique_keys;
+
+ /* Estimated cache hit ratio, kept for EXPLAIN */
+ double hit_ratio;
} Memoize;
/* ----------------
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 826018a8a9f..0fa4359e23c 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2246,10 +2246,11 @@ SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON
Cache Key: tenk1.hundred
Cache Mode: logical
Estimated Capacity: 100 Estimated Distinct Lookup Keys: 100
+ Estimated Hit Ratio: 0.99
-> Index Scan using tenk2_hundred on tenk2
Index Cond: (hundred = tenk1.hundred)
Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2))
-(11 rows)
+(12 rows)
--
-- Check behavior with duplicate index column contents
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index eca6ce2c6f0..212b7897919 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2741,9 +2741,10 @@ select * from onek t1
Cache Key: t2.two
Cache Mode: binary
Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
+ Estimated Hit Ratio: 1.00
-> Seq Scan on onek t3
Filter: (two = t2.two)
-(12 rows)
+(13 rows)
--
-- check a case where we formerly got confused by conflicting sort orders
@@ -5148,11 +5149,12 @@ where t1.f1 = ss.f1;
Cache Key: i8.q1
Cache Mode: binary
Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
+ Estimated Hit Ratio: 0.50
-> Limit
Output: (i8.q1), t2.f1
-> Seq Scan on public.text_tbl t2
Output: i8.q1, t2.f1
-(21 rows)
+(22 rows)
select * from
text_tbl t1
@@ -5194,6 +5196,7 @@ where t1.f1 = ss2.f1;
Cache Key: i8.q1
Cache Mode: binary
Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
+ Estimated Hit Ratio: 0.50
-> Limit
Output: (i8.q1), t2.f1
-> Seq Scan on public.text_tbl t2
@@ -5203,11 +5206,12 @@ where t1.f1 = ss2.f1;
Cache Key: (i8.q1), t2.f1
Cache Mode: binary
Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
+ Estimated Hit Ratio: 0.50
-> Limit
Output: ((i8.q1)), (t2.f1)
-> Seq Scan on public.text_tbl t3
Output: (i8.q1), t2.f1
-(32 rows)
+(34 rows)
select * from
text_tbl t1
@@ -5257,6 +5261,7 @@ where tt1.f1 = ss1.c0;
Cache Key: tt4.f1
Cache Mode: binary
Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
+ Estimated Hit Ratio: 0.50
-> Subquery Scan on ss1
Output: ss1.c0
Filter: (ss1.c0 = 'foo'::text)
@@ -5264,7 +5269,7 @@ where tt1.f1 = ss1.c0;
Output: (tt4.f1)
-> Seq Scan on public.text_tbl tt5
Output: tt4.f1
-(33 rows)
+(34 rows)
select 1 from
text_tbl as tt1
@@ -6475,10 +6480,11 @@ select * from sj t1
Cache Key: t1.a, t1.b
Cache Mode: binary
Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
+ Estimated Hit Ratio: 0.33
-> Sample Scan on sj
Sampling: system (t1.b)
Filter: (t1.a = a)
-(9 rows)
+(10 rows)
-- Ensure that SJE does not form a self-referential lateral dependency
explain (costs off)
@@ -7629,8 +7635,9 @@ explain (costs off)
Cache Key: a.two
Cache Mode: binary
Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
+ Estimated Hit Ratio: 1.00
-> Function Scan on generate_series g
-(8 rows)
+(9 rows)
explain (costs off)
select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
@@ -7643,8 +7650,9 @@ explain (costs off)
Cache Key: a.two
Cache Mode: binary
Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
+ Estimated Hit Ratio: 1.00
-> Function Scan on generate_series g
-(8 rows)
+(9 rows)
-- don't need the explicit LATERAL keyword for functions
explain (costs off)
@@ -7658,8 +7666,9 @@ explain (costs off)
Cache Key: a.two
Cache Mode: binary
Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
+ Estimated Hit Ratio: 1.00
-> Function Scan on generate_series g
-(8 rows)
+(9 rows)
-- lateral with UNION ALL subselect
explain (costs off)
@@ -7722,9 +7731,10 @@ explain (costs off)
Cache Key: "*VALUES*".column1
Cache Mode: logical
Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
+ Estimated Hit Ratio: 1.00
-> Index Only Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 = "*VALUES*".column1)
-(11 rows)
+(12 rows)
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 39c76aaa1a4..1e1b3419991 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -47,12 +47,13 @@ WHERE t2.unique1 < 1000;', false);
Cache Key: t2.twenty
Cache Mode: logical
Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20
+ Estimated Hit Ratio: 0.98
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N)
Index Cond: (unique1 = t2.twenty)
Heap Fetches: N
Index Searches: N
-(14 rows)
+(15 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
@@ -80,12 +81,13 @@ WHERE t1.unique1 < 1000;', false);
Cache Key: t1.twenty
Cache Mode: binary
Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20
+ Estimated Hit Ratio: 0.98
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1.00 loops=N)
Index Cond: (unique1 = t1.twenty)
Heap Fetches: N
Index Searches: N
-(14 rows)
+(15 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
@@ -116,6 +118,7 @@ WHERE t1.unique1 < 10;', false);
Cache Key: t1.two
Cache Mode: binary
Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
+ Estimated Hit Ratio: 0.80
Hits: 8 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Subquery Scan on t2 (actual rows=2.00 loops=N)
Filter: (t1.two = t2.two)
@@ -123,7 +126,7 @@ WHERE t1.unique1 < 10;', false);
-> Index Scan using tenk1_unique1 on tenk1 t2_1 (actual rows=4.00 loops=N)
Index Cond: (unique1 < 4)
Index Searches: N
-(16 rows)
+(17 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t2.t1two) FROM tenk1 t1 LEFT JOIN
@@ -153,13 +156,14 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
Cache Key: (t1.two + 1)
Cache Mode: binary
Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
+ Estimated Hit Ratio: 1.00
Hits: 998 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1.00 loops=N)
Filter: ((t1.two + 1) = unique1)
Rows Removed by Filter: 9999
Heap Fetches: N
Index Searches: N
-(15 rows)
+(16 rows)
-- And check we get the expected results.
SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
@@ -187,11 +191,12 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
Cache Key: t1.two, t1.twenty
Cache Mode: binary
Estimated Capacity: 40 Estimated Distinct Lookup Keys: 40
+ Estimated Hit Ratio: 0.96
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Seq Scan on tenk1 t2 (actual rows=1.00 loops=N)
Filter: ((t1.twenty = unique1) AND (t1.two = two))
Rows Removed by Filter: 9999
-(13 rows)
+(14 rows)
-- And check we get the expected results.
SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
@@ -226,13 +231,14 @@ ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false);
Cache Key: t1.x, (t1.t)::numeric
Cache Mode: logical
Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20
+ Estimated Hit Ratio: 0.50
Hits: 20 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using expr_key_idx_x_t on expr_key t2 (actual rows=2.00 loops=N)
Index Cond: (x = (t1.t)::numeric)
Filter: (t1.x = (t)::numeric)
Heap Fetches: N
Index Searches: N
-(12 rows)
+(13 rows)
DROP TABLE expr_key;
-- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions
@@ -256,12 +262,13 @@ WHERE t2.unique1 < 1200;', true);
Cache Key: t2.thousand
Cache Mode: logical
Estimated Capacity: 655 Estimated Distinct Lookup Keys: 721
+ Estimated Hit Ratio: 0.36
Hits: N Misses: N Evictions: N Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N)
Index Cond: (unique1 = t2.thousand)
Heap Fetches: N
Index Searches: N
-(14 rows)
+(15 rows)
CREATE TABLE flt (f float);
CREATE INDEX flt_f_idx ON flt (f);
@@ -281,12 +288,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false);
Cache Key: f1.f
Cache Mode: logical
Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
+ Estimated Hit Ratio: 0.50
Hits: 1 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2.00 loops=N)
Index Cond: (f = f1.f)
Heap Fetches: N
Index Searches: N
-(13 rows)
+(14 rows)
-- Ensure memoize operates in binary mode
SELECT explain_memoize('
@@ -301,12 +309,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false);
Cache Key: f1.f
Cache Mode: binary
Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
+ Estimated Hit Ratio: 0.50
Hits: 0 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2.00 loops=N)
Index Cond: (f <= f1.f)
Heap Fetches: N
Index Searches: N
-(13 rows)
+(14 rows)
DROP TABLE flt;
-- Exercise Memoize in binary mode with a large fixed width type and a
@@ -330,11 +339,12 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false);
Cache Key: s1.n
Cache Mode: binary
Estimated Capacity: 3 Estimated Distinct Lookup Keys: 3
+ Estimated Hit Ratio: 0.50
Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Scan using strtest_n_idx on strtest s2 (actual rows=4.00 loops=N)
Index Cond: (n <= s1.n)
Index Searches: N
-(11 rows)
+(12 rows)
-- Ensure we get 3 hits and 3 misses
SELECT explain_memoize('
@@ -348,11 +358,12 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
Cache Key: s1.t
Cache Mode: binary
Estimated Capacity: 4 Estimated Distinct Lookup Keys: 4
+ Estimated Hit Ratio: 0.33
Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Scan using strtest_t_idx on strtest s2 (actual rows=4.00 loops=N)
Index Cond: (t <= s1.t)
Index Searches: N
-(11 rows)
+(12 rows)
DROP TABLE strtest;
-- Ensure memoize works with partitionwise join
@@ -378,6 +389,7 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
Cache Key: t1_1.a
Cache Mode: logical
Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
+ Estimated Hit Ratio: 0.50
Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using iprt_p1_a on prt_p1 t2_1 (actual rows=4.00 loops=N)
Index Cond: (a = t1_1.a)
@@ -391,12 +403,13 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
Cache Key: t1_2.a
Cache Mode: logical
Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2
+ Estimated Hit Ratio: 0.50
Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using iprt_p2_a on prt_p2 t2_2 (actual rows=4.00 loops=N)
Index Cond: (a = t1_2.a)
Heap Fetches: N
Index Searches: N
-(27 rows)
+(29 rows)
-- Ensure memoize works with parameterized union-all Append path
SET enable_partitionwise_join TO off;
@@ -414,6 +427,7 @@ ON t1.a = t2.a;', false);
Cache Key: t1.a
Cache Mode: logical
Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1
+ Estimated Hit Ratio: 0.75
Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Append (actual rows=4.00 loops=N)
-> Index Only Scan using iprt_p1_a on prt_p1 (actual rows=4.00 loops=N)
@@ -424,7 +438,7 @@ ON t1.a = t2.a;', false);
Index Cond: (a = t1.a)
Heap Fetches: N
Index Searches: N
-(18 rows)
+(19 rows)
DROP TABLE prt;
RESET enable_partitionwise_join;
@@ -451,10 +465,11 @@ WHERE unique1 < 3
Cache Key: t2.hundred
Cache Mode: logical
Estimated Capacity: 100 Estimated Distinct Lookup Keys: 100
+ Estimated Hit Ratio: 0.99
-> Index Scan using tenk1_unique1 on tenk1 t1
Index Cond: (unique1 = t2.hundred)
Filter: (t0.ten = twenty)
-(14 rows)
+(15 rows)
-- Ensure the above query returns the correct result
SELECT unique1 FROM tenk1 t0
@@ -499,9 +514,10 @@ WHERE t1.unique1 < 1000;
Cache Key: t1.twenty
Cache Mode: logical
Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20
+ Estimated Hit Ratio: 0.95
-> Index Only Scan using tenk1_unique1 on tenk1 t2
Index Cond: (unique1 = t1.twenty)
-(15 rows)
+(16 rows)
-- And ensure the parallel plan gives us the correct results.
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index d3c37ffe22b..15a6d94d7c0 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -5299,6 +5299,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
Cache Key: p1_1.c
Cache Mode: logical
Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
+ Estimated Hit Ratio: 0.76
-> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
Index Cond: (c = p1_1.c)
-> Nested Loop
@@ -5307,6 +5308,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
Cache Key: p1_2.c
Cache Mode: logical
Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
+ Estimated Hit Ratio: 0.90
-> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
Index Cond: (c = p1_2.c)
-> Nested Loop
@@ -5315,9 +5317,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
Cache Key: p1_3.c
Cache Mode: logical
Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
+ Estimated Hit Ratio: 0.90
-> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
Index Cond: (c = p1_3.c)
-(26 rows)
+(29 rows)
-- If almost all the data should be fetched - prefer SeqScan
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1000;
@@ -5359,6 +5362,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
Cache Key: p1_1.c
Cache Mode: logical
Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
+ Estimated Hit Ratio: 0.76
-> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
Index Cond: (c = p1_1.c)
-> Nested Loop
@@ -5367,6 +5371,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
Cache Key: p1_2.c
Cache Mode: logical
Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
+ Estimated Hit Ratio: 0.90
-> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
Index Cond: (c = p1_2.c)
-> Nested Loop
@@ -5375,9 +5380,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
Cache Key: p1_3.c
Cache Mode: logical
Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12
+ Estimated Hit Ratio: 0.90
-> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
Index Cond: (c = p1_3.c)
-(29 rows)
+(32 rows)
RESET debug_parallel_query;
-- Remove indexes from the partitioned table and its partitions
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index f5575c7d332..81e3e07d968 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1326,13 +1326,14 @@ where o.ten = 1;
Cache Key: o.four
Cache Mode: binary
Estimated Capacity: 4 Estimated Distinct Lookup Keys: 4
+ Estimated Hit Ratio: 0.96
-> CTE Scan on x
CTE x
-> Recursive Union
-> Result
-> WorkTable Scan on x x_1
Filter: (a < 10)
-(14 rows)
+(15 rows)
select sum(o.four), sum(ss.a) from
onek o cross join lateral (
@@ -2644,6 +2645,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
Cache Key: b.hundred, b.odd
Cache Mode: binary
Estimated Capacity: 1000 Estimated Distinct Lookup Keys: 1000
+ Estimated Hit Ratio: 0.90
-> Subquery Scan on "ANY_subquery"
Filter: (b.hundred = "ANY_subquery".min)
-> Result
@@ -2652,7 +2654,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
-> Index Scan using tenk2_hundred on tenk2 c
Index Cond: (hundred IS NOT NULL)
Filter: (odd = b.odd)
-(17 rows)
+(18 rows)
--
-- Test VALUES to ARRAY (VtA) transformation
--
2.34.1
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-15 00:23 David Rowley <[email protected]>
parent: Ilia Evdokimov <[email protected]>
0 siblings, 2 replies; 49+ messages in thread
From: David Rowley @ 2025-04-15 00:23 UTC (permalink / raw)
To: Ilia Evdokimov <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On Tue, 15 Apr 2025 at 11:14, Ilia Evdokimov
<[email protected]> wrote:
> On 14.04.2025 23:53, David Rowley wrote:
> If we can't get consensus for everything people want to add at once
> then maybe the patch could be broken into two, with 0001 being pretty
> much the v4 patch and then have 0002 add the Estimated Hit Ratio.
> Having the physical patches and being able to try it out or view the
> regression test changes might lower the bar on people chipping in with
> their views.
>
>
> Agreed - I’ll split the patch into two: one adds Estimated Capacity and Estimated Distinct Keys, and the second adds Estimated Hit Ratio. I’ll also add regression test differences to make it easier to evaluate the usefulness of each part.
Thanks for the patches.
I'm just looking for ways to allow us to group all three of these
together for the TEXT format type. I see the BUFFERS are displayed
quite compactly, e.g. "Buffers: shared hit=17 read=4".
show_wal_usage() does something similar and so does
show_modifytable_info() for MERGE.
Maybe we could compress the text output a bit with:
"Estimates: capacity=N distinct keys=N hit ratio=N.N%"
If we get it that compact, maybe lookups could fit in there too, i.e.:
"Estimates: capacity=N distinct keys=N lookups=N hit ratio=N.N%"
I'd also like to vote that you modify explain.c and multiply the
hit_ratio by 100 and use "%" as the unit in ExplainPropertyFloat().
Just looking at the raw number of "1.00" in the expected output, it
isn't obvious if the planner expects every lookup to be a cache hit or
just 1% of them.
Also, to get something commitable, you'll also need to modify
explain_memoize() at the top of memoize.sql and add handling to mask
out the value of these new properties. These are not going to be
anywhere near stable enough across platforms to have these shown in
the expected output files.
David
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-15 09:44 Ilia Evdokimov <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: Ilia Evdokimov @ 2025-04-15 09:44 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On 15.04.2025 03:23, David Rowley wrote:
> I'm just looking for ways to allow us to group all three of these
> together for the TEXT format type. I see the BUFFERS are displayed
> quite compactly, e.g. "Buffers: shared hit=17 read=4".
> show_wal_usage() does something similar and so does
> show_modifytable_info() for MERGE.
>
> Maybe we could compress the text output a bit with:
>
> "Estimates: capacity=N distinct keys=N hit ratio=N.N%"
>
> If we get it that compact, maybe lookups could fit in there too, i.e.:
>
> "Estimates: capacity=N distinct keys=N lookups=N hit ratio=N.N%"
>
> I'd also like to vote that you modify explain.c and multiply the
> hit_ratio by 100 and use "%" as the unit in ExplainPropertyFloat().
> Just looking at the raw number of "1.00" in the expected output, it
> isn't obvious if the planner expects every lookup to be a cache hit or
> just 1% of them.
+1. I'll attach three patches with 'capacity=N distinct keys=N' output,
'lookups=N' and 'ratio=N.N%'
>
> Also, to get something commitable, you'll also need to modify
> explain_memoize() at the top of memoize.sql and add handling to mask
> out the value of these new properties. These are not going to be
> anywhere near stable enough across platforms to have these shown in
> the expected output files.
>
> David
There are other regression tests - such as create_index, subselect,
join, and partition_join - that also use Memoize nodes. These tests
currently don't have any masking in place, so the new output fields may
cause instability across platforms.
Wrapping the line in costs or verbose would help with test stability,
but doing that only to simplify test output doesn't look like the right
reason. Maybe it makes more sense to mask these values in other tests
that use Memoize too. What do you think?
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-15 16:24 Robert Haas <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: Robert Haas @ 2025-04-15 16:24 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Ilia Evdokimov <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On Mon, Apr 14, 2025 at 8:23 PM David Rowley <[email protected]> wrote:
> Maybe we could compress the text output a bit with:
>
> "Estimates: capacity=N distinct keys=N hit ratio=N.N%"
Sure, that works for me.
> If we get it that compact, maybe lookups could fit in there too, i.e.:
>
> "Estimates: capacity=N distinct keys=N lookups=N hit ratio=N.N%"
Is lookups=N here the estimated number of lookups i.e. what we think
nloops will end up being?
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-15 20:20 David Rowley <[email protected]>
parent: Ilia Evdokimov <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: David Rowley @ 2025-04-15 20:20 UTC (permalink / raw)
To: Ilia Evdokimov <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On Tue, 15 Apr 2025 at 21:44, Ilia Evdokimov
<[email protected]> wrote:
> Wrapping the line in costs or verbose would help with test stability, but doing that only to simplify test output doesn't look like the right reason. Maybe it makes more sense to mask these values in other tests that use Memoize too. What do you think?
Disregard what I said about the explain_memoize() function. The new
output just shouldn't be shown with EXPLAIN (costs off).
David
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-15 20:49 David Rowley <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: David Rowley @ 2025-04-15 20:49 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Ilia Evdokimov <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On Wed, 16 Apr 2025 at 04:25, Robert Haas <[email protected]> wrote:
>
> On Mon, Apr 14, 2025 at 8:23 PM David Rowley <[email protected]> wrote:
> > "Estimates: capacity=N distinct keys=N lookups=N hit ratio=N.N%"
>
> Is lookups=N here the estimated number of lookups i.e. what we think
> nloops will end up being?
Yes. The estimate is the "calls" variable in cost_memoize_rescan(),
which is fairly critical in the hit ratio estimate calculation.
Technically this is just the Nested Loop's outer_path->rows. There was
an argument earlier in the thread for putting this in along with the
other fields to make things easier to read. I did argue that it was
redundant due to the fact that the reader can look at the row estimate
for the outer side of the Nest Loop, but maybe it's small enough to go
in using the above format.
David
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-15 22:09 Ilia Evdokimov <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: Ilia Evdokimov @ 2025-04-15 22:09 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
I've prepared the updated patches as discussed, including the addition
of estimated lookups in the EXPLAIN output for Memoize. Please let me
know if you have any feedback or further suggestions.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
Attachments:
[text/x-patch] v8-0001-Show-ndistinct-and-est_entries-in-EXPLAIN-for-Mem.patch (5.9K, ../../[email protected]/2-v8-0001-Show-ndistinct-and-est_entries-in-EXPLAIN-for-Mem.patch)
download | inline diff:
From 021cc6f5629a607cab3a0ec1021fc5f102dc0439 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email protected]>
Date: Tue, 15 Apr 2025 23:47:57 +0300
Subject: [PATCH v8 1/3] Show ndistinct and est_entries in EXPLAIN for Memoize
Reviewed-by: David Rowley <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
Reviewed-by: Andrei Lepikhov <[email protected]>
Reviewed-by: Tom Lane <[email protected]>
---
src/backend/commands/explain.c | 16 ++++++++++++++++
src/backend/optimizer/path/costsize.c | 3 +++
src/backend/optimizer/plan/createplan.c | 10 +++++++---
src/backend/optimizer/util/pathnode.c | 6 ++++++
src/include/nodes/pathnodes.h | 2 ++
src/include/nodes/plannodes.h | 6 ++++++
6 files changed, 40 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 786ee865f14..0634d0a982e 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3628,6 +3628,22 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
ExplainPropertyText("Cache Key", keystr.data, es);
ExplainPropertyText("Cache Mode", mstate->binary_mode ? "binary" : "logical", es);
+ if (es->costs)
+ {
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f\n",
+ ((Memoize *) plan)->est_entries,
+ ((Memoize *) plan)->est_unique_keys);
+ }
+ else
+ {
+ ExplainPropertyUInteger("Estimated Capacity", "", ((Memoize *) plan)->est_entries, es);
+ ExplainPropertyFloat("Estimated Distinct Lookup Keys", "", ((Memoize *) plan)->est_unique_keys, 0, es);
+ }
+ }
+
pfree(keystr.data);
if (!es->analyze)
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 60b0fcfb6be..f72319d903c 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2604,6 +2604,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
mpath->est_entries = Min(Min(ndistinct, est_cache_entries),
PG_UINT32_MAX);
+ /* Remember ndistinct for a potential EXPLAIN later */
+ mpath->est_unique_keys = ndistinct;
+
/*
* When the number of distinct parameter values is above the amount we can
* store in the cache, then we'll have to evict some entries from the
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index a8f22a8c154..a1456c9014d 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -284,7 +284,8 @@ static Material *make_material(Plan *lefttree);
static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids);
+ uint32 est_entries, Bitmapset *keyparamids,
+ double est_unique_keys);
static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1703,7 +1704,8 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
plan = make_memoize(subplan, operators, collations, param_exprs,
best_path->singlerow, best_path->binary_mode,
- best_path->est_entries, keyparamids);
+ best_path->est_entries, keyparamids,
+ best_path->est_unique_keys);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6636,7 +6638,8 @@ materialize_finished_plan(Plan *subplan)
static Memoize *
make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
List *param_exprs, bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids)
+ uint32 est_entries, Bitmapset *keyparamids,
+ double est_unique_keys)
{
Memoize *node = makeNode(Memoize);
Plan *plan = &node->plan;
@@ -6654,6 +6657,7 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
node->binary_mode = binary_mode;
node->est_entries = est_entries;
node->keyparamids = keyparamids;
+ node->est_unique_keys = est_unique_keys;
return node;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 93e73cb44db..76bdea52127 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1701,6 +1701,12 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
Assert(enable_memoize);
pathnode->path.disabled_nodes = subpath->disabled_nodes;
+ /*
+ * Estimated number of distinct memoization keys,
+ * computed using estimate_num_groups()
+ */
+ pathnode->est_unique_keys = 0;
+
/*
* Add a small additional charge for caching the first entry. All the
* harder calculations for rescans are performed in cost_memoize_rescan().
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index bb678bdcdcd..07d97dc0b5b 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2138,6 +2138,8 @@ typedef struct MemoizePath
uint32 est_entries; /* The maximum number of entries that the
* planner expects will fit in the cache, or 0
* if unknown */
+ double est_unique_keys; /* Estimated number of distinct memoization keys,
+ * used for cache size evaluation. Kept for EXPLAIN */
} MemoizePath;
/*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 658d76225e4..3d9d3a1159d 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1063,6 +1063,12 @@ typedef struct Memoize
/* paramids from param_exprs */
Bitmapset *keyparamids;
+
+ /*
+ * Estimated number of distinct memoization keys,
+ * used for cache size evaluation. Kept for EXPLAIN
+ */
+ double est_unique_keys;
} Memoize;
/* ----------------
--
2.34.1
[text/x-patch] v8-0002-Add-Estimated-Hit-Ratio-for-Memoize-plan-nodes-in.patch (5.6K, ../../[email protected]/3-v8-0002-Add-Estimated-Hit-Ratio-for-Memoize-plan-nodes-in.patch)
download | inline diff:
From 18052d2d3ac54e12042673e35ca357ae11f99093 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email protected]>
Date: Wed, 16 Apr 2025 00:20:49 +0300
Subject: [PATCH v8 2/3] Add Estimated Hit Ratio for Memoize plan nodes in
EXPLAIN
Reviewed-by: David Rowley <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
Reviewed-by: Andrei Lepikhov <[email protected]>
Reviewed-by: Tom Lane <[email protected]>
---
src/backend/commands/explain.c | 6 ++++--
src/backend/optimizer/path/costsize.c | 3 +++
src/backend/optimizer/plan/createplan.c | 7 ++++---
src/backend/optimizer/util/pathnode.c | 6 ++++++
src/include/nodes/pathnodes.h | 1 +
src/include/nodes/plannodes.h | 3 +++
6 files changed, 21 insertions(+), 5 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 0634d0a982e..85b22561aa6 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3633,14 +3633,16 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
if (es->format == EXPLAIN_FORMAT_TEXT)
{
ExplainIndentText(es);
- appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f\n",
+ appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f hit ratio=%.2f%%\n",
((Memoize *) plan)->est_entries,
- ((Memoize *) plan)->est_unique_keys);
+ ((Memoize *) plan)->est_unique_keys,
+ ((Memoize *) plan)->hit_ratio * 100.0);
}
else
{
ExplainPropertyUInteger("Estimated Capacity", "", ((Memoize *) plan)->est_entries, es);
ExplainPropertyFloat("Estimated Distinct Lookup Keys", "", ((Memoize *) plan)->est_unique_keys, 0, es);
+ ExplainPropertyFloat("Estimated Hit Ratio", "", ((Memoize *) plan)->hit_ratio * 100.0, 2, es);
}
}
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f72319d903c..3e99214501b 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2624,6 +2624,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
hit_ratio = ((calls - ndistinct) / calls) *
(est_cache_entries / Max(ndistinct, est_cache_entries));
+ /* Remember cache hit ratio for a potential EXPLAIN later */
+ mpath->hit_ratio = hit_ratio;
+
Assert(hit_ratio >= 0 && hit_ratio <= 1.0);
/*
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index a1456c9014d..ccb880158fe 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -285,7 +285,7 @@ static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
uint32 est_entries, Bitmapset *keyparamids,
- double est_unique_keys);
+ double est_unique_keys, double hit_ratio);
static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1705,7 +1705,7 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
plan = make_memoize(subplan, operators, collations, param_exprs,
best_path->singlerow, best_path->binary_mode,
best_path->est_entries, keyparamids,
- best_path->est_unique_keys);
+ best_path->est_unique_keys, best_path->hit_ratio);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6639,7 +6639,7 @@ static Memoize *
make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
List *param_exprs, bool singlerow, bool binary_mode,
uint32 est_entries, Bitmapset *keyparamids,
- double est_unique_keys)
+ double est_unique_keys, double hit_ratio)
{
Memoize *node = makeNode(Memoize);
Plan *plan = &node->plan;
@@ -6658,6 +6658,7 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
node->est_entries = est_entries;
node->keyparamids = keyparamids;
node->est_unique_keys = est_unique_keys;
+ node->hit_ratio = hit_ratio;
return node;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 76bdea52127..40674027f9f 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1707,6 +1707,12 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
*/
pathnode->est_unique_keys = 0;
+ /*
+ * The estimated cache hit ratio will calculated later
+ * by cost_memoize_rescan().
+ */
+ pathnode->hit_ratio = 0;
+
/*
* Add a small additional charge for caching the first entry. All the
* harder calculations for rescans are performed in cost_memoize_rescan().
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 07d97dc0b5b..e17da6f8f02 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2140,6 +2140,7 @@ typedef struct MemoizePath
* if unknown */
double est_unique_keys; /* Estimated number of distinct memoization keys,
* used for cache size evaluation. Kept for EXPLAIN */
+ double hit_ratio; /* Estimated cache hit ratio. Kept for EXPLAIN */
} MemoizePath;
/*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 3d9d3a1159d..4354d4f66d3 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1069,6 +1069,9 @@ typedef struct Memoize
* used for cache size evaluation. Kept for EXPLAIN
*/
double est_unique_keys;
+
+ /* Estimated cache hit ratio. Kept for EXPLAIN */
+ double hit_ratio;
} Memoize;
/* ----------------
--
2.34.1
[text/x-patch] v8-0003-Show-estimated-lookups-in-EXPLAIN-ouput-for-Memoi.patch (4.0K, ../../[email protected]/4-v8-0003-Show-estimated-lookups-in-EXPLAIN-ouput-for-Memoi.patch)
download | inline diff:
From bbce295626e45d899e93a572d38074c40e8c9335 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email protected]>
Date: Wed, 16 Apr 2025 00:53:50 +0300
Subject: [PATCH v8 3/3] Show estimated lookups in EXPLAIN ouput for Memoize
Reviewed-by: David Rowley <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
Reviewed-by: Andrei Lepikhov <[email protected]>
Reviewed-by: Tom Lane <[email protected]>
---
src/backend/commands/explain.c | 4 +++-
src/backend/optimizer/plan/createplan.c | 10 +++++++---
src/include/nodes/plannodes.h | 3 +++
3 files changed, 13 insertions(+), 4 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 85b22561aa6..6fcfa53a166 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3633,15 +3633,17 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
if (es->format == EXPLAIN_FORMAT_TEXT)
{
ExplainIndentText(es);
- appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f hit ratio=%.2f%%\n",
+ appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f lookups=%.0f hit ratio=%.2f%%\n",
((Memoize *) plan)->est_entries,
((Memoize *) plan)->est_unique_keys,
+ ((Memoize *) plan)->lookups,
((Memoize *) plan)->hit_ratio * 100.0);
}
else
{
ExplainPropertyUInteger("Estimated Capacity", "", ((Memoize *) plan)->est_entries, es);
ExplainPropertyFloat("Estimated Distinct Lookup Keys", "", ((Memoize *) plan)->est_unique_keys, 0, es);
+ ExplainPropertyFloat("Estimated Lookups", "", ((Memoize *) plan)->lookups, 0, es);
ExplainPropertyFloat("Estimated Hit Ratio", "", ((Memoize *) plan)->hit_ratio * 100.0, 2, es);
}
}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index ccb880158fe..47a092747d5 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -285,7 +285,8 @@ static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
uint32 est_entries, Bitmapset *keyparamids,
- double est_unique_keys, double hit_ratio);
+ double est_unique_keys, double hit_ratio,
+ double lookups);
static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1705,7 +1706,8 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
plan = make_memoize(subplan, operators, collations, param_exprs,
best_path->singlerow, best_path->binary_mode,
best_path->est_entries, keyparamids,
- best_path->est_unique_keys, best_path->hit_ratio);
+ best_path->est_unique_keys, best_path->hit_ratio,
+ best_path->calls);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6639,7 +6641,8 @@ static Memoize *
make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
List *param_exprs, bool singlerow, bool binary_mode,
uint32 est_entries, Bitmapset *keyparamids,
- double est_unique_keys, double hit_ratio)
+ double est_unique_keys, double hit_ratio,
+ double lookups)
{
Memoize *node = makeNode(Memoize);
Plan *plan = &node->plan;
@@ -6659,6 +6662,7 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
node->keyparamids = keyparamids;
node->est_unique_keys = est_unique_keys;
node->hit_ratio = hit_ratio;
+ node->lookups = lookups;
return node;
}
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 4354d4f66d3..6eb08866304 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1072,6 +1072,9 @@ typedef struct Memoize
/* Estimated cache hit ratio. Kept for EXPLAIN */
double hit_ratio;
+
+ /* Estimated number of lookups. Kept for EXPLAIN */
+ double lookups;
} Memoize;
/* ----------------
--
2.34.1
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-16 16:09 Maciek Sakrejda <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 0 replies; 49+ messages in thread
From: Maciek Sakrejda @ 2025-04-16 16:09 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Ilia Evdokimov <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On Tue, Apr 15, 2025 at 1:50 PM David Rowley <[email protected]> wrote:
> On Wed, 16 Apr 2025 at 04:25, Robert Haas <[email protected]> wrote:
> > On Mon, Apr 14, 2025 at 8:23 PM David Rowley <[email protected]> wrote:
> > > "Estimates: capacity=N distinct keys=N lookups=N hit ratio=N.N%"
> >
> > Is lookups=N here the estimated number of lookups i.e. what we think
> > nloops will end up being?
>
> Yes. The estimate is the "calls" variable in cost_memoize_rescan(),
> which is fairly critical in the hit ratio estimate calculation.
>
> Technically this is just the Nested Loop's outer_path->rows. There was
> an argument earlier in the thread for putting this in along with the
> other fields to make things easier to read. I did argue that it was
> redundant due to the fact that the reader can look at the row estimate
> for the outer side of the Nest Loop, but maybe it's small enough to go
> in using the above format.
This kind of thing is nice to have in the text format, but it's really
nice to have when working with structured formats. Currently, to get a
loops estimate for the inner node, I need to find the parent, find the
outer node in the parent's children, and get that sibling's Plan Rows
(I think). It'd be nice to make things like that easier.
Thanks,
Maciek
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-17 21:29 David Rowley <[email protected]>
parent: Ilia Evdokimov <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: David Rowley @ 2025-04-17 21:29 UTC (permalink / raw)
To: Ilia Evdokimov <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On Wed, 16 Apr 2025 at 10:09, Ilia Evdokimov
<[email protected]> wrote:
> I've prepared the updated patches as discussed, including the addition
> of estimated lookups in the EXPLAIN output for Memoize. Please let me
> know if you have any feedback or further suggestions.
While this is fresh, as I might forget before the July CF...
I know I did suggest that the hit_ratio should be a percent and it
should be multiplied by 100.0. This seems fine for the text format as
you can have the % unit there. However, looking at
ExplainPropertyFloat(), we don't print the unit for non-text formats.
I wonder if printing a number between 0 and 100 there will be
confusing. I don't believe we have anything else in EXPLAIN that shows
a percentage. I don't quite know what to do about this. One thought I
had was to only * 100 for text format, but that might be more
confusing.
Aside from that, I'd prefer the new fields in struct Memoize to be
prefixed with "est_" the same as the existing "est_entries" field. I'm
unsure why MemoizePath.calls becomes Memoize.lookups. Seems
unnecessary and just means more brain space being used to maintain a
mental model.
David
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-04-27 19:58 Ilia Evdokimov <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: Ilia Evdokimov @ 2025-04-27 19:58 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
Sorry for the delayed reply.
On 18.04.2025 00:29, David Rowley wrote:
> I know I did suggest that the hit_ratio should be a percent and it
> should be multiplied by 100.0. This seems fine for the text format as
> you can have the % unit there. However, looking at
> ExplainPropertyFloat(), we don't print the unit for non-text formats.
> I wonder if printing a number between 0 and 100 there will be
> confusing. I don't believe we have anything else in EXPLAIN that shows
> a percentage. I don't quite know what to do about this. One thought I
> had was to only * 100 for text format, but that might be more
> confusing.
I agree that displaying the percentage instead of the fraction is a
better choice, because it is much more convenient for analysis. If this
value were intended to be passed into some API for further computations,
it would make sense to keep it as a fraction to avoid unnecessary
multiplications and divisions by 100. However, such use cases seem
extremely unlikely.
Therefore, I think it is better to report percentages directly. Since
non-text EXPLAIN formats do not display units, I propose to rename the
field to "hit_percent" in all formats, including the text format. This
way, the meaning of the value remains clear without needing additional
context. What do you think about this approach?
>
> Aside from that, I'd prefer the new fields in struct Memoize to be
> prefixed with "est_" the same as the existing "est_entries" field. I'm
> unsure why MemoizePath.calls becomes Memoize.lookups. Seems
> unnecessary and just means more brain space being used to maintain a
> mental model.
Agree.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-05-01 12:22 Ilia Evdokimov <[email protected]>
parent: Ilia Evdokimov <[email protected]>
0 siblings, 2 replies; 49+ messages in thread
From: Ilia Evdokimov @ 2025-05-01 12:22 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On 27.04.2025 22:58, Ilia Evdokimov wrote:
> Therefore, I think it is better to report percentages directly. Since
> non-text EXPLAIN formats do not display units, I propose to rename the
> field to "hit_percent" in all formats, including the text format. This
> way, the meaning of the value remains clear without needing additional
> context. What do you think about this approach?
I attached updated v9 patch with the suggested changes. The updated line
in the EXPLAIN looks like this:
Text format:
Estimates: capacity=1 distinct keys=1 lookups=2 hit percent=50.00%
Non-text format:
Estimated Capacity: 1
Estimated Distinct Lookup Keys: 1
Estimated Lookups: 2
Estimated Hit Percent: 50.00
Any suggestions?
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
Attachments:
[text/x-patch] v9-0001-Expose-cache-hit-statistics-in-Memoize-node-EXPLAIN.patch (7.3K, ../../[email protected]/2-v9-0001-Expose-cache-hit-statistics-in-Memoize-node-EXPLAIN.patch)
download | inline diff:
From eff0cb420c922343af9e09e58cec7b5be19c35e4 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email protected]>
Date: Thu, 1 May 2025 15:20:07 +0300
Subject: [PATCH v9] Expose cache hit statistics in Memoize node EXPLAIN
output.
This patch adds additional information to the Memoize node's EXPLAIN output:
estimated cache capacity, number of distinct keys, total lookups, and
cache hit percentage.
Reviewed-by: David Rowley <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
Reviewed-by: Andrei Lepikhov <[email protected]>
Reviewed-by: Tom Lane <[email protected]>
---
src/backend/commands/explain.c | 20 ++++++++++++++++++++
src/backend/optimizer/path/costsize.c | 6 ++++++
src/backend/optimizer/plan/createplan.c | 15 ++++++++++++---
src/backend/optimizer/util/pathnode.c | 12 ++++++++++++
src/include/nodes/pathnodes.h | 3 +++
src/include/nodes/plannodes.h | 12 ++++++++++++
6 files changed, 65 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 786ee865f14..3a5b436b561 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3628,6 +3628,26 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
ExplainPropertyText("Cache Key", keystr.data, es);
ExplainPropertyText("Cache Mode", mstate->binary_mode ? "binary" : "logical", es);
+ if (es->costs)
+ {
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f lookups=%.0f hit percent=%.2f%%\n",
+ ((Memoize *) plan)->est_entries,
+ ((Memoize *) plan)->est_unique_keys,
+ ((Memoize *) plan)->est_calls,
+ ((Memoize *) plan)->est_hit_ratio * 100.0);
+ }
+ else
+ {
+ ExplainPropertyUInteger("Estimated Capacity", NULL, ((Memoize *) plan)->est_entries, es);
+ ExplainPropertyFloat("Estimated Distinct Lookup Keys", NULL, ((Memoize *) plan)->est_unique_keys, 0, es);
+ ExplainPropertyFloat("Estimated Lookups", NULL, ((Memoize *) plan)->est_calls, 0, es);
+ ExplainPropertyFloat("Estimated Hit Percent", NULL, ((Memoize *) plan)->est_hit_ratio * 100.0, 2, es);
+ }
+ }
+
pfree(keystr.data);
if (!es->analyze)
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 60b0fcfb6be..226a7861543 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2604,6 +2604,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
mpath->est_entries = Min(Min(ndistinct, est_cache_entries),
PG_UINT32_MAX);
+ /* Remember ndistinct for a potential EXPLAIN later */
+ mpath->est_unique_keys = ndistinct;
+
/*
* When the number of distinct parameter values is above the amount we can
* store in the cache, then we'll have to evict some entries from the
@@ -2621,6 +2624,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
hit_ratio = ((calls - ndistinct) / calls) *
(est_cache_entries / Max(ndistinct, est_cache_entries));
+ /* Remember cache hit ratio for a potential EXPLAIN later */
+ mpath->est_hit_ratio = hit_ratio;
+
Assert(hit_ratio >= 0 && hit_ratio <= 1.0);
/*
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index a8f22a8c154..b3d3fac187f 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -284,7 +284,9 @@ static Material *make_material(Plan *lefttree);
static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids);
+ uint32 est_entries, Bitmapset *keyparamids,
+ double est_unique_keys, double est_hit_ratio,
+ double est_calls);
static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1703,7 +1705,9 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
plan = make_memoize(subplan, operators, collations, param_exprs,
best_path->singlerow, best_path->binary_mode,
- best_path->est_entries, keyparamids);
+ best_path->est_entries, keyparamids,
+ best_path->est_unique_keys, best_path->est_hit_ratio,
+ best_path->calls);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6636,7 +6640,9 @@ materialize_finished_plan(Plan *subplan)
static Memoize *
make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
List *param_exprs, bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids)
+ uint32 est_entries, Bitmapset *keyparamids,
+ double est_unique_keys, double est_hit_ratio,
+ double est_calls)
{
Memoize *node = makeNode(Memoize);
Plan *plan = &node->plan;
@@ -6654,6 +6660,9 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
node->binary_mode = binary_mode;
node->est_entries = est_entries;
node->keyparamids = keyparamids;
+ node->est_unique_keys = est_unique_keys;
+ node->est_hit_ratio = est_hit_ratio;
+ node->est_calls = est_calls;
return node;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 93e73cb44db..7e35421f410 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1701,6 +1701,18 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
Assert(enable_memoize);
pathnode->path.disabled_nodes = subpath->disabled_nodes;
+ /*
+ * Estimated number of distinct memoization keys,
+ * computed using estimate_num_groups()
+ */
+ pathnode->est_unique_keys = 0;
+
+ /*
+ * The estimated cache hit ratio will calculated later
+ * by cost_memoize_rescan().
+ */
+ pathnode->est_hit_ratio = 0;
+
/*
* Add a small additional charge for caching the first entry. All the
* harder calculations for rescans are performed in cost_memoize_rescan().
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 011e5a811c3..938e4c43a81 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2138,6 +2138,9 @@ typedef struct MemoizePath
uint32 est_entries; /* The maximum number of entries that the
* planner expects will fit in the cache, or 0
* if unknown */
+ double est_unique_keys; /* Estimated number of distinct memoization keys,
+ * used for cache size evaluation. Kept for EXPLAIN */
+ double est_hit_ratio; /* Estimated cache hit ratio. Kept for EXPLAIN */
} MemoizePath;
/*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 658d76225e4..26a4c78afe8 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1063,6 +1063,18 @@ typedef struct Memoize
/* paramids from param_exprs */
Bitmapset *keyparamids;
+
+ /*
+ * Estimated number of distinct memoization keys,
+ * used for cache size evaluation. Kept for EXPLAIN
+ */
+ double est_unique_keys;
+
+ /* Estimated cache hit ratio. Kept for EXPLAIN */
+ double est_hit_ratio;
+
+ /* Estimated number of rescans. Kept for EXPLAIN */
+ double est_calls;
} Memoize;
/* ----------------
--
2.34.1
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-05-01 13:37 Andrei Lepikhov <[email protected]>
parent: Ilia Evdokimov <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: Andrei Lepikhov @ 2025-05-01 13:37 UTC (permalink / raw)
To: Ilia Evdokimov <[email protected]>; David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>
On 5/1/25 14:22, Ilia Evdokimov wrote:
> Estimated Hit Percent: 50.00
>
> Any suggestions?
I still think that two meaningful digits are enough for an EXPLAIN. We
usually need to estimate the tuple set's size, not a precise number of
tuples or groups. And definitely, not an arbitrarily chosen two digits
after the point. So, IMO, good examples may look like the following:
50%, 5.0%, 0.00051%.
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-05-01 19:10 Ilia Evdokimov <[email protected]>
parent: Andrei Lepikhov <[email protected]>
0 siblings, 0 replies; 49+ messages in thread
From: Ilia Evdokimov @ 2025-05-01 19:10 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>
On 01.05.2025 16:37, Andrei Lepikhov wrote:
> I still think that two meaningful digits are enough for an EXPLAIN. We
> usually need to estimate the tuple set's size, not a precise number of
> tuples or groups. And definitely, not an arbitrarily chosen two digits
> after the point. So, IMO, good examples may look like the following:
> 50%, 5.0%, 0.00051%.
>
The idea is not bad. But I think it might be better to raise this in a
new thread, because this is not the only place [0] where we might want
to output numbers like that. We could also discuss there what else might
benefit from such formatting.
[0]:
https://www.postgresql.org/message-id/3365160.1739385615%40sss.pgh.pa.us
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-07-04 08:30 Ilia Evdokimov <[email protected]>
parent: Ilia Evdokimov <[email protected]>
1 sibling, 2 replies; 49+ messages in thread
From: Ilia Evdokimov @ 2025-07-04 08:30 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On 01.05.2025 15:22, Ilia Evdokimov wrote:
> I attached updated v9 patch with the suggested changes. The updated
> line in the EXPLAIN looks like this:
>
> Text format:
> Estimates: capacity=1 distinct keys=1 lookups=2 hit percent=50.00%
>
> Non-text format:
> Estimated Capacity: 1
> Estimated Distinct Lookup Keys: 1
> Estimated Lookups: 2
> Estimated Hit Percent: 50.00
>
> Any suggestions?
I attached rebased v10 patch on 5a6c39b.
--
Best Regards,
Ilia Evdokimov,
Tantor Labs LLC.
Attachments:
[text/x-patch] v10-0001-Expose-cache-hit-statistics-in-Memoize-node-EXPLAIN.patch (7.1K, ../../[email protected]/2-v10-0001-Expose-cache-hit-statistics-in-Memoize-node-EXPLAIN.patch)
download | inline diff:
From 48cacc9b59eaa73c69a891e0cf6967f1a3f3c398 Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email protected]>
Date: Fri, 4 Jul 2025 11:24:56 +0300
Subject: [PATCH v10] Expose cache hit statistics in Memoize node EXPLAIN
output.
This patch adds additional information to the Memoize node's EXPLAIN output:
estimated cache capacity, number of distinct keys, total lookups, and
cache hit percentage.
---
src/backend/commands/explain.c | 20 ++++++++++++++++++++
src/backend/optimizer/path/costsize.c | 6 ++++++
src/backend/optimizer/plan/createplan.c | 15 ++++++++++++---
src/backend/optimizer/util/pathnode.c | 12 ++++++++++++
src/include/nodes/pathnodes.h | 3 +++
src/include/nodes/plannodes.h | 12 ++++++++++++
6 files changed, 65 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 7e2792ead71..561eb8957b8 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3616,6 +3616,26 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
ExplainPropertyText("Cache Key", keystr.data, es);
ExplainPropertyText("Cache Mode", mstate->binary_mode ? "binary" : "logical", es);
+ if (es->costs)
+ {
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f lookups=%.0f hit percent=%.2f%%\n",
+ ((Memoize *) plan)->est_entries,
+ ((Memoize *) plan)->est_unique_keys,
+ ((Memoize *) plan)->est_calls,
+ ((Memoize *) plan)->est_hit_ratio * 100.0);
+ }
+ else
+ {
+ ExplainPropertyUInteger("Estimated Capacity", NULL, ((Memoize *) plan)->est_entries, es);
+ ExplainPropertyFloat("Estimated Distinct Lookup Keys", NULL, ((Memoize *) plan)->est_unique_keys, 0, es);
+ ExplainPropertyFloat("Estimated Lookups", NULL, ((Memoize *) plan)->est_calls, 0, es);
+ ExplainPropertyFloat("Estimated Hit Percent", NULL, ((Memoize *) plan)->est_hit_ratio * 100.0, 2, es);
+ }
+ }
+
pfree(keystr.data);
if (!es->analyze)
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 3d44815ed5a..d5f2cc15817 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2604,6 +2604,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
mpath->est_entries = Min(Min(ndistinct, est_cache_entries),
PG_UINT32_MAX);
+ /* Remember ndistinct for a potential EXPLAIN later */
+ mpath->est_unique_keys = ndistinct;
+
/*
* When the number of distinct parameter values is above the amount we can
* store in the cache, then we'll have to evict some entries from the
@@ -2621,6 +2624,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
hit_ratio = ((calls - ndistinct) / calls) *
(est_cache_entries / Max(ndistinct, est_cache_entries));
+ /* Remember cache hit ratio for a potential EXPLAIN later */
+ mpath->est_hit_ratio = hit_ratio;
+
Assert(hit_ratio >= 0 && hit_ratio <= 1.0);
/*
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 0b61aef962c..4f28a2d906c 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -284,7 +284,9 @@ static Material *make_material(Plan *lefttree);
static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids);
+ uint32 est_entries, Bitmapset *keyparamids,
+ double est_unique_keys, double est_hit_ratio,
+ double est_calls);
static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1703,7 +1705,9 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
plan = make_memoize(subplan, operators, collations, param_exprs,
best_path->singlerow, best_path->binary_mode,
- best_path->est_entries, keyparamids);
+ best_path->est_entries, keyparamids,
+ best_path->est_unique_keys, best_path->est_hit_ratio,
+ best_path->calls);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6699,7 +6703,9 @@ materialize_finished_plan(Plan *subplan)
static Memoize *
make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
List *param_exprs, bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids)
+ uint32 est_entries, Bitmapset *keyparamids,
+ double est_unique_keys, double est_hit_ratio,
+ double est_calls)
{
Memoize *node = makeNode(Memoize);
Plan *plan = &node->plan;
@@ -6717,6 +6723,9 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
node->binary_mode = binary_mode;
node->est_entries = est_entries;
node->keyparamids = keyparamids;
+ node->est_unique_keys = est_unique_keys;
+ node->est_hit_ratio = est_hit_ratio;
+ node->est_calls = est_calls;
return node;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index e0192d4a491..a68a32c89cf 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1701,6 +1701,18 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
Assert(enable_memoize);
pathnode->path.disabled_nodes = subpath->disabled_nodes;
+ /*
+ * Estimated number of distinct memoization keys,
+ * computed using estimate_num_groups()
+ */
+ pathnode->est_unique_keys = 0;
+
+ /*
+ * The estimated cache hit ratio will calculated later
+ * by cost_memoize_rescan().
+ */
+ pathnode->est_hit_ratio = 0;
+
/*
* Add a small additional charge for caching the first entry. All the
* harder calculations for rescans are performed in cost_memoize_rescan().
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 6567759595d..5bb7451e160 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2135,6 +2135,9 @@ typedef struct MemoizePath
uint32 est_entries; /* The maximum number of entries that the
* planner expects will fit in the cache, or 0
* if unknown */
+ double est_unique_keys; /* Estimated number of distinct memoization keys,
+ * used for cache size evaluation. Kept for EXPLAIN */
+ double est_hit_ratio; /* Estimated cache hit ratio. Kept for EXPLAIN */
} MemoizePath;
/*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 4f59e30d62d..598c0d5eb1f 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1056,6 +1056,18 @@ typedef struct Memoize
/* paramids from param_exprs */
Bitmapset *keyparamids;
+
+ /*
+ * Estimated number of distinct memoization keys,
+ * used for cache size evaluation. Kept for EXPLAIN
+ */
+ double est_unique_keys;
+
+ /* Estimated cache hit ratio. Kept for EXPLAIN */
+ double est_hit_ratio;
+
+ /* Estimated number of rescans. Kept for EXPLAIN */
+ double est_calls;
} Memoize;
/* ----------------
--
2.34.1
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-07-07 13:49 Andrei Lepikhov <[email protected]>
parent: Ilia Evdokimov <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: Andrei Lepikhov @ 2025-07-07 13:49 UTC (permalink / raw)
To: Ilia Evdokimov <[email protected]>; David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>
On 4/7/2025 10:30, Ilia Evdokimov wrote:
> On 01.05.2025 15:22, Ilia Evdokimov wrote:
>
>> I attached updated v9 patch with the suggested changes. The updated
>> line in the EXPLAIN looks like this:
>>
>> Text format:
>> Estimates: capacity=1 distinct keys=1 lookups=2 hit percent=50.00%
>>
>> Non-text format:
>> Estimated Capacity: 1
>> Estimated Distinct Lookup Keys: 1
>> Estimated Lookups: 2
>> Estimated Hit Percent: 50.00
>>
>> Any suggestions?
>
>
> I attached rebased v10 patch on 5a6c39b.
Exposing internal information about the estimation of the number of
groups in the Memoise node, shouldn't we do the same in even more vague
cases, such as IncrementalSort? For example, in [1] (see its
attachment), I observe that IncrementalSort is considerably better than
Sort, but has a larger cost. It would be helpful to understand if an
incorrect ngroups estimation causes this.
[1]
https://www.postgresql.org/message-id/[email protected]
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-07-08 13:06 Ilia Evdokimov <[email protected]>
parent: Andrei Lepikhov <[email protected]>
0 siblings, 0 replies; 49+ messages in thread
From: Ilia Evdokimov @ 2025-07-08 13:06 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; David Rowley <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>
On 07.07.2025 16:49, Andrei Lepikhov wrote:
> Exposing internal information about the estimation of the number of
> groups in the Memoise node, shouldn't we do the same in even more
> vague cases, such as IncrementalSort? For example, in [1] (see its
> attachment), I observe that IncrementalSort is considerably better
> than Sort, but has a larger cost. It would be helpful to understand if
> an incorrect ngroups estimation causes this.
LGTM.
If we still don't expose any information for nodes like IncrementalSort
that would help explain why the planner chose them, then I fully agree -
we definitely should add it.
I suggest discussing that in a separate thread.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-07-21 22:17 David Rowley <[email protected]>
parent: Ilia Evdokimov <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: David Rowley @ 2025-07-21 22:17 UTC (permalink / raw)
To: Ilia Evdokimov <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>; Andrei Lepikhov <[email protected]>
On Fri, 4 Jul 2025 at 20:30, Ilia Evdokimov
<[email protected]> wrote:
> I attached rebased v10 patch on 5a6c39b.
I've gone over this and made some cosmetic adjustments. A few
adjustments to the comments and used Cardinality rather than double
for some data types. I also moved the MemoizePath.calls field down
below est_entries so that est_entries would take up some padding
space. This saves 8 bytes of struct, and IMO, improves the logical
order of fields. I renamed "calls" to "est_calls" so it's more aligned
with the new fields being added by this patch.
The final thing which I'm not so sure about is the EXPLAIN format.
Currently it looks like:
-> Memoize (cost=0.43..0.46 rows=1 width=4) (actual
time=0.000..0.000 rows=1.00 loops=1000000)
Cache Key: t.a
Cache Mode: logical
Estimates: capacity=10010 distinct keys=10010 lookups=1000000
hit ratio=99.00%
Hits: 989999 Misses: 10001 Evictions: 0 Overflows: 0
Memory Usage: 1016kB
This format for the Estimates copies the "Buffers:" format for putting
multiple sub-values under a single heading. However, we don't seem
very consistent with this as JIT has a similar need but formats things
another way, i.e.:
JIT:
Functions: 6
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.473 ms (Deform 0.112 ms), Inlining 0.000 ms,
Optimization 0.701 ms, Emission 5.778 ms, Total 6.952 ms
We could get rid of the "Estimates" heading and just prefix each value
with "Estimated", as in:
-> Memoize (cost=0.43..0.46 rows=1 width=4) (actual
time=0.000..0.000 rows=1.00 loops=1000000)
Cache Key: t.a
Cache Mode: logical
Estimated capacity: 10010 Estimated distinct keys: 10010
Estimated lookups: 1000000 Estimated hit ratio: 99.00%
Hits: 989999 Misses: 10001 Evictions: 0 Overflows: 0
Memory Usage: 1016kB
Buffers: shared hit=30004
That removes the dilemma about which example to follow, but it's more verbose.
Does anyone have any opinions on this?
v11 patch attached.
David
Attachments:
[application/octet-stream] v11-0001-Expose-cache-hit-statistics-in-Memoize-node-EXPL.patch (10.0K, ../../CAApHDvq+uqf3MRFb+Ai3W1k3fMnrwWBLhzUAYj3_+MM8jK32fA@mail.gmail.com/2-v11-0001-Expose-cache-hit-statistics-in-Memoize-node-EXPL.patch)
download | inline diff:
From d7e35cfaa7efef534bd81ab21590e243db1aa685 Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email protected]>
Date: Fri, 4 Jul 2025 11:24:56 +0300
Subject: [PATCH v11] Expose cache hit statistics in Memoize node EXPLAIN
output.
This patch adds additional information to the Memoize node's EXPLAIN output:
estimated cache capacity, number of distinct keys, total lookups, and
cache hit percentage.
---
src/backend/commands/explain.c | 20 ++++++++++++++++++++
src/backend/optimizer/path/costsize.c | 14 ++++++++++----
src/backend/optimizer/plan/createplan.c | 13 ++++++++++---
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, 62 insertions(+), 12 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 7e2792ead71..c3c52fbe190 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3616,6 +3616,26 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
ExplainPropertyText("Cache Key", keystr.data, es);
ExplainPropertyText("Cache Mode", mstate->binary_mode ? "binary" : "logical", es);
+ if (es->costs)
+ {
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f lookups=%.0f hit percent=%.2f%%\n",
+ ((Memoize *) plan)->est_entries,
+ ((Memoize *) plan)->est_unique_keys,
+ ((Memoize *) plan)->est_calls,
+ ((Memoize *) plan)->est_hit_ratio * 100.0);
+ }
+ else
+ {
+ ExplainPropertyUInteger("Estimated Capacity", NULL, ((Memoize *) plan)->est_entries, es);
+ ExplainPropertyFloat("Estimated Distinct Lookup Keys", NULL, ((Memoize *) plan)->est_unique_keys, 0, es);
+ ExplainPropertyFloat("Estimated Lookups", NULL, ((Memoize *) plan)->est_calls, 0, es);
+ ExplainPropertyFloat("Estimated Hit Percent", NULL, ((Memoize *) plan)->est_hit_ratio * 100.0, 2, es);
+ }
+ }
+
pfree(keystr.data);
if (!es->analyze)
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 1f04a2c182c..3a60c55803c 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2572,7 +2572,7 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
Cost input_startup_cost = mpath->subpath->startup_cost;
Cost input_total_cost = mpath->subpath->total_cost;
double tuples = mpath->subpath->rows;
- double calls = mpath->calls;
+ double est_calls = mpath->est_calls;
int width = mpath->subpath->pathtarget->width;
double hash_mem_bytes;
@@ -2604,7 +2604,7 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
est_cache_entries = floor(hash_mem_bytes / est_entry_bytes);
/* estimate on the distinct number of parameter values */
- ndistinct = estimate_num_groups(root, mpath->param_exprs, calls, NULL,
+ ndistinct = estimate_num_groups(root, mpath->param_exprs, est_calls, NULL,
&estinfo);
/*
@@ -2616,7 +2616,7 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
* certainly mean a MemoizePath will never survive add_path().
*/
if ((estinfo.flags & SELFLAG_USED_DEFAULT) != 0)
- ndistinct = calls;
+ ndistinct = est_calls;
/*
* Since we've already estimated the maximum number of entries we can
@@ -2630,6 +2630,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
mpath->est_entries = Min(Min(ndistinct, est_cache_entries),
PG_UINT32_MAX);
+ /* Remember the ndistinct estimate for EXPLAIN */
+ mpath->est_unique_keys = ndistinct;
+
/*
* When the number of distinct parameter values is above the amount we can
* store in the cache, then we'll have to evict some entries from the
@@ -2644,9 +2647,12 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
* must look at how many scans are estimated in total for this node and
* how many of those scans we expect to get a cache hit.
*/
- hit_ratio = ((calls - ndistinct) / calls) *
+ hit_ratio = ((est_calls - ndistinct) / est_calls) *
(est_cache_entries / Max(ndistinct, est_cache_entries));
+ /* Remember the hit ratio estimate for EXPLAIN */
+ mpath->est_hit_ratio = hit_ratio;
+
Assert(hit_ratio >= 0 && hit_ratio <= 1.0);
/*
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 8a9f1d7a943..331b0b66a69 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -284,7 +284,9 @@ static Material *make_material(Plan *lefttree);
static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids);
+ uint32 est_entries, Bitmapset *keyparamids,
+ double est_calls, double est_unique_keys,
+ double est_hit_ratio);
static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1753,7 +1755,8 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
plan = make_memoize(subplan, operators, collations, param_exprs,
best_path->singlerow, best_path->binary_mode,
- best_path->est_entries, keyparamids);
+ best_path->est_entries, keyparamids, best_path->est_calls,
+ best_path->est_unique_keys, best_path->est_hit_ratio);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6749,7 +6752,8 @@ materialize_finished_plan(Plan *subplan)
static Memoize *
make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
List *param_exprs, bool singlerow, bool binary_mode,
- uint32 est_entries, Bitmapset *keyparamids)
+ uint32 est_entries, Bitmapset *keyparamids, double est_calls,
+ double est_unique_keys, double est_hit_ratio)
{
Memoize *node = makeNode(Memoize);
Plan *plan = &node->plan;
@@ -6767,6 +6771,9 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
node->binary_mode = binary_mode;
node->est_entries = est_entries;
node->keyparamids = keyparamids;
+ node->est_calls = est_calls;
+ node->est_unique_keys = est_unique_keys;
+ node->est_hit_ratio = est_hit_ratio;
return node;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 9cc602788ea..1812eb55821 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1689,7 +1689,7 @@ create_material_path(RelOptInfo *rel, Path *subpath)
MemoizePath *
create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
List *param_exprs, List *hash_operators,
- bool singlerow, bool binary_mode, double calls)
+ bool singlerow, bool binary_mode, double est_calls)
{
MemoizePath *pathnode = makeNode(MemoizePath);
@@ -1710,7 +1710,6 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
pathnode->param_exprs = param_exprs;
pathnode->singlerow = singlerow;
pathnode->binary_mode = binary_mode;
- pathnode->calls = clamp_row_est(calls);
/*
* For now we set est_entries to 0. cost_memoize_rescan() does all the
@@ -1720,6 +1719,12 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
*/
pathnode->est_entries = 0;
+ pathnode->est_calls = clamp_row_est(est_calls);
+
+ /* These will also be set later in cost_memoize_rescan() */
+ pathnode->est_unique_keys = 0;
+ pathnode->est_hit_ratio = 0;
+
/* we should not generate this path type when enable_memoize=false */
Assert(enable_memoize);
pathnode->path.disabled_nodes = subpath->disabled_nodes;
@@ -4259,7 +4264,7 @@ reparameterize_path(PlannerInfo *root, Path *path,
mpath->hash_operators,
mpath->singlerow,
mpath->binary_mode,
- mpath->calls);
+ mpath->est_calls);
}
default:
break;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 6567759595d..40166e22a49 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2131,10 +2131,12 @@ typedef struct MemoizePath
* complete after caching the first record. */
bool binary_mode; /* true when cache key should be compared bit
* by bit, false when using hash equality ops */
- Cardinality calls; /* expected number of rescans */
uint32 est_entries; /* The maximum number of entries that the
* planner expects will fit in the cache, or 0
* if unknown */
+ Cardinality est_calls; /* expected number of rescans */
+ Cardinality est_unique_keys; /* estimated unique keys, for EXPLAIN */
+ double est_hit_ratio; /* estimated cache hit ratio, for EXPLAIN */
} MemoizePath;
/*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 4f59e30d62d..008328577cc 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1056,6 +1056,16 @@ typedef struct Memoize
/* paramids from param_exprs */
Bitmapset *keyparamids;
+
+ /* Estimated number of rescans, for EXPLAIN */
+ Cardinality est_calls;
+
+ /* Estimated number of distinct lookup keys, for EXPLAIN */
+ Cardinality est_unique_keys;
+
+ /* Estimated cache hit ratio, for EXPLAIN */
+ double est_hit_ratio;
+
} Memoize;
/* ----------------
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 60dcdb77e41..95f98b0ba40 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -90,7 +90,7 @@ extern MemoizePath *create_memoize_path(PlannerInfo *root,
List *hash_operators,
bool singlerow,
bool binary_mode,
- double calls);
+ double est_calls);
extern UniquePath *create_unique_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath, SpecialJoinInfo *sjinfo);
extern GatherPath *create_gather_path(PlannerInfo *root,
--
2.43.0
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-07-22 14:35 Andrei Lepikhov <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: Andrei Lepikhov @ 2025-07-22 14:35 UTC (permalink / raw)
To: David Rowley <[email protected]>; Ilia Evdokimov <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>
On 22/7/2025 00:17, David Rowley wrote:
> On Fri, 4 Jul 2025 at 20:30, Ilia Evdokimov
> <[email protected]> wrote:
>> I attached rebased v10 patch on 5a6c39b.
>
> I've gone over this and made some cosmetic adjustments. A few
> adjustments to the comments and used Cardinality rather than double
> for some data types. I also moved the MemoizePath.calls field down
> below est_entries so that est_entries would take up some padding
> space. This saves 8 bytes of struct, and IMO, improves the logical
> order of fields. I renamed "calls" to "est_calls" so it's more aligned
> with the new fields being added by this patch.
Looks good
>
> That removes the dilemma about which example to follow, but it's more verbose.
The 'Buffers:' way looks more natural to me. I don't like duplicated
text in the explain format - it is already cluttered by multiple
unnecessary elements that distract attention from the problematic plan
elements, such as unplaggable costs output if we only need row
predictions, '.00' in estimations, etc.
However, at first, I'd consider how it could be added to the
IncrementalSort and HashJoin. The number of estimated groups/buckets may
also provide some insights into the planner's decision.
Will you add the ExplainOpenGroup call to the final version of the patch?
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-07-23 00:11 David Rowley <[email protected]>
parent: Andrei Lepikhov <[email protected]>
0 siblings, 2 replies; 49+ messages in thread
From: David Rowley @ 2025-07-23 00:11 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; +Cc: Ilia Evdokimov <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>
On Wed, 23 Jul 2025 at 02:35, Andrei Lepikhov <[email protected]> wrote:
> The 'Buffers:' way looks more natural to me. I don't like duplicated
> text in the explain format - it is already cluttered by multiple
> unnecessary elements that distract attention from the problematic plan
> elements, such as unplaggable costs output if we only need row
> predictions, '.00' in estimations, etc.
Seems logical.
> However, at first, I'd consider how it could be added to the
> IncrementalSort and HashJoin. The number of estimated groups/buckets may
> also provide some insights into the planner's decision.
Sounds like another patch for another thread.
> Will you add the ExplainOpenGroup call to the final version of the patch?
I'm leaning towards not doing that as a reader might expect all the
"Estimates" to be within that group, but the estimated cost and row
counts won't be in there. Maybe it's possible to circumvent that
expectation by naming the group something like "MemoizeEstimates", but
IMO, that seems excessive for 4 properties.
Do you have an argument in mind to support adding the group?
David
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-07-23 07:00 Andrei Lepikhov <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 0 replies; 49+ messages in thread
From: Andrei Lepikhov @ 2025-07-23 07:00 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Ilia Evdokimov <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>
On 23/7/2025 02:11, David Rowley wrote:
> On Wed, 23 Jul 2025 at 02:35, Andrei Lepikhov <[email protected]> wrote:
>> However, at first, I'd consider how it could be added to the
>> IncrementalSort and HashJoin. The number of estimated groups/buckets may
>> also provide some insights into the planner's decision.
>
> Sounds like another patch for another thread.
Maybe, it is up to you
> Do you have an argument in mind to support adding the group?
I have not. I just thought that grouping would be helpful for an explain
parser.
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-07-23 11:07 Ilia Evdokimov <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 1 reply; 49+ messages in thread
From: Ilia Evdokimov @ 2025-07-23 11:07 UTC (permalink / raw)
To: David Rowley <[email protected]>; Andrei Lepikhov <[email protected]>; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>
On 23.07.2025 03:11, David Rowley wrote:
> On Wed, 23 Jul 2025 at 02:35, Andrei Lepikhov <[email protected]> wrote:
>> The 'Buffers:' way looks more natural to me. I don't like duplicated
>> text in the explain format - it is already cluttered by multiple
>> unnecessary elements that distract attention from the problematic plan
>> elements, such as unplaggable costs output if we only need row
>> predictions, '.00' in estimations, etc.
> Seems logical.
+1
>
>> Will you add the ExplainOpenGroup call to the final version of the patch?
> I'm leaning towards not doing that as a reader might expect all the
> "Estimates" to be within that group, but the estimated cost and row
> counts won't be in there. Maybe it's possible to circumvent that
> expectation by naming the group something like "MemoizeEstimates", but
> IMO, that seems excessive for 4 properties.
I agree. I would consider adding a group if we displayed information in
a looped format, like for Workers, or if we had some particularly useful
data for parsers - for example, timings or memory usage. But for four
estimates, grouping seems unnesseray.
Given that, patch v11 still looks like the most appropriate version to me.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-07-29 03:25 David Rowley <[email protected]>
parent: Ilia Evdokimov <[email protected]>
0 siblings, 1 reply; 49+ messages in thread
From: David Rowley @ 2025-07-29 03:25 UTC (permalink / raw)
To: Ilia Evdokimov <[email protected]>; +Cc: Andrei Lepikhov <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Lukas Fittl <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>
On Wed, 23 Jul 2025 at 23:08, Ilia Evdokimov
<[email protected]> wrote:
> Given that, patch v11 still looks like the most appropriate version to me.
I spent a bit more time cleaning a few things up in this. With all the
complexity from the extra fields, I felt that a round of adjusting the
"double" types so they use the "Cardinality" typedef where appropriate
was in order. costsize.c isn't very good at doing that, but I didn't
see that as a reason to make the problem worse. The only other change
was in explain.c where I added a local "Memoize *" variable rather
than casting the "plan" variable to the subclass type in each usage.
I understand not everyone got what they wanted here, but I hope what's
been added is ok for the majority of people.
Thanks for working on this Ilia and Lukas.
David
^ permalink raw reply [nested|flat] 49+ messages in thread
* Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
@ 2025-07-29 03:59 Lukas Fittl <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 0 replies; 49+ messages in thread
From: Lukas Fittl @ 2025-07-29 03:59 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Ilia Evdokimov <[email protected]>; Andrei Lepikhov <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; PostgreSQL Hackers <[email protected]>; Nikolay Samokhvalov <[email protected]>
On Mon, Jul 28, 2025 at 8:26 PM David Rowley <[email protected]> wrote:
> I understand not everyone got what they wanted here, but I hope what's
> been added is ok for the majority of people.
>
For what its worth, the last version you posted (v11) looks good to me as
well, and +1 on the "Estimates:" style that puts them all on one line in
text mode.
I think this will help a lot in understanding Memoize's impact on plans
better, thanks everyone for pushing this forward!
Thanks,
Lukas
--
Lukas Fittl
^ permalink raw reply [nested|flat] 49+ messages in thread
* [PATCH] Fix quotation logic for unreserved keywords in window specifications
@ 2026-07-10 09:28 Kwangwon Seo <[email protected]>
0 siblings, 0 replies; 49+ messages in thread
From: Kwangwon Seo @ 2026-07-10 09:28 UTC (permalink / raw)
To: [email protected]
Hi hackers,
I was testing views with pg_dump/pg_restore and found a case where an
unreserved keyword is not quoted correctly.
example:
-- Just a base table
CREATE TABLE t (v int);
INSERT INTO t VALUES (1), (2);
-- Create a view
CREATE VIEW v AS
SELECT count(*) OVER w2 FROM t
WINDOW "rows" AS (PARTITION BY v), w2 AS ("rows" ORDER BY v);
But the problem is, here, that quotation is gone.
-- Problem: Quotation is disappared
SELECT pg_get_viewdef('v');
pg_get_viewdef
-------------------------------------------------------------
SELECT count(*) OVER w2 AS count +
FROM t +
WINDOW rows AS (PARTITION BY v), w2 AS (rows ORDER BY v);
(1 row)
-- If you use this for recovering the view above, You'll face this error.
SELECT count(*) OVER w2 AS count
FROM t
WINDOW rows AS (PARTITION BY v), w2 AS (rows ORDER BY v);
ERROR: syntax error at or near "ORDER"
LINE 3: WINDOW rows AS (PARTITION BY v), w2 AS (rows ORDER BY v);
This breaks pg_dump/restore: the view is lost.
As a result, pg_dump/pg_restore cannot restore the view successfully.
Fortunately, I found this comment in the gram.y:
It says:
* If we see PARTITION, RANGE, ROWS or GROUPS as the first token after the
'('
* of a window_specification, we want the assumption to be that there is
* no existing_window_name; but those keywords are unreserved and so could
* be ColIds.
I think that assumption can be broken when inheritance is used
across multiple window definitions.
I mean this: w2 AS ("rows" ORDER BY v)
Some comments for patch file:
The attached patch quotes the window name when it is a keyword
in get_rule_windowspec().
Output for ordinary window names is unchanged. The window definition itself
is a ColId so no change is needed there; only the refname is quoted.
Maybe this is a known thing on the team's side, but I think, at least,
should not break
compatibility with current tools. That is the motivation of this patch...
I'd appreciate any feedback or suggestions...!!
Best regards...
Attachments:
[text/x-patch] v1-0001-Fix-quotation-logic-for-unreserved-keywords-in-wi.patch (4.5K, ../../CAHJxwBWx_v=aWp7ZrGRFw2r_7MJdxYX2um3ZOmxTg4c_tL1qLA@mail.gmail.com/3-v1-0001-Fix-quotation-logic-for-unreserved-keywords-in-wi.patch)
download | inline diff:
From 72c0dd0ea78155daa348845fcd74b9102a404056 Mon Sep 17 00:00:00 2001
From: Kwangwon Seo <[email protected]>
Date: Fri, 10 Jul 2026 16:26:33 +0900
Subject: [PATCH v1] Fix quotation logic for unreserved keywords in window
specifications
---
src/backend/utils/adt/ruleutils.c | 30 +++++++++++++++++++++++++++-
src/test/regress/expected/window.out | 12 +++++++++++
src/test/regress/sql/window.sql | 7 +++++++
3 files changed, 48 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 819631781c0..6084b48f435 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -450,6 +450,7 @@ static void get_rule_orderby(List *orderList, List *targetList,
static void get_rule_windowclause(Query *query, deparse_context *context);
static void get_rule_windowspec(WindowClause *wc, List *targetList,
deparse_context *context);
+static void appendWindowRefName(StringInfo buf, const char *refname);
static void get_window_frame_options(int frameOptions,
Node *startOffset, Node *endOffset,
deparse_context *context);
@@ -7159,7 +7160,7 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
appendStringInfoChar(buf, '(');
if (wc->refname)
{
- appendStringInfoString(buf, quote_identifier(wc->refname));
+ appendWindowRefName(buf, wc->refname);
needspace = true;
}
/* partition clauses are always inherited, so only print if no refname */
@@ -7201,6 +7202,33 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
appendStringInfoChar(buf, ')');
}
+/*
+ * Emit the name of the window definition.
+ *
+ * PARTITION, RANGE, ROWS, and GROUPS have the same precedence as IDENT
+ * at the start of a window specification, preventing them from being
+ * recognized as an existing_window_name (see opt_existing_window_name
+ * in gram.y). Since these are unreserved keywords, quote_identifier()
+ * does not quote them, causing the generated SQL to fail when reparsed.
+ * Therefore, quote any keyword here rather than maintaining a list.
+ */
+static void
+appendWindowRefName(StringInfo buf, const char *refname)
+{
+ const char *quoted = quote_identifier(refname);
+
+ if (quoted == refname &&
+ ScanKeywordLookup(refname, &ScanKeywords) >= 0)
+ {
+ /* quote_identifier() left it bare, so it needs no escaping */
+ appendStringInfoChar(buf, '"');
+ appendStringInfoString(buf, refname);
+ appendStringInfoChar(buf, '"');
+ }
+ else
+ appendStringInfoString(buf, quoted);
+}
+
/*
* Append the description of a window's framing options to context->buf
*/
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index c0bde1c5eec..5080b415e0b 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -1361,6 +1361,18 @@ SELECT pg_get_viewdef('v_window');
FROM generate_series(now(), (now() + '@ 100 days'::interval), '@ 1 hour'::interval) i(i);
(1 row)
+-- A window name that is an unreserved keyword cannot be an existing_window_name
+CREATE TEMP VIEW v2_window_unreserved_kw AS
+ SELECT count(*) OVER w2 FROM generate_series(1, 1) s(v)
+ WINDOW "rows" AS (PARTITION BY v), w2 AS ("rows" ORDER BY v);
+SELECT pg_get_viewdef('v2_window_unreserved_kw');
+ pg_get_viewdef
+---------------------------------------------------------------
+ SELECT count(*) OVER w2 AS count +
+ FROM generate_series(1, 1) s(v) +
+ WINDOW rows AS (PARTITION BY v), w2 AS ("rows" ORDER BY v);
+(1 row)
+
-- test overflow frame specifications
SELECT sum(unique1) over (rows between current row and 9223372036854775807 following exclude current row),
unique1, four
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 8e6f92d94c7..396b95d0d39 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -330,6 +330,13 @@ CREATE TEMP VIEW v_window AS
SELECT pg_get_viewdef('v_window');
+-- A window name that is an unreserved keyword cannot be an existing_window_name
+CREATE TEMP VIEW v2_window_unreserved_kw AS
+ SELECT count(*) OVER w2 FROM generate_series(1, 1) s(v)
+ WINDOW "rows" AS (PARTITION BY v), w2 AS ("rows" ORDER BY v);
+
+SELECT pg_get_viewdef('v2_window_unreserved_kw');
+
-- test overflow frame specifications
SELECT sum(unique1) over (rows between current row and 9223372036854775807 following exclude current row),
unique1, four
--
2.52.0
^ permalink raw reply [nested|flat] 49+ messages in thread
end of thread, other threads:[~2026-07-10 09:28 UTC | newest]
Thread overview: 49+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-13 06:50 ` newtglobal postgresql_contributors <[email protected]>
2025-03-26 19:04 ` Kirill Reshke <[email protected]>
2025-03-29 06:46 ` jian he <[email protected]>
2025-03-29 12:38 ` Kirill Reshke <[email protected]>
2025-03-29 14:40 ` David G. Johnston <[email protected]>
2025-03-29 16:06 ` Andrew Dunstan <[email protected]>
2025-03-29 16:17 ` David G. Johnston <[email protected]>
2025-03-29 18:56 ` Andrew Dunstan <[email protected]>
2025-03-29 18:58 ` David G. Johnston <[email protected]>
2025-03-29 19:27 ` Kirill Reshke <[email protected]>
2025-03-29 19:49 ` David G. Johnston <[email protected]>
2025-03-29 20:01 ` Andrew Dunstan <[email protected]>
2025-03-31 15:26 ` Fujii Masao <[email protected]>
2025-04-01 03:50 ` David G. Johnston <[email protected]>
2025-04-01 09:45 ` vignesh C <[email protected]>
2025-04-01 10:18 ` Kirill Reshke <[email protected]>
2025-04-01 13:52 ` vignesh C <[email protected]>
2025-04-01 14:14 ` Kirill Reshke <[email protected]>
2025-04-01 14:27 ` David G. Johnston <[email protected]>
2025-04-01 21:06 Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment David Rowley <[email protected]>
2025-04-02 09:35 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Ilia Evdokimov <[email protected]>
2025-04-14 09:02 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Ilia Evdokimov <[email protected]>
2025-04-14 16:31 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Robert Haas <[email protected]>
2025-04-14 19:49 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Andrei Lepikhov <[email protected]>
2025-04-14 20:53 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment David Rowley <[email protected]>
2025-04-14 23:14 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Ilia Evdokimov <[email protected]>
2025-04-15 00:23 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment David Rowley <[email protected]>
2025-04-15 09:44 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Ilia Evdokimov <[email protected]>
2025-04-15 20:20 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment David Rowley <[email protected]>
2025-04-15 22:09 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Ilia Evdokimov <[email protected]>
2025-04-17 21:29 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment David Rowley <[email protected]>
2025-04-27 19:58 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Ilia Evdokimov <[email protected]>
2025-05-01 12:22 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Ilia Evdokimov <[email protected]>
2025-05-01 13:37 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Andrei Lepikhov <[email protected]>
2025-05-01 19:10 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Ilia Evdokimov <[email protected]>
2025-07-04 08:30 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Ilia Evdokimov <[email protected]>
2025-07-07 13:49 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Andrei Lepikhov <[email protected]>
2025-07-08 13:06 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Ilia Evdokimov <[email protected]>
2025-07-21 22:17 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment David Rowley <[email protected]>
2025-07-22 14:35 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Andrei Lepikhov <[email protected]>
2025-07-23 00:11 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment David Rowley <[email protected]>
2025-07-23 07:00 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Andrei Lepikhov <[email protected]>
2025-07-23 11:07 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Ilia Evdokimov <[email protected]>
2025-07-29 03:25 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment David Rowley <[email protected]>
2025-07-29 03:59 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Lukas Fittl <[email protected]>
2025-04-15 16:24 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Robert Haas <[email protected]>
2025-04-15 20:49 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment David Rowley <[email protected]>
2025-04-16 16:09 ` Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment Maciek Sakrejda <[email protected]>
2026-07-10 09:28 [PATCH] Fix quotation logic for unreserved keywords in window specifications Kwangwon Seo <[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