Mastering PostgreSQL 12 - Third Edition

Optimizing common table expressions and planner support functions

Common table expressions (CTEs) are often used to make queries more readable. However, in older versions (pre 12), CTEs came with some PostgreSQL specifics. The result of a CTE is always calculated—just like an independent query. The optimizer could not inline the query and turn it into something faster. A CTE was an optimization barrier that greatly limited the freedom of the planner to do smart things. In PostgreSQL, the situation has changed. The optimizer is now a lot smarter and has more options to deal with CTEs in general. 

Let's take a look at an example. The following plan was created in PostgreSQL 11:

test=# explain WITH x AS (SELECT * FROM generate_series(1, 5) AS ...