Query Structure Patterns for Performance in SQL
Query structure influences how the PostgreSQL planner can execute a query. Two queries that produce identical results can generate very different execution plans depending on how they are written, because the planner's ability to push filters down, choose join strategies, and avoid unnecessary materializations depends on the shape of the SQL it receives.
Query structure influences how PostgreSQL can execute your SQL. Two queries that produce identical results can generate very different execution plans depending on how they are written — because the planner's ability to eliminate rows early and choose efficient join strategies depends on the shape of the SQL it receives.
The most important structural principle is filter pushdown: filtering should happen as early as possible in the query, reducing the number of rows flowing through subsequent joins and aggregations. The earlier rows are eliminated, the less work every downstream operation has to do.
Where you place a filter determines whether the planner can push it to the scan level (reading fewer rows from disk) or must apply it after materializing a larger intermediate result.
-- Filter inside a standalone CTE: CTE may materialize fully before the outer filter applies
WITH recent_orders AS (
SELECT * FROM orders
)
SELECT * FROM recent_orders WHERE order_date > '2024-01-01';
-- Filter directly on the base table: planner can push it to the scan
SELECT * FROM orders WHERE order_date > '2024-01-01';In PostgreSQL 12+, CTEs referenced exactly once are often inlined — the planner treats them like subqueries and can optimize across them. CTEs referenced multiple times are still materialized. Understanding this matters when a CTE produces a large intermediate result that a later filter would cut down significantly.
Correlated subquery cost
Correlated subqueries execute once per outer row. For small outer tables, this is fine. For large tables, one subquery per row becomes the dominant cost in the plan. The planner can sometimes transform a correlated subquery into a join, but not always. When EXPLAIN ANALYZE shows a correlated subquery executing millions of times, rewrite it as a pre-aggregated LEFT JOIN.
Sort late, filter early
A query that sorts a large intermediate result and then filters down to a small output pays for a sort over more data than necessary. Apply filters before the sort step — in a CTE or subquery that reduces the row count first. Window functions that ORDER BY inside OVER also trigger sorts per partition; reducing the partition size with an earlier filter reduces that sort cost.
These patterns work together. Filter early (in a WHERE clause or a CTE that includes its own filter) — then pre-aggregate before joining — then reduce partition size before windowing — then sort. Each step flows into the next. The query's structure should mirror the planner's preferred execution order: eliminate rows as early as possible, then transform what remains.
The one thing that trips people up
Structural intuition without plan verification is speculation. A change that looks like it should help is only confirmed to help when EXPLAIN ANALYZE shows lower actual row counts and costs at the expensive nodes. Run the plan before and after any structural adjustment and compare them directly.
10 Query Structure Patterns for Performance practice problems
Write a query to return each order's `id` and the combined line-item value for every order that has at least one line item.
Write a query to return each `department_id` and the count of active employees assigned to it.
Write a query to return the `id` and `name` of every category that has at least one product assigned to it.
Write a query to return each `department_id` and the average current salary across employees in that department.
Write a query to return each `product_id` and its total line-item count.
Write a query to return each qualifying `employee_id` and their recorded salary `amount`.
Write a query to return each `product_id` and the average price of all `products` in that same category.
Write a query to return each qualifying `product_id` and its total units sold.
Write a query to return each qualifying department's name and its current average salary.
Write a query to return each qualifying `product_id` and its `price`.
These problems are part of the Query Structure Patterns for Performance lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.