Optimize Queries with Database Indexing

Overview

A query that takes 40ms on a 10K-row table can take 40 seconds on 10 million rows — same code, same database, just more data. The difference is almost always whether the planner can use an index or has to walk the whole table. Indexes are ordered lookup structures that let the engine jump straight to matching rows instead of scanning everything.

They aren’t free, though. Every INSERT, UPDATE, and DELETE has to maintain each index on the table, and each index costs disk space and memory. The real skill isn’t “add indexes” — it’s indexing the columns your read patterns actually use, and removing the ones they don’t.

When to Use

Use this recipe when:

• Queries slow down as table size grows
• Slow query logs or EXPLAIN output show sequential scans on large tables
• You’re adding pagination or search filters to an existing table
• You’re designing a new schema and predicting access patterns
• Long-running reads are causing lock contention

Solution

Basic Index (Single Column)

-- Create an index on the email column
CREATE INDEX idx_users_email ON users(email);

-- Query now uses the index instead of scanning the entire table
SELECT * FROM users WHERE email = 'alice@example.com';

Composite Index (Multiple Columns)

-- Column order matters: equality filters first, range filters second
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

-- Supports:
-- WHERE user_id = 1
-- WHERE user_id = 1 AND created_at > '2025-01-01'
-- ORDER BY user_id, created_at DESC

Partial Index

-- Only index active users — smaller and faster for this specific query
CREATE INDEX idx_active_users_email
ON users(email)
WHERE active = true;

Analyzing Query Plans

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

Look for:

• Seq Scan = sequential table scan (slow on large tables, needs an index)
• Index Scan or Index Only Scan = using an index (fast)
• Bitmap Heap Scan = using multiple indexes or a partial match

Companion code: the database-indexing companion repo has the demo schema, all index variants, and EXPLAIN queries ready to run.

Explanation

• B-tree indexes: the default index type, and the right choice for most columns. They handle equality and range queries (=, <, >, BETWEEN) plus ORDER BY. Most databases create one for primary keys automatically.
• Composite indexes: usable for any leftmost prefix of the column list. An index on (a, b, c) supports queries on (a), (a, b), and (a, b, c) — but not (b) or (c) alone.
• Covering indexes: if every column a query needs is already in the index, the engine answers it without touching the table at all — an “index-only scan,” often an order of magnitude faster.
• Partial indexes: smaller indexes over a subset of rows, useful when most queries share a filter condition (e.g., active = true).

Variants

Index Type	Best For	Trade-off
B-tree	Equality, range, ordering	General purpose, higher write cost
Hash	Exact equality only	Faster lookups, no range support
GiST / GIN	Full-text search, JSON, arrays	Larger, slower to build
BRIN	Very large, naturally ordered tables	Tiny size, approximate results

What Works

• Index the columns in your WHERE clause: if a query filters on user_id and status, an index on (user_id, status) is the first thing to try.
• Put equality columns before range columns: for WHERE a = 1 AND b > 100, the index (a, b) is far more useful than (b, a).
• Avoid indexing low-cardinality columns alone: a status column with 3 values doesn’t benefit from a standalone index. Combine it with a high-cardinality column.
• Remove unused indexes: every index slows down writes.
• Index foreign key columns: databases don’t always auto-index foreign keys, and missing FK indexes cause expensive nested loop scans. See database design and SQL Joins for join-side optimization.

Common Mistakes

• Indexing every column: wastes disk, slows writes, and confuses the optimizer with too many choices.
• Wrong column order in composite indexes: an index on (created_at, user_id) can’t help a query filtering only on user_id.
• Indexing columns that are never queried: check query logs before creating indexes.
• Ignoring index maintenance: fragmented indexes on high-churn tables degrade over time. See SQL performance tuning.
• Indexing tiny tables: tables under a few thousand rows are often faster with a sequential scan — the index indirection costs more than the scan saves.

Finding and Fixing Missing Indexes

Identify missing indexes in production

Turn on slow query logging: in PostgreSQL set log_min_duration_statement = 100 to log anything over 100ms; in MySQL set long_query_time = 0.1 and enable slow_query_log. Then use pg_stat_statements to rank queries by cost:

SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 20;

Check seq_scan counts on large tables in pg_stat_user_tables — a high count means rows are being read without an index. pgBadger can analyze PostgreSQL logs and report the slowest queries with their plans.

