SQL Indexes Explained — How They Work, When to Use Them, and What to Avoid

-- Create a realistic users table with a million-row scenario in mind
CREATE TABLE users (
    user_id     SERIAL PRIMARY KEY,  -- PK automatically gets an index
    email       VARCHAR(255) NOT NULL,
    full_name   VARCHAR(100),
    country     VARCHAR(50),
    created_at  TIMESTAMP DEFAULT NOW()
);

-- Insert some sample rows so we can demonstrate the concept
INSERT INTO users (email, full_name, country) VALUES
    ('alice@example.com',   'Alice Nguyen',   'USA'),
    ('bob@example.com',     'Bob Martins',    'UK'),
    ('carol@example.com',   'Carol Osei',     'Ghana'),
    ('dave@example.com',    'Dave Kowalski',  'Poland'),
    ('eve@example.com',     'Eve Santos',     'Brazil');

-- WITHOUT an index: this causes a full table scan on `email`
-- The database reads every row to find a match
EXPLAIN ANALYZE
SELECT user_id, full_name
FROM   users
WHERE  email = 'carol@example.com';
-- Output before index: Seq Scan on users (cost=0.00..1.06 rows=1)
-- On a 10M row table this becomes: cost=0.00..254819.00 rows=1 — very slow

-- CREATE the index — this builds the B-tree sorted by email
CREATE INDEX idx_users_email ON users (email);
-- Name convention: idx_<table>_<column(s)> — makes maintenance easy

-- NOW run the same query — the engine will use the index
EXPLAIN ANALYZE
SELECT user_id, full_name
FROM   users
WHERE  email = 'carol@example.com';
-- Output after index: Index Scan using idx_users_email on users
--                     (cost=0.28..8.29 rows=1) -- dramatically cheaper

-- Verify the index exists
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'users';

-- Scenario: An e-commerce orders table — a classic real-world case
CREATE TABLE orders (
    order_id     SERIAL PRIMARY KEY,
    customer_id  INT          NOT NULL,
    status       VARCHAR(20)  NOT NULL,  -- 'pending', 'shipped', 'delivered', 'cancelled'
    order_date   DATE         NOT NULL,
    total_amount NUMERIC(10,2)
);

-- ─────────────────────────────────────────────────
-- SCENARIO 1: The wrong index order
-- ─────────────────────────────────────────────────
-- Our most common query: find all pending orders for a specific customer
-- Wrong approach: putting order_date first even though we almost always
-- filter by customer_id
CREATE INDEX idx_wrong_order ON orders (order_date, customer_id, status);

-- This query CANNOT efficiently use idx_wrong_order because
-- customer_id is not the leftmost column
EXPLAIN
SELECT order_id, total_amount
FROM   orders
WHERE  customer_id = 42
  AND  status = 'pending';
-- Result: Seq Scan — the index is skipped entirely

-- ─────────────────────────────────────────────────
-- SCENARIO 2: The right index order
-- ─────────────────────────────────────────────────
-- Rule: equality filters first, then range filters last
-- customer_id (equality) → status (equality) → order_date (range)
DROP INDEX idx_wrong_order;

CREATE INDEX idx_orders_customer_status_date
    ON orders (customer_id, status, order_date);

-- Now this query uses the index perfectly:
-- Step 1: jump to customer_id = 42 subtree
-- Step 2: within that, jump to status = 'pending'
-- Step 3: within that, scan the sorted order_date range
EXPLAIN
SELECT order_id, total_amount
FROM   orders
WHERE  customer_id = 42
  AND  status = 'pending'
  AND  order_date >= '2024-01-01';
-- Result: Index Scan using idx_orders_customer_status_date

-- ─────────────────────────────────────────────────
-- BONUS: Index Only Scan — the database never touches the main table
-- ─────────────────────────────────────────────────
-- If we only SELECT columns that exist IN the index, it's even faster
EXPLAIN
SELECT customer_id, status, order_date  -- all three are IN the index
FROM   orders
WHERE  customer_id = 42
  AND  status = 'pending';
-- Result: Index Only Scan — zero heap (table) access needed!

-- ─────────────────────────────────────────────────
-- 1. UNIQUE INDEX — constraint + performance in one shot
-- ─────────────────────────────────────────────────
-- Enforce that no two users share an email, while also making
-- login queries (WHERE email = ?) lightning fast
CREATE UNIQUE INDEX idx_users_email_unique
    ON users (email);

