As part of AXLE project work I recently found myself re-examining issues around optimisation fencing and non-semantic query execution changes in PostgreSQL. One key issue was the inability to use a CTE where optimisation (inlining, push-up/pull-down, flattening, etc) was desired. I'd like to explain that in more detail, as it's something that can surprise new and experienced PostgreSQL users alike.
When optimising queries in PostgreSQL (true at least in 9.4 and older), it's worth keeping in mind that - unlike newer versions of various other databases - PostgreSQL will always materialise a CTE term in a query.
This can have quite surprising effects for those used to working with DBs like MS SQL:
A query that should touch a small amount of data instead reads a whole table and (more…)