S043 · Query Planning — EXPLAIN, Execution Plans, Tuning

-- explain_lab.sql — reproduce the six patterns and fix each one.
-- Run: psql -h localhost -U postgres -f explain_lab.sql
 
\timing on
\pset pager off
 
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,
  customer_id  INT       NOT NULL,
  amount       NUMERIC   NOT NULL,
  status       TEXT      NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  notes        TEXT
);
INSERT INTO orders (customer_id, amount, status, created_at, notes)
SELECT
  (random()*100000)::INT,
  (random()*1000)::NUMERIC(10,2),
  CASE WHEN random() < 0.02 THEN 'refunded' ELSE 'paid' END,
  now() - (random()*365 || ' days')::INTERVAL,
  'note ' || (random()*1000)::INT
FROM generate_series(1, 5000000);
ANALYZE orders;
 
-- Pattern 1: Seq Scan on a highly selective predicate (fix = index)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'refunded';
CREATE INDEX ix_orders_status ON orders(status);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'refunded';
 
-- Pattern 2: Function on indexed column (fix = functional index)
CREATE INDEX ix_orders_notes ON orders(notes);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE upper(notes) = 'NOTE 42';
CREATE INDEX ix_orders_notes_upper ON orders(upper(notes));
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE upper(notes) = 'NOTE 42';
 
-- Pattern 3: Sort spill (fix = index on order-by column, or bump work_mem)
SHOW work_mem;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY amount DESC LIMIT 100;
SET work_mem = '256MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY amount DESC LIMIT 100;
CREATE INDEX ix_orders_amount ON orders(amount DESC);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY amount DESC LIMIT 100;
 
-- Pattern 4: Leading wildcard LIKE (fix = pg_trgm GIN index)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE notes LIKE '%42%';
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ix_orders_notes_trgm ON orders USING GIN(notes gin_trgm_ops);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE notes LIKE '%42%';
 
-- Pattern 5: Nested Loop with high outer rows (rewrite so outer is small)
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders WHERE amount > 999) big ON big.id = o.id;
 
-- Pattern 6: Stale stats — insert lots, don't ANALYZE, watch the plan lie
INSERT INTO orders (customer_id, amount, status)
SELECT 1, 500, 'paid' FROM generate_series(1, 500000);
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
ANALYZE orders;
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;