Surprising behavior with pushing predicates down into a view
Hey guys,
Postgres version: PostgreSQL 18.2 on x86_64-pc-linux-musl, compiled by gcc
(Alpine 15.2.0) 15.2.0, 64-bit
I've been toying around with some financial data and I ran into a
surprising result with passing qualifiers down into views that may or may
not be intentional on postgres' part. I'm hoping you guys can help
clarify...
Here's the view. It attempts to calculate rolling 1 year daily beta across
all stock quotes in my table. The design intention here was to define the
"how" and leave it to the consumer of the view to define the "what" by
filtering down to the tickers and date ranges they care about:
https://gist.github.com/JustinChristensen/1cad256ea61ad30aa39d42aebc4557b9#file-view-sql
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:
https://gist.github.com/JustinChristensen/1cad256ea61ad30aa39d42aebc4557b9#file-subquery-filter-sql
I'm no expert... My expectation here was that it would be able to apply the
filtering first regardless of the form the ticker predicate in the outer
query takes.
Is this a bug? Working as intended? Something that could be improved but we
haven't gotten around to it yet? Sorry if this is a duplicate of something
you're already tracking.
Thanks,
Justin Christensen
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'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
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 AM David Rowley <dgrowleyml@gmail.com> wrote:
Show quoted text
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'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
Justin Christensen <justin.christensen89@gmail.com> writes:
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."
Actually the relevant comment is this one:
* 1. rinfo's clause must not contain any SubPlans (mainly because it's
* unclear that it will work correctly: SubLinks will already have been
* transformed into SubPlans in the qual, but not in the subquery).
Somebody who was sufficiently determined could probably make that
happen, but it's very unclear how much code and messiness would be
required. Another potential path is to make SubLink->SubPlan
transformations happen later, after this step --- but I think that
would metastasize into a lot of places, and possibly have some
downsides in plan quality.
Short answer is that we might well end up rejecting such a patch even
if it got written. This limitation is decades old, and there have not
been that many complaints, so I doubt we'd accept any large increase
in complexity to remove it.
regards, tom lane