Why Your Database Indexes Are Not Working (And How to Fix Them)

Without index (sequential scan):
Query: WHERE user_id = 12345

Table: Row 1 → check → no
       Row 2 → check → no
       Row 3 → check → no
       ... (50 million rows later)
       Row 38,291,003 → check → YES
       ... (continues scanning remaining rows)

Time: O(n) - checks every single row


With index (index scan):
Query: WHERE user_id = 12345

Index (B-tree):
       [1-25000] → [12000-12500] → [12345] → Row pointer

Table: Jump directly to Row 38,291,003

Time: O(log n) - a few tree traversals

-- Index on 'email' column
CREATE INDEX idx_users_email ON users(email);

-- ❌ THIS WILL NOT USE THE INDEX
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- ❌ THIS WILL NOT USE THE INDEX EITHER
SELECT * FROM users WHERE UPPER(email) = 'JOHN@EXAMPLE.COM';

-- ❌ ALSO BROKEN - any function wrapping the column
SELECT * FROM users WHERE TRIM(email) = 'john@example.com';
SELECT * FROM users WHERE CONCAT(first_name, ' ', last_name) = 'John Doe';
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';

-- PostgreSQL: Create an index on the expression
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Now this query USES the index
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';


-- MySQL: Use a generated column + index
ALTER TABLE users ADD email_lower VARCHAR(255)
    GENERATED ALWAYS AS (LOWER(email)) STORED;
CREATE INDEX idx_users_email_lower ON users(email_lower);

-- Now query against the generated column
SELECT * FROM users WHERE email_lower = 'john@example.com';

-- Common date function trap and fix
-- ❌ BROKEN: Function on column prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- ✅ FIXED: Range query uses index on created_at
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- ❌ BROKEN: DATE() function on column
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';

-- ✅ FIXED: Range query on the original column
SELECT * FROM orders
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16';

-- Index on 'name' column
CREATE INDEX idx_users_name ON users(name);

-- ✅ THIS USES THE INDEX (prefix match)
SELECT * FROM users WHERE name LIKE 'John%';

-- ❌ THIS DOES NOT USE THE INDEX (leading wildcard)
SELECT * FROM users WHERE name LIKE '%John';

-- ❌ THIS DOES NOT USE THE INDEX EITHER (wildcard on both sides)
SELECT * FROM users WHERE name LIKE '%John%';

-- Need to find emails by domain? (@gmail.com)
ALTER TABLE users ADD email_reversed VARCHAR(255)
    GENERATED ALWAYS AS (REVERSE(email)) STORED;
CREATE INDEX idx_users_email_reversed ON users(email_reversed);

-- Search for @gmail.com → search for reversed prefix
SELECT * FROM users WHERE email_reversed LIKE REVERSE('%.gmail.com');
-- This becomes: WHERE email_reversed LIKE 'moc.liamg.%' → uses index

-- PostgreSQL: Full-text search with GIN index
CREATE INDEX idx_products_search ON products USING GIN(to_tsvector('english', name));

SELECT * FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('english', 'wireless & headphones');

-- Or use trigram indexes for partial matching
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIN(name gin_trgm_ops);

-- Now LIKE '%john%' CAN use the trigram index
SELECT * FROM users WHERE name LIKE '%john%';

-- High selectivity - good index candidate
-- email: 10,000,000 unique values out of 10,000,000 rows
-- Selectivity: 1.0 (perfect)
CREATE INDEX idx_users_email ON users(email);  -- ✅ Useful

-- Low selectivity - bad index candidate
-- status: 3 unique values ('active', 'inactive', 'pending') out of 10,000,000 rows
-- Selectivity: 0.0000003 (terrible)
CREATE INDEX idx_users_status ON users(status);  -- ❌ Usually useless

-- Low selectivity - boolean column
-- is_verified: 2 unique values (true/false) out of 10,000,000 rows
CREATE INDEX idx_users_verified ON users(is_verified);  -- ❌ Waste of space

10,000,000 row table
Query: WHERE status = 'active' (matches 6,000,000 rows = 60%)

Sequential scan: Read entire table sequentially
  → ~10,000,000 × sequential read cost = fast (sequential I/O is cheap)

