How to design and tune PostgreSQL indexes for query performance

What and why

Indexes let PostgreSQL find rows without scanning a whole table, but each index slows writes and consumes disk. Good tuning means adding the few indexes that matter and removing the rest. This tutorial walks from diagnosis to verified improvement.

Prerequisites

• A running PostgreSQL instance you can connect to with psql.
• A table with enough rows that scans are visibly slow (tens of thousands or more).
• Permission to create indexes and read system catalogs.

Steps

1. Capture slow queries

Enable the pg_stat_statements extension to find the worst offenders:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

2. Read the query plan

Run the candidate query with timing and buffers:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';

Look for Seq Scan on large tables, high rows estimates that miss actual counts, and expensive sorts.

3. Choose an index type

• B-tree (default): equality and range on scalar columns.
• GIN: jsonb, arrays, and full-text search.
• BRIN: huge, naturally ordered tables such as time series.
• Partial: index only the rows you query, e.g. WHERE status = 'open'.

For the example, a composite index fits:

CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status);

4. Create the index concurrently

CONCURRENTLY avoids locking the table for writes during the build. It cannot run inside a transaction block and takes longer, but it is the safe choice on production.

5. Update statistics

The planner relies on statistics. After a large data change, run:

ANALYZE orders;

For skewed columns, raise the target: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

6. Verify and prune unused indexes

Re-run EXPLAIN (ANALYZE, BUFFERS) and confirm an Index Scan replaced the sequential scan and that timing dropped. Then find dead weight:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Drop indexes that are never scanned and not enforcing a constraint.

Verification

The tuned query should show an index scan, lower actual time, and fewer shared buffers read. Overall write latency should remain acceptable; if it degrades, you added too many indexes.

Next Steps

Automate plan checks in CI for hot queries, enable auto_explain for slow statements in production, and revisit indexes after major schema or traffic changes.