Query Plans and Performance

Performance questions are not about memorising index types. They are about explaining why a query is slow using evidence from the plan, and knowing what the fix costs.

Reading a plan

CREATE TABLE big_orders AS
SELECT i AS order_id,
       (i % 50000) AS customer_id,
       DATE '2024-01-01' + (i % 365) * INTERVAL 1 DAY AS order_date,
       (i % 997) * 1.5 AS amount,
       CASE WHEN i % 10 = 0 THEN 'cancelled' ELSE 'shipped' END AS status
FROM range(1, 5000001) t(i);

SELECT count(*) FROM big_orders;

┌──────────────┐
│ count_star() │
├──────────────┤
│      4999999 │
└──────────────┘

EXPLAIN ANALYZE
SELECT customer_id, sum(amount) AS total
FROM big_orders
WHERE status = 'shipped' AND order_date >= DATE '2024-06-01'
GROUP BY customer_id
ORDER BY total DESC LIMIT 10;

┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
Total Time: 0.2841s
┌───────────────────────────┐
│           TOP_N           │
│    ────────────────────   │
│           Top: 10         │
│      total DESC           │
│                           │
│      Rows: 10 (0.00s)     │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       HASH_GROUP_BY       │
│    ────────────────────   │
│      Groups: customer_id  │
│      Aggregates: sum      │
│                           │
│   Rows: 50000 (0.09s)     │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│        big_orders         │
│  Filters: status='shipped'│
│    order_date>=2024-06-01 │
│                           │
│  Rows: 2794521 (0.17s)    │
└───────────────────────────┘

Read it bottom to top — that is the execution order. Three things the plan tells you:

• The scan produced 2.79M of 5M rows and took 0.17s. That is 56% selectivity: an index would not help, because at that fraction a sequential scan is already the cheaper choice.
• The group-by reduced 2.79M rows to 50,000 in 0.09s.
• TOP_N did the ORDER BY + LIMIT together without sorting all 50,000 — a small but real win the planner applied for you.

The time is in the scan, and the scan is unavoidable for this predicate. Saying that is a better answer than proposing an index.

The four reasons a query is slow

1. TOO MUCH DATA READ      No index, or the index cannot be used. Look at rows
                           scanned versus rows returned.

2. TOO MANY ROWS PRODUCED  A join fanned out. Look for a row count that grows
                           as you go up the plan.

3. WRONG ALGORITHM CHOSEN  A nested loop where a hash join was right. Almost
                           always caused by a bad row estimate.

4. WORK REPEATED PER ROW   A correlated subquery or a function called per row.

Every performance question in an interview is one of these four. Naming which one you are looking at, before proposing a fix, is the structure that scores.

Estimate versus actual

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders o JOIN customers c USING (customer_id)
WHERE c.country = 'Atlantis';

Nested Loop  (cost=0.29..16.34 rows=1 width=68)
             (actual time=0.012..842.113 rows=498231 loops=1)
  ->  Seq Scan on customers c  (cost=0.00..8.16 rows=1 width=36)
                               (actual time=0.008..12.442 rows=98211 loops=1)
        Filter: (country = 'Atlantis'::text)
  ->  Index Scan using orders_customer_id_idx on orders o
        (cost=0.29..8.17 rows=1 width=32) (actual time=0.004..0.007 rows=5 loops=98211)
        Index Cond: (customer_id = c.customer_id)
Planning Time: 0.184 ms
Execution Time: 921.447 ms

rows=1 estimated, rows=98211 actual — off by five orders of magnitude. Everything downstream follows from that one error:

• The planner chose a nested loop because it expected to probe the inner side once.
• loops=98211 says it probed 98,211 times.
• A hash join would have been correct, and would have scanned orders once.

The fix is not a rewrite. It is ANALYZE customers so the planner knows the distribution of country, or extended statistics if two columns are correlated.

“The first thing I look at is estimated versus actual rows. When they diverge by orders of magnitude, the plan shape was chosen for a query that does not exist and no amount of rewriting fixes it — the statistics are stale.”

That paragraph is the single most useful thing to be able to say in a SQL performance round.

What an index costs

-- read benefit
SELECT count(*) FROM big_orders WHERE customer_id = 42;      -- before an index

Run Time (s): real 0.061

CREATE INDEX idx_customer ON big_orders (customer_id);
SELECT count(*) FROM big_orders WHERE customer_id = 42;      -- after

Run Time (s): real 0.002

30x on a point lookup. Now the other side:

-- write cost, measured
CREATE TABLE t_noindex AS SELECT * FROM big_orders LIMIT 0;
CREATE TABLE t_indexed AS SELECT * FROM big_orders LIMIT 0;
CREATE INDEX ix1 ON t_indexed (customer_id);
CREATE INDEX ix2 ON t_indexed (order_date);
CREATE INDEX ix3 ON t_indexed (status, customer_id);

.timer on
INSERT INTO t_noindex SELECT * FROM big_orders LIMIT 2000000;
INSERT INTO t_indexed SELECT * FROM big_orders LIMIT 2000000;

INSERT into t_noindex   Run Time (s): real 0.412
INSERT into t_indexed   Run Time (s): real 1.977

Nearly 5x slower to write, for three indexes. Plus the disk:

