4 ms·

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.
  - pgaddict 11y ago
    Yep, I agree with that. The problem is we currently have an implementation that behaves as explained above (planned in isolation, evaluated once), and there are applications relying on that behaviour. We can't just change that without breaking them.
    - CuriousSkeptic 11y ago
      True. At the same time you can't condemn future users to this suboptimal behavior just because a bad desicion in the past. I'm not arguing for just abruptly reverse the design, but that the aim should be to reverse in an orderly manner with suitable deprecations and migration paths in place.
  - radiowave 11y ago
    In order to be free of execution semantics, you'd have to disallow calling side-effectful functions within a query, which would be a major departure from the current behaviour.
- mistermann 11y ago
  > 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. Not disputing what you're saying, but I swear I read something the other day recommending being careful using CTE's as they are re-executed once for every reference in the query (or something to that effect).

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.
- pgaddict 11y ago
  Yep, I agree with that. The problem is we currently have an implementation that behaves as explained above (planned in isolation, evaluated once), and there are applications relying on that behaviour. We can't just change that without breaking them.
  - CuriousSkeptic 11y ago
    True. At the same time you can't condemn future users to this suboptimal behavior just because a bad desicion in the past. I'm not arguing for just abruptly reverse the design, but that the aim should be to reverse in an orderly manner with suitable deprecations and migration paths in place.
- radiowave 11y ago
  In order to be free of execution semantics, you'd have to disallow calling side-effectful functions within a query, which would be a major departure from the current behaviour.
mistermann 11y ago
> 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. Not disputing what you're saying, but I swear I read something the other day recommending being careful using CTE's as they are re-executed once for every reference in the query (or something to that effect).