3 ms·

I can't speak to Parent's situation, but temp tables are common practice in OLAP workloads across many database engines (personal experience with Postgres, Pres

I can't speak to Parent's situation, but temp tables are common practice in OLAP workloads across many database engines (personal experience with Postgres, Presto, Spark, Netezza). At a certain level of query complexity, no query planner is able to accurately predict the characteristics of sub-queries or CTE, resulting in query plans that are ill-suited to the problem. More often than not, materializing CTEs inside a large query into 2-3 temporary tables results in order-magnitude performance as the database now knows exactly how many rows its dealing with, stats on # of nulls, etc.

avianlyric 4y ago
Certainly don’t disagree, I’m well acquainted with the limits of the query planner. Especially when dealing with high complexity queries. My broader point is that CTEs in Postgres are more nuanced than they appear at first blush. They’re frequently presented as simply a method of writing cleaner, more readable SQL. The fact that CTEs receive special treatment by the planner is often missed, along with the fact that two functionally identical queries, one using sub-queries, the other CTEs, can have wildly different performance characteristics.