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.

HardTechnical

75 practiced

A complex dashboard pulls data from multiple databases via federated queries and is extremely slow. Evaluate pros and cons of three strategies: ETL into a central warehouse, using async replica sync, or pushing computation to the source databases. Recommend which to choose for a BI product that needs freshness within 5 minutes.

Sample Answer

Clarify requirements & constraints:- Freshness SLA: ≤5 minutes- Query complexity: joins/aggregations across sources- Sources: heterogeneous OLTP DBs with different loads- Users: interactive BI product (low-latency UX)- Cost/ops: team limited; acceptable infra changes if justified

Evaluate three strategies

1) ETL into central warehouse (near‑real-time micro-batches)Pros:- Single optimized schema for analytics; simplifies dashboard logic- Fast, predictable query performance; supports complex joins/aggregations- Easier governance, lineage, caching, and user-level permissioningCons:- Introduces latency from batch; achieving 1–5 minute latency needs robust streaming or very frequent micro-batches (higher cost)- More infra + operational overhead (CDC, schema drift)When suitable: use streaming CDC (Kafka, Debezium, Snowpipe/BigQuery streaming) to hit 5-minute freshness.

2) Async replica sync (read replicas or materialized replicas per source)Pros:- Keeps data close to source semantics; lower integration effort than full ETL- Can offload reporting traffic from primary OLTPCons:- Cross‑database federation still required; cross-replica joins can remain slow or complex- Consistency windows and replication lag; operational complexity managing many replicas

3) Push computation to source databases (federated queries / pushdown)Pros:- No data movement; highest freshness (real-time)- Simpler pipeline opsCons:- Source DBs not optimized for analytical workloads → heavy queries hurt OLTP performance and are slow- Network and cross-source join latency; limited scalability- Hard to enforce governance and caching

Recommendation:For an interactive BI product needing ≤5 minutes freshness, choose a hybrid: implement streaming ETL/CDC into a central analytics warehouse (or data lake + materialized marts) for most heavy, cross-source joins and dashboards; use read-replicas or direct pushdown only for lightweight, single-source real-time widgets where absolute freshness trumps latency. Rationale: central warehouse provides consistent low-latency queries and maintainability; streaming CDC can meet 5-minute SLA while protecting OLTP performance. Start with high-impact dashboards converted first, add monitoring, SLA alerts, and incremental materialized views to optimize cost.

HardSystem Design

144 practiced

An operational ETL job updates a reporting table during business hours and causes locking that slows dashboard queries. Propose strategies (copy-then-swap, partition-based swap, online-updates with transactional batches) to minimize locking and quantify their pros and cons for availability and complexity.

Sample Answer

Requirements & constraints:- Reporting table must remain queryable during business hours with low latency for dashboards.- ETL updates run during business hours and currently cause table/row/page locks.- Minimize user-facing downtime, keep data fresh (SLAs e.g., <15–60 min), and balance implementation complexity.

Approach overview (three strategies):

1) Copy‑then‑swap (full table replace)- How: ETL writes a new table (report_snapshot_new), validate, then atomically rename/swap with existing table (report_snapshot → report_snapshot_old).- Availability: Near-zero downtime if DB supports atomic rename or metadata swap (milliseconds). Dashboards switch instantly.- Locking: Locks limited to rename operation (short). No long-lived locks during build.- Complexity: Medium — needs extra storage (≈100% of table), coordination for views/permissions, potential cache invalidation.- Tradeoffs: Best when table size is moderate and full rebuild is acceptable. Recovery simple (keep old to rollback).

2) Partition-based swap- How: Partition the table by time (e.g., hourly/day). ETL builds new partition separately, then attach/swap partitions (ALTER TABLE EXCHANGE PARTITION or partition rename).- Availability: Minimal locking — partition attach is fast (milliseconds to seconds). Dashboards can target current partition set.- Locking: Very small window during partition attach; avoids scanning/rebuilding whole table.- Complexity: Higher - requires partitioning scheme, retention management, and partition-aware queries/indices.- Tradeoffs: Excellent for incremental loads and large datasets. Lower storage overhead than full copy if only one partition is staged.

