3 ms·
> CTEs won’t always be quite as performant as optimizing your SQL to be as concise as possible. In most cases I have seen performance differences smaller than a
by saurik 2y ago
> CTEs won’t always be quite as performant as optimizing your SQL to be as concise as possible. In most cases I have seen performance differences smaller than a 2X difference, this tradeoff for readability is a nobrainer as far as I’m concerned. And with time the Postgres optimizer should continue to get better about such performance.
So, my knowledge here might be out of date, but this behavior isn't "the optimizer isn't smart enough": using a CTE in PostgreSQL causes an explicit boundary in the optimizer which prevents some optimizations from being performed. In some cases you really need/want this behavior, and it might increase the performance of your query; in other cases, this is the last thing you would want. People keep asking for optimizer hints, which PostgreSQL refuses to add, and yet they leave this quirk in explicitly as an escape hatch.
- hyperman1 2y agoThis changed in pg 12. https://www.postgresql.org/docs/release/12.0/ https://www.postgresql.org/docs/release/12.0/ : Automatic (but overridable) inlining of common table expressions (CTEs)
- camgunz 2y agoYep, it's a huge improvement for complicated pipelines. One thing to be aware of is "NOT MATERIALIZED". Here's what the docs say: A useful property of WITH queries is that they are normally 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 not able to push restrictions from the parent query down into a multiply-referenced WITH query, since that might affect all uses of the WITH query's output when it should affect only one. The multiply-referenced WITH query will 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.) However, if a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. By default, this happens if the parent query references the WITH query just once, but not if it references the WITH query more than once. You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query, or by specifying NOT MATERIALIZED to force it to be merged into the parent query. The latter choice risks duplicate computation of the WITH query, but it can still give a net savings if each usage of the WITH query needs only a small part of the WITH query's full output.