1

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.

user5735224
  • 461
  • 1
  • 7
  • 16
  • Why do you use a CTE at all and not just `SELECT x1, x2 from v1 where v1.id = :id`? – Bergi May 15 '23 at 16:01
  • the view is expensive to calculate and it provides up-to-date data so i can't materialize it – user5735224 May 15 '23 at 16:07
  • 1
    I assume that the referenced speed improvement was in a query that inluded multiple subqueries against `v1` and not the simple case in the original post. I haven't looked at the PostgreSQL internals to see how CTEs are stored, but I also don't think it particularly important. Either your environment has the resources to complete the query or it doesn't. Marking a CTE as `MATERIALIZED` means that the subquery will be evaluated only once for full query but also creates an optimization fence that prevents folding it into the larger query. – JohnH May 15 '23 at 17:47
  • 2
    A CTE with the keyword MATERIALIZED is completely different from a MATERIALIZED VIEW. In a CTE it's just a barrier between the different sections of a query plan. For the VIEW it means that the results from the view will be stored like a table on disk. – Frank Heikens May 15 '23 at 18:47

1 Answers1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263