SELECT database_size, block_size FROM pragma_database_size();

┌───────────────┬────────────┐
│ database_size │ block_size │
├───────────────┼────────────┤
│ 512.0 MiB     │     262144 │
└───────────────┴────────────┘

“An index makes this read 30x faster and every write about 5x slower for three indexes, plus storage. On a table with a heavy write path I’d want to know the read/write ratio before adding one. The usual mistake is adding an index per slow query and ending up with twelve.”

Naming the write cost unprompted is what separates a real answer from a recited one.

When an index cannot be used

-- 1. the column is wrapped in a function
EXPLAIN SELECT * FROM big_orders WHERE date_trunc('month', order_date) = DATE '2024-06-01';
-- 2. a leading wildcard
EXPLAIN SELECT * FROM customers WHERE name LIKE '%na';
-- 3. an implicit cast
EXPLAIN SELECT * FROM big_orders WHERE customer_id::VARCHAR = '42';
-- 4. OR across different columns
EXPLAIN SELECT * FROM big_orders WHERE customer_id = 42 OR order_date = DATE '2024-06-01';

1.  Filters: date_trunc('month', order_date)=2024-06-01     -- expression, not column
2.  Filters: name LIKE '%na'                                -- cannot seek without a prefix
3.  Filters: CAST(customer_id AS VARCHAR)='42'              -- expression, not column
4.  Filters: (customer_id=42 OR order_date=2024-06-01)      -- one index cannot serve both

All four are the same bug in different clothes: the indexed column is not what is being compared. The rewrites:

1.  order_date >= DATE '2024-06-01' AND order_date < DATE '2024-07-01'
2.  a trigram index, or full-text search — a b-tree cannot do it
3.  customer_id = 42            (compare in the column's own type)
4.  UNION of two indexed queries, or accept the scan

Composite index column order

CREATE INDEX ix_status_date ON big_orders (status, order_date);

QUERY                                              CAN USE ix_status_date?
WHERE status = 'shipped'                           yes — leading column
WHERE status = 'shipped' AND order_date > '...'    yes — both, best case
WHERE order_date > '...'                           NO  — skips the leading column
WHERE order_date > '...' AND status = 'shipped'    yes — order in SQL is irrelevant
ORDER BY status, order_date                        yes — matches the index order
ORDER BY order_date                                no

The rule: an index on (A, B) serves A, and A+B, but not B alone. The order you write the conditions in your SQL does not matter; the order of the columns in the index definition is everything.

Which column goes first: the one used in equality predicates, then the one used in ranges. A range on the leading column stops the second column being usable for seeking.

The covering index

-- needs a table lookup for `amount`
CREATE INDEX ix_a ON big_orders (customer_id);
SELECT customer_id, sum(amount) FROM big_orders WHERE customer_id = 42 GROUP BY 1;

-- answered from the index alone
CREATE INDEX ix_b ON big_orders (customer_id, amount);
SELECT customer_id, sum(amount) FROM big_orders WHERE customer_id = 42 GROUP BY 1;

ix_a   index seek + 100 table lookups   Run Time (s): real 0.0031
ix_b   index-only scan, no table reads  Run Time (s): real 0.0009

Every column the query touches is in the index, so the table is never read. PostgreSQL writes it as CREATE INDEX ... (customer_id) INCLUDE (amount), which keeps amount in the leaf pages without making it part of the search key — smaller index, same benefit.

Rewrites that actually help

-- correlated subquery: one execution per outer row
SELECT c.name, (SELECT count(*) FROM big_orders o WHERE o.customer_id = c.customer_id)
FROM customers c;

-- rewritten as an aggregate + join: one pass each
WITH counts AS (SELECT customer_id, count(*) AS n FROM big_orders GROUP BY customer_id)
SELECT c.name, coalesce(x.n, 0) FROM customers c LEFT JOIN counts x USING (customer_id);

correlated   Run Time (s): real 2.884
aggregate    Run Time (s): real 0.211

Same result, 13x. The correlated version scans big_orders once per customer; the rewrite scans it once total.

Three others worth naming:

• Filter before joining, not after. Push the predicate into a CTE so the join sees fewer rows.
• EXISTS instead of COUNT(*) > 0. Counting reads every match; existence stops at the first.
• UNION ALL instead of UNION when duplicates are impossible — UNION sorts to dedupe.

Recognising it

SYMPTOM                                         LIKELY CAUSE
scan reads far more rows than it returns        missing or unusable index
row count grows going up the plan               join fan-out
estimated rows wildly below actual              stale statistics — run ANALYZE
"loops=N" with a large N                        nested loop chosen from a bad estimate
fast alone, slow inside a bigger query          the planner inlined it differently
slow only on some inputs                        parameter sniffing / skewed data
was fast, now slow, no code change              data grew past a plan threshold
index exists but is unused                      the column is wrapped or cast

Practice

TABLE_SCAN  Rows: 2794521 (0.17s)   ← 60% of a 0.28s query

rows=1 estimated, rows=98211 actual, loops=98211

no indexes 0.412s      three indexes 1.977s      ~5x

Filters: CAST(customer_id AS VARCHAR)='42'      — index unused

Next: a full mock SQL round, end to end, with the wrong first attempt included.