Query Performance

Because SQL is declarative, the database must decide how to execute each query.

Concept

Because SQL is declarative, the database must decide how to execute each query. Performance tuning means understanding that process and helping the database choose (and be able to use) efficient strategies.

Application
    ↓
SQL Query
    ↓
Parser ── syntax check, parse tree
    ↓
Binder / Analyzer ── resolve names, types, permissions
    ↓
Rewriter ── expand views, simplify expressions, decorrelate subqueries
    ↓
Query Optimizer ── enumerate candidate plans, estimate cost using statistics, pick cheapest
    ↓            (plan may be cached and reused for parameterized queries)
Executor ── run plan operators (scan, seek, join, sort, aggregate)
    ↓
Storage Engine ── buffer pool, locks, I/O
    ↓
Tables / Indexes

Why It Matters

A query that takes 30 seconds instead of 30 milliseconds can bring down an application under load. Most performance problems come from a small set of causes: missing or unusable indexes, returning too much data, poor join strategies caused by bad estimates, and N+1 query patterns in application code.

Syntax

Warning: EXPLAIN ANALYZE executes the statement. For INSERT/UPDATE/DELETE, wrap it in a transaction and roll back.

-- PostgreSQL
EXPLAIN SELECT ...;                         -- estimated plan
EXPLAIN ANALYZE SELECT ...;                 -- runs the query, shows actual rows and times
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- MySQL 8
EXPLAIN SELECT ...;
EXPLAIN FORMAT=TREE SELECT ...;
EXPLAIN ANALYZE SELECT ...;                 -- 8.0.18+

-- SQL Server
SET SHOWPLAN_TEXT ON;  -- or "Display Estimated/Actual Execution Plan" in SSMS
SET STATISTICS IO ON; SET STATISTICS TIME ON;

-- Oracle
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- SQLite
EXPLAIN QUERY PLAN SELECT ...;

Example

20.1 Reading an execution plan Illustrative PostgreSQL output on a large dataset:

How to read it:

Read inside-out, bottom-up: the most indented nodes run first and feed their parents.

cost=start..total — optimizer's estimate in arbitrary units.

rows= (estimate) vs actual ... rows= — large differences mean bad statistics or complex predicates, a common root cause of bad plans.

loops= — how many times the node executed (important for nested loops).

Look for the node where most time is spent.

20.2 Access methods: full table scan, index scan, index seek SQL Server name MySQL EXPLAIN type Full table scan Table Scan / Clustered Index Scan Read every row Index seek (point/range lookup) Index Scan (with Index Cond) ref, eq_ref, range, const Navigate the B-tree to matching entries Full index scan

Index Scan without condition / Index Only Scan Read the whole index in order Index-only (covering) Index Only Scan Index Seek without Key Lookup Using index in Extra No table access Bitmap Index Scan + Bitmap Heap Scan

Collect matching row locations, then read table pages in order Row fetch after index (part of Index Scan) Key Lookup / RID Lookup Fetch non-indexed columns

A full scan is not always bad: for small tables or queries returning a large fraction of rows, it is the fastest option.

20.3 Join algorithms (conceptual) Nested loop join For each row in the outer input, look up matches in the inner input (ideally via index)

Outer input is small and inner has an index on the join key O(N × log M) with index; O(N × M) without

Build a hash table on the smaller input, probe it with each row of the larger Large unsorted inputs, equality joins O(N + M), needs memory Merge join

Both inputs sorted on the join key; walk them together Inputs already sorted (indexes) or large sorted sets O(N + M) plus sorting if needed

MySQL historically only had nested loop joins (hash join added in 8.0.18); PostgreSQL, SQL Server, and Oracle use all three.

20.4 Sorting cost ORDER BY, DISTINCT, UNION, GROUP BY (sort-based), and window functions may require sorting: O(n log n), and spilling to disk when data exceeds memory (work_mem in PostgreSQL, sort_buffer_size in MySQL, memory grants in SQL Server).

Avoid unnecessary sorts:

Use UNION ALL instead of UNION when possible.

Do not add DISTINCT "just in case".

Provide an index whose order matches ORDER BY ... LIMIT n.

Do not ORDER BY in subqueries/views when the outer query re-sorts anyway.

20.5 Optimization techniques with before/after examples 1. Filter early and on sargable predicates 2. Avoid SELECT \*

3. Avoid unnecessary subqueries and repeated work 4. Use EXISTS instead of COUNT for existence checks

5. Avoid OR across different columns when it prevents index use

6. Keyset pagination instead of large OFFSET (Module 9).

7. Batch operations instead of row-by-row (avoid N+1) 8. Match data types between join columns and between parameters and columns.

9. Keep statistics current after bulk loads (ANALYZE).

10. Pre-aggregate with materialized views or summary tables for dashboards.

20.6 Practical slow-query troubleshooting Also check factors outside the query: lock waits (blocked by another transaction), server resource saturation (CPU, I/O, memory), network latency, and connection pool exhaustion.

EXPLAIN ANALYZE
SELECT c.name, SUM(o.total_amount)
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE '2026-01-01'
GROUP BY c.name;

