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.

EasyTechnical

142 practiced

You run EXPLAIN for the query:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

The planner reports a Seq Scan on orders despite an index on customer_id. List the possible reasons the optimizer prefers a sequential scan, describe steps and SQL commands you would run to diagnose the cause, and outline fixes you might apply.

Sample Answer

Common reasons the planner chooses a sequential scan despite an index:- Low selectivity: customer_id = 123 returns a large fraction of rows, so Seq Scan + filter is cheaper.- Out-of-date or missing statistics: planner under/overestimates row counts.- Index not usable (e.g., expression mismatch, different collation, or wrong type).- Index bloated or disabled (corruption, marked invalid).- Planner cost settings or random_page_cost tuned high for SSDs.- table is very small: sequential scan cheaper due to startup cost.- Visibility issues (recent bulk loads not VACUUMed, many dead tuples).

Diagnostic steps & SQL to run:- Check the explain with actual timing/counts:

sql

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE customer_id = 123;

- Inspect table size & row estimate:

sql

SELECT reltuples::bigint AS est_rows, pg_total_relation_size('orders') FROM pg_class WHERE relname='orders';

- Check index existence and definition:

sql

\d+ orders
SELECT indexdef FROM pg_indexes WHERE tablename='orders';

- Check statistics and null fraction:

sql

SELECT attname, n_distinct, null_frac, most_common_vals FROM pg_stats WHERE tablename='orders' AND attname='customer_id';

- Check bloat and dead tuples:

sql

SELECT n_dead_tup, n_live_tup FROM pg_stat_all_tables WHERE relname='orders';

- Check planner cost settings:

sql

SHOW random_page_cost;
SHOW seq_page_cost;
SHOW effective_cache_size;

Possible fixes:- If low selectivity: accept Seq Scan or add a covering index (include columns used) or partition by customer_id.- If stats stale: run ANALYZE or VACUUM ANALYZE:

sql

ANALYZE orders;
VACUUM VERBOSE orders;

- If index not matching query: create appropriate index (e.g., same type/collation or expression):

sql

CREATE INDEX ON orders (customer_id);
-- or for frequent queries selecting other cols:
CREATE INDEX ON orders (customer_id) INCLUDE (order_date, total);

- If bloat / many dead tuples: VACUUM or consider REINDEX:

sql

REINDEX TABLE orders;
VACUUM FULL orders; -- with caution (locks table)

- If planner costs wrong: tune random_page_cost/effective_cache_size to reflect environment.- If table small: no action required.

Explain trade-offs: adding indexes speeds selective reads but increases write overhead and storage; partitioning helps large tables but complicates queries. After fixes, re-run EXPLAIN ANALYZE to confirm improvement.

HardTechnical

92 practiced

A table contains very wide JSONB columns and queries filter inside JSONB fields. Explain strategies to optimize such queries: expression indexes, creating generated columns, using document stores, or moving to a columnar schema. Discuss trade-offs in flexibility vs performance.

Sample Answer

Start by clarifying requirements: query patterns (which JSON paths, selectivity, read/write ratio), data size, and latency targets. Then choose one or a combination of these strategies.

1) Expression (JSONB path) indexes- Use GIN index for containment queries: CREATE INDEX ON table USING gin (jsonb_col);- For highly selective single-path filters, use expression/btree index: CREATE INDEX ON table (( (jsonb_col ->> 'userId') )); - Pros: minimal schema change, fast lookups for indexed paths, flexible for evolving documents.- Cons: GIN indexes can be large and slower for equality; expression indexes must be created per path and maintained on writes.

2) Generated (stored) columns- Create a persisted column that extracts a value and index it: CREATE TABLE t (..., user_id text GENERATED ALWAYS AS (jsonb_col->>'userId') STORED); CREATE INDEX ON t (user_id);- Pros: Query planner can use btree indexes, smaller index size, predictable performance, good for frequent filters/joins.- Cons: Less flexible (schema coupling), requires migration to add new generated columns; storage and write overhead.

