> What you hope happens is that Postgres will recursively inline every view, merge them all together into a gigaquery, and shuffle the predicates up and down to maximally optimize the query. That was the conceit. And of course that never happens.
The author seems to be describing a Progress-specific problem. Oracle, SQL Server (and probably other "enterprise" databases as well) are pretty good at inlining views and then optimizing the whole resulting gigaquery as if the views were not there in the first place.
> The central problem is that views have to be written for the general case, and then you filter on the view’s output. Views can’t take parameters.
SQL Server supports inline table-valued functions, which are exactly that - parametrized and inlinable views.
Unfortunately, they are not as composable as they could be. For example, a function returning table and a function accepting the same type of a table as input cannot be directly composed - you'd first need to store the first function's result in a temporary table or table variable. But despite the limitations, functions are extremely useful for constructing complex queries which reuse logic.
Oracle, MSSQL, DB2, et. al., are essentially like magic when it comes to making hopeless piles of SQL run quickly. This is because these engines have all ostensibly targeted the business people over the decades more than the tech people.
Features like dynamically optimizing queries as they return results [0,1,2] isn't something you chase if your audience is willing to rewrite the universe at the first sign of incongruity.
I mentioned this down thread, MSSQL and Oracle cache query plans allow their optimizers to take their time which I imagine helps with doing more sophisticated optimization.
PG always re optimizes on every call unless you manually prepare the statement and that only last for the session. So PG's optimizer is designed to be very fast not very thorough.
> PG's optimizer is designed to be very fast not very thorough
Some parameters weight on this policy: geqo_threshold, geqo_effort, constraint_exclusion, from_collapse_limit, join_collapse_limit, jit_above_cost, enable_partition*...
As requiring heavy query optimization usually slows simple queries down, this is a matter of context.
> What you hope happens is that Postgres will recursively inline every view, merge them all together into a gigaquery, and shuffle the predicates up and down to maximally optimize the query. That was the conceit. And of course that never happens.
The author seems to be describing a Progress-specific problem. Oracle, SQL Server (and probably other "enterprise" databases as well) are pretty good at inlining views and then optimizing the whole resulting gigaquery as if the views were not there in the first place.
> The central problem is that views have to be written for the general case, and then you filter on the view’s output. Views can’t take parameters.
SQL Server supports inline table-valued functions, which are exactly that - parametrized and inlinable views.
Unfortunately, they are not as composable as they could be. For example, a function returning table and a function accepting the same type of a table as input cannot be directly composed - you'd first need to store the first function's result in a temporary table or table variable. But despite the limitations, functions are extremely useful for constructing complex queries which reuse logic.