SQL Performance and Anti Patterns Questions

Recognition and remediation of common SQL performance anti patterns and pitfalls, such as accidental cartesian joins, N plus one query patterns, inefficient correlated subqueries, using functions in WHERE clauses that prevent index use, SELECT star usage, lack of appropriate indexes, large unbounded sorts or aggregations, and poor join ordering. Covers methods to diagnose problems using execution plans, explain analyze, and rewriting queries for better performance and scalability.

EasyTechnical

48 practiced

You ran EXPLAIN on a query and saw lines like: Seq Scan on orders (cost=0.00..1200.00 rows=100000 width=48) Filter: (status = 'completed') and elsewhere Index Scan using idx_orders_customer_id on orders (cost=0.30..50.00 rows=10 width=40). Explain what 'seq scan', 'index scan', 'cost', and 'rows' mean in planner output and what signals you would look for to decide whether to add an index.

MediumTechnical

58 practiced

Explain index selectivity and cardinality and how they affect index design. Given two columns status (low-cardinality: 4 distinct values) and user_id (high-cardinality), recommend ordering for a composite index used in queries that filter both columns and explain why.

Sample Answer

Selectivity and cardinality are related but distinct concepts that guide index design.

- Cardinality: the number of distinct values in a column (e.g., user_id has very high cardinality; status with values {new, active, archived, deleted} has low cardinality).- Selectivity: how well a predicate narrows rows — formally, 1 / (fraction of rows matching). High selectivity means few rows match (good for indexes); low selectivity means many rows match.

How they affect index design:- Indexes are most effective when they filter to a small subset (high selectivity). A column with high cardinality typically yields high selectivity for equality filters (user_id = 123).- Low-cardinality columns (status) usually have poor selectivity; indexing them alone rarely helps for point lookups but can be useful when combined with other columns or for covering/index-only queries.

Recommendation for composite index on (status, user_id) vs (user_id, status):- Put the more selective / higher-cardinality column first: (user_id, status).Why:1. Query patterns: If you filter both columns (WHERE user_id = ? AND status = ?), an index starting with user_id supports an index seek directly. An index starting with status then user_id can still be used but the DB first locates all rows for a status (potentially many) and then filters by user_id — less efficient.2. Range/prefix rules: Most RDBMS use leftmost-prefix rules; the leading column should be the one used in selective lookups.3. Size: Putting low-cardinality first increases b-tree fan-out for that level and can produce less efficient seeks.

Example: For queries like SELECT * FROM events WHERE user_id = 42 AND status = 'active', create index(user_id, status). If you often query only by status (e.g., reports by status) consider a separate index on status or a partial/indexed view for that status to avoid bloating indexes.

Edge notes:- If user_id is never queried alone but status often is, tune accordingly.- Consider covering indexes (include other selected columns) and monitor optimizer statistics to ensure accurate selectivity estimates.

EasyTechnical

50 practiced

Explain how missing indexes can cause slow queries. Given query SELECT SUM(total) FROM orders WHERE customer_id = 123 AND order_date >= '2024-01-01'; propose an index to improve performance and explain why column order matters for composite indexes. When might separate indexes be preferred?

Sample Answer

Missing indexes force the database to scan many rows (sequential scan), so queries that should touch a small subset read the whole table — slow for large tables. For your query:

SELECT SUM(total) FROM orders WHERE customer_id = 123 AND order_date >= '2024-01-01';

A good index is a composite index on (customer_id, order_date). If your DB supports covering/included columns, include total so the engine can answer the query from the index alone:

sql

-- PostgreSQL (include total to make it covering)
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date) INCLUDE (total);

Why this helps:- The DB can use the index to quickly find rows for customer_id = 123, then efficiently range-scan order_date >= ... instead of scanning the whole table.- Including total avoids accessing the heap (table), reducing IO for aggregation.

Why column order matters:- Composite indexes follow the leftmost-prefix rule: an index on (customer_id, order_date) supports queries filtering by customer_id or by both customer_id+order_date, but not efficiently by order_date alone. Put the most selective / equality column first (customer_id here), then range columns (order_date).

When separate indexes might be preferred:- If many queries filter only by order_date (and not customer_id), an index on order_date alone could be useful.- If customer_id has very low cardinality (few distinct values) and order_date is highly selective, a single-column index on order_date might perform better.- When the optimizer can do index-merge/intersection efficiently, two single-column indexes can work.Always validate with EXPLAIN ANALYZE and check cardinalities before finalizing.

HardTechnical

46 practiced

Hash join in Postgres spills to disk for your large join. How do you diagnose and tune work_mem to avoid spills, and how do you estimate a safe work_mem given table sizes and expected concurrency? Discuss the trade-offs of increasing per-session memory versus alternative plan changes.

Sample Answer

Approach summary- Diagnose first (prove it’s spilling), then compute a realistic work_mem target for the hash build, consider concurrency and parallelism, and weigh alternatives (plan change, index, batching) before globally raising memory.

Diagnosis steps (how to prove spill + root cause)- Run EXPLAIN (ANALYZE, BUFFERS, VERBOSE) on the query. A hash join that spilled will show “Hash Join” with “work_mem” / “temporary files” activity and non-zero temp blocks read/written.- Check pg_stat_activity / pg_stat_statements for long-running queries and monitor pg_stat_io or temporary file sizes (postgres log: temp_file created).- Reproduce with SET client_min_messages TO log if needed; use auto_explain to log plans that spill.- Confirm which side is build (EXPLAIN shows which table is hashed); only the build side hash needs to fit in work_mem.

