The article talks about how CTEs are efficiency minefields and how Postgres doesn't do a good job of pushing down predicates, for example.
TBH, the solution is to improve the Postgres engine in this regard?
For example, in Bigquery I often use CTEs and see predictable speedups that are explained by predicate pushdown when slapping a WHERE clause on the user of a CTE etc. Bigquery has plenty of performance gotchas but I don't think CTEs are one of them; they seem to be transparent to the optimiser.
Something else that Bigquery supports is user-defined 'table' functions. These are like the 'functors' the article creates.
However, in practice they aren't very widely used, and I think this hints that they aren't as useful as the article is hoping for.
The thing I _really_ want is scoping. If only functions could be described like CTE WITH blocks, e.g.
WITH visits AS (
SELECT ...
FROM web_logs
WHERE ...
),
foo(bar INT, baz STRING) AS (
... function body here
)
SELECT ..., foo(a, b), ...
FROM visits
WHERE ...
Apart from the tidiness of this, this allows inlining of functions into the SQL statement body rather than as separate statements. Often various tooling e.g. DBT makes it really hard to with separate function definitions etc, and being able to inline logic would make things possible.
Bigquery also supports user-defined aggregate functions. Although there is rarely the need to stray from the standard builtin aggregate functions, when you need them they are brilliant.
(Anyone working _on_ Bigquery reading? If so, please ping me, I have loads of feedback on what works well and where the user pain and toil is ;) )
This suffers from the "sufficiently smart compiler" problem. The query planner that can do what I mean with maximal efficiency is always just over the horizon. There's always yet another query that can't be optimized automatically.
One significant difference between PG and MSSQL is MSSQL caches query plans allowing its optimizer to be much more sophisticated and slower.
PG re-optmizes every query over and over unless you manually do a prepared statement and that only last for the session its prepared on. Therefore it's very important the optimizer not take much time before execution.
> PG re-optmizes every query over and over unless you manually do a prepared statement and that only last for the session its prepared on.
But you really should using at least query builder that does the prepared statement thing under the hood, and also because writing dynamic queries with string concatenation sucks.
Parameterized sql is a good thing regardless of plan caching, but it also helps with systems that do plan caching.
That the client must explicitly prepare the statement and the preparation is tied to the connection is pretty clunky and still means the statement must be replanned for each separate connection.
Also since it is assumed by the devs of PG that prepared statements are not the norm they seem to avoid making the optimizer take its time and shoot for a fast optimizer rather than the fastest query.
DB's like MSSQL and Oracle concern themselves much less with how long it takes to optimize since repetitive queries will all use the same plan automatically it also allows interesting features like saving and loading plans from different systems and freezing them so they don't all the sudden go bad in production, but those systems also supports hints unlike PG.
TBH, the solution is to improve the Postgres engine in this regard?
For example, in Bigquery I often use CTEs and see predictable speedups that are explained by predicate pushdown when slapping a WHERE clause on the user of a CTE etc. Bigquery has plenty of performance gotchas but I don't think CTEs are one of them; they seem to be transparent to the optimiser.
Something else that Bigquery supports is user-defined 'table' functions. These are like the 'functors' the article creates.
However, in practice they aren't very widely used, and I think this hints that they aren't as useful as the article is hoping for.
The thing I _really_ want is scoping. If only functions could be described like CTE WITH blocks, e.g.
Apart from the tidiness of this, this allows inlining of functions into the SQL statement body rather than as separate statements. Often various tooling e.g. DBT makes it really hard to with separate function definitions etc, and being able to inline logic would make things possible.Bigquery also supports user-defined aggregate functions. Although there is rarely the need to stray from the standard builtin aggregate functions, when you need them they are brilliant.
(Anyone working _on_ Bigquery reading? If so, please ping me, I have loads of feedback on what works well and where the user pain and toil is ;) )