2 ms·
> CTEs should not be materialized by default (latest PG if you reference a CTE more than once it stores intermediate rows on disk and disables indexes and cause
by jeltz 4y ago
> CTEs should not be materialized by default (latest PG if you reference a CTE more than once it stores intermediate rows on disk and disables indexes and causes a lot of trouble.)
Nope, it does not write anything to disk unless you have more data than work_mem.
- srcreigh 4y agoNice, TIL. The default value for work_mem is only 4MB though, and when I’ve seen materialized CTEs go wrong they’re usually using temp tables in the 10s or even 100s of MBs. Usually “with products as (select * from products where customer_id = 123)” type of stuff where some customers have hundreds of products with large rows.