public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Justin Christensen <[email protected]>
Cc: [email protected]
Subject: Re: Surprising behavior with pushing predicates down into a view
Date: Wed, 1 Apr 2026 01:44:41 +1300
Message-ID: <CAApHDvpCn2t07ag0Bh+P66pGMJj=u97ROBetTHyj71byMvcYAg@mail.gmail.com> (raw)
In-Reply-To: <CAH+MXdxJusXfQoyzF3x8U=nh+QxmfiqGxZ1kPYXTfd8Srx5ZHg@mail.gmail.com>
References: <CAH+MXdxJusXfQoyzF3x8U=nh+QxmfiqGxZ1kPYXTfd8Srx5ZHg@mail.gmail.com>

On Tue, 31 Mar 2026 at 22:45, Justin Christensen
<[email protected]> 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've included the EXPLAIN output after the query in this gist.
>
> https://gist.github.com/JustinChristensen/1cad256ea61ad30aa39d42aebc4557b9#file-literal-filter-sql
>
> 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 the 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 = '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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Surprising behavior with pushing predicates down into a view
  In-Reply-To: <CAApHDvpCn2t07ag0Bh+P66pGMJj=u97ROBetTHyj71byMvcYAg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox