14 — Performance Optimization: Making Queries Actually Fast

Guessing, sprinkling indexes, and hoping was my query-optimization method, and it worked about half the time. Writing it down turned it into a method: optimization is a measurement loop — read the execution plan with EXPLAIN, find the most expensive operation, address it (index, join order, projection, or rewriting), and repeat. [1][2] Guessing doesn't scale; the plan always tells you where the time goes.

The framing that clicked is that the database doesn't run my SQL literally — it hands my statement to an optimizer, which picks an execution plan: which tables to read first, which indexes to use, what join algorithm to apply, in what order [1][2]. Two logically-equivalent queries can have wildly different plans and wildly different runtimes. My job isn't to "make the query faster" in the abstract; it's to understand the plan the optimizer chose and give it a query it can plan well.

Query analysis: read the plan first

EXPLAIN (Oracle/PostgreSQL/MySQL) or EXPLAIN PLAN (Oracle) shows what the optimizer intends to do with my query — the access method for each table, the join algorithm, the estimated row counts and cost per step [3][4]. This is the single most important tool, because it replaces guessing with observation:

EXPLAIN SELECT c.name, SUM(o.total)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.region = 'APAC'
GROUP BY c.name;

What I'm scanning the plan for:

• Full table scans on large tables. A Seq Scan / FULL SCAN on a million-row table is the usual suspect. If the predicate (your WHERE condition) is selective, that's a missing index.
• Joins on unindexed columns. A join condition that isn't backed by an index forces a nested-loop or hash over more rows than necessary.
• Row-count misestimates. If the optimizer thinks a step produces 10 rows and it actually produces a million, the plan it picked is probably wrong — often a sign of stale statistics.

The plan tells me where; the next levers tell me what to change.

Using indexes — make lookups cheap

Most slow queries are slow because they scan when they should seek. Adding an index on the columns used in WHERE, JOIN ... ON, and ORDER BY turns a scan into a logarithmic lookup [5][6]. The discipline from the index notes still applies: index for the queries I actually run, lead composite indexes with the most selective column, and confirm via EXPLAIN that the index is being used (not silently bypassed because the predicate wrapped the column in a function).

Optimizing joins

For multi-table queries, the join strategy dominates cost [7][8]:

• Index the join columns. ON o.customer_id = c.id should have an index on orders.customer_id (and customers.id is the primary key).
• Filter before joining. A WHERE that prunes rows before the join shrinks the inputs, so the join does less work.
• Reduce the join count and the projected width. Fewer joins and fewer selected columns mean less data shuffled between stages.
• Pick the right join type for the question. Don't use a LEFT JOIN when only matched rows matter — an INNER JOIN gives the optimizer more freedom.

Reducing subqueries

Correlated subqueries — re-evaluated once per outer row — are a classic bottleneck [9]. The fix is usually structural:

• Replace a correlated subquery with a JOIN against a grouped subquery, so the aggregation happens once.
• Lift a subquery used in multiple places into a CTE (WITH name AS (...)), which both avoids re-execution and improves readability.
• For an expensive subquery used repeatedly, materialize it into a temporary table.

The general principle: anything computed per-row that could be computed once should be computed once.

Selective projection

The simplest win, often overlooked: select only the columns I need [10]. SELECT * forces the engine to read and ship every column, including large text and binary fields I'll never render. Naming the columns I actually use narrows the I/O at every stage of the plan — scans touch fewer pages, joins shuffle fewer bytes, the network sends less.

How I use this

The habit I keep is the measurement loop, strictly in order: never add an index or rewrite a query without first reading the plan. I run EXPLAIN, identify the single most expensive node, change exactly one thing to address it (an index, a join-column index, a narrower projection, a rewritten subquery), re-run EXPLAIN, and confirm the cost dropped. Optimization one lever at a time, measured each step, is how I avoid the "I added five indexes and it's somehow slower" outcome — because each change is verified before the next is layered on.

References

[1] Mode Analytics, "Performance Tuning SQL Queries," mode.com, 2024. [Online]. Available: https://mode.com/sql-tutorial/sql-performance-tuning

[2] Stackify, "SQL performance tuning," stackify.com, 2024. [Online]. Available: https://stackify.com/performance-tuning-in-sql-server-find-slow-queries/

[3] Snowflake, "EXPLAIN," docs.snowflake.com, 2024. [Online]. Available: https://docs.snowflake.com/en/sql-reference/sql/explain

[4] Oracle, "EXPLAIN PLAN," docs.oracle.com, 2024. [Online]. Available: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/EXPLAIN-PLAN.html

[5] Stack Overflow, "What is an index in SQL?," stackoverflow.com, 2024. [Online]. Available: https://stackoverflow.com/questions/2955459/what-is-an-index-in-sql

[6] YouTube, "SQL Indexes — Definition, Examples, and Tips," 2023. [Online]. Available: https://www.youtube.com/watch?v=NZgfYbAmge8

[7] Dezbor, "How to Optimize a SQL Query with Multiple Joins," dezbor.com, 2024. [Online]. Available: https://dezbor.com/blog/optimize-sql-query-with-multiple-joins

[8] YouTube, "Secret to optimizing SQL queries," 2023. [Online]. Available: https://www.youtube.com/watch?v=BHwzDmr6d7s

[9] DeveloperNation, "12 Ways to Optimize SQL Queries," developernation.net, 2024. [Online]. Available: https://www.developernation.net/blog/12-ways-to-optimize-sql-queries-in-database-management/

[10] Mode Analytics, "SQL Tutorial — Selective projection," mode.com, 2024. [Online]. Available: https://mode.com/sql-tutorial/sql-performance-tuning