Index scan: Look up 6,000,000 entries in index, then random-read each row
  → 6,000,000 × random read cost = SLOW (random I/O is 10-100x more expensive)

The query planner correctly chooses sequential scan.
Your index is ignored. This is the right behavior.

-- Instead of indexing all status values...
CREATE INDEX idx_users_status ON users(status);  -- ❌ Mostly useless

-- ...index only the values you actually query for
-- If you only query for 'pending' users (which are rare):
CREATE INDEX idx_users_pending ON users(status) WHERE status = 'pending';  -- ✅

-- PostgreSQL: Only 50,000 rows indexed instead of 10,000,000
-- Much smaller, much faster, actually gets used

-- Composite index
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);

-- ✅ Uses the index (leftmost prefix: user_id)
SELECT * FROM orders WHERE user_id = 123;

-- ✅ Uses the index (leftmost prefix: user_id + status)
SELECT * FROM orders WHERE user_id = 123 AND status = 'shipped';

-- ✅ Uses the index (all three columns)
SELECT * FROM orders WHERE user_id = 123 AND status = 'shipped' AND created_at > '2024-01-01';

-- ❌ DOES NOT USE THE INDEX (missing user_id - the leftmost column)
SELECT * FROM orders WHERE status = 'shipped';

-- ❌ DOES NOT USE THE INDEX (skips status - breaks the prefix chain)
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';
-- (Actually uses the index partially - only the user_id part)

-- ❌ DOES NOT USE THE INDEX (none of the leftmost columns)
SELECT * FROM orders WHERE created_at > '2024-01-01';

-- Query pattern:
SELECT * FROM orders
WHERE user_id = ?          -- equality
  AND status = ?           -- equality
  AND created_at > ?       -- range
ORDER BY created_at DESC;

-- ✅ Best index for this query:
CREATE INDEX idx_orders_optimized ON orders(user_id, status, created_at);
-- Equality columns first (user_id, status), range column last (created_at)

-- ❌ Wrong order:
CREATE INDEX idx_orders_bad ON orders(created_at, user_id, status);
-- Range column first breaks the effectiveness for equality lookups

-- If user_id has 1,000,000 unique values and status has 3:
CREATE INDEX idx_orders ON orders(user_id, status);     -- ✅ Better
CREATE INDEX idx_orders ON orders(status, user_id);     -- ❌ Worse
-- user_id narrows to ~10 rows, then status filters further
-- vs. status narrows to ~3,333,333 rows, then user_id filters - more work

-- PostgreSQL: Check when statistics were last updated
SELECT
    relname AS table_name,
    last_autoanalyze,
    last_analyze,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- If last_analyze is days old and you've done major data changes: problem.

-- PostgreSQL: Force statistics update
ANALYZE orders;

-- MySQL: Update statistics
ANALYZE TABLE orders;

-- MySQL: Check cardinality estimates (what the planner sees)
SHOW INDEX FROM orders;

-- After bulk imports, always update statistics
LOAD DATA INFILE 'orders_import.csv' INTO TABLE orders;
ANALYZE TABLE orders;  -- ← Don't forget this

-- Column: phone_number VARCHAR(20)
-- Index on phone_number exists

-- ❌ BROKEN: Comparing varchar to integer forces casting
SELECT * FROM users WHERE phone_number = 5551234567;
-- Database does: WHERE CAST(phone_number AS INTEGER) = 5551234567
-- Function on column → index ignored

-- ✅ FIXED: Use the correct type
SELECT * FROM users WHERE phone_number = '5551234567';


-- Column: id BIGINT
-- ❌ BROKEN in some databases: Comparing bigint to string
SELECT * FROM users WHERE id = '12345';
-- Some databases cast the column, not the literal

-- ✅ FIXED: Match the type
SELECT * FROM users WHERE id = 12345;

-- Index on user_id, separate index on email
CREATE INDEX idx_users_user_id ON users(user_id);
CREATE INDEX idx_users_email ON users(email);

-- ❌ May not use either index effectively
SELECT * FROM users WHERE user_id = 123 OR email = 'john@example.com';
-- Some databases will bitmap-scan both indexes and merge
-- Others will fall back to sequential scan

