Reading PostgreSQL EXPLAIN Plan - Missing Index Detection

intermediate

Analyze execution plan to identify why a customer lookup query is slow

-- Query taking 8 seconds on 5M row table

EXPLAIN ANALYZE

SELECT

o.order_id,

o.order_date,

o.total_amount,

c.customer_name

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id

WHERE o.order_date >= '2024-01-01'

AND o.status = 'completed'

ORDER BY o.order_date DESC

LIMIT 100;

-- EXECUTION PLAN OUTPUT:

-- Limit (cost=125000..125025 rows=100 width=120) (actual time=8234..8235 rows=100 loops=1)

-- -> Sort (cost=125000..130000 rows=500000 width=120) (actual time=8234..8234 rows=100 loops=1)

-- Sort Key: o.order_date DESC

-- Sort Method: top-N heapsort Memory: 45kB

-- -> Hash Join (cost=25000..100000 rows=500000 width=120) (actual time=450..7800 rows=500000 loops=1)

-- Hash Cond: (o.customer_id = c.customer_id)

-- -> Seq Scan on orders o (cost=0..50000 rows=500000 width=100) (actual time=0..3500 rows=500000 loops=1)

-- Filter: ((order_date >= '2024-01-01') AND (status = 'completed'))

-- Rows Removed by Filter: 4500000

-- -> Hash (cost=15000..15000 rows=100000 width=50) (actual time=445..445 rows=100000 loops=1)

-- Buckets: 131072 Batches: 1 Memory Usage: 8192kB

-- -> Seq Scan on customers c (cost=0..15000 rows=100000 width=50) (actual time=0..220 rows=100000 loops=1)

-- ANALYSIS:

-- RED FLAG 1: Seq Scan on orders - scanned 5M rows, removed 4.5M by filter

-- RED FLAG 2: Seq Scan on customers - unnecessary full table scan

-- RED FLAG 3: Expensive sort operation on 500K rows

-- SOLUTION: Add indexes

CREATE INDEX idx_orders_date_status ON orders(order_date, status) WHERE status = 'completed';

CREATE INDEX idx_orders_customer ON orders(customer_id);

CREATE INDEX idx_customers_pk ON customers(customer_id); -- If not already primary key

-- OPTIMIZED QUERY WITH NEW PLAN:

EXPLAIN ANALYZE

SELECT