MIME-Version: 1.0
References: <CAAiQw3yBPrCw6ZLeTwVS4QhKDWgJkmmp9LnGPdodxeQmn=kqVg@mail.gmail.com>
 <CAApHDvoWuMGh4z+=vY_gh-Oyh_9ZXWJnhO2JY+f+h4s9PL88Jg@mail.gmail.com>
In-Reply-To: <CAApHDvoWuMGh4z+=vY_gh-Oyh_9ZXWJnhO2JY+f+h4s9PL88Jg@mail.gmail.com>
From: Jacob Jackson <jej.jackson.08@gmail.com>
Date: Wed, 29 Oct 2025 20:09:14 -0400
Message-ID: <CAAiQw3w-uEZkz13Y2iU76CucRXBoR=kw6wOfq6gocT63dZLfYg@mail.gmail.com>
Subject: Re: Postgres memoizing inner loop of join when outer loop join key is
 guaranteed unique?
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000d36ef80642550f0c"
Archived-At: <https://www.postgresql.org/message-id/CAAiQw3w-uEZkz13Y2iU76CucRXBoR%3Dkw6wOfq6gocT63dZLfYg%40mail.gmail.com>
Precedence: bulk

--000000000000d36ef80642550f0c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thanks! This is very helpful.

On Wed, Oct 29, 2025 at 8:01=E2=80=AFPM David Rowley <dgrowleyml@gmail.com>=
 wrote:

> On Thu, 30 Oct 2025 at 07:29, Jacob Jackson <jej.jackson.08@gmail.com>
> wrote:
> > Hello. I was looking at some query plans recently and noticed something
> that didn't make sense. I have a query that joins a table of questions wi=
th
> results for each question (using a table with a composite primary key of
> question id and a user id), filtered by user id. The question IDs and the
> combined question-userIds are guaranteed unique due to being primary keys=
,
> and yet Postgres still memoizes the inner loop results. Any ideas why?
>
> I agree that when the outer side of the join has unique values that
> Memoize does not make any sense. The planner currenrly puts quite a
> bit of faith in the row estimates for this and if you're getting this
> plan, then the estimates came back indicating there'd be fewer unique
> values of "QuestionUserStatus".question in the input than there are
> input rows to the Memoize node. If you delve into
> cost_memoize_rescan(), you'll see the code for this (look for where
> "hit_ratio" is calculated).
>
> There are also a few prechecks in get_memoize_path() to try to avoid
> this sort of thing, but unfortunately, the information to avoid
> Memoize when the outer side of the join is unique isn't available.  We
> do have an "inner_unique" in JoinPathExtraData, but what we'd need for
> this and don't have is "outer_unique". If we had that, we could just
> exit early in get_memoize_path() if that's set to true. Whether or not
> going to the trouble of calculating "outer_unique" is worth the
> trouble, I'm not sure. There was some work on UniqueKeys a few years
> ago, which could have helped in this scenario as we could have more
> easily identified uniqueness at different join levels. That's no
> longer being worked on, as I understand it.
>
> On the other hand, it may be better to somehow enhance
> estimate_num_groups() so it can be given more details about the
> context of the request, i.e the set of Relids that are joined already
> for the input_rows. That way the code could do more analysis into the
> RelOptInfo base quals for the relevant relations. Extended statistics
> for n_distinct could also be applied in some cases too by looking for
> baserestrictinfo with equality quals or EquivalenceClasses with
> ec_has_const =3D true and a member for other Vars/Exprs in the extended
> statistics.
>
> Unfortunately, neither of these is a trivial fix.
>
> David
>

--000000000000d36ef80642550f0c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thanks! This is very helpful.</div><br><div class=3D"gmail=
_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed,=
 Oct 29, 2025 at 8:01=E2=80=AFPM David Rowley &lt;<a href=3D"mailto:dgrowle=
yml@gmail.com">dgrowleyml@gmail.com</a>&gt; wrote:<br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex">On Thu, 30 Oct 2025 at 07:29, Jacob Jack=
son &lt;<a href=3D"mailto:jej.jackson.08@gmail.com" target=3D"_blank">jej.j=
ackson.08@gmail.com</a>&gt; wrote:<br>
&gt; Hello. I was looking at some query plans recently and noticed somethin=
g that didn&#39;t make sense. I have a query that joins a table of question=
s with results for each question (using a table with a composite primary ke=
y of question id and a user id), filtered by user id. The question IDs and =
the combined question-userIds are guaranteed unique due to being primary ke=
ys, and yet Postgres still memoizes the inner loop results. Any ideas why?<=
br>
<br>
I agree that when the outer side of the join has unique values that<br>
Memoize does not make any sense. The planner currenrly puts quite a<br>
bit of faith in the row estimates for this and if you&#39;re getting this<b=
r>
plan, then the estimates came back indicating there&#39;d be fewer unique<b=
r>
values of &quot;QuestionUserStatus&quot;.question in the input than there a=
re<br>
input rows to the Memoize node. If you delve into<br>
cost_memoize_rescan(), you&#39;ll see the code for this (look for where<br>
&quot;hit_ratio&quot; is calculated).<br>
<br>
There are also a few prechecks in get_memoize_path() to try to avoid<br>
this sort of thing, but unfortunately, the information to avoid<br>
Memoize when the outer side of the join is unique isn&#39;t available.=C2=
=A0 We<br>
do have an &quot;inner_unique&quot; in JoinPathExtraData, but what we&#39;d=
 need for<br>
this and don&#39;t have is &quot;outer_unique&quot;. If we had that, we cou=
ld just<br>
exit early in get_memoize_path() if that&#39;s set to true. Whether or not<=
br>
going to the trouble of calculating &quot;outer_unique&quot; is worth the<b=
r>
trouble, I&#39;m not sure. There was some work on UniqueKeys a few years<br=
>
ago, which could have helped in this scenario as we could have more<br>
easily identified uniqueness at different join levels. That&#39;s no<br>
longer being worked on, as I understand it.<br>
<br>
On the other hand, it may be better to somehow enhance<br>
estimate_num_groups() so it can be given more details about the<br>
context of the request, i.e the set of Relids that are joined already<br>
for the input_rows. That way the code could do more analysis into the<br>
RelOptInfo base quals for the relevant relations. Extended statistics<br>
for n_distinct could also be applied in some cases too by looking for<br>
baserestrictinfo with equality quals or EquivalenceClasses with<br>
ec_has_const =3D true and a member for other Vars/Exprs in the extended<br>
statistics.<br>
<br>
Unfortunately, neither of these is a trivial fix.<br>
<br>
David<br>
</blockquote></div>

--000000000000d36ef80642550f0c--