-- ✅ REWRITE as UNION for guaranteed index usage
SELECT * FROM users WHERE user_id = 123
UNION
SELECT * FROM users WHERE email = 'john@example.com';
-- Each sub-query uses its own index, results are merged

Benchmark: INSERT performance on a table with varying index counts
Table: 10 million rows, PostgreSQL 16, NVMe SSD

Indexes: 0  → 45,000 inserts/second
Indexes: 2  → 32,000 inserts/second  (-29%)
Indexes: 5  → 18,000 inserts/second  (-60%)
Indexes: 10 → 8,500 inserts/second   (-81%)
Indexes: 15 → 4,200 inserts/second   (-91%)

-- PostgreSQL: Find indexes that are never scanned
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_index_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'  -- Don't drop primary keys
ORDER BY pg_relation_size(indexrelid) DESC;

-- MySQL: Check index usage statistics (MySQL 8.0+, performance_schema)
SELECT
    object_schema,
    object_name AS table_name,
    index_name,
    count_star AS rows_read_via_index
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'your_database'
ORDER BY object_name;

-- PostgreSQL: Check index bloat
-- Install pgstattuple extension
CREATE EXTENSION pgstattuple;

SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    avg_leaf_density,
    leaf_fragmentation
FROM pgstatindex('idx_orders_user_id');

-- avg_leaf_density below 50% suggests significant bloat
-- leaf_fragmentation above 30% suggests the index needs rebuilding

-- PostgreSQL: Rebuild index (locks the table - careful in production)
REINDEX INDEX idx_orders_user_id;

-- PostgreSQL: Rebuild without locking (available since PG 12)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- Better: Ensure autovacuum is tuned for your workload
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,   -- Vacuum when 2% of rows are dead (default 20%)
    autovacuum_analyze_scale_factor = 0.01   -- Analyze when 1% of rows change
);

-- PostgreSQL: Check index sizes vs available memory
SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS times_used
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- Compare against shared_buffers (your buffer cache size)
SHOW shared_buffers;
-- If your largest index is 8GB and shared_buffers is 4GB, that index
-- will constantly swap in and out of cache

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 10;

-- ✅ GOOD: Index Scan - the index is being used
Index Scan using idx_orders_user_status on orders
  Index Cond: (user_id = 12345 AND status = 'shipped')
  Rows Removed by Filter: 0
  Buffers: shared hit=8
  Planning Time: 0.2ms
  Execution Time: 0.05ms

-- ❌ BAD: Seq Scan - full table scan, index ignored
Seq Scan on orders
  Filter: (user_id = 12345 AND status = 'shipped')
  Rows Removed by Filter: 9,999,990
  Buffers: shared hit=425,000
  Planning Time: 0.3ms
  Execution Time: 3,200ms

-- MySQL equivalent
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 10;

-- Or the classic EXPLAIN (without ANALYZE)
EXPLAIN SELECT ...;
-- Look for:
--   type: ref, range, index → good (index is used)
--   type: ALL → bad (full table scan)
--   key: which index is being used (NULL = none)
--   rows: estimated rows to examine

-- PostgreSQL: Index usage statistics
SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS number_of_scans,
    idx_tup_read AS rows_read_from_index,
    idx_tup_fetch AS rows_fetched_from_table,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

-- Capture baseline
\timing on  -- PostgreSQL: enables timing

-- Run the slow query 3 times, note the average
SELECT * FROM orders WHERE user_id = 12345 AND created_at > '2024-01-01';
-- Time: 2,450ms, 2,380ms, 2,510ms → Average: ~2,450ms

-- Add the index
CREATE INDEX CONCURRENTLY idx_orders_user_created
    ON orders(user_id, created_at);

-- Update statistics
ANALYZE orders;

-- Run the same query 3 times again
SELECT * FROM orders WHERE user_id = 12345 AND created_at > '2024-01-01';
-- Time: 1.2ms, 0.8ms, 0.9ms → Average: ~1.0ms

-- Verify with EXPLAIN that the index is being used
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345 AND created_at > '2024-01-01';
-- Should show: Index Scan using idx_orders_user_created

-- ❌ DON'T do this in production - locks the table
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- ✅ DO this - builds index without locking
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at);

