⚡ Indexes & Performance

                         [50]                    ← Root node
                       /      \
                   [25]        [75]               ← Internal nodes
                  /    \      /    \
              [10,20] [30,40] [60,70] [80,90]      ← Leaf nodes (point to actual rows)

Looking for customer_id = 70:
  1. Root: 70 > 50 → go right
  2. Node: 70 < 75 → go left
  3. Leaf: found 70! → points to the actual row on disk
  Only 3 steps instead of scanning 1,000,000 rows!

-- BEFORE creating an index (slow!)
EXPLAIN ANALYZE
SELECT * FROM big_orders WHERE customer_id = 42;
-- Seq Scan on big_orders  (cost=0.00..20834.00 rows=100 width=52)
--   Filter: (customer_id = 42)
--   Execution Time: 85.432 ms  ← SLOW!

-- Create the index
CREATE INDEX idx_big_orders_customer_id ON big_orders (customer_id);

-- AFTER creating the index (fast!)
EXPLAIN ANALYZE
SELECT * FROM big_orders WHERE customer_id = 42;
-- Index Scan using idx_big_orders_customer_id  (cost=0.42..8.87 rows=100 width=52)
--   Index Cond: (customer_id = 42)
--   Execution Time: 0.215 ms  ← 400x FASTER!

CREATE INDEX idx_users_uuid ON users USING HASH (uuid);

-- ✅ Fast:
SELECT * FROM users WHERE uuid = 'abc-123-def';

-- ❌ Cannot use this index:
SELECT * FROM users WHERE uuid > 'abc';
SELECT * FROM users ORDER BY uuid;

-- Full-text search on articles
CREATE INDEX idx_articles_search ON articles USING GIN (
    to_tsvector('english', title || ' ' || body)
);

-- Search for articles about "database performance"
SELECT title FROM articles
WHERE to_tsvector('english', title || ' ' || body)
   @@ to_tsquery('database & performance');

-- JSONB index for flexible schemas
CREATE INDEX idx_events_data ON events USING GIN (metadata jsonb_path_ops);

-- Array containment
CREATE INDEX idx_products_tags ON products USING GIN (tags);
SELECT * FROM products WHERE tags @> ARRAY['organic', 'vegan'];

-- Spatial data (PostGIS)
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

-- Range types
CREATE INDEX idx_reservations_period ON reservations USING GIST (date_range);

-- Perfect for time-series data where rows arrive in order
CREATE INDEX idx_logs_created_at ON server_logs USING BRIN (created_at);

-- Tiny index size: ~50 KB instead of ~200 MB for a B-tree on 100M rows!

EXPLAIN ANALYZE
SELECT * FROM big_orders
WHERE customer_id = 42 AND status = 'completed';

-- Sample output (with index on customer_id):

Index Scan using idx_big_orders_customer_id on big_orders
  (cost=0.42..12.55 rows=25 width=52)
  (actual time=0.035..0.128 rows=23 loops=1)
  Index Cond: (customer_id = 42)
  Filter: (status = 'completed'::text)
  Rows Removed by Filter: 77
Planning Time: 0.125 ms
Execution Time: 0.168 ms

-- Check the size of all indexes on a table
SELECT indexname,
       pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = 'big_orders';

-- Find unused indexes (waste of space!)
SELECT schemaname, relname AS table_name,
       indexrelname AS index_name,
       idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Only index active orders (ignore completed/cancelled ones)
CREATE INDEX idx_orders_active
ON big_orders (customer_id)
WHERE status = 'pending';

-- This query uses the partial index:
SELECT * FROM big_orders
WHERE customer_id = 42 AND status = 'pending';

-- This query does NOT use it (different status):
SELECT * FROM big_orders
WHERE customer_id = 42 AND status = 'completed';

-- Index on LOWER(email) for case-insensitive email lookups
CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

-- This query now uses the index:
SELECT * FROM users
WHERE LOWER(email) = 'alice@example.com';

-- ❌ This does NOT use it (no LOWER function):
SELECT * FROM users
WHERE email = 'alice@example.com';

-- Index on extracted year from a timestamp
CREATE INDEX idx_orders_year
ON big_orders (EXTRACT(YEAR FROM created_at));

-- Fast lookup of all 2024 orders
SELECT * FROM big_orders
WHERE EXTRACT(YEAR FROM created_at) = 2024;

-- Create a multi-column index
CREATE INDEX idx_orders_cust_status
ON big_orders (customer_id, status);

-- ✅ Uses the index (customer_id is the leftmost column)
EXPLAIN ANALYZE
SELECT * FROM big_orders
WHERE customer_id = 42 AND status = 'pending';

-- ❌ Cannot use the index (status is NOT the leftmost column)
EXPLAIN ANALYZE
SELECT * FROM big_orders
WHERE status = 'pending';

-- ❌ SLOW: If orders.customer_id has no index, this scans the entire orders table for EVERY customer
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

-- ✅ FIX: Create an index on the foreign key
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- ❌ SLOW: UPPER() wraps the column, so the index on "email" can't be used
SELECT * FROM users
WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';

-- ✅ FIX Option A: Create an expression index
CREATE INDEX idx_users_email_upper ON users (UPPER(email));

-- ✅ FIX Option B: Store data normalized, query without function
SELECT * FROM users WHERE email = 'alice@example.com';

-- ❌ SLOW: Leading % means "anything before" — can't use B-tree
SELECT * FROM products
WHERE name LIKE '%phone%';

-- ✅ FIX: Use a GIN trigram index
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm
ON products USING GIN (name gin_trgm_ops);

-- Now '%phone%' can use the GIN index!

-- ❌ BAD: Indexing every column wastes space and slows writes
CREATE INDEX idx1 ON orders (customer_id);
CREATE INDEX idx2 ON orders (status);
CREATE INDEX idx3 ON orders (amount);
CREATE INDEX idx4 ON orders (created_at);
CREATE INDEX idx5 ON orders (customer_id, status);
CREATE INDEX idx6 ON orders (customer_id, created_at);
-- 6 indexes on one table! Each INSERT now updates 6 indexes + the table.

-- ✅ BETTER: Identify actual query patterns and create only what's needed
CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);
CREATE INDEX idx_orders_created ON orders (created_at);
-- 2 indexes that cover the real query patterns

-- ❌ Fetches ALL columns — can't use "Index Only Scan"
SELECT * FROM big_orders WHERE customer_id = 42;

-- ✅ Only fetch what you need — might enable "Index Only Scan"
SELECT id, amount FROM big_orders WHERE customer_id = 42;