4 ms·
> caveat: WITH expressions are optimization boundaries This is a pretty big caveat and one of the rare areas in which postgres does worse than other database s
by bjourne 14y ago
> caveat: WITH expressions are optimization boundaries
This is a pretty big caveat and one of the rare areas in which postgres does worse than other database systems. In SQL Server a non-recursive common table expression is treated by the optimizer similar to a macro - You can break up a complicated query with unions and group bys into easier to read cte:s and be confident that the optimizer will piece them all together into a query whose execution plan is equal to the original one.
But with postgres you can't because of the optimization boundaries. Its optimizer will attempt to make each cte as individually efficient as possible which can lead to a much worse execution plan overall. You can use views instead which, in contrast to cte:s, postgres optimizer can see through.
- gleb 14y agoIt's more subtle than "worse." Apparently CTE optimization fence is part of SQL standard, and it can be useful to hand-optimize queries. But you are right, when using CTEs for readability it tends to hurt more than help. Interesting thread about being able to control this: http://www.postgresql.org/message-id/201209191305.44674.db@kavod.com http://www.postgresql.org/message-id/201209191305.44674.db@k...
- bjourne 14y agoYeah, I've also heard that claim being made on the mailing list but it is probably wrong: http://dba.stackexchange.com/questions/27425/is-the-optimisation-fence-behaviour-of-a-cte-with-query-specified-in-the-sql2 http://dba.stackexchange.com/questions/27425/is-the-optimisa.... For writable cte:s it matters, for read-only ones the number of execution times doesn't.
- pvh 14y agoSorta. It can be handy for guiding the optimizer away from a bad optimization choice but that's really a bad habit to get into.
- bsg75 14y agoAre there more details about this? As a regular CTE use is both Postgres and MSSQL, but was unaware of this difference, I would like to know where I may be shooting performance to hell.
- lobster_johnson 14y agoFrom the docs (http://www.postgresql.org/docs/9.2/static/queries-with.html http://www.postgresql.org/docs/9.2/static/queries-with.html): > A useful property of WITH queries is that they are evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects. However, the other side of this coin is that the optimizer is less able to push restrictions from the parent query down into a WITH query than an ordinary sub-query. The WITH query will generally be evaluated as written, without suppression of rows that the parent query might discard afterwards. (But, as mentioned above, evaluation might stop early if the reference(s) to the query demand only a limited number of rows.) In other words, something like: with x as (select * from users) select * from x limit 1 will be executed identically to select * from users limit 1 However, a query like this: with x as ( select * from users join images on images.id = users.image_id ) select * from x limit 1 will incur a huge overhead compared to: select * from users join images on images.id = users.image_id limit 1 In my test, the "WITH" query had a cost of 97840 and touched 8881 buffers, compared to a cost of 132 and 41 buffers for the second query. In other words, the planner pretty much considers the "WITH" subquery separately from the outer query, and plans them separately.