Query Optimization with CTE

Today we’re diving into the exciting (and a little scary) world of query optimization with CTEs (Common Table Expressions). If you already know how to create CTEs (we covered that in previous lectures), now’s the time to talk about their inner workings, gotchas, and how to squeeze the most performance out of them.

At first glance, CTEs seem perfect: they look clean, are easy to write, and let you break code into logical chunks. But there’s a catch — PostgreSQL’s CTE execution strategy has changed over the years.

Before PostgreSQL 12, WITH always acted as an "optimization fence": the CTE result was first materialized into a temporary table and only then used in the main query.

Starting with PostgreSQL 12, the behavior is smarter: if a CTE has no side effects (it’s a plain SELECT) and is referenced exactly once in the main query, the planner inlines it directly into the query (as if it were a subquery) — this is the NOT MATERIALIZED default. If a CTE is referenced two or more times, or it contains INSERT/UPDATE/DELETE, or it’s recursive, it is still materialized.

You can force the behavior with the MATERIALIZED / NOT MATERIALIZED keywords right after the CTE name. Materialization can still be a problem when:

1. The CTE holds a ton of data, but you only use a small part of it.
2. The CTE gets called way too many times, adding overhead.
3. You’re making unnecessarily complex CTEs that you don’t really need.

Getting to Know Materialization

Materialization is when PostgreSQL saves the CTE result in memory or on disk (depending on how big it is). This means the data is fetched just once, but if you only use the CTE in one place, materialization might be overkill. For example:

WITH large_set AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

In this case, PostgreSQL first creates a temp table with the full CTE result (grade > 60), then filters rows where grade > 90. That adds an unnecessary extra step and can slow things down.

How to Avoid Unnecessary Materialization?

Starting with PostgreSQL 12, you can force the planner’s choice with the MATERIALIZED or NOT MATERIALIZED keywords (the default depends on how many times the CTE is referenced, as described above). Example:

WITH large_set AS NOT MATERIALIZED (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

Here, we’re telling PostgreSQL not to materialize large_set, but to inline the query right into the main expression. This makes the query more efficient since there’s no temp table created.

When is Materialization Actually Good?

Don’t think materialization is always bad! If the CTE data is used multiple times in the query or needs to be calculated independently, materialization can be helpful. Example:

WITH materialized_example AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)

SELECT student_id
FROM materialized_example
WHERE grade > 90

UNION ALL

SELECT student_id
FROM materialized_example
WHERE grade < 70;

Here, materialization saves you from recalculating the grade > 60 filter.

Optimizing Queries with Indexes

To make CTEs faster, use indexes on the base tables you’re pulling data from. For example:

CREATE INDEX idx_students_grades_grade ON students_grades(grade);

WITH filtered_students AS (
    SELECT student_id, grade
    FROM students_grades
    WHERE grade > 90
)
SELECT *
FROM filtered_students;

An index on the grade column lets PostgreSQL grab rows matching grade > 90 way faster. This is especially important with big tables.

Breaking Up Big CTEs into Smaller Ones

If your CTE returns a lot of data that you then filter or aggregate, it’s better to break it into steps. Instead of one huge CTE, make a few smaller ones:

Not great (big CTE):

WITH large_query AS (
    SELECT s.student_id, AVG(g.grade) AS avg_grade
    FROM students s
    JOIN grades g ON s.student_id = g.student_id
    WHERE g.subject_id = 101 AND g.grade > 85
    GROUP BY s.student_id
)
SELECT *
FROM large_query
WHERE avg_grade > 90;

Better (split into steps):

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE subject_id = 101 AND grade > 85
),
average_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM filtered_grades
    GROUP BY student_id
)
SELECT *
FROM average_grades
WHERE avg_grade > 90;

This approach helps PostgreSQL optimize query execution better.

Hands-On Example: Structure Analysis and Optimization

Let’s check out a more complex example. We’ve got tables for students, courses, and grades. We want to find students with high average grades and show their list along with the courses they’re in:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)
SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

You can optimize this query by adding indexes to the grades and enrollments tables, which will speed up filtering and joining.

Monitoring: Performance Analysis

To see how efficient your query is, use EXPLAIN or EXPLAIN ANALYZE. For example:

EXPLAIN ANALYZE
WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

This query shows how long each step takes and helps you spot where you can boost performance.

We’ll dig into EXPLAIN ANALYZE in more detail in the next levels :P

Common Mistakes When Optimizing CTEs

1. Forgetting about indexes. If you filter data in a CTE but the base table has no index, performance will tank.
2. Using CTEs that are too big. If one query does too much, you might end up materializing huge amounts of data.
3. Overusing NOT MATERIALIZED. Sometimes you actually need materialization to avoid recalculating the CTE.
4. Ignoring monitoring. Without checking with EXPLAIN, you might not notice your queries are slow.

Now you’re ready to optimize queries with CTEs, dodge the traps, and boost performance! Remember, CTEs are a tool, not a magic bullet. Use them wisely, and they’ll be your best friends in PostgreSQL.