Speed Up Slow Queries with SQL Indexes

Diagnose slow SQL queries using EXPLAIN and add targeted indexes to reduce row scans from millions to dozens. Covers B-tree indexes, composite ordering, and when indexes hurt performance.

~20 minsqlpostgresperformanceindexing

Task

Identify a slow query on a large table, analyze its execution plan, and add the correct index to achieve sub-10ms response time.

When to use this

A SELECT query on a 5-million-row orders table takes 8 seconds. Users experience timeouts on the order history page. The query filters by customer_id and date range, sorted by created_at.

Prerequisites

PostgreSQL 14+ or MySQL 8+ (any SQL database with EXPLAIN)
Read access to the database with permissions to run EXPLAIN
Understanding of basic SELECT, WHERE, and ORDER BY

Steps

1. Run the slow query with EXPLAIN (ANALYZE, BUFFERS) to see the actual execution plan.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 12345 AND created_at > '2025-01-01' ORDER BY created_at DESC LIMIT 20;

Expected output: Shows 'Seq Scan on orders' with actual time ~8000ms, rows removed by filter: 4998000, rows returned: 20. The Seq Scan reads all 5M rows just to filter down to 20.

2. Identify that a sequential scan on a large table is the root cause. Note the filter conditions and sort order.

3. Create a composite index matching the WHERE clause and ORDER BY to avoid a separate sort step.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

Expected output: CREATE INDEX (on large tables this may take seconds to minutes; use CONCURRENTLY in production to avoid locking)

4. Re-run EXPLAIN ANALYZE to verify the index is used and measure improvement.

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE customer_id = 12345 AND created_at > '2025-01-01' ORDER BY created_at DESC LIMIT 20;

Expected output: Shows 'Index Scan using idx_orders_customer_date' with actual time <5ms. No separate 'Sort' step since the index returns rows in the desired order.

5. Verify index is being used in production-equivalent conditions.

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

Expected output: The new index appears in the list. Confirm with pg_stat_user_indexes that idx_scan counter increases over time.

Troubleshooting

Problem

Index exists but query planner still uses Seq Scan

Solution

The table may be too small (sequential scan is faster for <500 rows). Or the query cannot use the index due to a function wrapping the indexed column (e.g., WHERE LOWER(email) = '[email protected]' can't use a plain index on email).

Problem

Index creation times out on production

Solution

Use CREATE INDEX CONCURRENTLY to avoid locking the table. Note: CONCURRENTLY cannot run inside a transaction block and takes longer to build.

Problem

Query slower after adding index

Solution

Check with EXPLAIN that the planner is actually using your index. If not, run ANALYZE orders to update statistics. The planner may choose wrongly on stale stats.

Limitations

Indexes speed up reads but slow down INSERT/UPDATE/DELETE — every index adds write overhead
Indexes consume storage — a large composite index can be 10-20% of table size
Third+ column of a composite index is rarely used: (a,b,c) index helps queries on (a), (a,b), and (a,b,c) but NOT (b) or (c) alone

How to Read an EXPLAIN Plan

An execution plan shows the database’s strategy for retrieving data. The most important node types:

Seq Scan: Reads every row — slow on large tables
Index Scan: Uses an index to find matching rows directly
Index Only Scan: Best case — all needed columns are in the index (no table access)
Sort: Expensive if not using a pre-ordered index

Visualizing a Full Seq Scan Problem

Seq Scan on orders  (cost=0.00..123456.78 rows=20 width=40)
  Filter: (customer_id = 12345 AND created_at > '2025-01-01')
  Rows Removed by Filter: 4980000
  Buffers: shared read=83334
Execution Time: 7892.341 ms

Problem: Read 83,334 disk pages (5M rows), filtered out all but 20. The index would reduce this to ~3-5 pages.

Composite Index Column Order Matters

-- This index serves queries filtering on:
-- 1. customer_id alone
-- 2. customer_id + created_at
-- But NOT created_at alone (leading column rule)
CREATE INDEX idx_orders_customer_date
  ON orders (customer_id, created_at DESC);

Rule: Put equality columns first (customer_id = X), then range columns (created_at > Y), then sort columns.

Covering Index for Index-Only Scan

Include selected columns to avoid lookups:

-- Include order_status so the query never touches the heap
CREATE INDEX idx_orders_covering
  ON orders (customer_id, created_at DESC)
  INCLUDE (order_status, total_amount);

Production-Safe Index Creation

-- Non-blocking on PostgreSQL 11+
CREATE INDEX CONCURRENTLY idx_orders_customer_date
  ON orders (customer_id, created_at DESC);

-- After creation, update planner statistics
ANALYZE orders;

When Indexes Hurt Performance

Write-heavy tables: Each index adds ~1ms per write operation
Low-cardinality columns: An index on gender (3 values) won’t help much
Small tables: Planner correctly ignores indexes on tables with <1000 rows
Overlapping indexes: (a,b) and (a) are redundant — the first covers the second

Monitoring Index Usage

-- Find unused indexes (candidates for removal)
SELECT indexrelid::regclass AS index_name,
       idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC
LIMIT 10;