4 ms·
In Postgres you can use AS NIT MATERIALIZED to avoid the temp buffers. A recent PG version also makes this default, but only when the CTE is only queried once l
by srcreigh 4y ago
In Postgres you can use AS NIT MATERIALIZED to avoid the temp buffers. A recent PG version also makes this default, but only when the CTE is only queried once later.
This can definitely cause issues. I’ve seen queries with CTEs that basically process unindexed 100 MB data due to querying the materialized rows.
It would be cool to be able generate indexes with the CTEs though
- warmwaffles 4y agoHow would this look with a sample query? (using AS NIT MATERIALIZED)
- jdmichal 4y agoWITH one AS NOT MATERIALIZED ( SELECT 1 AS value, 'one' AS name ) SELECT * FROM one As srcreigh said, this is the default since I believe PostgreSQL 12, as long as the CTE is only used a single time. If you use it more than once, it by default wants to materialize it instead of recalculating each time. You can also force materialization by dropping the `NOT`. Also note that materialization acts as an optimization fence. That is, PostgreSQL will not push down filter criteria and such from the query into the `WITH` clause. It can do such if it's not materialized.
- warmwaffles 4y agoThis is incredibly helpful. I did not know this was a possibility.