The thing is, CTEs are not named subqueries. They are supposed to execute just once, even if used on multiple places in the query. This causes a number of chal

The thing is, CTEs are not named subqueries. They are supposed to execute just once, even if used on multiple places in the query. This causes a number of challenges for the planner, because: * some of the places may benefit from quickly producing the first row (e.g. in EXISTS) while other places may need all the rows (e.g. when you pass the rows to GROUP BY or something) * the places may use additional clauses, but these can't be pushed down to the CTEs because that'd influence the other place There may be databases with planner that handle this in a different way, but in PostgreSQL the CTEs act as optimization fences - each CTE is optimized on it's own, without considering the rest of the query (additional clauses etc.) People who use CTEs as named subqueries are often surprised by the impact this may have on the plans performance-wise. Consider for example this: CREATE TABLE foo (a INT UNIQUE, b FLOAT); INSERT INTO foo SELECT i, random() FROM generate_series(1,1000000) s(i); CREATE TABLE bar (a INT); INSERT INTO bar SELECT i FROM generate_series(1,1000000) s(i); WITH foo_filtered AS (SELECT * FROM foo WHERE b < 0.5) SELECT * FROM bar JOIN foo_filtered USING (a) LIMIT 10; QUERY PLAN ---------------------------------------------------------------------------------------- Limit (cost=36986.19..38236.83 rows=10 width=12) CTE foo_filtered -> Seq Scan on foo (cost=0.00..17906.00 rows=510375 width=12) Filter: (b < '0.5'::double precision) -> Hash Join (cost=19080.19..63848915.94 rows=510375 width=12) Hash Cond: (bar.a = foo_filtered.a) -> Seq Scan on bar (cost=0.00..14425.00 rows=1000000 width=4) -> Hash (cost=10207.50..10207.50 rows=510375 width=12) -> CTE Scan on foo_filtered (cost=0.00..10207.50 rows=510375 width=12) (9 rows) and without the CTE: SELECT * FROM bar JOIN foo USING (a) WHERE b < 0.5 LIMIT 10; QUERY PLAN ---------------------------------------------------------------------------------- Limit (cost=0.42..10.26 rows=10 width=12) -> Nested Loop (cost=0.42..502029.00 rows=510375 width=12) -> Seq Scan on bar (cost=0.00..14425.00 rows=1000000 width=4) -> Index Scan using foo_a_key on foo (cost=0.42..0.48 rows=1 width=12) Index Cond: (a = bar.a) Filter: (b < '0.5'::double precision) (6 rows) All because the planner was unable to consider the join condition and the LIMIT 10, when planning the CTE.