--- title: "14 — Performance Optimization: Making Queries Actually Fast" uid: performance-optimization tags: ["sql", "roadmap:sql", "explain", "projection", "indexes", "execution-plan", "query-analysis", "optimizing-joins", "performance"] excerpt: "Optimization is a measurement loop — read the execution plan with EXPLAIN, find the most expensive operation, address it, repeat. Guessing doesn't scale; the plan tells you where the time goes." date: 2026-08-13T03:27:35+0000 source: https://www.aveshina.my.id/en/blog/performance-optimization --- 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. ```figure ``` ## 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](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/](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](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](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](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](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](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](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/](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](https://mode.com/sql-tutorial/sql-performance-tuning) ```quiz Q: What is the first thing to do when optimizing a slow query? - Add indexes to every column involved - Run EXPLAIN to read the execution plan and find the expensive operation correct: 1 explain: Optimization is measurement-driven. EXPLAIN shows the plan and its costs, so I can target the actual bottleneck rather than guessing. Q: A full table scan on a large, selectively-filtered table most often indicates… - a missing index on the filtered column - too many rows in the result correct: 0 explain: If the WHERE clause is selective, the engine should seek via an index, not scan. A scan usually means the index doesn't exist or isn't usable (e.g., the predicate wraps the column in a function). Q: Why is SELECT * usually slower than naming specific columns? - It isn't; the optimizer treats them identically - It forces the engine to read and ship every column, including large fields you don't need correct: 1 explain: Selective projection narrows I/O at every stage of the plan — fewer pages scanned, fewer bytes joined and transmitted. Q: A correlated subquery computing per-row totals is slow. The usual rewrite is… - a JOIN against a grouped subquery (or a CTE), so the work happens once - adding more subqueries correct: 0 explain: Correlated subqueries re-evaluate per outer row. Lifting the logic into a joined grouped query or a CTE computes it once, which is dramatically cheaper at scale. Q: Two logically-equivalent queries can have very different runtimes because… - the optimizer may pick different execution plans (join order, algorithms, index use) for each - one query is "more SQL" than the other correct: 0 explain: The optimizer chooses the plan. Logically equivalent SQL can lead it to different plans with very different costs — which is why we measure rather than guess. ```