[SOLVED] How are materialized CTE's stored in Postgres14

How are materialized CTE's stored in Postgres14

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.

Solution

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.