PostgreSQL Performance Tuning: Indexes and Queries

PostgreSQL is one of the most capable open-source relational databases available, but out of the box it won't automatically know how your application queries data. Performance tuning is the discipline of closing the gap between the queries you write and the way Postgres executes them. This guide walks through the two highest-leverage areas: designing effective indexes and writing queries the planner can execute efficiently.

Start by Measuring, Not Guessing

The single most common tuning mistake is optimizing based on intuition. Before you add a single index, learn to read what PostgreSQL is actually doing.

The EXPLAIN and EXPLAIN ANALYZE commands are your primary tools. EXPLAIN shows the planner's chosen execution plan and its cost estimates; EXPLAIN ANALYZE actually runs the query and reports real timing and row counts.

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

Read the output from the innermost node outward. Key things to look for:

Seq Scan on a large table in a hot query path usually signals a missing index.
A large gap between rows (estimated) and actual rows means your statistics are stale — run ANALYZE.
Buffers shows how much data was read from cache versus disk; heavy shared read numbers indicate I/O pressure.

Enable pg_stat_statements to find your worst offenders across the whole workload rather than tuning queries in isolation:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Sort by total_exec_time, not mean_exec_time — a fast query called ten million times often costs more than a slow one called twice.

Understand How Indexes Actually Help

An index is a separate data structure that lets Postgres find rows without scanning the whole table. The default and most versatile index type is the B-tree, which supports equality and range comparisons (=, <, >, BETWEEN, ORDER BY).

Create one on columns that appear frequently in WHERE, JOIN, and ORDER BY clauses:

CREATE INDEX idx_orders_customer ON orders (customer_id);

But indexes are not free. Every INSERT, UPDATE, and DELETE must maintain them, and they consume disk and memory. The goal is the smallest set of indexes that covers your most important read paths.

Composite Indexes and Column Order

When queries filter on multiple columns, a composite (multi-column) index often beats two separate indexes. Column order matters enormously because a B-tree can only use a prefix of the index efficiently.

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

This index serves queries filtering on customer_id alone, or on customer_id AND status. It does not efficiently serve a query filtering only on status. The rule of thumb: put the most selective, most frequently filtered column first, and place equality columns before range columns.

Covering Indexes

If an index contains all the columns a query needs, Postgres can answer it from the index alone — an index-only scan — without touching the table heap. Use INCLUDE to add non-key columns:

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

This is powerful for hot read paths, but the wider index costs more to store and maintain.

Partial Indexes

When you only ever query a subset of rows, index just that subset. Partial indexes are smaller, faster, and cheaper to maintain:

CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

If 95% of your orders are completed and you constantly query pending ones, this index is a fraction of the size of a full index.

Specialized Index Types

Beyond B-tree, Postgres offers indexes for specific workloads:

GIN — ideal for jsonb, arrays, and full-text search. Use it when you query containment (@>) or membership.
GiST — geometric data, ranges, and nearest-neighbor searches.
BRIN — tiny indexes for very large tables where data is naturally ordered (like append-only time-series). A BRIN index on a timestamp column can be thousands of times smaller than a B-tree.
Hash — equality-only lookups; rarely worth it over B-tree in modern versions.

CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);

Write Queries the Planner Can Optimize

Even perfect indexes won't help if your query prevents their use. A few patterns matter most.

Avoid Functions on Indexed Columns

Wrapping an indexed column in a function defeats the index:

-- Index cannot be used
SELECT * FROM users WHERE lower(email) = '[email protected]';

Either store the normalized value, or create an expression index that matches the query:

CREATE INDEX idx_users_lower_email ON users (lower(email));

The same applies to implicit type casts and date arithmetic. Prefer created_at >= '2026-01-01' over date_trunc('day', created_at) = ... when you can.

Select Only What You Need

SELECT * forces Postgres to read and transfer every column, and it prevents index-only scans. Name the columns you actually use. This reduces I/O, network transfer, and memory in sorts and hashes.

