TECH **SQL Execution Plans: How to Read Them (Seq Scan vs. Index Scan)**

SQL Execution Plans: How to Read Them (Seq Scan vs. Index Scan)

A Hyper-Practical, Zero-Fluff Guide for Data Analysts

1. What This Is & Why It Matters

An execution plan is the step-by-step blueprint your database engine follows to retrieve data for your query. Think of it like a GPS route for SQL: it tells you exactly how the database will fetch your results—whether it’s scanning every row in a table (slow) or using an index (fast).

Why this matters in production:
- Performance: A bad execution plan can turn a 100ms query into a 10-minute disaster, especially on large datasets.
- Cost: In cloud databases (Redshift, BigQuery, Snowflake), inefficient queries = higher bills.
- Debugging: When a dashboard times out or a report runs forever, the execution plan is your first diagnostic tool.

Real-world scenario:
You’re analyzing customer churn for a SaaS company. Your query:

SELECT user_id, last_login_date
FROM users
WHERE signup_date > '2023-01-01'
ORDER BY last_login_date DESC;

Takes 30 seconds to run on a 10M-row table. Your manager needs the report in 5 minutes. The execution plan will show you why it’s slow—and how to fix it.

2. Core Concepts & Components

? Execution Plan

• Definition: A tree of operations the database performs to execute your query.
• Production insight: Always check the plan before deploying a query to production. A missing index can silently kill performance.

? Seq Scan (Sequential Scan)

• Definition: The database reads every row in a table, one by one.
• Production insight: Seq scans are fine for small tables but deadly for large ones. If you see this on a 10M-row table, you’re doing it wrong.

? Index Scan

• Definition: The database uses an index (like a book’s index) to jump directly to relevant rows.
• Production insight: Indexes speed up WHERE, JOIN, and ORDER BY clauses—but they slow down INSERT/UPDATE operations (extra write overhead).

? Cost (in Execution Plans)

• Definition: A relative measure of how "expensive" an operation is (lower = better).
• Production insight: Cost is not time in seconds—it’s a unitless estimate. Compare costs between plans to pick the better one.

? Explain (Command)

• Definition: The SQL command to generate an execution plan (e.g., EXPLAIN SELECT ...).
• Production insight: Always run EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN with actuals (MySQL) to see real runtime stats.

? Filter vs. Index Condition

• Definition:
• Filter: Applied after fetching rows (e.g., WHERE status = 'active' on a non-indexed column).
• Index Condition: Applied during the index scan (e.g., WHERE id = 123 on an indexed column).
• Production insight: Filters are slower because they process more rows. Index conditions are faster.

? Join Types (Nested Loop, Hash Join, Merge Join)

• Definition:
• Nested Loop: For each row in Table A, scan Table B (slow for large tables).
• Hash Join: Builds a hash table for one table, then probes it (fast for large tables).
• Merge Join: Requires sorted inputs (fast for pre-sorted data).
• Production insight: Hash joins are usually best for large datasets, but they use more memory.

? Buffers (Memory Usage)

• Definition: How much memory the query uses (e.g., Buffers: shared hit=100 read=50).
• Production insight: High read values mean the database is hitting disk (slow). Optimize to increase hit (memory access).

3. Step-by-Step: How to Read an Execution Plan

Prerequisites

• A PostgreSQL/MySQL/SQL Server database (this guide uses PostgreSQL).
• A table with at least 100K rows (use pgbench to generate test data if needed).
• Basic SQL knowledge (you know SELECT, WHERE, JOIN).

Step 1: Generate a Slow Query

-- Create a test table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    signup_date DATE,
    last_login_date TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active'
);

-- Insert 1M rows (PostgreSQL)
INSERT INTO users (name, email, signup_date, last_login_date, status)
SELECT
    'user_' || i,
    'user_' || i || '@example.com',
    DATE '2020-01-01' + (random() * 1000)::int,
    NOW() - (random() * 1000)::int * INTERVAL '1 day',
    CASE WHEN random() > 0.5 THEN 'active' ELSE 'inactive' END
FROM generate_series(1, 1000000) i;

Step 2: Run EXPLAIN on a Slow Query

EXPLAIN ANALYZE
SELECT id, name, last_login_date
FROM users
WHERE signup_date > '2023-01-01'
ORDER BY last_login_date DESC
LIMIT 100;

Step 3: Interpret the Output

Here’s a simplified execution plan (PostgreSQL):

Limit  (cost=12345.67..12345.92 rows=100 width=24) (actual time=500.123..500.456 rows=100 loops=1)
  ->  Sort  (cost=12345.67..12678.90 rows=133333 width=24) (actual time=500.120..500.345 rows=100 loops=1)
        Sort Key: last_login_date DESC
        Sort Method: top-N heapsort  Memory: 35kB
        ->  Seq Scan on users  (cost=0.00..10000.00 rows=133333 width=24) (actual time=0.012..450.678 rows=133333 loops=1)
              Filter: (signup_date > '2023-01-01'::date)
              Rows Removed by Filter: 866667

Breakdown:
1. Seq Scan on users
- The database reads all 1M rows (rows=133333 after filtering).
- Problem: No index on signup_date, so it scans the entire table.
- Actual time: 450ms (slow!).

1. Filter: (signup_date > '2023-01-01')

2. Applied after fetching all rows (inefficient).

3. Sort (last_login_date DESC)

4. Sorts 133K rows in memory (top-N heapsort).

5. Cost: High because it’s sorting a large dataset.

6. Limit 100

7. Returns only the top 100 rows after sorting.

Step 4: Optimize the Query

Add an index:

CREATE INDEX idx_users_signup_date ON users(signup_date);
CREATE INDEX idx_users_last_login_date ON users(last_login_date);

