public inbox for [email protected]help / color / mirror / Atom feed
Re: Surprising behavior with pushing predicates down into a view 3+ messages / 3 participants [nested] [flat]
* Re: Surprising behavior with pushing predicates down into a view @ 2026-03-31 12:44 David Rowley <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: David Rowley @ 2026-03-31 12:44 UTC (permalink / raw) To: Justin Christensen <[email protected]>; +Cc: [email protected] 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Surprising behavior with pushing predicates down into a view @ 2026-03-31 16:00 Justin Christensen <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Justin Christensen @ 2026-03-31 16:00 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: [email protected] 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 <[email protected]> wrote: > 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 > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Surprising behavior with pushing predicates down into a view @ 2026-03-31 17:54 Tom Lane <[email protected]> parent: Justin Christensen <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2026-03-31 17:54 UTC (permalink / raw) To: Justin Christensen <[email protected]>; +Cc: David Rowley <[email protected]>; [email protected] Justin Christensen <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-03-31 17:54 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-03-31 12:44 Re: Surprising behavior with pushing predicates down into a view David Rowley <[email protected]> 2026-03-31 16:00 ` Justin Christensen <[email protected]> 2026-03-31 17:54 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox