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-sqlWhen 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-sqlWhen 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-sqlI'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
