CTE vs subquery performance in PostgreSQL - Visual Side-by-Side Comparison

Concept Flow - CTE vs subquery performance

Start Query

↓

Check for CTE or Subquery

↓

Materialize?

↓

Use Result

↓

Return Final Result

The query starts, checks if it uses a CTE or subquery, executes accordingly, possibly materializing CTE results, then returns the final output.

Execution Sample

PostgreSQL

WITH cte AS (
  SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM cte WHERE customer_id = 1;

This query uses a CTE to filter orders with amount > 100, then selects those for customer_id 1.

Execution Table

Step	Action	Evaluation	Result
1	Start query execution	WITH clause found	Prepare CTE execution
2	Execute CTE query	SELECT * FROM orders WHERE amount > 100	Filtered rows with amount > 100
3	Materialize CTE result	Store filtered rows	CTE result stored in memory
4	Execute outer query	SELECT * FROM cte WHERE customer_id = 1	Filter CTE rows by customer_id
5	Return final result	Rows matching both conditions	Final filtered rows returned
6	End	No more steps	Query complete

💡 Query ends after returning filtered rows from CTE

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
CTE Result	empty	filtered orders > 100	stored in memory	filtered by customer_id=1	final filtered rows

Key Moments - 2 Insights

Why does the CTE sometimes cause slower performance than a subquery?

Because in PostgreSQL, CTEs are often materialized (stored) before the outer query runs, as shown in step 3 of the execution_table, which can add overhead compared to subqueries that are inlined.

Does a subquery always run faster than a CTE?

Not always. Subqueries can be optimized and inlined by the planner, avoiding materialization, but complex queries or repeated use of the same CTE can make CTEs more efficient.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the CTE result materialized?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Check the 'Action' and 'Result' columns in step 3 for materialization details.

According to variable_tracker, what is the state of 'CTE Result' after step 4?

A empty

B stored in memory

C filtered by customer_id=1

D filtered orders > 100

💡 Hint

Look at the 'After Step 4' column for 'CTE Result' in variable_tracker.

If the CTE was replaced by a subquery, which step might be skipped?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Materialization (step 3) is typical for CTEs but often skipped for subqueries.

Concept Snapshot

CTE vs Subquery Performance in PostgreSQL:
- CTEs often materialize results before outer query runs.
- Subqueries are usually inlined and optimized.
- Materialization can slow queries if large data sets.
- Use CTEs for readability or repeated use.
- Use subqueries for potential performance gains.

Full Transcript

This visual execution shows how PostgreSQL handles queries with CTEs versus subqueries. The query starts by detecting a CTE, executes the inner query filtering orders with amount greater than 100, then materializes the result in memory. After that, the outer query filters these results by customer_id. This materialization step can add overhead compared to subqueries, which are often inlined and optimized without storing intermediate results. The variable tracker shows how the CTE result changes state through execution. Key moments clarify why CTEs can be slower and when subqueries might be better. The quiz tests understanding of materialization and variable states during execution.