Databases & SQL Interview Questions: Database Performance

An index is a data structure (commonly a B-tree) that the database maintains alongside a table so that rows matching a WHERE clause or ORDER BY can be located without scanning every row. While a UNIQUE index also enforces uniqueness, that is a secondary effect — the primary purpose of an index is faster reads. Indexes do not compress data or prevent NULLs.

The EXPLAIN keyword (used alone or as EXPLAIN ANALYZE in PostgreSQL/MySQL) instructs the database engine to output the query execution plan — including join strategies, index usage, and estimated costs — without executing the query. This is the primary tool developers use to understand and optimize query performance.

Indexes speed up reads but slow down writes (INSERT, UPDATE, DELETE) because the database must maintain each index whenever data changes. They also consume additional disk space. Over-indexing is a common anti-pattern: only columns that appear frequently in WHERE, JOIN, or ORDER BY clauses benefit enough to justify the write overhead and storage cost.

Most relational databases (PostgreSQL, MySQL, SQLite, etc.) support the EXPLAIN keyword, which instructs the query planner to return the execution plan — showing operations like sequential scans, index scans, joins, and their estimated costs — without executing the query. Developers use EXPLAIN (and EXPLAIN ANALYZE in some databases) to diagnose slow queries and decide whether indexes are being used.

A database index is a data structure that allows the database engine to find rows matching a query predicate without scanning the entire table. This makes SELECT queries with WHERE clauses much faster. Indexes do add overhead to inserts, updates, and deletes because the index must be maintained alongside the table data.

Indexes help the query planner speed up row lookups by filtering (WHERE), joining (JOIN), sorting (ORDER BY/GROUP BY), or enabling index-only scans. If a column appears only in the SELECT list and not in any filtering or ordering clause, an index on that column alone provides no benefit to the planner — the database still needs to access the table rows by another means and then simply read the column value. The index would be unused and just add overhead to writes.

A relational query planner works in four major stages: (1) Parsing — the SQL string is lexed and parsed into a structured parse tree, catching syntax errors early. (2) Rewriting — the parse tree is transformed by applying rewrite rules such as view expansion, subquery flattening, and constant folding. (3) Planning / Optimization — the planner enumerates candidate execution plans (e.g., which indexes to use, which join algorithm to apply) and estimates the cost of each using table statistics (row counts, histograms). (4) Execution — the chosen lowest-cost plan is executed by the storage engine. This pipeline is consistent across PostgreSQL, MySQL, and most other RDBMSs.

Without an index on customer_id, the database must perform a full table scan over all 10 million rows for every query. Creating an index on customer_id allows the database engine to use a B-tree (or similar) lookup and retrieve matching rows in O(log n) time instead of O(n). Reducing the selected columns can reduce data transfer but doesn't eliminate the costly scan, and partitioning on id doesn't help filter by customer_id.

Composite (multi-column) indexes follow the left-prefix rule: the index can only be used efficiently when the query filters start from the leftmost column(s) of the index. Here the index is (status, created_at), so status is the leading column. A query that filters only on created_at — skipping status — cannot use the index for that lookup and will fall back to a full table scan (or index scan). To support the created_at-only filter efficiently, a separate index (created_at) would be needed.

The correct workflow is: (1) Identify which query is slow using logs or monitoring, (2) Inspect its execution plan with EXPLAIN to understand why it's slow (e.g., full table scan, missing index), (3) Apply the appropriate fix such as adding an index, and (4) Verify with EXPLAIN and benchmarking that the change actually improved performance. Skipping the EXPLAIN step before making changes is a common mistake that can lead to ineffective or even harmful modifications.

When a query planner encounters a predicate on a column that has a composite (multi-column) index, the leading-column rule applies: the index can only be used efficiently if the WHERE clause includes the leftmost prefix of the index columns. For an index on (last_name, first_name, dob), a filter solely on first_name skips the leading column last_name, so the planner cannot perform an index range scan and must fall back to a full sequential scan of the table. The clauses that include last_name = 'Smith' both include the leading column so the index is usable; the clause that uses the full key is also fine.

EXPLAIN ANALYZE executes the query and annotates each plan node with estimated cost/rows, actual rows returned, and actual timing (wall-clock time per node and total). This lets you identify bad row-count estimates (estimated vs. actual rows) and expensive nodes (total wall-clock execution time). Network latency is outside the scope of the query planner output entirely. Buffer/block-level cache statistics are only visible with EXPLAIN (ANALYZE, BUFFERS) and even then show buffer hit counts, not specific block addresses.