Verify index usage with EXPLAIN ANALYZE

Run EXPLAIN (ANALYZE, BUFFERS) SELECT ... to see the actual plan with timing and I/O. Seq Scan means a full table scan; Index Scan uses an index; Index Only Scan means all data came from the index with no heap access. Other useful signals:

• Heap Fetches: 0 → the covering index is fully effective.
• Rows Removed by Filter high → the index isn’t selective enough.
• Planning Time high → statistics may be stale; run ANALYZE table_name.
• Buffers: shared hit=N read=N → high read means disk I/O, high hit means cache hits.

In MySQL 8.0.18+ use EXPLAIN ANALYZE or EXPLAIN FORMAT=JSON; look for type: ref or type: range (index used) vs type: ALL (full scan).

Monitor index usage and remove unused indexes

Query pg_stat_user_indexes for indexes never touched:

SELECT schemaname, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 ORDER BY indexname;

An idx_scan of 0 means unused since the last stats reset. Compare size with pg_size_pretty(pg_relation_size('idx_name')), then drop with DROP INDEX CONCURRENTLY idx_name so the table stays writable. In MySQL, check sys.schema_unused_indexes. Audit monthly, but be careful with indexes that only serve infrequent batch jobs or quarterly reports — idx_scan = 0 over a week doesn’t prove they’re safe to drop.

Tune planner statistics when the wrong plan is chosen

The planner picks plans from table statistics. Run ANALYZE table_name after bulk loads or schema changes. Inspect distributions with SELECT * FROM pg_stats WHERE tablename = 'orders' — check most_common_vals for skewed columns, and raise the stats target where needed:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;  -- default 100

If plans are still wrong on SSDs, SET random_page_cost = 1.1 (default 4.0) reflects cheap random reads, and effective_cache_size should match available memory. The auto_explain extension logs plans for slow queries automatically.

Test hypothetical indexes before building them

On a huge table, building an index just to see if the planner uses it is expensive. The hypopg extension simulates indexes in memory:

CREATE EXTENSION hypopg;
SELECT * FROM hypopg_create_index('CREATE INDEX idx_test ON orders(user_id, status)');
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

If the plan uses the hypothetical index, build the real one; otherwise drop it with hypopg_reset() — it only exists in your session.

Benchmark before deploying to production

Clone a representative slice of production data to staging (pg_dump --table=orders --data-only prod_db | psql staging_db), then measure before and after with EXPLAIN (ANALYZE, BUFFERS) and pgbench:

pgbench -c 10 -j 4 -T 60 -f queries.sql   # read workload
pgbench -i -c 10 -T 60                    # write-heavy workload

Compare p99 latency, throughput (TPS), and buffer hit ratio. Estimate index build time on a subset of a large table first, and record the numbers in your deployment runbook.

Advanced Index Types

Covering indexes with INCLUDE

INCLUDE stores extra columns in the index without adding them to the sort key:

CREATE INDEX idx_orders_covering
ON orders(user_id, status) INCLUDE (total_amount, created_at);

Now SELECT total_amount, created_at FROM orders WHERE user_id = 1 AND status = 'paid' is served entirely from the index — Heap Fetches: 0 in EXPLAIN confirms it. MySQL doesn’t have INCLUDE; the InnoDB workaround is a composite index covering all selected columns (secondary indexes already carry the primary key in their leaves).

Expression indexes for computed columns

Index an expression, not a column, when queries wrap the column in a function:

CREATE INDEX idx_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

The query’s expression must match the index expression exactly. MySQL needs a generated column instead:

ALTER TABLE users ADD COLUMN email_lower VARCHAR(255)
  GENERATED ALWAYS AS (LOWER(email)) STORED;
CREATE INDEX idx_email_lower ON users(email_lower);

Partial indexes for soft-delete patterns

Soft-deleted rows (deleted_at timestamp) bloat indexes with data nobody queries. A partial index skips them:

CREATE INDEX idx_active_orders ON orders(user_id) WHERE deleted_at IS NULL;

The planner only uses it when the query includes the matching predicate — if some queries forget WHERE deleted_at IS NULL, they fall back to a full scan. Enforce the filter in the app or via a view. Predicates must match exactly or be implied: a query filtering active = true won’t use an index predicated on active = true AND deleted_at IS NULL.