3) Online updates with transactional batches- How: Perform updates/inserts in small transactional batches (e.g., 1k–10k rows) with reduced isolation (read-committed) and row-level operations; use indexes and upsert strategies; throttle concurrency.- Availability: No table-level locks if DB supports row-level locking. But long-running updates can still cause hotspotting and increased query latency.- Locking: Locks per-row/page during each transaction; cumulative effect can still impact queries, especially with hot rows or full-table updates.- Complexity: High — needs idempotency, resume logic, monitoring, retry/backoff, and careful tuning of batch size and timing.- Tradeoffs: Best when only small subsets change; avoids large storage overhead. Harder to guarantee low impact under heavy update volumes.

Quantified example (approximate):- Rename/partition attach lock: milliseconds → negligible dashboard impact.- Full table rebuild time: depends on size (e.g., 100M rows might take minutes–tens of minutes to build but doesn’t block reads).- Batch update impact: per-batch lock ~10–200 ms; total duration = batches × latency; dashboard latency increases proportionally and can be significant if many batches.

Recommendation for BI context:- If ETL can produce full snapshots and storage is available: copy‑then‑swap for simplicity and predictable availability.- If data is naturally time-partitioned and updates are incremental: partition-based swap — best balance of low locking and storage efficiency.- Use transactional batches only for small incremental updates or when neither swap approach is feasible; combine with throttling and off-peak windows.

Operational best practices:- Add health checks + circuit-breaker: pause ETL if dashboard latency rises.- Warm caches after swap if BI tool caches metadata/results.- Keep one rollback snapshot for quick recovery.- Monitor lock metrics, query latency, and implement alerts tied to SLA thresholds.

MediumTechnical

73 practiced

A BI analyst notices wide variance between cold-cache and warm-cache query times. Explain how caching (OS cache, DB buffer cache, CDN) affects EXPLAIN ANALYZE results and how you would design performance tests that separate cold-cache baseline from steady-state performance for regression analysis.

Sample Answer

Caching layers change observed query cost and timing — EXPLAIN ANALYZE shows actual runtime including I/O and CPU but not whether data came from disk, OS page cache, DB buffer, or upstream CDN. Key effects:- OS cache / DB buffer cache: warm cache drastically reduces physical reads → much lower actual runtime and fewer I/O waits; EXPLAIN ANALYZE will show smaller actual time and less time in seq/scans.- CDN / reverse-proxy: reduces network and backend load for repeated dashboard assets or API results; timings reflect network/cache hit latency, not DB work.- Cold cache: first-run includes physical disk reads, index population, plan compilation and JIT; EXPLAIN ANALYZE shows higher timing and more I/O.

Designing tests to separate cold-cache baseline from steady-state:1. Define scope & environment: identical hardware, controlled load, stable dataset snapshot.2. Isolate layers: decide whether testing DB-only, app+DB, or end-to-end (CDN). For DB-only, disable CDN and minimize network variability.3. Cold-cache baseline: - Ensure DB buffer & OS page cache cleared (e.g., restart DB, drop_caches on Linux) and CDN cleared. - Run the query once (or a small set) and record EXPLAIN ANALYZE + OS metrics (iostat, vmstat).4. Warm-cache / steady-state: - Execute warm-up phase: run the query N times (N determined empirically, e.g., 5–20) to populate caches. - Then run M measured iterations (M ≥ 30) collecting EXPLAIN ANALYZE, latency, CPU, I/O stats.5. Statistical rigor: - Randomize query order if testing many queries to avoid serial correlation. - Compute median, p95, stddev for cold vs warm. Use t-test or Mann-Whitney to detect regressions.6. Automation & regression: - Automate cache-clearing, warm-up, measurement, and metric collection in CI. - Store historical baselines; on new runs compare against thresholds (e.g., >10% increase in median or p95).7. Edge concerns: - Consider plan caching vs data caching: include EXPLAIN (ANALYZE, BUFFERS) to see buffer usage and temp files. - For end-to-end dashboards, include CDN TTL variations, browser caching, and connection pooling effects.