The FILL_BLANK tests understanding of two fundamental query-planning concepts: (1) after bulk data changes the query planner relies on table statistics, which must be refreshed via ANALYZE (or VACUUM ANALYZE) so that cardinality estimates remain accurate; and (2) an index is not used when the planner estimates the query will touch a large fraction of the table — this threshold is called the 'selectivity' of the predicate. Low selectivity (many matching rows) typically leads the planner to prefer a sequential scan over an index scan because random I/O for most rows is more expensive.

In a PostgreSQL query plan, a Seq Scan (Sequential Scan) reads every page of the table from start to finish — no index is involved. An Index Scan uses the B-tree (or other) index to find matching row pointers and then follows each pointer back to the heap one row at a time to retrieve columns not stored in the index. This row-by-row heap access distinguishes it from the Bitmap Index Scan + Bitmap Heap Scan pair, which instead collects all matching tuple IDs into a bitmap first and then visits heap pages in physical order — a separate plan shape PostgreSQL typically chooses when many rows match. A third variant, Index Only Scan, avoids the heap entirely when all needed columns are covered by the index and the visibility map shows those pages are all-visible.

The intent is to fetch the top 10 players by highest score, making this a leaderboard. ORDER BY score ASC LIMIT 10 is a purely logical bug: it returns the 10 lowest scores, not the highest. Changing it to ORDER BY score DESC LIMIT 10 fixes the correctness issue. Importantly, B-tree indexes on (game_id, score) support both forward (ASC) and backward (DESC) scans with equal efficiency — modern optimizers (PostgreSQL, MySQL, SQL Server) simply traverse the index leaf pages in the opposite direction for DESC, incurring no extra cost. So the bug is entirely about correctness, not performance. The claim that the index should be on (score, game_id) instead of (game_id, score) is wrong: (game_id, score) is the correct column order — placing the equality-filtered column (game_id) first and the sort column (score) second is the standard pattern for this access pattern. The claim that LIMIT is incompatible with indexed lookups and should be removed is wrong: LIMIT actually enables an early-stop optimization, reducing work. The claim that WHERE game_id = 7 should use a range predicate (e.g., game_id >= 7) to allow the index to be used is wrong: WHERE game_id = 7 is an exact equality predicate, which is ideal for the leading index column and allows the index to be used efficiently.

This is the N+1 query problem: one query loads N posts, then the loop fires one additional query per post to fetch its author — N+1 round trips total. Replace it with a single JOIN (or a batched WHERE id IN (...) over the collected author ids) so the database returns posts and author names in one round trip.

For a B-tree index on (a, b, c), PostgreSQL can use a column as an Index Cond only when every column to its left has an equality predicate. Equality on customer_id lets the index seek into a contiguous range; equality on status further narrows that range; a range predicate on created_at can then also be applied as a seek condition because within the fixed (customer_id, status) pair the entries are sorted by created_at. The query WHERE customer_id = 42 AND status = 'shipped' AND created_at >= '2024-01-01' satisfies all three, so the EXPLAIN plan shows all three columns listed in Index Cond. By contrast, a query that omits an equality on status or only filters status through a non-equality expression would force created_at (and possibly status) into Filter rather than Index Cond, because the B-tree cannot skip levels and still maintain the ordering needed for a range seek on later keys.

PostgreSQL's query planner chooses between a sequential scan and an index scan based on cost estimates derived from statistics (pg_statistic). When a predicate matches a large fraction of rows (e.g., a low-selectivity condition like status = 'active' on a table where 95% of rows are active), the planner correctly decides that reading the entire heap sequentially is cheaper than random index lookups plus heap fetches. The planner uses the page-level cost model: random I/O has a higher cost constant (random_page_cost) than sequential I/O (seq_page_cost). An index is only beneficial when selectivity is high (few rows returned). Options about outdated statistics (ANALYZE not run) and fillfactor are distractors — the described behavior is correct planner behavior, not a bug.

The correct sequence for how PostgreSQL processes a query through the planner/optimizer is: (1) Parser – turns SQL text into a parse tree; (2) Analyzer/Rewriter – resolves names, applies rules/views to produce a query tree; (3) Planner/Optimizer – generates and costs candidate plans using statistics, then selects the cheapest; (4) Executor – executes the chosen plan. Understanding this pipeline is essential for diagnosing why plans are chosen and how statistics feed into cost estimation.

