CTE vs subquery performance in PostgreSQL

Introduction

We want to understand when to use CTEs or subqueries and how they affect speed in PostgreSQL.

When you want to organize complex queries into readable parts.

When you need to reuse a query result multiple times.

When you want to check if performance changes by using CTE or subquery.

When debugging or optimizing slow queries.

When deciding how to write queries for better speed.

Syntax

PostgreSQL

WITH cte_name AS (
  SELECT ...
)
SELECT ... FROM cte_name;

-- vs --

SELECT ... FROM (
  SELECT ...
) AS subquery;

A CTE (Common Table Expression) is defined with WITH and can be referenced like a temporary table.

A subquery is a SELECT inside another query, used directly in FROM or WHERE clauses.

Examples

This CTE selects recent orders and then queries from it.

PostgreSQL

WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date > '2024-01-01'
)
SELECT * FROM recent_orders;

This subquery does the same but inside the main query.

PostgreSQL

SELECT * FROM (
  SELECT * FROM orders WHERE order_date > '2024-01-01'
) AS recent_orders;

Sample Program

This example shows the same filtering using a CTE and a subquery. Both return the same result.

PostgreSQL

CREATE TEMP TABLE orders (id INT, order_date DATE);
INSERT INTO orders VALUES (1, '2024-02-01'), (2, '2023-12-31');

-- Using CTE
WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date > '2024-01-01'
)
SELECT id FROM recent_orders ORDER BY id;

-- Using Subquery
SELECT id FROM (
  SELECT * FROM orders WHERE order_date > '2024-01-01'
) AS recent_orders ORDER BY id;

Important Notes

In PostgreSQL, CTEs act like optimization fences before version 12, meaning they always run separately and can be slower.

From PostgreSQL 12+, CTEs can be inlined like subqueries, improving performance.

Subqueries are often faster because the planner can optimize them better.

Summary

CTEs help organize queries but may affect speed depending on PostgreSQL version.

Subqueries can be faster because they allow more optimization.

Test both in your situation to see which is faster.