SQL EXPLAIN

Complete SQL EXPLAIN Technical Guide

1. Fundamental Syntax

-- Standard EXPLAIN syntax
EXPLAIN [ANALYZE] [VERBOSE] query;

-- MySQL variant
EXPLAIN [FORMAT = {JSON | TREE | TRADITIONAL}] query;

-- PostgreSQL variant
EXPLAIN [ANALYZE] [BUFFERS] [FORMAT {TEXT | XML | JSON | YAML}] query;

ANALYZE: Actually executes the query and shows real runtime statistics
VERBOSE: Displays additional details about the plan
FORMAT: Changes output format (JSON, XML, etc.)
Database Variations: Significant differences exist between MySQL, PostgreSQL, SQL Server, and Oracle

2. Interpreting EXPLAIN Output

Key Plan Operations

/* PostgreSQL example */
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

QUERY PLAN
------------------------------------------------------------------
Index Scan using idx_orders_customer on orders  (cost=0.29..8.31 rows=1 width=45)
  Index Cond: (customer_id = 123)
  Planning Time: 0.114 ms
  Execution Time: 0.028 ms

Cost Metrics Explained

/* MySQL example */
EXPLAIN FORMAT=JSON SELECT * FROM products WHERE price > 100;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "325.65"
    },
    "table": {
      "access_type": "range",
      "rows_examined_per_scan": 500,
      "rows_produced_per_join": 500,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "300.25",
        "eval_cost": "25.40",
        "prefix_cost": "325.65"
      }
    }
  }
}

3. Optimization Using EXPLAIN

Identifying Performance Issues

-- PostgreSQL example showing a problematic sequential scan
EXPLAIN ANALYZE SELECT * FROM large_table WHERE last_name LIKE 'Smith%';

QUERY PLAN
------------------------------------------------------------------
Seq Scan on large_table  (cost=0.00..12548.20 rows=1000 width=45)
  Filter: (last_name ~~ 'Smith%'::text)
  Rows Removed by Filter: 999000
  Planning Time: 0.112 ms
  Execution Time: 1250.456 ms

After Adding an Index

-- After creating index: CREATE INDEX idx_last_name ON large_table(last_name)
EXPLAIN ANALYZE SELECT * FROM large_table WHERE last_name LIKE 'Smith%';

QUERY PLAN
------------------------------------------------------------------
Bitmap Heap Scan on large_table  (cost=24.33..826.45 rows=1000 width=45)
  Filter: (last_name ~~ 'Smith%'::text)
  -> Bitmap Index Scan on idx_last_name  (cost=0.00..24.08 rows=1000)
       Index Cond: (last_name ~~ 'Smith%'::text)
  Planning Time: 0.456 ms
  Execution Time: 2.123 ms

4. Database-Specific EXPLAIN Features

SQL Server Execution Plans

-- SQL Server graphical execution plan
SET SHOWPLAN_XML ON;
GO
SELECT p.Name, c.Name 
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID;
GO
SET SHOWPLAN_XML OFF;

-- Estimated vs Actual execution plans
-- Use "Include Actual Execution Plan" in SSMS or:
SET STATISTICS PROFILE ON;

Oracle Explain Plan

-- Oracle EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- View the plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Advanced options
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(
    format => 'ALLSTATS LAST +OUTLINE +NOTE'
));

5. Advanced EXPLAIN Analysis

Analyzing Join Strategies

-- PostgreSQL example showing different join types
EXPLAIN ANALYZE SELECT o.*, c.*
FROM orders o
JOIN customers c ON o.customer_id = c.id;

QUERY PLAN
------------------------------------------------------------------
Hash Join  (cost=129.57..312.45 rows=1000 width=145)
  Hash Cond: (o.customer_id = c.id)
  -> Seq Scan on orders o  (cost=0.00..145.00 rows=10000)
  -> Hash  (cost=98.76..98.76 rows=876 width=72)
       -> Seq Scan on customers c  (cost=0.00..98.76 rows=876)

Understanding Parallel Execution

-- PostgreSQL parallel query example
EXPLAIN ANALYZE SELECT * FROM large_table WHERE value > 1000;

