Query Execution Plans - DSA Guide

🗺️ Introduction: The Commute Route Planner

Imagine you want to drive from your house to the airport:

You open your GPS map app. The app doesn't just guess a route. It evaluates different choices:

Option A: Drive down local side streets (slow, lots of stoplights).
Option B: Take the highway (faster, but has tolls).
Option C: A combination of backroads and highways.

The GPS calculates the travel times, chooses the fastest path, and prints out step-by-step turn directions.

In databases, a Query Execution Plan is this exact GPS route! When you write a SQL query, the database Query Optimizer builds a step-by-step execution path detailing how it will retrieve your data (e.g. using index scans, table scans, or hash joins).

🏗️ The Execution Lifecycle

Every SQL statement goes through a processing pipeline before it returns data:

        Raw SQL Query (e.g., SELECT name FROM users WHERE age = 30)
                            |
                            v
               +--------------------------+
               |      PARSER & LEXER      |  <--- Checks SQL syntax rules
               +--------------------------+
                            |
                  (Syntactically Valid)
                            |
                            v
               +--------------------------+
               |     QUERY OPTIMIZER      |  <--- Calculates costs of multiple routes
               +--------------------------+
                            |
                     (Selected Route)
                            |
                            v
               +--------------------------+
               |  QUERY EXECUTION PLAN    |  <--- Step-by-step directions
               +--------------------------+
                            |
                            v
                    Database Engine

Common Plan Nodes to Look For:

Seq Scan (Sequential/Table Scan): Scanning the table page-by-page. Extremely slow for large tables.
Index Scan: Traversing a B-Tree index to locate specific row keys.
Index Only Scan: Reading data directly from the index tree without loading table blocks from disk (covering query).
Nested Loop Join: Joining tables by loops: for every row in Table A, scan Table B. Fast for small tables.
Hash Join: Building a temporary hash table in memory from one table, and mapping the other table to it. Fast for large tables.

💻 Code Examples

Let's inspect the execution plan of a query using the EXPLAIN prefix.

SQL Syntax

sql

-- Ask the database to output its route choice without executing the query
EXPLAIN SELECT * FROM users WHERE id = 10;

-- Ask the database to run the query, measure timing, and print the actual plan
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 10;

Multi-Language Execution

⚠️ Common Mistakes

1. Ignoring Cost Parameters

Relying strictly on query syntaxes instead of executing EXPLAIN ANALYZE. Sometimes queries look correct but trigger slow sequential scans because the optimizer believes the index cost is too high.

2. Not Updating Database Statistics

The query optimizer relies on cached table statistics (like row counts and value distributions) to choose the best plan. If you insert millions of rows but forget to run ANALYZE, the optimizer will choose outdated, slow query plans!

🔍 Interview Corner

Q1: What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN generates and displays the query execution plan containing cost estimates, but does not execute the query.
EXPLAIN ANALYZE actually executes the query, records real execution times, and outputs both the optimizer estimates and the actual performance metrics.

Q2: What does a "Seq Scan" (Sequential Scan) in an execution plan mean?

A Seq Scan means the database engine has to read the entire table page-by-page from disk. For large tables, this is very slow and indicates a missing index on the columns queried in the WHERE filter.

📝 Summary

Query Execution Plans are step-by-step maps detailing how queries are executed.
Prepended using EXPLAIN or EXPLAIN ANALYZE.
Watch out for slow operations like Seq Scan or Nested Loop on large tables.