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…)
An interesting query has been twitted by Will Leinweber from Postgres Open:
-- this returns a different result each time it is ran
with recursive s as (
select random() from s
) select count(*) from s;
I like this example: a surprising result, which can be explained by (and indeed helps to explain) CTE behaviour.