Does WITH query store the results of referred tables?

Question

Following is the query structure:

WITH TAB1 AS ( SELECT * FROM HUGE_TABLE_1 WHERE id='something' ),
     TAB2 AS ( SELECT * FROM HUGE_TABLE_2 WHERE id='something' LEFT JOIN TAB1 ON TAB1.id=TAB2.id )
     TAB3 AS ( SELECT * FROM HUGE_TABLE_3 WHERE id='something' LEFT JOIN TAB2 ON TAB1.id=TAB2.id )

SELECT JSON_BUILD_OBJECT( ... , 'TAB2', JSON2 ) AS JSON1, id FROM TAB1 GROUP BY FK_ID
LEFT JOIN
    ( SELECT JSON_BUILD_OBJECT( ... , 'TAB3', JSON3 ) AS JSON2, id FROM TAB3 GROUP BY FK_ID
LEFT JOIN
        ( SELECT ROW_TO_JSON(TAB3.*) AS JSON3, id FROM TAB3 GROUP BY FK_ID ) AS JOIN2 ... ) AS JOIN1 ...

In the above sample query, tables TAB1 and TAB2 are referred multiple times, so will the TAB1 or TAB2 queries be executed every time they are referred or the results are stored by Postgres?

Answer 1

Generally, CTEs are always materialized in Postgres 11 or older.
Or if they are referenced repeatedly in Postgres 12 or later. The manual:

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 WITHqueries.

Additionally, you can force one behavior or the other with the clauses MATERIALIZED or NOT MATERIALIZED in Postgres 12 or later.

You can check the actual query plan with EXPLAIN. Any mention of CTE Scan means that the involved CTE has been materialized.

Also note that CTEs with a SELECT that are not referenced, are not executed at all.

Your code example is logical and syntactical nonsense. So it's hard to make sense of it. Chances are, this query doesn't need any CTEs at all. CTEs always add overhead. (Less in Postgres 12 or later.) They only pay when materializing saves repeated work. (Or, possibly, if it forces a different query plan as a side effect.) So only use CTEs when needed, or when you don't care about performance and prefer the syntax.