SQL Query Optimization: 10 Techniques That Work (2026)

Quick Answer: Where Does Query Optimization Actually Start?

The 45-Second Query

A query runs instantly in development, against a table with 10,000 test rows. It ships. Production has 40 million rows, and the same unchanged query now takes 45 seconds — long enough to time out a web request. The team's first instinct is often to guess: maybe add an index somewhere, maybe rewrite the JOIN, maybe just add a LIMIT and call it fixed. Guessing wastes time, because the actual bottleneck could be any one of a dozen things, and fixing the wrong one leaves the query just as slow.

The techniques in this article are ordered deliberately, starting with the one step that replaces guessing with evidence: reading the query's actual execution plan before changing a single line of SQL.

1. Read the Execution Plan First

EXPLAIN shows the plan the database intends to use; EXPLAIN ANALYZE actually runs the query and shows real timing alongside the plan.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 1024
ORDER BY order_date DESC;

The output shows, in plain terms, whether the database used an index or scanned every row in the table (a "Seq Scan" in PostgreSQL, a full table scan in most other systems), how many rows it estimated versus actually found, and where the real time was spent. A full table scan on a filter condition that should be selective — like a specific customer ID out of millions — is the single most common and most fixable finding.

2. Index the Columns WHERE, JOIN, and ORDER BY Actually Use

An index lets the database jump directly to matching rows instead of scanning the whole table — but only on columns it's actually built for.

-- speeds up any query filtering or joining on customer_id
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

3. Composite Indexes and Column Order

An index spanning multiple columns only fully helps queries that filter on a leading prefix of that column order — not any combination of the columns involved.

CREATE INDEX idx_orders_country_status ON orders (country, status);

-- fully helped: filters on the leading column, country
SELECT * FROM orders WHERE country = 'US';

-- fully helped: filters on both columns, in the index's order
SELECT * FROM orders WHERE country = 'US' AND status = 'shipped';

-- generally NOT helped: skips the leading column entirely
SELECT * FROM orders WHERE status = 'shipped';

Think of a composite index the way a phone book is ordered by last name, then first name — you can jump straight to "Smith, John" efficiently, but you can't efficiently find every "John" across all last names using that same ordering. Build composite indexes with the most frequently filtered, most selective column first.

4. Keep WHERE Clauses Sargable

"Sargable" means a condition can actually use an index. Wrapping an indexed column in a function is the most common way to accidentally lose that ability.

-- NOT sargable: forces the database to compute UPPER() on every row first
SELECT * FROM customers WHERE UPPER(email) = 'TEST@EXAMPLE.COM';

-- SARGABLE: index on email can be used directly
SELECT * FROM customers WHERE email = 'test@example.com';

-- NOT sargable: function on a date column defeats an index on order_date
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- SARGABLE: a range comparison lets the index be used
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

Even with an index on email or order_date, the moment the column is passed into a function inside WHERE, the database generally can't use a standard index for that lookup — it has to evaluate the function for every row first. Rewriting the condition to compare the raw column directly, as shown above, is usually possible and restores the index's usefulness.

5. Filter Before Joining and Aggregating

Reducing the row count as early as possible in a query's logical flow means every downstream step — joins, grouping, sorting — has less data to work through.

-- filtering inside a CTE before the join, instead of joining everything first
WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date >= '2026-01-01'
)
SELECT c.name, r.order_total
FROM customers c
JOIN recent_orders r ON c.customer_id = r.customer_id;

Query planners are often smart enough to push a WHERE filter down before a join automatically, but not always — particularly across more complex queries with several joins and subqueries. Writing the filter explicitly early, as shown here, removes the guesswork and documents the intent clearly for the next person reading the query.

6. Avoid the N+1 Query Problem

This one lives in application code more than in SQL itself, but it's one of the most common real-world performance problems: one query fetches a list of N items, then a separate query runs once per item to fetch related data — N+1 total round trips to the database.

-- THE PROBLEM (in application code, pseudocode):
-- customers = SELECT * FROM customers
-- for each customer: SELECT * FROM orders WHERE customer_id = customer.id
-- 1 query + N queries, one per customer

-- THE FIX: one query with IN, or a JOIN
SELECT * FROM orders
WHERE customer_id IN (101, 102, 103 /* ...all N customer IDs at once */);

Each individual query in the N+1 pattern can look perfectly reasonable and fast on its own — the problem is entirely about volume, running hundreds or thousands of round trips where one or two would do. This is a common trap in ORMs specifically, where lazy-loaded related records silently trigger a new query per row unless eager loading is explicitly configured.

7–10. Four More Quick Wins

These are covered in depth in earlier articles in this cluster — worth restating here as part of a complete optimization checklist.

• 7. Select only the columns you actually need. SELECT * pulls unnecessary data across the network on wide tables — covered fully in the SELECT statement article.
• 8. Avoid large OFFSET for pagination. A growing OFFSET forces the database to scan and discard every skipped row — use keyset pagination instead, covered in the ORDER BY and LIMIT article.
• 9. Watch for join fan-out before aggregating. A one-to-many join inflates row counts before SUM or COUNT runs on top of it — covered in the JOINs article.
• 10. Keep table statistics current. The query planner relies on statistics about data distribution to choose a good plan; running ANALYZE (PostgreSQL) or the equivalent for your database after major data changes keeps those estimates accurate, which directly affects whether the planner picks a good plan or a bad one.

Mistakes That Give Away a Beginner

• Guessing at a fix without running EXPLAIN ANALYZE first — the exact trap from this article's opening. Confirm the bottleneck before changing anything.
• Wrapping an indexed column in a function inside WHERE, silently losing the ability to use an existing index.
• Adding an index to every column "just in case," ignoring the real write-performance and storage cost of each one.
• Not noticing an N+1 pattern in application code because each individual query looks fast in isolation.
• Never re-testing after a change to confirm the fix actually improved the execution plan, rather than assuming it did.

Practice: Diagnose a Slow Query

Given a query SELECT * FROM orders WHERE LOWER(status) = 'shipped' ORDER BY order_date DESC LIMIT 20 OFFSET 50000; against a 10-million-row table, identify the issues using what this article covered:

Frequently Asked Questions

Conclusion: Evidence Over Guessing

Every technique in this article matters only in proportion to what EXPLAIN ANALYZE actually shows — that's the habit worth taking away above any individual tip. Index deliberately based on real query patterns, keep WHERE clauses sargable, filter as early as possible, and watch for N+1 patterns creeping in from application code. A query that's instant on a small dev table can behave completely differently at production scale, which is exactly why testing performance-sensitive queries against realistic data volume matters.

Practice diagnosing the example query above, then move to the next article in this cluster, which covers SQL vs NoSQL — when a relational database is the right tool, and when it isn't.