Each test is 5 questions with varying difficulty.
AI Prep covers AI Agents, Generative AI, ML Fundamentals, NLP & LLMs and a lot more, with adaptive tests and daily challenges. Fully offline on Android. Free to try, one-time unlock for lifetime access.
SQL Execution Plans, typically accessed via the EXPLAIN command, are the blueprint of how a relational database management system (RDBMS) processes a query. In 2026, as data volumes grow and latency requirements tighten, the ability to interpret these plans is a critical skill for backend, data, and database engineers. Interviewers use this topic to distinguish between developers who write functional SQL and those who understand the underlying cost-based optimizer (CBO). At a junior level, candidates are expected to identify basic index usage and scan types. Senior candidates must demonstrate deep knowledge of join algorithms (Hash vs. Nested Loop), cost estimation, cardinality miscalculations, and how to resolve performance bottlenecks by manipulating the plan via indexing, statistics updates, or query refactoring.
Understanding SQL Execution Plans is the difference between a system that scales linearly and one that collapses under load. In production environments, an unoptimized query can cause CPU spikes, I/O saturation, and transaction lock contention. For instance, a missing index on a join key can force a full table scan, turning an O(log N) operation into an O(N) operation, which, at scale, results in multi-second latencies. This topic is high-signal because it reveals whether a candidate understands the cost-based optimizer's decision-making process. A strong candidate doesn't just suggest 'adding an index'; they explain how the optimizer calculates the cost of different access paths, why it might choose a nested loop over a hash join based on cardinality estimates, and how to verify the fix using the EXPLAIN output. In 2026, with the rise of complex analytical queries and vector-relational hybrid workloads, the ability to diagnose plan regressions—where a query suddenly slows down due to stale statistics—is a hallmark of a senior engineer.
The execution plan generation process follows a structured pipeline where the SQL statement is transformed from raw text into an optimized tree of operations.
The SQL text is parsed into a parse tree, validated against the system catalog, rewritten for canonical form, optimized by evaluating various access paths against cost models, and finally executed as a tree of physical operators.
SQL Statement
↓
[Parser / Analyzer]
↓
[Query Rewriter]
↓
[Cost-Based Optimizer]
↓
[Execution Plan Tree]
↓
[Execution Engine]
↓
[Storage Engine / Buffer Cache]Include all columns required by the query in the index to avoid table heap lookups.
Trade-offs: Increases index size and write latency.
Using database-specific hints (e.g., /*+ INDEX(...) */) to override the optimizer.
Trade-offs: Fragile; breaks if schema changes.
Using MATERIALIZED keywords to force the optimizer to cache subquery results.
Trade-offs: Uses memory; prevents predicate pushdown.
| Reliability | Plan regression is a major risk; use plan baselines or query store snapshots to lock known good plans. |
| Scalability | Horizontal scaling requires consistent statistics across nodes; data skew can lead to uneven plan execution. |
| Performance | Bottlenecks usually stem from memory spilling (sorts/hashes) or high I/O from full table scans. |
| Cost | High-cost queries drive up cloud DB instance requirements; optimizing plans reduces CPU/IOPS billing. |
| Security | EXPLAIN output can reveal schema details; restrict access to production execution plans. |
| Monitoring | Monitor 'slow query' logs and 'high cost' queries via pg_stat_statements. |
EXPLAIN provides the optimizer's estimated plan without running the query. EXPLAIN ANALYZE actually executes the query, measures the time taken, and provides the actual row counts and resource usage, allowing you to compare estimates against reality.
The optimizer likely determined that for the given data volume and selectivity, a sequential scan is cheaper than the random I/O required for an index lookup. This often happens on small tables or when the query returns a large percentage of the table.
A Hash Join builds a hash table in memory for one input (the build side) and then probes it with the other input (the probe side). It is typically used for large, unsorted datasets where a merge join would require an expensive sort.
Check if the inner table's join column is indexed. If it is, ensure the statistics are up to date. If the inner table is large, consider if the optimizer should be using a Hash Join or Merge Join instead.
Cost is an arbitrary unit used by the optimizer to compare different paths. It is calculated based on estimates of I/O, CPU, and memory usage. It does not represent actual time in milliseconds.
Predicate pushdown is an optimization where the database engine moves filters (WHERE clauses) as deep into the execution plan as possible, often directly into the scan operation, to minimize the amount of data processed by subsequent steps.
Plans change due to shifts in data distribution (e.g., a table grows significantly) or stale statistics. The optimizer recalculates costs based on the latest metadata; if the metadata is inaccurate, the optimizer may choose a suboptimal plan.
A Merge Join requires both inputs to be sorted by the join key and merges them in a single pass. A Hash Join builds a hash table in memory for one input. Merge Joins are better for sorted data; Hash Joins are better for unsorted data.
Most databases allow hints (e.g., /*+ INDEX(...) */). However, this is generally discouraged as it makes the query plan fragile. Instead, focus on improving statistics or refactoring the query to make the index more attractive to the optimizer.
A Bitmap Heap Scan is a two-step process: first, it scans an index to create a bitmap of row locations (pages), then it accesses the table heap using that bitmap. It is more efficient than a standard index scan for queries that return many rows.
AI Prep covers AI Agents, Generative AI, ML Fundamentals, NLP & LLMs and a lot more, with adaptive tests and daily challenges. Fully offline on Android. Free to try, one-time unlock for lifetime access.