MIME-Version: 1.0
References: <CAH+MXdxJusXfQoyzF3x8U=nh+QxmfiqGxZ1kPYXTfd8Srx5ZHg@mail.gmail.com>
 <CAApHDvpCn2t07ag0Bh+P66pGMJj=u97ROBetTHyj71byMvcYAg@mail.gmail.com>
In-Reply-To: <CAApHDvpCn2t07ag0Bh+P66pGMJj=u97ROBetTHyj71byMvcYAg@mail.gmail.com>
From: Justin Christensen <justin.christensen89@gmail.com>
Date: Tue, 31 Mar 2026 11:00:09 -0500
Message-ID: <CAH+MXdy3JJjtH1=AaWO2JcF1SHJk3v2AvnhSPyr4xfWB8cORFA@mail.gmail.com>
Subject: Re: Surprising behavior with pushing predicates down into a view
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000007ae1e7064e541058"
Archived-At: <https://www.postgresql.org/message-id/CAH%2BMXdy3JJjtH1%3DAaWO2JcF1SHJk3v2AvnhSPyr4xfWB8cORFA%40mail.gmail.com>
Precedence: bulk

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

Ah. I did happen to skim the postgres source and I did see that subplans
were being rejected in qual_is_pushdown_safe before I sent this over, and I
noticed a few comments that made it seem like that was a design choice at
the time that could stand to be revisited later: "XXX that could stand to
be reconsidered, now that we use Paths."

Is this a potentially desirable enhancement for it to work the way I
expected or is it an explicit design choice for it to remain as-is? Correct
me if I'm wrong, but it theoretically could be pushed down into the sub
query without affecting correctness, and it's just a question of effort and
query planning efficiency? Are we just saying the juice is not worth the
squeeze and making this change could affect stability?

On Tue, Mar 31, 2026 at 7:44=E2=80=AFAM David Rowley <dgrowleyml@gmail.com>=
 wrote:

> On Tue, 31 Mar 2026 at 22:45, Justin Christensen
> <justin.christensen89@gmail.com> wrote:
> > When I query this view using a simple constant ticker like 'AAPL' the
> query plan shows that it correctly filters the set of tickers before
> applying the return calculations, joining, and then calculating beta. I'v=
e
> included the EXPLAIN output after the query in this gist.
> >
> >
> https://gist.github.com/JustinChristensen/1cad256ea61ad30aa39d42aebc4557b=
9#file-literal-filter-sql
> >
> > When I query this view using a subquery to find the tickers to filter o=
n
> it instead tries to execute the view and calculate the beta for all of th=
e
> tickers in the table before filtering:
>
> In short, this isn't a bug.
>
> When planning subqueries, which is effectively how a VIEW will be
> planned unless it passed is_simple_subquery()'s tests, we only
> consider pushing down "base" quals into that subquery. When you do
> ticker =3D 'AAPL', that's a base qual, and that can be pushed down
> because ticker is in the WINDOW's PARTITION BY clause, but ticker IN
> (SELECT ticker FROM metrics ORDER BY random() LIMIT 50) is converted
> into a SEMI join much earlier in planning, so that isn't a base qual
> anymore. Even if we didn't do that tranformation in
> convert_ANY_sublink_to_join(), we'd still fail to push down the base
> qual into the subquery as qual_is_pushdown_safe() doesn't accept base
> quals with subplans.
>
> You might be better off changing the view to a table returning
> function which accepts a ticker parameter and calling that function
> once for each ticker you need.
>
> David
>

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

<div dir=3D"ltr"><div>Ah. I did happen to skim the postgres source and I di=
d see that subplans were being rejected in qual_is_pushdown_safe before I s=
ent this over, and I noticed a few comments that made it seem like that was=
 a design choice at the time that could stand to be revisited later: &quot;=
XXX that could stand to be reconsidered, now that we use Paths.&quot;<br><b=
r></div>Is this a potentially desirable enhancement for it to work the way =
I expected or is it an explicit design choice for it to remain as-is? Corre=
ct me if I&#39;m wrong, but it theoretically could be pushed down into the =
sub query without affecting correctness, and it&#39;s just a question of ef=
fort and query planning efficiency? Are we just saying the juice is not wor=
th the squeeze and making this change could affect stability?</div><br><div=
 class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmai=
l_attr">On Tue, Mar 31, 2026 at 7:44=E2=80=AFAM David Rowley &lt;<a href=3D=
"mailto:dgrowleyml@gmail.com">dgrowleyml@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);padding-left:1ex">On Tue, 31 Mar 2026 at 22=
:45, Justin Christensen<br>
&lt;<a href=3D"mailto:justin.christensen89@gmail.com" target=3D"_blank">jus=
tin.christensen89@gmail.com</a>&gt; wrote:<br>
&gt; When I query this view using a simple constant ticker like &#39;AAPL&#=
39; the query plan shows that it correctly filters the set of tickers befor=
e applying the return calculations, joining, and then calculating beta. I&#=
39;ve included the EXPLAIN output after the query in this gist.<br>
&gt;<br>
&gt; <a href=3D"https://gist.github.com/JustinChristensen/1cad256ea61ad30aa=
39d42aebc4557b9#file-literal-filter-sql" rel=3D"noreferrer" target=3D"_blan=
k">https://gist.github.com/JustinChristensen/1cad256ea61ad30aa39d42aebc4557=
b9#file-literal-filter-sql</a><br>
&gt;<br>
&gt; When I query this view using a subquery to find the tickers to filter =
on it instead tries to execute the view and calculate the beta for all of t=
he tickers in the table before filtering:<br>
<br>
In short, this isn&#39;t a bug.<br>
<br>
When planning subqueries, which is effectively how a VIEW will be<br>
planned unless it passed is_simple_subquery()&#39;s tests, we only<br>
consider pushing down &quot;base&quot; quals into that subquery. When you d=
o<br>
ticker =3D &#39;AAPL&#39;, that&#39;s a base qual, and that can be pushed d=
own<br>
because ticker is in the WINDOW&#39;s PARTITION BY clause, but ticker IN<br=
>
(SELECT ticker FROM metrics ORDER BY random() LIMIT 50) is converted<br>
into a SEMI join much earlier in planning, so that isn&#39;t a base qual<br=
>
anymore. Even if we didn&#39;t do that tranformation in<br>
convert_ANY_sublink_to_join(), we&#39;d still fail to push down the base<br=
>
qual into the subquery as qual_is_pushdown_safe() doesn&#39;t accept base<b=
r>
quals with subplans.<br>
<br>
You might be better off changing the view to a table returning<br>
function which accepts a ticker parameter and calling that function<br>
once for each ticker you need.<br>
<br>
David<br>
</blockquote></div>

--0000000000007ae1e7064e541058--