3) Document stores (e.g., MongoDB, DynamoDB, Elasticsearch)- Use if access patterns are document-centric, need schemaless writes, or require built-in secondary indexes/scaling.- Pros: Natural fit for nested documents, horizontal scaling, fast path queries.- Cons: Loses relational features, ACID trade-offs, additional system to manage and integrate.

4) Columnar / Relational normalization- Pull repeated or highly queried fields into dedicated columns/tables in a normalized or columnar warehouse (BigQuery, Redshift).- Pros: Best read performance and compression for analytics, easier indexing and statistics.- Cons: Upfront ETL cost, reduces flexibility for arbitrary JSON fields.

Trade-offs summary- Flexibility vs performance: JSONB+expression indexes preserves flexibility but costs index size and write overhead. Generated columns and columnar schemas give predictable, high performance but require schema design and ETL work. Document stores trade relational guarantees for natural document query performance and scale.- Operational cost: more indexes and stored columns increase write latency and storage; moving systems increases integration/maintenance overhead.

Recommendation for a Data Engineer- Start by measuring: collect slow-query samples and cardinality of JSON paths.- Add selective expression indexes for the hottest paths first.- If paths are stable and heavily used, add generated columns + btree indexes.- For analytics at scale, ETL frequently accessed fields into a columnar warehouse.- Consider a document store only if operational complexity is justified by access patterns.

EasyTechnical

140 practiced

You see a query plan with a Nested Loop Join where the inner side is driven by an index scan and the outer table has 1M rows. Explain when a nested loop is appropriate and when you would expect the optimizer to choose hash or merge join instead. What factors determine join algorithm selection?

HardTechnical

78 practiced

A long-running aggregation query reads a large amount of data. Explain trade-offs between computing aggregations on demand, pre-aggregating via ETL jobs, and using materialized aggregates. Include cost models (compute vs storage) and how you'd project break-even points.

Sample Answer

Requirements & constraints:- Query pattern: frequency (q/day), latency SLO, freshness window, dimensionality (cardinality), allowed staleness.- Data size: raw bytes per day (D), growth rate, compute cost per byte scanned (c_scan), storage cost per GB-month (c_store), ETL compute cost per run (c_etl), materialized refresh cost (c_refresh), and expected query cost if on-demand (c_query = D * c_scan_effective).

Option 1 — On-demand compute- Pros: always fresh, no storage overhead, simplest pipeline.- Cons: high per-query compute cost, variable latency; costs scale with q and data scanned.- Cost model: Monthly = q * c_query.- Good when q is small or D small; or freshness critical.

Option 2 — Pre-aggregate via ETL (stored aggregates)- Pros: amortizes compute across queries, lower query latency, enables denormalized models for analytics.- Cons: storage overhead for aggregates, ETL maintenance complexity, staleness between runs.- Cost model: Monthly = #ETL_runs * c_etl + storage_agg * c_store + q * c_query_agg (query cost on smaller dataset).- Good when q large and aggregations reduce data massively (selectivity factor s << 1).

Option 3 — Materialized aggregates (DB materialized views / incrementally maintained)- Pros: near-real-time if incremental, low query latency, often automated by DB.- Cons: storage + maintenance cost, potential write amplification or complex incremental logic.- Cost model: Monthly = c_refresh * refresh_freq + storage_mv * c_store + q * c_query_mv.

Projecting break-even:1) Estimate reduction factor s = size(aggregate)/D and per-query compute ratio r = c_query_agg / c_query ≈ s.2) Solve for q where On-demand cost = Pre-agg cost: q * c_query = #runs * c_etl + storage_agg * c_store + q * c_query_agg => q * (c_query - c_query_agg) = #runs * c_etl + storage_agg * c_store => q_break = ( #runs * c_etl + storage_agg * c_store ) / (c_query * (1 - r))3) For materialized views, replace #runs*c_etl with c_refresh.

