S041 · Indexes — B-Tree Intuition, When to Add

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user_987654@example.com';

#!/usr/bin/env bash
# indexes_lab.sh — 5 minutes of hands-on with a real Postgres.
set -euo pipefail
 
# 1. Start Postgres 16 in a throwaway container
docker run --rm -d --name pg-lab \
  -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:16
sleep 4
 
# 2. Run the lab
docker exec -i pg-lab psql -U postgres <<'SQL'
-- Create the table
CREATE TABLE users (
    id          bigserial PRIMARY KEY,
    email       text NOT NULL,
    country     text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);
 
-- Stuff it with 1 million rows
INSERT INTO users (email, country)
SELECT
    'user_' || g || '@example.com',
    (ARRAY['US','IN','DE','JP','BR'])[1 + (g % 5)]
FROM generate_series(1, 1000000) g;
 
-- Update planner statistics
ANALYZE users;
 
-- 3. Baseline: point lookup WITHOUT index
\echo '=== 3. Point lookup WITHOUT index (expect Seq Scan, ~150-400 ms) ==='
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user_987654@example.com';
 
-- 4. Add B-tree index
CREATE INDEX idx_users_email ON users(email);
 
-- 5. Same query WITH index (expect Index Scan, ~0.1-0.5 ms — 1000x faster)
\echo '=== 5. Point lookup WITH index (expect Index Scan, sub-ms) ==='
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user_987654@example.com';
 
-- 6. Low-selectivity query — planner IGNORES the index even if we add one
\echo '=== 6. Low selectivity — planner prefers Seq Scan ==='
CREATE INDEX idx_users_country ON users(country);
ANALYZE users;
EXPLAIN ANALYZE
SELECT count(*) FROM users WHERE country = 'US';
 
-- 7. Range scan uses the index nicely (created_at is unique-ish)
\echo '=== 7. Range scan on created_at (add index first) ==='
CREATE INDEX idx_users_created_at ON users(created_at);
EXPLAIN ANALYZE
SELECT * FROM users WHERE created_at > now() - interval '1 second'
ORDER BY created_at DESC LIMIT 10;
 
-- 8. Composite index — column order matters
\echo '=== 8. Composite index (country, created_at) — helps (country=? AND created_at>?) ==='
CREATE INDEX idx_users_country_ts ON users(country, created_at);
EXPLAIN ANALYZE
SELECT * FROM users
WHERE country = 'DE' AND created_at > now() - interval '5 seconds'
ORDER BY created_at DESC LIMIT 100;
 
-- 9. The function-on-column trap
\echo '=== 9. Function on column defeats B-tree (Seq Scan again) ==='
EXPLAIN ANALYZE
SELECT * FROM users WHERE upper(email) = 'USER_500000@EXAMPLE.COM';
 
-- 9b. Fix with a functional index
CREATE INDEX idx_users_upper_email ON users(upper(email));
ANALYZE users;
\echo '=== 9b. With functional index — Index Scan restored ==='
EXPLAIN ANALYZE
SELECT * FROM users WHERE upper(email) = 'USER_500000@EXAMPLE.COM';
 
-- 10. Leading-wildcard trap (still Seq Scan)
\echo '=== 10. Leading wildcard: LIKE %example.com — no B-tree can help ==='
EXPLAIN ANALYZE
SELECT * FROM users WHERE email LIKE '%example.com' LIMIT 5;
 
-- 11. Cost check: index size vs table size
\echo '=== 11. Index sizes are NOT free ==='
SELECT relname,
       pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(relid) DESC;
 
SELECT pg_size_pretty(pg_relation_size('users')) AS table_size;
 
-- 12. Unused-index audit (idx_scan = 0 → drop candidate)
\echo '=== 12. Which indexes have never been used? ==='
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan;
 
SQL
 
# Cleanup
docker stop pg-lab