SQL Performance
EXPLAIN plans, indexes, sargable predicates, fan-out cost, columnar storage, and join strategies.
10 questions
JuniorTheoryVery commonWhat is a database index, and when can adding one actually hurt performance?
What is a database index, and when can adding one actually hurt performance?
An index is a sorted B-tree mapping a column's values to row locations, so the planner finds matching rows without a full table scan. But each index is updated on every write and costs disk — on a write-heavy table or a low-selectivity column it adds cost without speeding up reads.
Common mistakes
- ✗Thinking more indexes are always better, even on write-heavy tables
- ✗Ignoring that each index must be maintained on every insert and update
- ✗Adding an index on a low-selectivity column expecting a speed-up
Follow-up questions
- →How does a covering index avoid the extra heap fetch?
- →Why can an index on a boolean column be nearly useless?
MiddleCodeVery commonA join created duplicate rows and someone added SELECT DISTINCT — why is that costly?
A join created duplicate rows and someone added SELECT DISTINCT — why is that costly?
DISTINCT sorts or hashes every column of the exploded result to drop duplicates — costly on wide fan-out rows, and it only masks the bug. The duplicates come from the one-to-many join; fix the cause with WHERE EXISTS (...) (a semi-join) or pre-aggregate to one row per key.
Common mistakes
- ✗Thinking DISTINCT is a cheap streaming flag, not a sort or hash
- ✗Assuming a LEFT JOIN returns one row per order despite fan-out
- ✗Patching duplicates with DISTINCT instead of removing the join fan-out
Follow-up questions
- →How would EXPLAIN show the DISTINCT as a Sort or HashAggregate node?
- →When is EXISTS faster than pre-aggregating the child table?
SeniorDebuggingCommonIdentical SQL is fast in dev but slow in prod — is it stale stats, data skew, or parameter sniffing?
Identical SQL is fast in dev but slow in prod — is it stale stats, data skew, or parameter sniffing?
The plan estimated 5 rows but the Index Scan returned 3.8M, and the planner chose a Nested Loop that only pays off for a few rows — a stale-statistics symptom. Run ANALYZE so the estimate matches reality and it switches to a hash join. Data skew fits when stats are fresh but one value dominates.
Common mistakes
- ✗Calling it parameter sniffing when Postgres re-plans per session
- ✗Attributing the whole slowdown to data skew despite the estimate gap
- ✗Forcing a Seq Scan instead of refreshing statistics
Follow-up questions
- →How would you confirm data skew if ANALYZE did not help?
- →When does a PL/pgSQL generic plan cause a sniffing-like effect in Postgres?
MiddleDebuggingOccasionalA query over 300M rows times out — how do you use EXPLAIN ANALYZE to find the culprit?
A query over 300M rows times out — how do you use EXPLAIN ANALYZE to find the culprit?
Read bottom-up for the node whose actual time and rows dominate. Here a Seq Scan on the 300M-row events estimates 1200 rows but returns 300M — a huge estimate-vs-actual gap from stale statistics, with no index on country. Fix: run ANALYZE events and index the filtered column.
Common mistakes
- ✗Raising statement_timeout instead of fixing the plan
- ✗Blaming the hash-join algorithm rather than the estimate and missing index
- ✗Misreading Rows Removed by Filter as a broken filter
Follow-up questions
- →How does the BUFFERS line confirm the Seq Scan is the bottleneck?
- →Why does a bad row estimate mislead the join algorithm choice?
MiddleCodeOccasionalMake WHERE DATE(created_at) = '2026-01-01' use the index on created_at
Make WHERE DATE(created_at) = '2026-01-01' use the index on created_at
Wrapping the column in DATE() makes the predicate non-sargable — the function runs per row, so the index on created_at is unusable and the planner does a Seq Scan. Rewrite it as a bare-column half-open range on created_at >= '2026-01-01' AND created_at < '2026-01-02'.
Common mistakes
- ✗Casting the constant instead of unwrapping the column
- ✗Blaming the SELECT list rather than the non-sargable predicate
- ✗Forcing enable_seqscan off instead of making the predicate sargable
Follow-up questions
- →When is a functional index on DATE(created_at) the better fix?
- →Why is a half-open range safer than casting to date for a timestamp?
SeniorDesignOccasionalA Spark job joins a 300M-row fact table to five small dimension tables, each under 100k rows, on their keys. It runs slowly, and most of the wall-clock time is spent shuffling data across the network. You can broadcast a small table to every executor (a broadcast join) or repartition both sides by the join key (a shuffle join). The fact table is huge; each dimension is tiny and fits comfortably in executor memory. Which strategy avoids moving the large table across the network, and what is the main risk if you broadcast a table that turns out not to be small enough to fit in memory?
A Spark job joins a 300M-row fact table to five small dimension tables, each under 100k rows, on their keys. It runs slowly, and most of the wall-clock time is spent shuffling data across the network. You can broadcast a small table to every executor (a broadcast join) or repartition both sides by the join key (a shuffle join). The fact table is huge; each dimension is tiny and fits comfortably in executor memory. Which strategy avoids moving the large table across the network, and what is the main risk if you broadcast a table that turns out not to be small enough to fit in memory?
Broadcast the small dimensions: each is copied to every executor, so the 300M-row fact table stays put and is never shuffled — the join runs locally. A shuffle join would repartition both sides by key, dragging the huge fact table across the network. Broadcasting a table too big for memory then risks OOM or spills.
Common mistakes
- ✗Broadcasting the huge fact table instead of the small dimensions
- ✗Assuming a shuffle is always cheaper than replicating a small table
- ✗Ignoring that a broadcast too large for memory triggers OOM or spills
Follow-up questions
- →How does spark.sql.autoBroadcastJoinThreshold decide this automatically?
- →When does a skewed join key defeat both strategies?
JuniorTheoryRareReading an EXPLAIN plan for the first time, what do you check first?
Reading an EXPLAIN plan for the first time, what do you check first?
Read the plan tree bottom-up — leaf scan nodes run first. Check the scan type each table gets (Seq Scan vs Index Scan) and the estimated rows; a bad leaf estimate misleads every node above. EXPLAIN ANALYZE adds actual rows so you see where the estimate was wrong.
Common mistakes
- ✗Reading the plan top-down instead of bottom-up
- ✗Treating the top-line cost as milliseconds rather than an abstract estimate
- ✗Ignoring the row estimates that drive every node above
Follow-up questions
- →What does a large gap between estimated and actual rows tell you?
- →Why is the top node's cost cumulative rather than its own?
MiddleTheoryRareWhen is the planner right to choose a sequential scan over an index scan?
When is the planner right to choose a sequential scan over an index scan?
When a predicate matches a large share of rows, a Seq Scan is cheaper: sequential page reads beat many random index lookups plus heap fetches. The planner weighs estimated rows against page costs — on a broad filter, scanning everything beats the index.
Common mistakes
- ✗Believing an index scan is always faster than a sequential scan
- ✗Deciding scan type by table size instead of predicate selectivity
- ✗Forgetting the random-lookup and heap-fetch cost of an index scan
Follow-up questions
- →How does the random_page_cost setting shift this choice?
- →When does a bitmap heap scan sit between the two?
SeniorPerformanceRareIn a columnar store (ClickHouse, BigQuery), why is SELECT * costly while a full-column scan is cheap?
In a columnar store (ClickHouse, BigQuery), why is SELECT * costly while a full-column scan is cheap?
A columnar store lays each column out contiguously, so a query reads only the columns it names — scanning one full column still touches few of the table's bytes. SELECT * forces reading every column's segment, multiplying I/O and defeating per-column compression.
Common mistakes
- ✗Assuming SELECT * is as cheap as it is in a row store
- ✗Thinking columnar speed comes from caching rather than column pruning
- ✗Believing a full-column scan is expensive because values are scattered
Follow-up questions
- →How does per-column compression amplify the SELECT * penalty?
- →Why does a columnar store still benefit from sort keys?
SeniorPerformanceRareWhy does filtering on a non-partition column scan every partition, even with partition pruning?
Why does filtering on a non-partition column scan every partition, even with partition pruning?
Pruning skips partitions only when the predicate targets the partition key — the planner maps that value to specific partitions and ignores the rest. A filter on any other column can't be mapped, so every partition is scanned; pushdown then only filters rows inside each partition.
Common mistakes
- ✗Assuming pruning works on any WHERE column, not just the partition key
- ✗Confusing predicate pushdown with partition pruning
- ✗Expecting an index to enable pruning on a non-key column
Follow-up questions
- →How does a partition-key expression like date_trunc break pruning?
- →When can the planner prune with a join condition on the key?
