Hacker News Reader

Comment by srcreigh | Hacker News Reader

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

View on HN

How would this look with a sample query? (using AS NIT MATERIALIZED)

    WITH 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.

This is incredibly helpful. I did not know this was a possibility.

Reply on news.ycombinator.com