Query Optimization and Execution Plans Questions

Focuses on diagnosing slow queries and reducing execution cost through analysis of query execution plans and systematic query rewrites. Candidates should be able to read and interpret explain output and execution plans including identifying expensive operators such as sequential table scans index scans sorts nested loop join hash join and merge join and explaining why those operators appear. Core skills include cost and cardinality estimation understanding join order and predicate placement predicate pushdown and selectivity reasoning comparing exists versus in versus join patterns and identifying common anti patterns such as N plus one queries. The topic covers profiling and benchmarking approaches using explain analyze and runtime statistics comparing estimated and actual row counts proposing and validating query rewrites and configuration or schema changes and reasoning about trade offs when using materialized views caching denormalization or partitioning to improve performance. Candidates should present step by step approaches to diagnose problems measure improvements and assess impact on other workloads.

MediumTechnical

137 practiced

A query sorts on a computed expression like lower(email) and the plan shows a Seq Scan + Sort. Propose an index-based solution and provide the DDL for creating a functional index to support such queries. Also mention limitations and when the index will not be used.

Sample Answer

To avoid a Seq Scan + Sort when ordering by a computed expression like lower(email), create a functional (expression) index on that expression so the planner can scan the index in order and avoid an explicit sort.

Approach:- Create an index on lower(email).- Ensure your query’s ORDER BY exactly matches the indexed expression (including collation, nulls ordering, and case).- If SELECT lists only include indexed columns (or the index is covering), the planner may do an index-only scan.

DDL:

sql

-- PostgreSQL example: functional index on lower(email)
CREATE INDEX idx_users_lower_email ON users (lower(email));
-- If you need a specific collation or case-insensitive using citext:
-- CREATE INDEX idx_users_lower_email_ci ON users (lower(email) COLLATE "C");
-- Covering index (include other columns for index-only scans, PG 11+):
CREATE INDEX idx_users_lower_email_incl ON users (lower(email)) INCLUDE (id, created_at);

Key points / limitations:- The ORDER BY must be exactly "ORDER BY lower(email)" (same expression, same collation and ASC/DESC direction). Queries using ORDER BY email or different function won’t use it.- If the planner still needs to fetch non-indexed columns (and visibility map not all-visible), it may choose a plain index scan + heap fetch or fall back to seq scan + sort.- Expressions must be immutable/deterministic — volatile functions can’t be indexed.- Different nulls ordering (NULLS FIRST/LAST) or different collations may prevent use.- For LIKE/ILIKE filtering, a separate functional index (e.g., lower(email) text_pattern_ops) or trigram index might be better.

Alternatives:- Add a stored/generated column lower_email and index it:

sql

ALTER TABLE users ADD COLUMN lower_email text GENERATED ALWAYS AS (lower(email)) STORED;
CREATE INDEX idx_users_lower_email_gcol ON users (lower_email);

This can be simpler for portability and ensures exact-match use.

MediumTechnical

83 practiced

Your team sees intermittent slow queries tied to autovacuum lag causing visibility map issues and preventing index-only scans. Explain how autovacuum, vacuum, and visibility map interact, and propose a tuning plan to restore index-only scan opportunities without starving autovacuum.

Sample Answer

Autovacuum, VACUUM, and the visibility map (VM) work together to allow PostgreSQL to reclaim space and enable index-only scans. VACUUM physically scans a table, removes dead tuples and — when it confirms a page has only visible tuples — sets the VM bit for that page. Autovacuum is the background orchestrator that runs VACUUM/ANALYZE automatically using cost-based throttling. Index-only scans can be used only when every heap page referenced by an index page has its VM bit set; if autovacuum lags, VM bits stay clear and queries fall back to expensive heap fetches.

Tuning plan to restore index-only scans without starving autovacuum

1. Immediate remediation- Run a manual VACUUM ANALYZE on hot tables to set VM bits now (avoid VACUUM FULL). This restores index-only scans quickly.- While manual vacuum runs, monitor pg_stat_all_tables (n_dead_tup, last_autovacuum) and pg_class.relallvisible.