Composite index column order

An index (user_id, status, created_at) serves WHERE user_id = 1, WHERE user_id = 1 AND status = 'paid', and the same with ORDER BY created_at — but not WHERE status = 'paid' alone. Order the columns equality-first, range-second, and match the index’s sort order to your ORDER BY. Keep composites to 3–4 columns; wider indexes cost more disk and memory with diminishing returns.

LIKE queries and full-text search

A B-tree can’t help LIKE '%pattern%' (leading wildcard). In PostgreSQL, trigram indexes cover substring matching:

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING gin (name gin_trgm_ops);
SELECT * FROM users WHERE name ILIKE '%john%';

For full-text search, index a tsvector and query with @@:

ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_search ON articles USING gin(search_vector);
SELECT * FROM articles WHERE search_vector @@ websearch_to_tsquery('english', 'postgres index');

In MySQL, CREATE FULLTEXT INDEX + MATCH ... AGAINST covers the same ground.

JSONB containment and existence

GIN indexes support PostgreSQL’s JSONB operators (@>, ?, ?|, ?&):

CREATE INDEX idx_data_gin ON events USING gin(data);
SELECT * FROM events WHERE data @> '{"type": "click"}';

For queries that always hit the same JSON path, an expression index on data->>'type' is smaller and faster. jsonb_path_ops produces a more compact GIN index but only supports @>.

Geospatial queries with PostGIS

PostGIS adds GiST indexes for geometry/geography columns:

CREATE INDEX idx_locations_geom ON locations USING gist(geom);
SELECT * FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(-73.9, 40.7)::geography, 1000);

The same index accelerates nearest-neighbor with the <-> operator (ORDER BY geom <-> point LIMIT 10). On large datasets, CLUSTER locations USING idx_locations_geom rewrites the table in index order for better locality.

Exclusion constraints

Exclusion constraints prevent overlapping ranges — bookings, scheduling, price periods:

CREATE EXTENSION btree_gist;
ALTER TABLE bookings ADD EXCLUDE USING gist (room_id WITH =, time_range WITH &&);

PostgreSQL backs the constraint with a GiST index automatically. A conflicting insert fails with conflicting key value violates exclusion constraint.

Index types at a glance

B-tree handles equality, ranges, and sorting — the default for most columns. Hash only supports = and can’t sort or range — mainly for lookup tables. GIN indexes composite values (arrays, JSONB, full-text) and @> containment. BRIN stores min/max per block range — kilobytes instead of gigabytes — for naturally ordered append-only tables like time series. GiST covers geometric and nearest-neighbor searches.

Multi-tenant tables

For shared-schema tenancy, prefix composite indexes with tenant_id so the planner filters by tenant first:

CREATE INDEX idx_orders_tenant ON orders(tenant_id, user_id, status);

With PostgreSQL Row-Level Security the tenant filter is injected automatically, so tenant_id must lead every index on the table. For one very large tenant, a partial index WHERE tenant_id = 'tenant_a' stays small; beyond ~20% of the table, consider partitioning by tenant instead.

Enum and boolean columns

Low-cardinality columns rarely justify a standalone B-tree. For booleans or skewed enums, index only the minority values:

CREATE INDEX idx_pending_orders ON orders(user_id) WHERE status = 'pending';

Check distribution first (SELECT status, COUNT(*) FROM orders GROUP BY status) — if one value dominates over 80%, a partial index on the rest is cheaper than indexing everything.

Write Performance and Maintenance

How indexes affect writes

Every index on a table adds work to INSERT, UPDATE, and DELETE — the row and each index entry must be written together. On write-heavy tables (logs, events, metrics), keep indexes minimal: write to an unindexed staging table and batch-merge, or drop indexes before a bulk load and recreate them after. Use COPY instead of row-by-row INSERT for loads. Measure the actual impact with pgbench — comparing n_tup_ins, n_tup_upd, and n_tup_del in pg_stat_user_tables shows where write volume lands.

Bloat on high-update tables

Each UPDATE in PostgreSQL writes a new row version and dead-tuples the old one, and indexes must be updated unless the update is HOT (Heap-Only Tuple). HOT requires free space in the page — ALTER TABLE orders SET (fillfactor = 80) reserves 20% for in-place updates — and the changed columns must not be indexed. Check the HOT ratio:

