3 ms·
CTEs considered harmful. This query will run nornally as long as those CTEs stay unmaterialized. But all you need is a single intermediate CTE and due to the “t
by srcreigh 4y ago
CTEs considered harmful. This query will run nornally as long as those CTEs stay unmaterialized. But all you need is a single intermediate CTE and due to the “two+ references cause CTE materializations” rule in Postgres, the query will start to load an entire table into an unindexed set and use that for the query. Basically, disables all indexes for that table.
Idk why Postgres doesn’t disable materialization by default. IME it’s a niche use case compared to using CTEs as intermediate views (where you want to expand all joins and use indexes). It’d be better to have an explicit opt in “Yes, store this result on disk, disable all indexes, and process it O(N) fashion.”
- Footkerchief 4y ago> You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query, or by specifying NOT MATERIALIZED to force it to be merged into the parent query. The latter choice risks duplicate computation of the WITH query, but it can still give a net savings if each usage of the WITH query needs only a small part of the WITH query's full output. https://www.postgresql.org/docs/current/queries-with.html#id-1.5.6.12.7 https://www.postgresql.org/docs/current/queries-with.html#id...
- srcreigh 4y agoThanks, it is important for people to know this. In this article 5 tables are pulled into CTEs which aren’t marked as not materialized. It wasn’t the point of the example, but the default is scary, leading to disabling all indexes for the query once it gets more layers.