How are materialized CTE's stored in Postgres14

Question

I was able to significantly speed up a query that looks something as follows:

WITH MATERIALIZED t1 as (
    SELECT x1, x2 from v1 where v1.id = :id
) select * from t1

Here, v1 is a view (unmaterialized). My question, where is the materialized result of t1 stored? Is it stored in buffer cache or disk? I'm concerned about computing several materialized versions of t1 for different values of id and polluting my buffer cache or OS cache.

Answer 1

MATERIALIZED does not imply that the data are stored on disk. It only means that the query executor will first compute the result of the CTE, then use it in the main statement. Essentially, it is an “optimizer barrier”: without MATERIALIZED, PostgreSQL can treat the CTE like a subquery and optimize further.

The MATERIALIZED in a CTE has nothing to do with materialized views: those are actually persisted in a database table. A CTE only exist in the statement that contains it.

Answer 2

Starting from the 12th version of the kernel, the behavior of the optimizer changes when calculating the query plan using CTE. Depending on certain circumstances, the optimizer may decide to use materialize or inline veiw. The algorithm of the optimizer when working with CTE is described in the document https://www.postgresql.eu/events/pgconfeu2022/sessions/session/3902/slides/354/CTEsAndTheirMaterialization-DivyaSharma.pdf

I can’t see your query plan, but I suspect that it’s about the same:

explain analyse with w as  materialized  (select * from operation)
select *
from w

+--------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                    |
+--------------------------------------------------------------------------------------------------------------+
|CTE Scan on w  (cost=10.50..11.50 rows=50 width=1576) (actual time=0.004..0.004 rows=0 loops=1)               |
|  CTE w                                                                                                       |
|    ->  Seq Scan on operation  (cost=0.00..10.50 rows=50 width=1576) (actual time=0.003..0.003 rows=0 loops=1)|
|Planning Time: 0.307 ms                                                                                       |
|Execution Time: 0.064 ms                                                                                      |
+--------------------------------------------------------------------------------------------------------------+


explain analyse with w as not materialized  (select * from operation)
select *
from w

+------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                            |
+------------------------------------------------------------------------------------------------------+
|Seq Scan on operation  (cost=0.00..10.50 rows=50 width=1576) (actual time=0.002..0.003 rows=0 loops=1)|
|Planning Time: 0.056 ms                                                                               |
|Execution Time: 0.012 ms                                                                              |
+------------------------------------------------------------------------------------------------------+

Use MATERIALIZED ;)