4 ms·

I've been using these for some larger analytical queries that run in batch fashion (typically something that's done like once or couple of times per day). I fi

I've been using these for some larger analytical queries that run in batch fashion (typically something that's done like once or couple of times per day). I find it hard to get good performance from large queries with CTEs. Often, it's much easier (and uglier) to just split them to multiple steps, create intermediate tables for each step and add necessary indexes. Temporary tables are then of course even better, since usually you don't want to have these left around. But temporary tables are also unlogged.

avianlyric 4y ago
Which versions of Postgres have you been running your large CTE based queries with? Up until Postgres 12, all CTE were materialised before their results were used, effectively making every CTE a temporary table. In Postgres 12 onwards, for side-effect free CTE that are only referenced once, Postgres will take constrains from the parent query, and push them into the CTE, reducing the amount of data they need process, and allowing better use of indexes. OOI have you tried converting your CTEs into sub-queries as an experiment to see if they’re faster? Even in Postgres 12 onwards, sub-queries and CTEs are treated the same by the query planner, and you can get some surprising differences in performance.
- RandomBK 4y ago
  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.

avianlyric 4y ago

Which versions of Postgres have you been running your large CTE based queries with? Up until Postgres 12, all CTE were materialised before their results were used, effectively making every CTE a temporary table. In Postgres 12 onwards, for side-effect free CTE that are only referenced once, Postgres will take constrains from the parent query, and push them into the CTE, reducing the amount of data they need process, and allowing better use of indexes. OOI have you tried converting your CTEs into sub-queries as an experiment to see if they’re faster? Even in Postgres 12 onwards, sub-queries and CTEs are treated the same by the query planner, and you can get some surprising differences in performance.

RandomBK 4y ago
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.