5 ms·

The impact of CTEs being "optimization fences" in PostgreSQL really depends on workload, and how cleverly you write your queries. If you are not aware of this -

The impact of CTEs being "optimization fences" in PostgreSQL really depends on workload, and how cleverly you write your queries. If you are not aware of this - as most users using CTEs merely as an alias - you'll be badly surprised. I've been recently running TPC-DS on PostgreSQL 9.5 (alpha1 IIRC), to see how we're doing (I'm one of the contributors). Thanks to the grouping sets, we're now able to execute all 99 queries in the benchmark, which is awesome. Regarding performance, I've expected grouping sets to be a performance issue as the current implementation is rather simple. But to my mild surprise that's not the case - CTEs are the main problem. On 100GB data set some queries took hours to complete (well, I interrupted them so I'm not sure how long it'd actually take). After a rewrite (replace CTE with a subquery), the query took a a minute or so.

radiowave 11y ago
A point that's well worth stating for those that may not realize it: rewriting a query that uses CTE into one that uses subqueries is really just a case of using copy and paste to perform substitution: replace the CTE references with their definitions. So, to a large extent you can have the best of both worlds.
- calpaterson 11y ago
  SQLAlchemy (which is sort of an embedded dsl for SQL) lets you give variable names to subqueries and as a result I don't really use CTEs. I bet the people using iBatis and JOOQ can do the same thing
  - lukaseder 11y ago
    As far as using CTEs for structuring your SQL is concerned, you're right, unless people really want the side-effects CTEs sometimes have, such as materialisation in PostgreSQL. But don't forget: CTEs can be recursive.
- jsprogrammer 11y ago
  Which makes you wonder why Postgres can't just do that optimization for you.
  - alecdbrooks 11y ago
    It's planned, apparently. [0] When it was discussed on the mailing list, it sounds like people were in favor of adding a keyword to disable it, making the syntax something like WITH UNBOXED (subquery) or WITH VIEW (subquery). [0]: https://wiki.postgresql.org/wiki/Todo#Optimizer_.2F_Executor https://wiki.postgresql.org/wiki/Todo#Optimizer_.2F_Executor. Search for CTE.
  - pgaddict 11y ago
    Because CTEs are not named subqueries, really. For example, CTE is only evaluated once, even if it's referenced in multiple places, and those places may have conflicting ideas of what's the best way to evaluate it. Consider for example this: WITH x AS (SELECT a, b FROM t) SELECT * FROM x x1 JOIN x x2 ON (x1.a = x2.b); Now, had this been evaluated using a merge join, the "x" CTE would have to be sorted first by "a" or "b" (for either side of the join). Well, that can't really happen. Another issue is locking - consider this version, for example: WITH x AS (SELECT a, b FROM t FOR UPDATE) SELECT * FROM x x1 JOIN x x2 ON (x1.a = x2.b); In other words, it's way more complicated than it might seem, especially if you can't break existing uses (e.g. the locking).
    - osolo 11y ago
      It seems to me that these would be rare rather than the typical case. I think we could make the case that the more straightforward uses (like the ones in the articles) could be optimized away as described above. Leave the more complex/less optimized code path for these edge cases.
    - CuriousSkeptic 11y ago
      SQL is a declarative language with semantics derived from relational algebra, which to me implies that there should be no execution semantics at all. So the statement that "a CTE is only evaluated once" should not be part of any specification for the language. And I would not expect two different executions of a query to maintain that invariant if the optimizer finds it better not to.

radiowave 11y ago

A point that's well worth stating for those that may not realize it: rewriting a query that uses CTE into one that uses subqueries is really just a case of using copy and paste to perform substitution: replace the CTE references with their definitions. So, to a large extent you can have the best of both worlds.

calpaterson 11y ago
SQLAlchemy (which is sort of an embedded dsl for SQL) lets you give variable names to subqueries and as a result I don't really use CTEs. I bet the people using iBatis and JOOQ can do the same thing
- lukaseder 11y ago
  As far as using CTEs for structuring your SQL is concerned, you're right, unless people really want the side-effects CTEs sometimes have, such as materialisation in PostgreSQL. But don't forget: CTEs can be recursive.
jsprogrammer 11y ago
Which makes you wonder why Postgres can't just do that optimization for you.
- alecdbrooks 11y ago
  It's planned, apparently. [0] When it was discussed on the mailing list, it sounds like people were in favor of adding a keyword to disable it, making the syntax something like WITH UNBOXED (subquery) or WITH VIEW (subquery). [0]: https://wiki.postgresql.org/wiki/Todo#Optimizer_.2F_Executor https://wiki.postgresql.org/wiki/Todo#Optimizer_.2F_Executor. Search for CTE.
- pgaddict 11y ago
  Because CTEs are not named subqueries, really. For example, CTE is only evaluated once, even if it's referenced in multiple places, and those places may have conflicting ideas of what's the best way to evaluate it. Consider for example this: WITH x AS (SELECT a, b FROM t) SELECT * FROM x x1 JOIN x x2 ON (x1.a = x2.b); Now, had this been evaluated using a merge join, the "x" CTE would have to be sorted first by "a" or "b" (for either side of the join). Well, that can't really happen. Another issue is locking - consider this version, for example: WITH x AS (SELECT a, b FROM t FOR UPDATE) SELECT * FROM x x1 JOIN x x2 ON (x1.a = x2.b); In other words, it's way more complicated than it might seem, especially if you can't break existing uses (e.g. the locking).
  - osolo 11y ago
    It seems to me that these would be rare rather than the typical case. I think we could make the case that the more straightforward uses (like the ones in the articles) could be optimized away as described above. Leave the more complex/less optimized code path for these edge cases.
  - CuriousSkeptic 11y ago
    SQL is a declarative language with semantics derived from relational algebra, which to me implies that there should be no execution semantics at all. So the statement that "a CTE is only evaluated once" should not be part of any specification for the language. And I would not expect two different executions of a query to maintain that invariant if the optimizer finds it better not to.