PostgreSQL CTE vs Subquery Performance: When to Use Each
postgresql cte vs subquery performance settled — readability, inlining since PG12, recursive CTEs, MATERIALIZED, and runnable examples for each pattern.
Ask "are CTEs slower than subqueries in Postgres?" and you'll get confident answers in both directions. The real story of postgresql cte vs subquery performance changed with PostgreSQL 12, which started inlining simple CTEs into the main query — before that, a CTE was always an optimization fence. This guide settles the question with runnable examples: when CTEs and subqueries perform identically, when to choose each for readability, and the cases (recursive queries, MATERIALIZED , deduplication) where only a CTE will do. What you'll learn Subquery vs CTE: the same logic written both ways The truth about CTE vs subquery performance (PG12+ inlining) When to use a CTE instead of a subquery (readability rules) Recursive CTEs: the org-chart pattern with WITH RECURSIVE MATERIALIZED vs NOT MATERIALIZED : controlling the optimization fence Practical recipes: delete duplicates and filter on window functions with CTEs Step 1 — Set up an employees table An org chart is the perfect playground — it needs self-joins, aggregation, and recursion: CREATE TABLE employees ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER REFERENCES employees(id) ); INSERT INTO employees (name, manager_id) VALUES ('Ayesha (CEO)', NULL), ('Bilal (CTO)', 1), ('Danish (Lead)', 2), ('Esha (Dev)', 3), ('Faraz (Dev)', 3); Step 2 — The same logic as a subquery and as a CTE Goal: list every employee with their number of direct reports. First as a correlated subquery — a query inside the SELECT list that runs once per row: SELECT name, (SELECT COUNT(*) FROM employees e2 WHERE e2.manager_id = e1.id) AS reports FROM employees e1 ORDER BY id; -- name | reports -- ----------------+--------- -- Ayesha (CEO) | 1 -- Bilal (CTO) | 1 -- Danish (Lead)…