Execution plans are your X-ray vision into query performance. They reveal exactly how the database executes your query, showing you where time is spent and why queries are slow. Every performance optimization starts with reading an execution plan. Used daily by engineers at Google, Amazon, and Netflix to maintain sub-second query response times.

Shopify - Product Search Execution Plan Analysis

Shopify's product search was timing out for merchants with 100K+ products. The search query joined products, variants, inventory, and collections tables. Execution plans revealed the optimizer was choosing nested loop joins with sequential scans, causing 30+ second response times and merchant complaints.

E-commerce

Execution plan analysis revealed: 1) Sequential scan on products table removing 95% of rows by filter - added composite index on (merchant_id, status, title), 2) Nested loop join on variants table without index - added index on (product_id, sku), 3) Hash join spilling to disk due to insufficient memory - increased work_mem from 4MB to 64MB, 4) Expensive sort operation - added covering index to provide pre-sorted results, 5) Row estimate off by 100x - ran ANALYZE to update statistics. The key was reading the execution plan to identify each bottleneck systematically.

-- BEFORE: Execution plan showing problems

EXPLAIN ANALYZE

SELECT

p.product_id,

p.title,

p.price,

v.sku,

i.quantity

FROM products p

JOIN variants v ON p.product_id = v.product_id

JOIN inventory i ON v.variant_id = i.variant_id

WHERE p.merchant_id = 12345

AND p.status = 'active'

AND p.title ILIKE '%shirt%'

ORDER BY p.created_at DESC

LIMIT 50;

-- EXECUTION PLAN REVEALED:

-- Limit (actual time=32450..32451 rows=50)

-- -> Sort (actual time=32450..32450 rows=50)

-- -> Nested Loop (actual time=150..32400 rows=5000)

-- -> Nested Loop (actual time=100..28000 rows=5000)

-- -> Seq Scan on products p (actual time=0..25000 rows=5000)

-- Filter: (merchant_id = 12345 AND status = 'active' AND title ILIKE '%shirt%')

-- Rows Removed by Filter: 95000

-- -> Index Scan on variants v (actual time=0.5..0.8 rows=1)

-- -> Index Scan on inventory i (actual time=0.6..0.6 rows=1)

-- RED FLAGS IDENTIFIED:

-- 1. Seq Scan removing 95K rows - missing index

-- 2. Expensive sort on 5K rows - could use index

-- 3. ILIKE '%shirt%' prevents index usage - need full-text search

-- SOLUTION: Strategic indexes

CREATE INDEX idx_products_merchant_status ON products(merchant_id, status, created_at);

CREATE INDEX idx_products_title_gin ON products USING gin(to_tsvector('english', title));

CREATE INDEX idx_variants_product ON variants(product_id, variant_id);

CREATE INDEX idx_inventory_variant ON inventory(variant_id);