Re:Ask HN: How do you test SQL?
by colanderman on Tuesday January 31, 2023 @08:57PM
(#34605317)
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.
[ Parent
| on HN ]
This 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)
Re:Ask HN: How do you test SQL?
by dragonwriter on Tuesday January 31, 2023 @09:29PM
(#34605576)
Yeah, 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...
Is 'sun table' a particular concept here? A typo?
It's a typo. I was trying to write 'CTE table' and iOS "helpfully" autocorrected it.
Just to flag this behaviour changes in v12: https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...