This approach gives clear cold-cache baselines for worst-case I/O and steady-state numbers for day-to-day performance and supports reproducible regression detection.

HardTechnical

72 practiced

You suspect index bloat is causing poor index performance and planner decisions. Explain how you would detect index bloat, what maintenance operations (VACUUM, REINDEX, CLUSTER) can fix it, and the operational considerations (downtime, locks, IO) for each in a production BI database.

Sample Answer

Detection:- Start with metrics and planner signs: rising index scans taking longer, increased sequential scans, planner choosing seq scan over index, higher planner_cost estimates.- Inspect index vs table bloat with queries (Postgres example using pg_stat and pgstattuple):

sql

-- approximate index size vs live tuples
SELECT
  schemaname, relname, indexrelname,
  pg_relation_size(indexrelid) AS idx_bytes,
  idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_all_indexes
WHERE schemaname NOT IN ('pg_catalog','information_schema');
-- precise bloat estimate (requires pgstattuple)
SELECT * FROM pgstattuple('my_index');

- Look for high dead_tuple ratios, large index size with low live tuples, or vacuum_deadlocks in logs. Tools: pgstattuple, pg_freespace, pg_repack, and monitoring (I/O, latency).

Maintenance options and when to use them:1. VACUUM (plain / ANALYZE)- What: Reclaims some dead row space, updates stats.- Use when: regular MVCC churn; planner misestimates due to outdated stats.- Operational impact: non-blocking for reads/writes; uses I/O and CPU; autovacuum should handle most cases. VACUUM VERBOSE shows progress.

2. VACUUM FULL- What: Rewrites table and indexes to compact; exclusive lock.- Use when: extreme bloat and cannot wait for offline window.- Operational impact: takes ACCESS EXCLUSIVE lock on table — blocks reads/writes (downtime), heavy I/O. Schedule during maintenance window.

3. REINDEX- What: Rebuilds one or more indexes.- Use when: index bloat, corrupted index, or many dead index tuples.- Operational impact: default REINDEX acquires exclusive lock on index’s parent table (blocks writes and possibly reads). Postgres supports REINDEX CONCURRENTLY (versions >12) which reduces locking but still uses more time/IO. Reindexing is less intrusive than VACUUM FULL for large tables when targeting just indexes.

4. CLUSTER (or pg_repack)- What: Physically reorder table by index, rebuilds table and indexes.- Use when: want to improve locality for frequent range scans (BI queries), reduce bloat.- Operational impact: CLUSTER acquires exclusive lock on table for entire operation (downtime). pg_repack can do same without long exclusive locks by copying and swapping; requires extra disk space.

Operational considerations for BI production:- Prioritize non-blocking options first: autovacuum tuning, VACUUM (regular), REINDEX CONCURRENTLY, and pg_repack to avoid downtime.- Resource planning: all rebuilds are I/O and CPU heavy — throttle via maintenance_work_mem, vacuum_cost_* settings, schedule during low BI usage windows (nights/weekends).- Disk space: rebuilding needs extra space (about size of table/index). Ensure free space before starting.- Stats & planner: always run ANALYZE after rebuilds so planner uses fresh stats.- Rollout strategy: start with most-bloated indexes, test on staging, notify stakeholders of potential query latency, and have rollback/maintenance windows for VACUUM FULL or CLUSTER.- Monitoring: watch long-running locks (pg_locks), I/O saturation, and query performance post-maintenance.

Example small plan: tune autovacuum thresholds for high-churn reporting tables, run REINDEX CONCURRENTLY on largest indexes during off-peak, use pg_repack for tables needing clustering, reserve VACUUM FULL/CLUSTER for critical maintenance windows.

MediumTechnical

91 practiced

A BI report needs precomputed daily aggregates to deliver sub-second performance. Compare a materialized view refreshed nightly versus an incremental aggregation table populated during ETL. List pros/cons in terms of staleness, storage, refresh cost, and complexity.

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.