HashAggregate  (cost=2890.10..2902.60 rows=1000 width=48) (actual time=41.2..41.9 rows=987 loops=1)
  Group Key: c.name
  ->  Hash Join  (cost=38.50..2640.10 rows=50000 width=22) (actual time=0.6..30.8 rows=49211 loops=1)
        Hash Cond: (o.customer_id = c.customer_id)
        ->  Index Scan using ix_orders_order_date on orders o  (cost=0.43..2465.0 rows=50000 width=14)
              (actual time=0.02..15.1 rows=49211 loops=1)
              Index Cond: (order_date >= '2026-01-01'::date)
        ->  Hash  (cost=25.00..25.00 rows=1000 width=16) (actual time=0.5..0.5 rows=1000 loops=1)
              ->  Seq Scan on customers c  (cost=0.00..25.00 rows=1000 width=16)
Planning Time: 0.3 ms
Execution Time: 42.5 ms

Nested loop                     Hash join                          Merge join
for o in orders:                build: hash(customers.id)          sorted A: 1 2 2 3 5
  seek customers by o.cust_id   probe: for o in orders:            sorted B: 1 2 3 3 4
                                  look up hash(o.cust_id)          advance pointers together

-- Slow: function on the column prevents index use
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2026;

-- Fast: range on the bare column
SELECT * FROM orders
WHERE order_date >= DATE '2026-01-01' AND order_date < DATE '2027-01-01';

-- Reads and transfers every column; prevents index-only scans
SELECT * FROM orders WHERE customer_id = 2;

-- Only what you need; can be served by a covering index
SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = 2;

-- Slow pattern: correlated subquery per row for each column
SELECT c.customer_id,
       (SELECT COUNT(*)          FROM orders o WHERE o.customer_id = c.customer_id) AS n,
       (SELECT SUM(total_amount) FROM orders o WHERE o.customer_id = c.customer_id) AS total
FROM customers c;

-- Better: one aggregation, one join
SELECT c.customer_id, COUNT(o.order_id) AS n, COALESCE(SUM(o.total_amount), 0) AS total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

-- Counts all matching rows
SELECT CASE WHEN (SELECT COUNT(*) FROM orders WHERE customer_id = 5) > 0 THEN 'yes' ELSE 'no' END;
-- Stops at the first match
SELECT CASE WHEN EXISTS (SELECT 1 FROM orders WHERE customer_id = 5) THEN 'yes' ELSE 'no' END;

-- May cause a full scan
SELECT * FROM customers WHERE email = '[email protected]' OR name = 'Ben Carter';
-- Can use two indexes (many optimizers do this automatically via bitmap OR / index merge)
SELECT * FROM customers WHERE email = '[email protected]'
UNION
SELECT * FROM customers WHERE name = 'Ben Carter';

N+1 anti-pattern (application code):
  orders = SELECT * FROM orders WHERE ...            -- 1 query
  for each order: SELECT * FROM customers WHERE id = order.customer_id   -- N queries
Fix: one JOIN query, or SELECT ... WHERE id IN (...list...)

1. Identify     → slow query log (MySQL), pg_stat_statements (PostgreSQL),
                  Query Store / DMVs (SQL Server), AWR/ASH (Oracle), APM tools
2. Reproduce    → run with the same parameters; note time and rows returned
3. EXPLAIN      → get the actual plan (EXPLAIN ANALYZE / actual execution plan)
4. Find the hot node → which operator takes most time / reads most rows?
5. Compare estimates vs actuals → big mismatch? update statistics, simplify predicates
6. Check access paths → full scans on large tables? missing or unusable index?
7. Check joins → nested loop with huge loops count? missing join index? fan-out?
8. Check sorts/spills → sort to disk? add index or memory, reduce rows first
9. Fix one thing at a time → index, rewrite, stats, schema change
10. Measure again → compare time, logical reads; test on production-like data volumes
11. Monitor → watch for regressions (plan changes, data growth)

Expected Result

MySQL EXPLAIN before and after adding an index on orders(customer_id) (illustrative):

type changed from ALL (full scan) to ref (index lookup); estimated rows examined dropped from ~1 million to 11.

Common Mistakes

Tuning on a tiny development database where every query is fast.

Adding indexes without checking the plan.

Ignoring estimate-vs-actual row mismatches.

Running EXPLAIN ANALYZE on a DELETE in production without a transaction.

Optimizing a query that runs once a month while ignoring one that runs 10,000 times per minute (look at total time = calls × mean time).

Using query hints as a first resort (they freeze a plan that may become bad as data changes).

Best Practices

Measure first; optimize the biggest total-time consumers.

Keep predicates sargable; avoid functions on indexed columns.

Select only needed columns and rows.

Index for your workload; verify with EXPLAIN.

Keep statistics current.

Test with production-like data volumes and distributions.

Use parameterized queries (also enables plan caching).

Interview Relevance

Very high for backend/data roles: "How do you optimize a slow query?", "What is an execution plan?", "Index seek vs scan?", "What join algorithms exist?", "Why might the optimizer ignore an index?", "What is a sargable predicate?", "What is the N+1 problem?".

Practice Questions

List five reasons a query might be slow even though an index exists on the filtered column.

Rewrite WHERE SUBSTRING(vin, 1, 3) = 'MA1' to be index-friendly.

Explain when a hash join is preferred over a nested loop join.

What does a large difference between estimated rows and actual rows indicate?

Describe step by step how you would troubleshoot a report query that became slow after a data migration.