4 ms·
I suppose it is much more common that CTEs are used to make queries more readable (I certainly do!). If this is the case, I feel making the common case the defa
by ohlookabird 7y ago
I suppose it is much more common that CTEs are used to make queries more readable (I certainly do!). If this is the case, I feel making the common case the default behavior (inlining) is preferable, which is what PG12 did. It also is less surprising behavior in my opinion. The old behavior can be enforced using the MATERIALIZED keyword with the CTE.
However, I agree that this will make me go through every CTE I use and make sure there is no MATERIALIZED keyword missing. And I already know quite a few where I, like you, use CTEs to prevent recomputation and promote reuse. I will likely add the MATERIALIZED keyword there for clarity, but inlining will only happen if the CTE isn't used multiple times anyway (they will also not be inline if they have side-effects or are recursive).
- pgaddict 7y agoYeah, there was a lengthy discussion about what the default behavior should be during development. Ultimately, inline by default prevailed, and I think it was the right decision. Inlining seems like the right choice for vast majority of cases, but yeah - there are cases where enforcing materialization is a win. Once you try this on your application, it'd be interesting to share some stats about the queries / CTEs that benefited from materialization. That might be quite useful for improving the "materialize vs. inline" logic in future releases.
- ohlookabird 7y agoThat's a good suggestion. I try to gather some numbers and post to the mailing list.