Beware the N+1 Pattern

Fetching a list and then issuing one query per row is a classic application-layer killer. Replace loops of single-row lookups with a single JOIN or a WHERE id = ANY($1) batch query. The database is far better at set operations than your application is at iterating.

Keyset Pagination Over OFFSET

LIMIT 20 OFFSET 100000 forces Postgres to scan and discard 100,000 rows. For deep pagination, use keyset (cursor) pagination based on an indexed column:

SELECT * FROM orders
WHERE created_at < $last_seen
ORDER BY created_at DESC
LIMIT 20;

This stays fast no matter how deep you page.

Help the Planner With Good Statistics

The planner relies on statistics gathered by ANALYZE. If plans go bad after a bulk load, run ANALYZE manually. For columns that are correlated (like city and postal_code), create extended statistics so the planner doesn't underestimate row counts:

CREATE STATISTICS stat_city_zip (dependencies)
ON city, postal_code FROM addresses;

Maintenance and Configuration

Tuning isn't only about individual queries. Routine health matters:

Autovacuum reclaims dead tuples from updates and deletes. Bloated tables slow every scan. Monitor pg_stat_user_tables for high n_dead_tup and tune autovacuum thresholds on hot tables.
REINDEX occasionally rebuilds bloated indexes; REINDEX CONCURRENTLY avoids locking.
Key config values: set shared_buffers to roughly 25% of RAM, effective_cache_size to about 50–75% so the planner knows how much OS cache exists, and raise work_mem cautiously to let sorts and hash joins stay in memory (remember it is per-operation, not per-connection).
Drop unused indexes. Query pg_stat_user_indexes for indexes with idx_scan = 0 — they cost writes and disk for zero read benefit.

A Practical Tuning Workflow

Identify the slowest, most frequent queries with pg_stat_statements.
Run EXPLAIN (ANALYZE, BUFFERS) on each.
Look for sequential scans, bad row estimates, and expensive sorts.
Add or adjust one index, or rewrite the query.
Re-run EXPLAIN ANALYZE and confirm the improvement.
Verify write performance hasn't regressed, then move to the next query.

Change one thing at a time so you always know what helped.

Frequently Asked Questions

How many indexes are too many? There's no fixed number, but each index taxes every write. If a table has more than five or six indexes, audit them against pg_stat_user_indexes and remove any that aren't used by real queries.

Why is Postgres ignoring my index? Common reasons: the table is small enough that a sequential scan is genuinely faster, statistics are stale, a function or cast on the column blocks the index, or the query returns so many rows that a scan is cheaper than random index lookups. Check the plan with EXPLAIN ANALYZE before assuming it's a bug.

Should I use VACUUM FULL? Rarely. VACUUM FULL takes an exclusive lock and rewrites the whole table. Regular autovacuum plus occasional REINDEX CONCURRENTLY handles most bloat. Reserve VACUUM FULL for one-off recovery after massive deletes, during a maintenance window.

What's the difference between an index scan and an index-only scan? An index scan uses the index to locate rows, then visits the table heap to fetch the remaining columns. An index-only scan answers the query entirely from the index (helped by INCLUDE columns and an up-to-date visibility map), skipping the heap and saving significant I/O.

Does column order in a composite index really matter? Yes. A B-tree index on (a, b) supports filters on a and on a AND b, but not efficiently on b alone. Order columns by how you query them, putting equality predicates before range predicates.

When should I reach for GIN or BRIN instead of B-tree? Use GIN for jsonb, arrays, and full-text search where you test containment or membership. Use BRIN for very large, naturally ordered tables (like time-series) where you want a tiny index and can tolerate coarser filtering. B-tree remains the default for everything else.

Performance tuning is iterative. Measure, change one thing, measure again — and let EXPLAIN ANALYZE be the final word over intuition every time.