Optimizing PostgreSQL chevron_right Subqueries, JOINs, and CTEs: Performance Implications in PostgreSQL

Subqueries, JOINs, and CTEs: Performance Implications in PostgreSQL

calendar_today schedule 6 min read

Learning Objectives: By the end of this lesson, you will be able to:

  • Distinguish between subqueries, JOINs, and Common Table Expressions (CTEs) in PostgreSQL
  • Analyze the performance implications of each approach using EXPLAIN plans
  • Refactor queries for optimal performance and maintainability

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever written a query that worked, but you weren’t sure if it was the best way? Or wondered why a seemingly simple query was slow?

Lesson Overview

  • Duration: 90 minutes
  • Prerequisites: Basic SQL (SELECT, WHERE, JOIN), familiarity with PostgreSQL
  • Materials Needed: PostgreSQL database (local or cloud), SQL client (psql, DBeaver, etc.)

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

  1. Subqueries, JOINs, and CTEs – definitions and differences
  2. Performance implications and query planning
  3. Hands-on query rewriting and analysis
  4. Common pitfalls and how to avoid them
  5. Best practices for complex query logic

Success Criteria:

By the end of this session, you should be able to choose the most appropriate query structure for a given problem and justify your choice based on performance and clarity.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

Subqueries

A subquery is a query nested inside another query. It can appear in SELECT, FROM, or WHERE clauses.

Real-World Analogy: Think of a subquery as a small report you prepare before making a decision in a bigger report.

Why This Matters: Subqueries can make queries more readable, but may impact performance if not used carefully.

JOINs

A JOIN combines rows from two or more tables based on a related column.

Real-World Analogy: Like merging two spreadsheets based on a common column.

Why This Matters: JOINs are often more efficient than subqueries for combining data.

Common Table Expressions (CTEs)

A CTE is a temporary result set defined within the execution scope of a single statement, using the WITH clause.

Real-World Analogy: Like creating a named temporary worksheet to use in your main calculation.

Why This Matters: CTEs can improve readability and modularity, but may have performance trade-offs.

Key Terminology

  • Correlated Subquery: A subquery that references columns from the outer query.
  • Uncorrelated Subquery: A subquery that is independent of the outer query.
  • Optimization Fence: A barrier in query planning that prevents certain optimizations (CTEs can act as this in some PostgreSQL versions).

4. Detailed Explanation and Examples (25-30 minutes)

Understanding Subqueries, JOINs, and CTEs

Subqueries, JOINs, and CTEs can often be used to solve the same problem, but their performance and clarity can differ significantly.

Practical Applications

  • Subqueries: Filtering based on aggregated or derived data
  • JOINs: Combining related data from multiple tables
  • CTEs: Breaking complex logic into manageable steps

Common Misconceptions

  1. Misconception: “Subqueries are always slower than JOINs.” Reality: Sometimes subqueries are optimized away, and performance can be similar.
  2. Misconception: “CTEs always improve performance.” Reality: CTEs can act as optimization fences, sometimes making queries slower.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Basic Implementation

Suppose you have two tables: orders and customers.

-- Subquery in WHERE clause
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

Explanation:

  • The subquery finds all customer IDs with orders over $100.
  • The outer query selects those customers.

Example 2: Practical Application with JOIN

-- Equivalent query using JOIN
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.total > 100;

Key Points:

  • JOINs can be more efficient, especially with proper indexes.
  • DISTINCT is needed if a customer has multiple qualifying orders.
  • The planner may choose a different execution path.

Example 3: Using a CTE

-- Using a CTE for clarity
WITH high_value_orders AS (
  SELECT customer_id FROM orders WHERE total > 100
)
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM high_value_orders);

Debugging Demonstration

Show a common mistake:

-- Correlated subquery (can be slow)
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 100
);

-- Corrected version (may be faster)
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Retrieve all products that have been ordered more than 5 times.

Instructions:

  1. Write a subquery to count orders per product.
  2. Select products with more than 5 orders.

Starter Code:

SELECT * FROM products
WHERE id IN (
  SELECT product_id FROM orders GROUP BY product_id HAVING COUNT(*) > 5
);

Solution:

SELECT * FROM products
WHERE id IN (
  SELECT product_id FROM orders GROUP BY product_id HAVING COUNT(*) > 5
);

Exercise 2: Intermediate Challenge (15 minutes)

Objective: List customers who have placed at least 3 high-value orders (over $200).

Requirements:

  • Use a CTE or subquery
  • Only include customers meeting the criteria
  • Show customer name and order count

Hints:

  • Aggregate in the CTE or subquery
  • Use HAVING to filter

Exercise 3: Creative Application (15 minutes)

Objective: Find the top 5 products by total sales amount, including product name and total sales.

Challenge:

  • Use JOINs and aggregation
  • Order by total sales descending
  • Limit to 5 results

Extension Ideas:

  • Add a filter for a specific time period
  • Show the top customer for each product

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

  1. Problem: Using correlated subqueries unnecessarily Cause: Each row in the outer query triggers a subquery execution Solution: Rewrite as a JOIN or uncorrelated subquery

  2. Problem: Assuming CTEs are always faster Cause: CTEs can prevent the planner from optimizing across query boundaries Solution: Test with and without CTEs; check EXPLAIN plans

Debugging Tips

  • Use EXPLAIN and EXPLAIN ANALYZE to compare query plans
  • Look for sequential scans on large tables
  • Check for unnecessary DISTINCT or GROUP BY
  • Use indexes to support JOIN and WHERE conditions

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

  • Prefer JOINs for combining tables when possible
  • Use CTEs for readability, but check performance
  • Avoid correlated subqueries on large datasets
  • Always analyze query plans before and after changes

Next Steps in Your Learning Journey

  • Learn about window functions and their performance
  • Explore advanced indexing strategies
  • Practice query optimization on real datasets

9. Summary and Review (5 minutes)

Key Takeaways

  1. Subqueries, JOINs, and CTEs each have strengths and weaknesses
  2. Performance depends on data, indexes, and query structure
  3. Use EXPLAIN to guide your optimization efforts

Quick Review Questions

  1. When is a JOIN preferable to a subquery?
  2. What is an optimization fence, and how can it affect performance?
  3. How can you check if your query is using indexes effectively?

For Instructors

Teaching Tips

  • Encourage students to test queries with EXPLAIN
  • Discuss real-world scenarios where each approach is best
  • Allow time for hands-on query rewriting

Assessment Ideas

  • Have students refactor a slow query using different techniques
  • Use peer review for query clarity and performance
  • Quiz on identifying when to use subqueries, JOINs, or CTEs

Adaptation Notes

  • For beginners: Focus on subqueries and JOINs first
  • For advanced: Dive deeper into CTE optimization and EXPLAIN plans
  • Online: Use shared databases or online PostgreSQL sandboxes
  • In-person: Pair programming for query analysis