A composite index on (status, created_at) supports the WHERE clause filtering on status AND the ORDER BY on created_at within each status bucket, allowing the query planner to do an index range scan and avoid a filesort. A single-column index on created_at alone cannot efficiently satisfy the equality predicate on status first, and a single-column index on status has poor cardinality and still requires a separate sort. Reversing the order to (created_at, status) forces a full scan of the date range before filtering by status, which is far less selective. The covering index on (status, created_at, user_id) would also work but is not among the options — the best minimal choice here is (status, created_at).

MVCC (Multi-Version Concurrency Control) in PostgreSQL keeps old row versions (dead tuples) in the heap to satisfy ongoing transactions that started before the row was updated or deleted. VACUUM reclaims that space, but if autovacuum can't keep up — or a long-running transaction holds an old xmin — table bloat accumulates. Index bloat is a separate but related phenomenon: B-tree index pages also accumulate dead entries that VACUUM marks as reusable but cannot always compact. HOT (Heap Only Tuple) updates avoid index churn for non-indexed columns but don't apply when indexed columns change. Statement-level locking and MVCC snapshots are distinct mechanisms. All three of the listed bloat mitigations (VACUUM FULL, autovacuum tuning, pg_repack) are real strategies.

The EXPLAIN ANALYZE output shows Seq Scan with an estimated 50 000 rows but only 1 actual row returned, and the estimate of cost ~12 000 is far higher than a targeted index seek would be. The planner chose a sequential scan because it believed the predicate would match many rows (poor statistics). After running ANALYZE (or VACUUM ANALYZE), the planner would have an accurate histogram for the account_id column and would likely choose an index scan instead — bringing execution from ~900 ms down to single-digit milliseconds. The other options are either irrelevant or wrong: increasing work_mem helps sorts/hash joins, not seq-scan vs index-scan decisions; creating a GIN index is for full-text/JSONB; and increasing seq_page_cost would make the planner more likely to avoid seq scans but is a global config hack, not a targeted fix.

This question tests deep knowledge of multi-column index column ordering and query planner selectivity. The composite index on (status, created_at) is defined with status as the leading column. Because the query uses an equality predicate on status AND a range predicate on created_at, the planner can use both columns of the index efficiently (equality on the prefix, then range on the second column). Reversing the order to (created_at, status) would allow only the range prefix to be used — the status equality filter would have to be applied as a residual filter on the rows fetched by the range scan, which is less selective. The index on (status, created_at) therefore narrows the result set far more tightly using the B-tree structure, leading to fewer index pages read. EXPLAIN ANALYZE would show a much lower 'rows removed by index recheck' count for the correctly ordered index.

PostgreSQL's EXPLAIN output uses 'cost' units that are dimensionless and relative. The startup cost (first number) is the estimated cost to return the first row, while the total cost (second number) is the cost to return all rows. For a LIMIT node, the planner scales the total cost of the child node proportionally — it does NOT simply run the child to completion. A Hash Join always has a non-zero startup cost (it must build the hash table before returning any row), whereas a Nested Loop can start emitting rows immediately. When a LIMIT 1 is applied, the planner may switch from a Hash Join to a Nested Loop because the scaled total cost of the Nested Loop (almost equal to just its startup cost) beats the Hash Join's startup cost. This is a classic plan-flip scenario caused by the LIMIT hint to the planner, and it explains why adding LIMIT to a query can paradoxically make it slower if the plan flip is incorrect (e.g., when the nested loop's inner side has no index).

PostgreSQL's MVCC keeps old row versions (dead tuples) until VACUUM reclaims them. When a long-running transaction holds an old snapshot, autovacuum cannot remove dead tuples whose xmax is still needed by that snapshot, causing table bloat. The 'transaction ID wraparound' problem is a separate but related concern (vacuum must also freeze old XIDs), but the immediate cause of bloat from long-running transactions is the inability to reclaim dead tuples. Index bloat follows table bloat because the index entries for dead tuples also cannot be reclaimed. pg_stat_activity and pg_locks help diagnose, but the root cause is snapshot retention preventing VACUUM from advancing the 'oldest visible XID' horizon.