SELECT n_tup_hot_upd::float / NULLIF(n_tup_upd, 0) AS hot_ratio
FROM pg_stat_user_tables WHERE relname = 'orders';

Near 1.0 means most updates skip index maintenance. If a column changes constantly (last_seen, status), dropping its index may enable more HOT updates than the index is worth.

Fragmentation and bloat repair

Measure bloat with the pgstattuple extension (SELECT * FROM pgstattuple('idx_name') — watch free_percent). Rebuild with REINDEX INDEX CONCURRENTLY idx_name to avoid blocking writes, or pg_repack/pg_squeeze for online rebuilds. In MySQL, ANALYZE TABLE refreshes statistics and OPTIMIZE TABLE rebuilds table plus indexes. Watch n_dead_tup in pg_stat_user_tables — a dead-to-live ratio above ~20% is a reasonable reindex trigger.

Autovacuum tuning for index health

Autovacuum reclaims dead tuples and keeps the visibility map current, both of which affect index performance. Per-table tuning:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);  -- vacuum at 5% dead (default 20%)

On write-heavy tables go lower (0.02). Raise autovacuum_vacuum_cost_limit (e.g., 2000) and lower autovacuum_vacuum_cost_delay (1ms) if vacuum can’t keep up, and schedule manual VACUUM (ANALYZE) in low-traffic windows as a fallback.

Index-only scans, visibility maps, and hint bits

Index-only scans only avoid the heap when the visibility map marks pages all-visible and hint bits are set on the tuples. After bulk loads both may be stale, so the “covering” index still hits Heap Fetches. Fix with VACUUM (ANALYZE, FREEZE) orders — FREEZE marks tuples permanently visible — and check coverage via the pg_visibility extension. Frequent autovacuum keeps it that way.

Index memory in shared_buffers

PostgreSQL caches index pages in shared_buffers (25% of RAM is a sane start; 30–40% for index-heavy workloads). Inspect usage with pg_buffercache and per-index hit ratio via pg_statio_user_indexes:

SELECT indexname, idx_blks_hit::float / NULLIF(idx_blks_hit + idx_blks_read, 0) AS hit_ratio
FROM pg_statio_user_indexes WHERE idx_blks_read > 0;

Below ~90% the working set doesn’t fit in memory. pg_prewarm can reload hot indexes after a restart.

Online index creation and concurrent deployments

CREATE INDEX takes a lock that blocks writes. Use CREATE INDEX CONCURRENTLY instead — slower, but the table stays writable. A failed concurrent build leaves an invalid index; drop and retry. In MySQL use ALTER TABLE ... ADD INDEX ..., ALGORITHM=INPLACE, LOCK=NONE, or pt-online-schema-change for large tables. Always estimate build time on a staging replica first.

Detecting and repairing corruption

Corruption shows up as wrong results or errors like index "idx_name" contains unexpected page. Verify with amcheck (bt_index_check('idx_name'), or bt_index_parent_check for heap cross-checks) and repair with REINDEX. If the heap itself is corrupt, restore from backup — and investigate the root cause, because repeated corruption means hardware or filesystem trouble.

Monitoring and alerting on index health

Track three things regularly: unused indexes (idx_scan = 0 weekly), bloat (pgstattuple free_percent, alert above ~30%), and cache hit ratio (pg_statio_user_indexes). With postgres_exporter these flow into Prometheus; pg_cron can schedule the maintenance jobs themselves. Record a baseline after each deploy so regressions are visible.

Maintenance automation

pg_cron handles recurring vacuum/reindex jobs inside the database:

CREATE EXTENSION pg_cron;
SELECT cron.schedule('vacuum_analyze_weekly', '0 2 * * 0', 'VACUUM (ANALYZE) orders');
SELECT cron.schedule('reindex_monthly', '0 3 1 * *', 'REINDEX INDEX CONCURRENTLY idx_orders_user_id');

For MySQL, pt-index-usage from Percona Toolkit identifies unused indexes from the slow query log.

Production Scenarios

Foreign key columns

PostgreSQL does not index FK columns automatically. Without one, deleting a parent row scans the whole child table and can trigger lock waits. Index every FK:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Find missing ones with:

SELECT conrelid::regclass AS table, conname AS constraint
FROM pg_constraint WHERE contype = 'f'
AND NOT EXISTS (
  SELECT 1 FROM pg_index WHERE indrelid = conrelid AND conkey @> indkey
);

In MySQL, InnoDB creates the FK index itself.

Indexes in migrations

Version index changes in migration files alongside schema changes (flyway, liquibase, sqitch — each with UP/DOWN). CREATE INDEX CONCURRENTLY can’t run inside a transaction, so configure the migration tool accordingly (executeInTransaction: false in Flyway). Rehearse on a staging replica with production-like volume.

Read replicas and replication lag

Indexes replicate via WAL to physical replicas automatically — but CREATE INDEX CONCURRENTLY on the primary generates extra WAL and temporarily increases lag. Watch pg_stat_replication (write_lag, flush_lag, replay_lag) during index builds, and pg_stat_progress_create_index for build progress. For logical replication, create indexes on each subscriber independently.

ORDER BY and pagination

An index matching the sort order returns the first N rows without a sort step:

CREATE INDEX idx_orders_created_desc ON orders(created_at DESC);

For keyset pagination — which beats OFFSET on deep pages — index the cursor columns and compare as a tuple:

CREATE INDEX idx_orders_cursor ON orders(created_at DESC, id DESC);
SELECT * FROM orders
WHERE (created_at, id) < ('2025-01-15', 12345)
ORDER BY created_at DESC, id DESC LIMIT 20;

GROUP BY and aggregates

An index on the grouping columns enables Index-Only Scans for GROUP BY, and a (user_id, created_at DESC) index answers MAX(created_at) WHERE user_id = 1 in O(1) via a backward scan. COUNT(*) on the full table always scans the heap — use pg_class.reltuples for estimates or a counter table for exact counts.

UUID primary keys

Random UUIDs (v4) scatter inserts across the whole B-tree — poor cache locality and constant page splits. Prefer UUIDv7 or ULID, which are time-ordered and append to the index tail (PostgreSQL 18+ ships native uuidv7(); earlier versions need gen_random_uuid() or app-side v7). If insert throughput is critical, bigint GENERATED ALWAYS AS IDENTITY is still the cheapest option.

Table partitioning

Declarative partitioning splits a large table by a key, and each partition carries its own index:

CREATE TABLE orders (id serial, created_at timestamp, user_id int)
  PARTITION BY RANGE (created_at);
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE INDEX ON orders(user_id);  -- propagates to all partitions

Queries filtering the partition key prune irrelevant partitions entirely. Partition by what most queries filter on — usually date or tenant_id.

Safe index removal

Before dropping an index, check what depends on it: SELECT conname, contype FROM pg_constraint WHERE conindid = 'idx_name'::regclass finds constraints it backs (drop the constraint instead if it’s a PK/UNIQUE). Drop during low traffic with DROP INDEX CONCURRENTLY, then watch pg_stat_statements for 24–48h — if a query regresses, recreate the index.

Troubleshooting

• Index exists but the planner ignores it: statistics are likely stale (ANALYZE table_name), the query’s predicate doesn’t match a partial/expression index exactly, or the table is small enough that a seq scan is genuinely cheaper. Check pg_stats and test with hypopg.
• Writes got slower after adding indexes: count indexes per table — each one multiplies write work. Drop unused indexes (idx_scan = 0), and for write-heavy tables consider whether a covering approach can replace several single-column indexes.
• Seq Scan persists on a huge table even with an index: the index may not be selective enough for that query (e.g., a value present in 40% of rows), or the query wraps the column in a function the index doesn’t cover — that’s what expression indexes fix.
• Index-only scan still reads the heap (Heap Fetches > 0): the visibility map is stale. Run VACUUM (ANALYZE) and verify with pg_visibility.
• Index creation locks the table: use CREATE INDEX CONCURRENTLY; if it fails midway, drop the invalid index and retry.

See Also

• Query Optimization — rewriting slow queries beyond indexing
• SQL Performance Tuning — the broader tuning workflow
• Indexing Strategies Guide — choosing index strategies conceptually
• SQL Joins — join types and their index requirements

Further Reading

• PostgreSQL Indexes documentation — index types, INCLUDE, partial and expression indexes
• Use The Index, Luke — free deep guide on indexing and the query planner
• MySQL Optimization and Indexes — InnoDB index behavior