-- Test the constraint: this will fail fast (checked against the index)
INSERT INTO users (email, full_name, country)
VALUES ('alice@example.com', 'Alice Clone', 'USA');
-- ERROR: duplicate key value violates unique constraint

-- ─────────────────────────────────────────────────
-- 2. PARTIAL INDEX — only index the rows you actually query
-- ─────────────────────────────────────────────────
-- Problem: orders table has 10M rows, but 98% are status='delivered'
-- We almost never query delivered orders — we care about active ones
-- A full index on status wastes space on 9.8M rows we never need

-- Full index (expensive, bloated):
CREATE INDEX idx_orders_status_full ON orders (status);
-- Indexes ALL 10M rows including the 9.8M 'delivered' ones

-- Partial index (lean, surgical):
DROP INDEX idx_orders_status_full;

CREATE INDEX idx_orders_active_only
    ON orders (customer_id, order_date)
    WHERE status IN ('pending', 'processing', 'shipped');
-- Only indexes ~200K rows — 50x smaller, much faster to scan

-- This query will use the partial index:
EXPLAIN
SELECT customer_id, order_date
FROM   orders
WHERE  status = 'pending'
  AND  customer_id = 99;
-- The WHERE clause on the index matches, so the planner uses it

-- ─────────────────────────────────────────────────
-- 3. COVERING INDEX with INCLUDE — eliminate heap fetches
-- ─────────────────────────────────────────────────
-- Problem: a dashboard query needs customer_id, order_date, total_amount
-- We want an Index Only Scan but total_amount isn't a filter column
-- INCLUDE lets us store extra data in the leaf nodes without affecting sort order

CREATE INDEX idx_orders_covering
    ON orders (customer_id, order_date)
    INCLUDE (total_amount, status);
-- customer_id and order_date define the B-tree sort/search structure
-- total_amount and status are stored in leaf nodes as 'payload'

-- Now this SELECT never touches the main table at all:
EXPLAIN ANALYZE
SELECT customer_id, order_date, total_amount, status
FROM   orders
WHERE  customer_id = 42
  AND  order_date >= '2024-06-01';
-- Result: Index Only Scan using idx_orders_covering

-- ─────────────────────────────────────────────────
-- PITFALL 1: Function on an indexed column = index ignored
-- ─────────────────────────────────────────────────
CREATE INDEX idx_users_email_btree ON users (email);

-- BAD: wrapping the column in LOWER() defeats the index
-- The planner can't use idx_users_email_btree here
EXPLAIN
SELECT user_id FROM users
WHERE LOWER(email) = 'alice@example.com';  -- full scan!
-- Output: Seq Scan on users

-- FIX A: Store emails in lowercase at insert time (best long-term solution)
-- Then your original index on email works fine

-- FIX B: Create a function-based index that matches the query exactly
CREATE INDEX idx_users_email_lower
    ON users (LOWER(email));  -- index stores the lowercased value

-- Now this query WILL use the function-based index:
EXPLAIN
SELECT user_id FROM users
WHERE LOWER(email) = 'alice@example.com';
-- Output: Index Scan using idx_users_email_lower

-- ─────────────────────────────────────────────────
-- PITFALL 2: Implicit type cast silently breaks index usage
-- ─────────────────────────────────────────────────
CREATE TABLE sessions (
    session_id  SERIAL PRIMARY KEY,
    user_id     INT,
    token       VARCHAR(64)  -- stored as VARCHAR
);
CREATE INDEX idx_sessions_user_id ON sessions (user_id);

-- BAD: passing a string '42' when user_id is INT
-- Some databases cast silently, causing a full scan
EXPLAIN
SELECT session_id FROM sessions
WHERE user_id = '42';  -- '42' is a string literal — type mismatch risk

-- FIX: always match the data type of your parameter to the column
EXPLAIN
SELECT session_id FROM sessions
WHERE user_id = 42;  -- 42 as integer — index used correctly

-- ─────────────────────────────────────────────────
-- PITFALL 3: Checking index usage and detecting unused indexes
-- ─────────────────────────────────────────────────
-- In PostgreSQL, pg_stat_user_indexes tracks how often each index is used
-- Run this periodically to find indexes that are costing writes but helping no reads
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan   AS times_index_was_used,   -- low = likely useless
    idx_tup_read,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM
    pg_stat_user_indexes
ORDER BY
    idx_scan ASC,  -- show least-used indexes first
    pg_relation_size(indexrelid) DESC;  -- biggest wasted space at top