2. Short-term autovacuum tuning per-table- ALTER TABLE … SET (autovacuum_vacuum_scale_factor = lower_value, autovacuum_vacuum_threshold = lower_value) for high-churn tables so autovacuum triggers earlier.- Temporarily reduce autovacuum_vacuum_cost_delay (or set to 0) and/or increase autovacuum_vacuum_cost_limit for these tables to finish more work per run.

3. Cluster-level capacity adjustments- Increase autovacuum_max_workers if autovacuum jobs are queued.- Raise maintenance_work_mem so vacuums process pages faster.- Reduce autovacuum_naptime modestly to be more responsive.

4. Prevent starvation and side effects- Don’t disable cost controls cluster-wide; instead tune per-table. Use moderate cost_limit increases and finite cost_delay reductions to avoid IO spikes.- Stagger expensive maintenance during off-peak windows using schedule scripts if needed.

5. Monitoring and rollback- Track: index-only scan rates (pg_stat_user_tables, pg_stat_statements), relallvisible fraction, autovacuum worker queue, I/O and load.- If I/O or latency increases, revert cost_limit/delay to safer values and consider a phased approach (smaller maintenance_work_mem increases, more workers).

Rationale: per-table aggressive thresholds and targeted manual VACUUM restore VM bits quickly where needed, while increasing workers and maintenance memory improves throughput. Cost-based controls prevent autovacuum from monopolizing IO; tuning them narrowly avoids starving regular workload. As a data scientist I’d collaborate with DBAs to implement per-table settings, run controlled experiments, and monitor query plans to confirm index-only scan recovery.

EasyTechnical

71 practiced

Define selectivity. Explain with an example why a highly selective predicate is more likely to use an index and how combined predicate selectivity should affect join order choices in the optimizer.

HardSystem Design

117 practiced

Design a safe production rollout plan for creating a large index on a high-traffic table without blocking writes. Include steps such as concurrently building the index, monitoring write/replication metrics, staged rollout, fallback, and deciding when to drop the index if it hurts performance.

Sample Answer

Requirements and constraints:- Create a large index on a high-traffic table without blocking writes, minimal user impact, reversible if performance degrades. DB = PostgreSQL-like supporting CREATE INDEX CONCURRENTLY. Replication exists.

High-level plan (steps):1. Pre-check & baseline - Capture baseline metrics for 24–72h: write latency (avg/p95/p99), commit rate, replication lag, CPU, IO, lock wait counts, autovacuum activity, query latency for critical paths. - Identify affected queries and estimate index size and build time (EXPLAIN, pg_column_size sampling).

2. Build on replica(s) first (canary) - On a follower replica: run CREATE INDEX CONCURRENTLY idx_x ON table(cols); - Validate correctness on replica: run query plans and result correctness. - Monitor replica-specific metrics: index build CPU, IO, replication delay, catchup time.

3. Controlled concurrent build on primary - If replica canary is OK, run CREATE INDEX CONCURRENTLY on primary during a low-traffic window but at normal load (non-blocking for writes). - Monitor: - Write latency (p99 target increase <= X%, e.g., <=10%). - Commit time and aborted transactions. - Replication lag growth (alert if > Y seconds, e.g., 30s). - Background IO and CPU usage (alert on sustained >80%). - Lock wait/sec and deadlocks (should remain near baseline). - Autovacuum backlog.

4. Staged rollout for consumers - If the index is intended for a new query pattern, deploy application changes in stages: 5% → 25% → 100% traffic (feature flag). - For read-heavy queries, use explain ANALYZE to confirm the planner picks the index; collect latency improvements per cohort.

5. Monitoring & metrics dashboard (essential) - Dashboards: write latency (avg/p95/p99), transactions/sec, replication lag, CPU, IO wait, index bloat growth, query plan adoption, query latency for targeted endpoints. - Alerts: - p99 write latency increase >15% for 5 min - replication lag >30s - sustained CPU/IO >85% for 10 min - Logging: capture slow queries and time-of-day correlation.

6. Fallback / rollback plan - If alerts trigger, immediate actions: - Pause application rollout (feature flag). - If index build still in progress, cancel if possible (Postgres: cannot cancel CONCURRENTLY cleanly—plan accordingly). - If index exists and is causing harm, reroute traffic away from affected queries or disable use via query hints or planner cost adjustments (set enable_indexscan = off for that index via session-level GUC for canary clients), then drop index during low-traffic window: DROP INDEX CONCURRENTLY idx_x. - Communication plan and runbook: who executes DROP INDEX, expected time, and rollback verification steps.

