Materialized CTE

I came across this code

WITH namespaces AS MATERIALIZED (
    SELECT
        namespaces.traversal_ids
    FROM
        namespaces
    WHERE
        namespaces.type = 'Group' AND
        traversal_ids >= '{9970}' AND
        traversal_ids < '{9971}'
)
...

I guess there are times when you want Postgres to re-evaluate a CTE, and other times you do not. The rule of thumb is to AVOID NOT MATERIALIZED when you have expensive operations in the CTE query.

https://www.postgresql.org/docs/17/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION