Query Structure Patterns for Performance in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
What affects query performance in SQL?
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.
Why does filtering early make a SQL query faster?
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';SELECT id, total_amount, status FROM orders WHERE status = 'delivered' AND total_amount > 100 ORDER BY total_amount DESC LIMIT 10
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.
When does a correlated subquery become the bottleneck?
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.
Should you filter before or after sorting in SQL?
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.
How do you confirm a query rewrite actually helped?
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 the measured numbers move: run EXPLAIN ANALYZE before and after and compare Execution Time and the actual row counts at the expensive nodes. The cost= figures are not evidence. They are the planner's estimate, and EXPLAIN ANALYZE prints them unchanged from plain EXPLAIN — in the same plan where the estimate says 293 rows and the actual count is 183. Run the plan before and after any structural adjustment and compare them directly.
Practice Query Performance in SQL
Scenario: Brightlane's fulfillment analytics team needs each order paired with the combined value of its line items, computed from line-item data rather than any pre-stored total.
Task: 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.
Assumptions:
- A line item's value is
quantitymultiplied byunit_price. - An order's
total_item_valueis the combined line-item value across all of its line items. - The result covers only
orderswith at least one line item on record.
Output:
- One row per order with at least one line item.
- Columns in this order:
order_id,total_item_value.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 Query 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.
Start learning to practice all 10 Query Performance problems, with instant grading and mastery tracking.
Common questions about Query Performance
Does the order of conditions in a WHERE clause affect speed?
Not in the way people expect. The planner is free to evaluate them in whichever order it judges cheapest, so rewriting the sequence by hand usually changes nothing. What does matter is whether a condition can use an index at all.
Why does wrapping a column in a function slow a filter down?
Because an ordinary index stores the column values, not the results of calling a function on them, so the database has to compute the expression for every row before it can compare anything. Filtering on the bare column, or indexing the expression itself, is what gets the index back.
How do you know a rewrite actually helped?
Compare plans before and after with EXPLAIN ANALYZE, and look at actual rows and actual time on the expensive nodes. A rewrite that looks tidier but moves neither number has not helped, however much sense it makes on the page.
