MIME-Version: 1.0
References: 
 <CAMbWs49rWYrecgreDhKsfx3VSDW=qo35s+iAmgGu=wpARrM8_g@mail.gmail.com>
 <CAGjGUAK7cmV3u6YE3OBQCuhDh6hrhwY=meaEch0VDT3Kn32WDw@mail.gmail.com>
 <CAMbWs4-Nib006vz1y9i5xE-0of9c-27oGyVS88uZgmOmPqST5w@mail.gmail.com>
In-Reply-To: 
 <CAMbWs4-Nib006vz1y9i5xE-0of9c-27oGyVS88uZgmOmPqST5w@mail.gmail.com>
From: wenhui qiu <qiuwenhuifx@gmail.com>
Date: Tue, 14 Apr 2026 15:58:51 +0800
Message-ID: 
 <CAGjGUALqgfapfAy+i7mYSq9+dOhg_4sf6C4bUhbM1+-uQqyoPw@mail.gmail.com>
Subject: Re: Propagate stadistinct through GROUP BY/DISTINCT in subqueries and
 CTEs
To: Richard Guo <guofenglinux@gmail.com>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000009b2d8064f66f99a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGjGUALqgfapfAy%2Bi7mYSq9%2BdOhg_4sf6C4bUhbM1%2B-uQqyoPw%40mail.gmail.com>
Precedence: bulk

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

Hi Richard
> I don't think your proposed change would work.  vardata->rel is the
> CTE/subquery scan rel in the outer query, and its tuples count is the
> CTE's output row count, not the base table's.  Using it would be
> equivalent to not converting at all, since get_variable_numdistinct()
> already computes -stadistinct * vardata->rel->tuples.  What we need
> here is the base table's rel in the subroot, which gives us the
> correct rowcount for interpreting the negative fraction.
Thank you for your explanation. The path LGTM


Thanks

On Tue, Apr 14, 2026 at 2:11=E2=80=AFPM Richard Guo <guofenglinux@gmail.com=
> wrote:

> On Mon, Apr 13, 2026 at 12:27=E2=80=AFPM wenhui qiu <qiuwenhuifx@gmail.co=
m> wrote:
>
> > Thanks so much for working on this! While looking at the negative
> stadistinct conversion, I was wondering if we might run into a potential
> edge case with multi-level nested subqueries. What do you think?
> >
> > /* Convert negative stadistinct to absolute count */
> >
> >     if (stats->stadistinct < 0)
> >     {
> > -       RelOptInfo *baserel =3D find_base_rel(subroot, var->varno);
> > +       RelOptInfo *baserel =3D vardata->rel;
> >
> > -       if (baserel->tuples > 0)
> > +       if (baserel && baserel->tuples > 0)
> >         {
> >             stats->stadistinct =3D (float4)
> >                 clamp_row_est(-stats->stadistinct * baserel->tuples);
> >         }
> >     }
>
> I don't think your proposed change would work.  vardata->rel is the
> CTE/subquery scan rel in the outer query, and its tuples count is the
> CTE's output row count, not the base table's.  Using it would be
> equivalent to not converting at all, since get_variable_numdistinct()
> already computes -stadistinct * vardata->rel->tuples.  What we need
> here is the base table's rel in the subroot, which gives us the
> correct rowcount for interpreting the negative fraction.
>
> - Richard
>

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

<div dir=3D"ltr">Hi Richard<div>&gt; I don&#39;t think your proposed change=
 would work.=C2=A0 vardata-&gt;rel is the<br>&gt; CTE/subquery scan rel in =
the outer query, and its tuples count is the<br>&gt; CTE&#39;s output row c=
ount, not the base table&#39;s.=C2=A0 Using it would be<br>&gt; equivalent =
to not converting at all, since get_variable_numdistinct()<br>&gt; already =
computes -stadistinct * vardata-&gt;rel-&gt;tuples.=C2=A0 What we need<br>&=
gt; here is the base table&#39;s rel in the subroot, which gives us the<br>=
&gt; correct rowcount for interpreting the negative fraction.<br></div><div=
>Thank you for your explanation. The path LGTM</div><div><br></div><div><br=
></div><div>Thanks</div></div><br><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Apr 14, 2026 at 2:11=
=E2=80=AFPM Richard Guo &lt;<a href=3D"mailto:guofenglinux@gmail.com">guofe=
nglinux@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex">On Mon, Apr 13, 2026 at 12:27=E2=80=AFPM wenhui qiu &lt;<a=
 href=3D"mailto:qiuwenhuifx@gmail.com" target=3D"_blank">qiuwenhuifx@gmail.=
com</a>&gt; wrote:<br>
<br>
&gt; Thanks so much for working on this! While looking at the negative stad=
istinct conversion, I was wondering if we might run into a potential edge c=
ase with multi-level nested subqueries. What do you think?<br>
&gt;<br>
&gt; /* Convert negative stadistinct to absolute count */<br>
&gt;<br>
&gt;=C2=A0 =C2=A0 =C2=A0if (stats-&gt;stadistinct &lt; 0)<br>
&gt;=C2=A0 =C2=A0 =C2=A0{<br>
&gt; -=C2=A0 =C2=A0 =C2=A0 =C2=A0RelOptInfo *baserel =3D find_base_rel(subr=
oot, var-&gt;varno);<br>
&gt; +=C2=A0 =C2=A0 =C2=A0 =C2=A0RelOptInfo *baserel =3D vardata-&gt;rel;<b=
r>
&gt;<br>
&gt; -=C2=A0 =C2=A0 =C2=A0 =C2=A0if (baserel-&gt;tuples &gt; 0)<br>
&gt; +=C2=A0 =C2=A0 =C2=A0 =C2=A0if (baserel &amp;&amp; baserel-&gt;tuples =
&gt; 0)<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0{<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stats-&gt;stadistinct =
=3D (float4)<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0clamp_row=
_est(-stats-&gt;stadistinct * baserel-&gt;tuples);<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0}<br>
&gt;=C2=A0 =C2=A0 =C2=A0}<br>
<br>
I don&#39;t think your proposed change would work.=C2=A0 vardata-&gt;rel is=
 the<br>
CTE/subquery scan rel in the outer query, and its tuples count is the<br>
CTE&#39;s output row count, not the base table&#39;s.=C2=A0 Using it would =
be<br>
equivalent to not converting at all, since get_variable_numdistinct()<br>
already computes -stadistinct * vardata-&gt;rel-&gt;tuples.=C2=A0 What we n=
eed<br>
here is the base table&#39;s rel in the subroot, which gives us the<br>
correct rowcount for interpreting the negative fraction.<br>
<br>
- Richard<br>
</blockquote></div>

--00000000000009b2d8064f66f99a--