3 ms·
One caution with PostgreSQL, CTEs under some circumstances (and in all circumstances, prior to PostgreSQL 12) act as optimization barriers. Specify `NOT MATERI
by colanderman 4y ago
One caution with PostgreSQL, CTEs under some circumstances (and in all circumstances, prior to PostgreSQL 12) act as optimization barriers. Specify `NOT MATERIALIZED` before the CTE definition to ensure that they are optimized same as a sub-SELECT would be.
- nicoburns 4y agoThis is a good point, although it’s worth noting that there are definitely cases where you want the sun table to be materialised (esp. when that table is small and referenced many times)
- dragonwriter 4y agoYeah, its definitely one of those things where it is worth reading the docs [0], and not just adopting an “always do it this way” rule of thumb. [0] 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...
- bolanyo 4y agoIs 'sun table' a particular concept here? A typo?
- nicoburns 4y agoIt's a typo. I was trying to write 'CTE table' and iOS "helpfully" autocorrected it.
- timwis 4y agoJust to flag this behaviour changes in v12: https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
- srcreigh 4y agoNot really. CTEs are still optimization barriers after v12 if they're referenced more than once. That blog post is confused, but the commit message in the post is clear enough.