Example: D=1TB/day, c_scan=$0.01/GB => c_query≈$10 per full scan. Aggregates reduce data 100x (r=0.01), ETL runs once/day c_etl=$50, storage_agg=10GB => $0.30/month. Then q_break ≈ (30*50 + 0.3)/(10*(1-0.01)) ≈ (1500.3)/9.9 ≈151 queries/month.

Operational considerations:- Freshness tolerance: move from on-demand → daily ETL → incremental materialized views as freshness requirements tighten.- Complexity vs ROI: incremental maintenance complexity justified when q >> q_break or when latency gains yield business value.- Hybrid: use on-demand for ad-hoc high-cardinality queries; pre-aggregate common rollups; materialized for dashboards needing low latency.

Recommendation process:- Measure current q and D; run samples to estimate s and c_scan.- Compute q_break and sensitivity analysis (vary s, c_etl, growth).- Pilot ETL + materialized view for top 10 queries; monitor cost and latency, then generalize.

HardTechnical

70 practiced

You want to reduce planning time for very complex queries that are re-run often with different parameters but identical structure. Describe caching approaches (prepared statements, stored procedures, plan normalization) and their pros/cons in a high-concurrency environment.

Sample Answer

Start by clarifying the problem: identical SQL shape re-run with different literals => planning/compilation dominates. Three common caching approaches: prepared statements, stored procedures, and plan normalization/fingerprinting. Below I describe each, trade-offs in high-concurrency environments, and recommended mitigations.

Prepared statements- What: DB parses and generates a plan once for a statement with parameters; client binds values at execute.- Pros: Low per-execution planning cost, client-driven lifecycle, works with many DBs and drivers, supports parameter type specificity.- Cons: Connection-affine plan caches (per-session) waste memory under many short-lived connections; contention on statement cache; parameter sniffing can produce suboptimal plans for some parameter values.- Mitigations: use pooled connections to reuse prepared handles, limit cache size/eviction, client-side statement pooling.

Stored procedures- What: Server-side compiled code that runs without re-parsing SQL text.- Pros: Plan persisted on server, reduces network round-trips, can embed logic, often benefits from aggressive caching.- Cons: Deployment/release friction, less flexible for ad-hoc queries, similar parameter sniffing risks, may cause hot CPU on single node in centralized DB under high concurrency.- Mitigations: shard or scale read-only workloads, use multiple procedure versions if needed.

Plan normalization / fingerprinting (parameterization)- What: Normalize query text into a canonical form (replace literals with placeholders) so planner reuses cached plans.- Pros: Enables global plan cache hits across sessions; works well when structure identical; reduces plan memory usage vs storing many literal variants.- Cons: Poor defaults can under-parameterize (cache explosion) or over-parameterize (suboptimal generic plans). Requires DB features (forced parameterization, query hash) or front-door proxy that normalizes.- Mitigations: selective normalization (only for stable columns), adaptive caching where you keep both generic and special-case plans; use statistics feedback to recompile when generic plan performs badly.

High-concurrency considerations- Memory and lock contention: global caches must be concurrent-friendly (lock-free LRU, sharded caches). Avoid per-connection caches exploding memory—use connection pooling.- Compilation storms: when cache evicts under load, many sessions may recompile simultaneously. Use jittered backoff, singleflight compilation (leader compiles, others wait), or warm-up on deployment.- Parameter sniffing and skew: detect skewed values and keep specialized plans for hot keys; use histogram-based stats or query-store telemetry to decide.- Distributed systems: in MPP/worker architectures, ensure cached plans are portable (logical plans pushed to workers) and avoid central coordinator becoming bottleneck.

Recommendation- Start with prepared statements + connection pooling to cheaply reduce planning. Add server-side normalization/fingerprinting to share plans across sessions. Use stored procedures for extremely hot, stable workflows requiring lowest latency and security. Implement monitoring (cache hit ratio, compilation rate, tail latency) and automated adaptive rules: keep specialized plans for hot parameter ranges, singleflight compilation, and shard caches to reduce lock contention.

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.