I have function with CTE which looks like this:

function param(p_userId, p_templateId)

    with a(select...),
         b (select..), 
         c (insert..returning..), 
         d (update..returning..),
    insert ... 
    select * from k 
    where  userId = p_userId 
    and    templateId = p_templateId

I'm able to put the where clause right at the begining in the a CTE. If I place the condition at the end like I have it now, will it filtered the rows out at the earliest or at the latest time? What is faster?

Basically what I do is, in the CTE's which appear sooner, like the a or b, (rather then k), I'm prefetching data or precalculating columns in order to use them later in the following CTEs.

Hope this question is still not too vague.

  • You can run the query with EXPLAIN ANALYZE and see it for yourself ;) – dezso Jan 11 '18 at 11:43
  • You will have to look at the plan to see where filtering is happening. I guess that it will be difficult for the optimizer to push predicates through the cte's in the general case and almost impossible in case of recursive cte's. I would put the predicates as early as possible (as long as the semantics of the query is preserved). – Lennart Jan 11 '18 at 11:43
  • 1
    b can reference a (but not the other way around). If b references a, a has to be evaluated before b. If a and b are independent I guess the optimizer can choose to evaluate them in whatever order it finds appropriate. – Lennart Jan 11 '18 at 11:50
  • @DanielKatz don't post JSON but the plaintext output. That is easily consumable by mortals :) – dezso Jan 11 '18 at 12:43

CTEs pose as optimization fences in Postgres.

Each CTE is planned, executed and materialized separately. The predicates of a later CTE are not visible in earlier CTEs. Place conditions to eliminate rows as early as possible (a or b in your example).

Or rewrite with subqueries to allow more aggressive optimizations in Postgres. (But you should still eliminate irrelevant rows as early as possible, optimization only goes so far ...)

Details in Craig's blog post:

  • Definitely a lot of good points in the link I need to look at. – Daniel Katz Jan 13 '18 at 2:18

Update: Since v12, PostgreSQL automatically folds many CTEs into the parent query and pushes predicates to the optimal levels. The accepted answer is still robust for all CTEs, but may not be necessary for your case. Your psuedo-code doesn't give enough details for a definitive answer.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.