QUERY PLAN
------------------------------------------------------------------
Gather  (cost=1000.00..12548.20 rows=100000 width=45)
  Workers Planned: 4
  Workers Launched: 4
  -> Parallel Seq Scan on large_table  (cost=0.00..10548.20 rows=25000)
       Filter: (value > 1000)
  Planning Time: 0.112 ms
  Execution Time: 125.456 ms

6. Performance & Best Practices

Always use ANALYZE: For real-world performance metrics (but remember it executes the query)
Compare plans: Before and after changes to verify improvements
Look for warning signs: Sequential scans on large tables, high row estimates vs actuals, expensive sorts
Understand cost units: Cost is arbitrary but consistent within a database
Use visual tools: Many GUIs provide graphical explain plans that are easier to interpret

Common Optimization Patterns

-- Example of optimizing with EXPLAIN
-- Problem query:
EXPLAIN ANALYZE SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023;

-- Optimized version:
EXPLAIN ANALYZE SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

-- Create function-based index if needed:
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM order_date));

Advanced EXPLAIN Clause Insights

1. Execution Plan Visualizations

Modern database tools transform EXPLAIN output into interactive diagrams.

-- Tools that enhance EXPLAIN analysis:
• PostgreSQL: pgAdmin's graphical EXPLAIN
• MySQL: Workbench's Visual Execution Plan
• SQL Server: SSMS's Live Query Statistics
• Oracle: SQL Developer's Plan Visualization

Node Tree Navigation: Click-through exploration of complex plans
Cost Heatmaps: Color-coded highlighting of expensive operations
Tooltip Details: Hover-over explanations of plan operators

2. Planner Statistics Analysis

EXPLAIN relies on database statistics that sometimes need maintenance.

-- PostgreSQL statistics commands
ANALYZE table_name;  -- Update statistics
EXPLAIN (ANALYZE, BUFFERS) SELECT... -- Show buffer usage

-- MySQL statistics refresh
ANALYZE TABLE table_name;
OPTIMIZE TABLE table_name;

Cardinality Estimation: Why row estimates may be inaccurate
Histogram Bounds: How data distribution affects plans
Statistics Age: Checking when stats were last updated

3. Plan Regression Analysis

Tracking when and why execution plans change unexpectedly.

-- PostgreSQL plan capture
CREATE EXTENSION pg_store_plans;

-- MySQL optimizer switch control
SET optimizer_switch='block_nested_loop=off';

-- SQL Server plan forcing
EXEC sp_create_plan_guide_from_handle 'template_plan', @plan_handle;

Plan Baselines: Oracle's SQL Plan Management
Plan Hinting: Directing the optimizer (MySQL's FORCE INDEX)
Plan Freezing: PostgreSQL's plan_cache_mode

4. Distributed Query Analysis

EXPLAIN for sharded, partitioned, and federated queries.

-- PostgreSQL partition analysis
EXPLAIN ANALYZE SELECT * FROM partitioned_table WHERE date > '2023-01-01';

-- Citus (PostgreSQL extension) distributed plan
EXPLAIN (VERBOSE) SELECT * FROM distributed_table JOIN local_table USING (id);

Shard Routing: Identifying which shards are accessed
Network Costs: Cross-node join penalties
Partition Pruning: Verifying unneeded partitions are excluded

5. Transaction Context Effects

How transaction isolation and data visibility affect plans.

-- PostgreSQL example showing transaction impact
BEGIN;
SET LOCAL enable_indexscan = off;
EXPLAIN SELECT * FROM table_name WHERE condition;
COMMIT;

-- SQL Server snapshot isolation
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
EXPLAIN SELECT * FROM table_with_heavy_updates;

Parameter Sniffing: SQL Server's plan caching behavior
Temporary Table Stats: How temp tables affect plan quality
Plan Invalidation: When DDL changes force recompilation

Not Included Topics

Query Plan Fingerprinting: Cryptographic hashing of plans for comparison
ML-Based Plan Prediction: Using machine learning to forecast plan changes
Cross-Vendor Plan Translation: Converting plans between different database systems
Plan Change Alerting: Monitoring systems for unexpected plan regressions
EXPLAIN for NoSQL Systems: How document databases like MongoDB handle execution plans