Re-run EXPLAIN ANALYZE:

Limit  (cost=0.29..12.34 rows=100 width=24) (actual time=0.050..0.120 rows=100 loops=1)
  ->  Index Scan Backward using idx_users_last_login_date on users  (cost=0.29..12345.67 rows=133333 width=24) (actual time=0.048..0.100 rows=100 loops=1)
        Filter: (signup_date > '2023-01-01'::date)

What changed?
- Index Scan Backward on last_login_date (uses the index for sorting).
- Filter is still applied, but now on a smaller dataset (only rows matching the index).
- Actual time: 0.12ms (4000x faster!).

Step 5: Verify with a Composite Index

Problem: The filter on signup_date is still applied after the index scan. Let’s optimize further:

CREATE INDEX idx_users_signup_last_login ON users(signup_date, last_login_date DESC);

New plan:

Limit  (cost=0.29..12.34 rows=100 width=24) (actual time=0.020..0.050 rows=100 loops=1)
  ->  Index Scan using idx_users_signup_last_login on users  (cost=0.29..12345.67 rows=133333 width=24) (actual time=0.018..0.040 rows=100 loops=1)
        Index Cond: (signup_date > '2023-01-01'::date)

Key improvements:
- Index Cond (not Filter): The index is used during the scan (no extra filtering step).
- Actual time: 0.05ms (another 2x speedup).

4. ? Production-Ready Best Practices

Performance

• Always check execution plans for queries running > 100ms.
• Index high-cardinality columns (columns with many unique values, like user_id or email).
• Avoid SELECT *—it forces the database to read more data than needed.
• Use EXPLAIN ANALYZE in staging before deploying to production.

Cost Optimization

• Seq scans on large tables = $$$ (cloud databases charge by I/O).
• Index only what you need—every index slows down writes.
• Partition large tables (e.g., by date) to reduce scan sizes.

Reliability

• Test with realistic data volumes (a 10-row table won’t show performance issues).
• Monitor slow queries (PostgreSQL: pg_stat_statements; MySQL: slow query log).
• Set query timeouts (e.g., SET statement_timeout = '5s').

Observability

• Log execution plans for slow queries (e.g., EXPLAIN (FORMAT JSON) ...).
• Track buffer cache hit ratio (PostgreSQL: pg_stat_database).
• Alert on high-cost queries (e.g., queries with cost > 10,000).

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. "Which operation is faster: Seq Scan or Index Scan?"

2. Trick: It depends! Seq scan is faster for small tables; index scan is faster for large tables with selective filters.

3. "Why is this query slow?" (Given an execution plan)

4. Look for:

   
   
   • Seq scans on large tables.
   • Missing indexes on WHERE/JOIN columns.
   • Sort operations without indexes.

5. "How would you optimize this query?"

6. Add indexes on filtered/sorted columns.
7. Rewrite WHERE clauses to use indexed columns.
8. Avoid SELECT *.

Key Trap Distinctions

Scenario-Based Question

"You have a table with 10M rows. A query filtering on status (3 possible values) is slow. What do you do?"
- Wrong answer: Add an index on status (low cardinality = bad).
- Right answer:
- Use a partial index: CREATE INDEX idx_active_users ON users(id) WHERE status = 'active'.
- Or partition the table by status.

7. ? Hands-On Challenge

Challenge:
You have a table orders with 5M rows. A query filtering on order_date and sorting by total_amount is slow. Optimize it.

Given:

EXPLAIN ANALYZE
SELECT customer_id, total_amount
FROM orders
WHERE order_date > '2023-01-01'
ORDER BY total_amount DESC
LIMIT 100;

Current plan shows a seq scan and a sort.

Solution:

-- Add a composite index
CREATE INDEX idx_orders_date_amount ON orders(order_date, total_amount DESC);

-- Re-run the query
EXPLAIN ANALYZE
SELECT customer_id, total_amount
FROM orders
WHERE order_date > '2023-01-01'
ORDER BY total_amount DESC
LIMIT 100;

Why it works:
- The index covers both the WHERE and ORDER BY clauses.
- The database can now use an index scan and avoid sorting.

8. ? Rapid-Reference Crib Sheet

PostgreSQL

-- Basic execution plan
EXPLAIN SELECT * FROM users WHERE id = 1;

-- Execution plan with actual runtime stats
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- JSON format (for logging)
EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1;

-- Create an index
CREATE INDEX idx_name ON table(column);

-- Composite index
CREATE INDEX idx_name ON table(col1, col2 DESC);

-- Partial index
CREATE INDEX idx_name ON table(column) WHERE condition;

MySQL

-- Basic execution plan
EXPLAIN SELECT * FROM users WHERE id = 1;

-- Extended info
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;

-- Check if a query uses an index
EXPLAIN SELECT * FROM users WHERE name LIKE 'A%';
-- Look for "Using index" in the "Extra" column.

Key Metrics to Watch

⚠️ Exam Traps

• Default EXPLAIN doesn’t show actual time → Use EXPLAIN ANALYZE.
• Index on status with 2 values is useless → Use a partial index.
• LIKE '%term%' can’t use indexes → Use LIKE 'term%' or full-text search.
• Composite index order matters → Put the most selective column first.

9. ? Where to Go Next

1. PostgreSQL EXPLAIN Documentation – Official guide with advanced options.
2. Use The Index, Luke – Free book on SQL indexing.
3. MySQL EXPLAIN Explained – MySQL’s official docs.
4. SQL Performance Explained (Book) – Deep dive into execution plans and indexing.

Final Tip:
Execution plans are your SQL superpower. The next time a query is slow, don’t guess—run EXPLAIN and let the database tell you why. ?