-- ❌ Function on column → index ignored
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- ✅ Store data in the right format, query without functions
-- (Ensure email is stored lowercase at write time)
SELECT * FROM users WHERE email = 'test@example.com';

-- ❌ Leading wildcard → index ignored
SELECT * FROM users WHERE name LIKE '%john%';
-- ✅ If prefix search is acceptable:
SELECT * FROM users WHERE name LIKE 'john%';

-- ❌ Implicit type cast → index ignored
SELECT * FROM users WHERE phone = 5551234567;
-- ✅ Match the column type:
SELECT * FROM users WHERE phone = '5551234567';

-- ❌ OR condition across columns → may prevent index usage
SELECT * FROM users WHERE user_id = 123 OR email = 'john@example.com';
-- ✅ UNION for guaranteed index usage on each branch:
SELECT * FROM users WHERE user_id = 123
UNION
SELECT * FROM users WHERE email = 'john@example.com';

-- ❌ NOT IN / NOT EXISTS with large lists → may scan
SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded', 'archived');
-- ✅ Rewrite as positive condition if possible:
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped', 'delivered');

-- Your most common query:
SELECT * FROM orders
WHERE user_id = ? AND status = ?
ORDER BY created_at DESC
LIMIT 20;

-- The perfect index for this query:
CREATE INDEX idx_orders_user_status_created
    ON orders(user_id, status, created_at DESC);

-- Why this order:
-- 1. user_id (equality) → narrows to one user's orders
-- 2. status (equality) → narrows to orders with specific status
-- 3. created_at DESC (ordering) → results are pre-sorted, no extra sort step

-- The planner can:
-- → Use the index to find matching rows (user_id + status)
-- → Return results in order (created_at DESC) without sorting
-- → Stop after 20 rows (LIMIT) without scanning further

-- Only 2% of orders are 'pending', but you query them constantly
-- Full index on status - wastes space on 98% of rows you don't query
CREATE INDEX idx_orders_status ON orders(status);  -- 500MB

-- Partial index - only the rows you care about
CREATE INDEX idx_orders_pending ON orders(created_at)
    WHERE status = 'pending';  -- 10MB

-- This index serves:
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

-- And it's 50x smaller than the full index.

-- Partial index for "soft deleted" records
-- 99% of queries filter to non-deleted records
CREATE INDEX idx_users_active_email ON users(email)
    WHERE deleted_at IS NULL;

-- Serves the common case efficiently:
SELECT * FROM users WHERE email = ? AND deleted_at IS NULL;

-- Query:
SELECT user_id, status, created_at FROM orders
WHERE user_id = 12345 AND status = 'shipped';

-- Regular index - requires table lookup for each matching row
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Flow: Index → find matching entries → go to table → read user_id, status, created_at

-- Covering index - includes created_at, no table lookup needed
CREATE INDEX idx_orders_covering ON orders(user_id, status, created_at);
-- Flow: Index → find matching entries → return data directly from index
-- (Index-Only Scan in EXPLAIN)


-- PostgreSQL: INCLUDE clause for covering indexes (PG 11+)
-- Better: Only the first columns are used for searching, included columns are just stored
CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (created_at, total);
-- Searches by user_id + status, but also stores created_at and total in the index
-- Enables index-only scan without making the search tree wider

-- PostgreSQL: Generate DROP statements for unused indexes
SELECT
    'DROP INDEX CONCURRENTLY ' || indexrelname || ';' AS drop_command,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size_freed
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Reset statistics to start fresh observation
SELECT pg_stat_reset();

-- Wait 2-4 weeks...

-- Then check which indexes have still never been used
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;

-- PostgreSQL: Find potentially redundant indexes
SELECT
    a.indexrelid::regclass AS potentially_redundant,
    b.indexrelid::regclass AS covers_it,
    pg_size_pretty(pg_relation_size(a.indexrelid)) AS wasted_size
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
    AND a.indexrelid != b.indexrelid
    AND a.indkey::text = ANY(
        SELECT string_to_array(b.indkey::text, ' ')::text[]
    )
WHERE NOT a.indisprimary;

-- PostgreSQL: Top 10 slowest queries by total time
SELECT
    substring(query, 1, 100) AS query_preview,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;