Estimating safe work_mem (practical recipe + example)1. Estimate rows that reach the build phase (apply filters/qual predicates). Use EXPLAIN row estimates or a quick count on filtered subset.2. Estimate average tuple width (avg row size). Inspect pg_stats or use SELECT pg_column_size(...) on sample rows.3. Compute raw bytes = rows_build * avg_row_width.4. Apply hash overhead and workspace overhead: multiply by ~1.5–2.0 for hash table metadata and pointers; some recommend 2.0 for safety.5. Add margin for parallel workers: work_mem applies per sort/hash per worker.

Example:- Build side estimated rows = 1,000,000- avg_row_width = 200 bytes- raw = 200 MB- add overhead ×1.5 → ~300 MB- add safety → choose work_mem ≈ 400–512 MB for that operation.

Remember: work_mem is per operation per query and applies to each parallel worker. If you expect 10 concurrent sessions doing similar joins, total worst-case = 10 * 512 MB = 5 GB.

Putting it together for concurrency- Estimate max concurrent heavy operations (N_concurrent).- Total potential memory = N_concurrent * work_mem * expected parallel workers per query.- Compare to physical RAM available (leave room for OS, shared_buffers, other backends). Keep headroom (e.g., don’t exceed 60–70% of RAM for cumulative work_mem).

Tuning strategy (safe path)- Use SET LOCAL work_mem for single session or run SET work_mem for only the reporting session to test; don’t change postgresql.conf globally without impact analysis.- If query is ad-hoc: wrap query in a session-level SET work_mem = '512MB'; run and monitor temp file usage.- Use EXPLAIN ANALYZE before/after to confirm no temp files.

Trade-offs: raising work_mem vs alternatives- Pros of increasing work_mem: - Eliminates disk I/O for hash join → big latency gains. - Simple, fast fix for a single heavy query.- Cons: - work_mem is per operation per backend (and per parallel worker). Raising globally risks large memory consumption under concurrency, OOMs, or server swapping. - Hard to reason at scale; difficult to ensure headroom across many sessions.

Alternatives (often safer or complementary)- Reduce build size: add better WHERE predicates, pre-aggregate, or push predicates to reduce rows hashed.- Use an index and convert to index scan + nested loop (or enable merge join). For sorted inputs, a merge join avoids large hash memory.- Create a smaller/materialized temporary table with only needed columns/rows, then join (less memory, repeated use).- Partitioning or bucketing to process join in chunks (batching) so each batch fits in work_mem.- Use parallelism thoughtfully: too many workers multiplies memory; sometimes fewer workers with larger work_mem per worker is better.- Adjust planner cost settings to prefer merge join or nested loop when appropriate, or use pg_hint_plan to force plan for specific query.- Increase physical RAM or tune other memory settings if many heavy queries are expected.

Operational best practices- Test under expected concurrency (load test) to validate cumulative memory usage.- Use SET LOCAL for per-query tuning in scheduled reports or ETL scripts.- Monitor server memory, temp files, and swap after changes. Log queries that create temp files with auto_explain.

Bottom line- Diagnose with EXPLAIN ANALYZE and temp-file monitoring, estimate required work_mem from build-side rows × avg row size × overhead, multiply by expected concurrency and parallel workers, and prefer targeted per-session/work_mem increases or query rewrites over a large global raise. When safe, combine modest work_mem increases with plan/SQL changes (filtering, pre-aggregation, batching, or indexes) to eliminate spills with predictable memory usage.

MediumTechnical

61 practiced

Given this query: SELECT id, name FROM users WHERE country = 'US' AND created_at >= '2024-01-01' ORDER BY created_at DESC LIMIT 50; which composite index would you create and why? Discuss column order, selectivity, and how the planner uses the index for this pattern.

Sample Answer

Recommended index (PostgreSQL):CREATE INDEX idx_users_country_created_at ON users(country, created_at DESC) INCLUDE (id, name);

Why:- Column order: put the equality predicate column(s) first (country), then the range/ordering column (created_at). Databases can use an index efficiently when equality columns precede range or ORDER BY columns.- Ordering: specifying created_at DESC matches the ORDER BY created_at DESC so the planner can read the index in forward order and return rows already sorted — important for LIMIT.- Covering: including id and name (INCLUDE in Postgres, or appended PK/covered columns in other engines) enables an index-only scan so the engine can satisfy SELECT id,name without visiting the heap, improving performance for repeated reporting queries.

Selectivity considerations:- If country='US' selects a small fraction, country first is highly selective and yields a tight index range. If country is common (e.g., 80% of rows), the created_at predicate’s selectivity matters more — you may instead rely on a partial index (WHERE country='US') or reverse column order in specific engines, but generally equality-first is standard.- Always check cardinalities (ANALYZE) and test with EXPLAIN to confirm the planner’s choice.

How planner uses it:- Planner applies equality on country to seek into the index, then performs a range scan over created_at in descending order. Because rows are returned sorted by created_at, the engine can stop after LIMIT 50 (index-ordered limit scan). If id/name are covered, it can return rows without fetching the base table (index-only scan), reducing I/O.

Notes:- For MySQL/InnoDB: CREATE INDEX (country, created_at) — InnoDB secondary indexes include the PK automatically; include other columns if needed for covering via CONCAT or using a composite that appends id,name.- Consider a partial index: CREATE INDEX ... ON users(created_at DESC) WHERE country='US' if queries are always for US — smaller index, faster scans. Always validate with EXPLAIN and real data distribution.

Unlock Full Question Bank

Get access to hundreds of SQL Performance and Anti Patterns interview questions and detailed answers.

Join thousands of developers preparing for their dream job.