7. Decision criteria to keep or drop - Keep index if: - Target query median and p99 latency improved by >=20% and overall system metrics within thresholds. - No sustained replication lag or increased tail write latency beyond threshold. - Drop index if: - System-level regressions (p99 write latency >15% or replication lag >30s) persist beyond a defined rollback window (e.g., 30–60 minutes after mitigation). - Index causes unacceptable resource costs (IO/CPU spike causing other SLAs to fail) or planner chooses index but overall transaction throughput drops >5%.

Trade-offs and notes:- CREATE INDEX CONCURRENTLY avoids write locks but increases IO and can bloat; monitor autovacuum and bloat.- Building on replica first reduces risk but may not fully simulate primary load.- Have automation/scripts to collect diffs of baselines and post-change metrics to make objective decisions.

Example SQL (Postgres):

sql

-- on replica (read-only) or primary during low-risk window
CREATE INDEX CONCURRENTLY idx_table_col ON schema.table(col1, col2);
-- to drop without blocking
DROP INDEX CONCURRENTLY idx_table_col;

This plan emphasizes measurement, staged rollout, clear thresholds for rollback, and operational runbooks so you can safely create or remove a large index without blocking writes.

MediumSystem Design

94 practiced

Compare materialized views, denormalized tables, and caching for a nightly dashboard that aggregates data from a 1TB events table. For each option, list operational complexity, freshness/consistency guarantees, storage cost, and typical query latency you would expect. Which would you choose if dashboards must be updated within 5 minutes of new events?

Sample Answer

Requirements recap: nightly dashboard aggregates a 1TB events table; need to compare materialized views (MVs), denormalized tables (pre-aggregations), and caching across operational complexity, freshness/consistency, storage cost, and typical query latency. Dashboards must be updated within 5 minutes of new events.

Materialized views- Operational complexity: Low–medium. DB-managed refresh options (incremental/fast refresh) simplify ops, but need monitoring, refresh scheduling and dealing with failed refreshes.- Freshness/consistency: Depends on refresh mode. Near-real-time possible with incremental/continuous refresh but many systems only support scheduled refresh (e.g., every minute to hourly). Strong consistency when refreshed; otherwise stale.- Storage cost: Moderate — stores aggregated result set, typically much smaller than base table but depends on granularity.- Query latency: Low (ms–tens of ms) because queries read precomputed results.

Denormalized (pre-aggregated) tables- Operational complexity: Medium–high. You must design ETL/streaming jobs to maintain aggregates, handle late/duplicate events, backfills and schema evolution.- Freshness/consistency: Can achieve sub-minute freshness with streaming (e.g., Kafka+Flink/Spark Streaming) and idempotent updates; correctness requires careful windowing and watermarking.- Storage cost: Moderate to high depending on how many aggregation keys/time buckets you store.- Query latency: Very low (ms) — reads from tuned tables or columnar store.

Caching (query-result cache / CDN / app cache)- Operational complexity: Low. Simple to implement (cache layer + TTL or invalidation hooks), but cache invalidation can be tricky.- Freshness/consistency: Weak — TTL-based caching can be up to TTL stale. Event-driven invalidation improves freshness but adds complexity and risk of missed invalidations.- Storage cost: Low — caches only recent/queried results.- Query latency: Very low for cache hits (sub-ms to ms). Cache misses pay full query cost.

Recommendation for 5-minute freshness:Use denormalized pre-aggregated tables maintained by streaming ETL or incremental jobs (or fast-refresh materialized views if your DB supports continuous incremental refresh). Rationale: you need reliable, consistent sub-5-minute updates and low-latency reads; streaming pre-aggregations give precise control over correctness (late arrivals, dedup) and predictable performance. Combine with a short-TTL cache in front for hot queries to reduce load and MV or snapshot backups for nightly full-rollups.

Unlock Full Question Bank

Get access to hundreds of Query Optimization and Execution Plans interview questions and detailed answers.

Join thousands of developers preparing for their dream job.