PostgreSQL Performance Optimization Guide
A practical workflow for slow PostgreSQL queries: read EXPLAIN plans, add the smallest index that works, rank offenders with pg_stat_statements, and tune configuration last.

The orders endpoint behind our admin dashboard answered in about 80 ms for the first year. Then the table passed a few million rows and the same endpoint started taking seconds, with no code change at all. Nobody had touched the query. The planner had simply run out of cheap ways to answer it.
That is the usual shape of a PostgreSQL performance problem: it creeps in slowly, then arrives all at once. The cause is rarely a secret setting. It is almost always a missing index, a query that cannot use the index you already have, or a table nobody has vacuumed in a while. This guide is the workflow I follow: measure with EXPLAIN, fix with the smallest index that works, rank queries with pg_stat_statements, and touch configuration last.
Quick takeaways
- Run
EXPLAIN (ANALYZE, BUFFERS)before changing anything. The plan tells you whether you have a missing index, a bad row estimate or simply too much data. - In a composite index, put equality columns first and the range or sort column last.
- Partial and covering indexes keep indexes small and enable index-only scans, but every index makes writes slower.
- Rank queries by total time, not by the slowest single run. A 5 ms query called millions of times often costs more than a 2 s report.
- Change configuration last, one setting at a time, and keep connection counts low with a pooler.
How do I read an EXPLAIN plan?
Start with the query that hurts and ask PostgreSQL what it actually did:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents
FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
ANALYZE really executes the statement, so for UPDATE or DELETE wrap it in BEGIN; ... ROLLBACK;. A trimmed plan from a table without a suitable index looks like this (illustrative numbers):
Limit (actual time=212.4..212.4 rows=20 loops=1)
-> Sort (actual time=212.4..212.4 rows=20 loops=1)
Sort Key: created_at DESC
-> Seq Scan on orders (actual time=0.1..201.7 rows=312 loops=1)
Filter: ((customer_id = 4821) AND (status = 'paid'::text))
Rows Removed by Filter: 4188000
Three things to look for. First, the scan type: a Seq Scan that removes millions of rows to return a few hundred is the classic missing-index signature. Second, estimated versus actual rows: a large gap means stale statistics, so run ANALYZE on the table. Third, the Buffers line and sort method: reads from disk and external merge sorts point at memory pressure or a query returning far more than it needs.
Which index should I create?
B-tree is the default and fits equality, range and sort queries. Reach for GIN when you search arrays, jsonb or full-text vectors, and for GiST on geometric or range data. BRIN is a tiny index that works well on huge, append-only tables ordered by time. Hash indexes support equality only and are rarely better than a B-tree.
For the query above, three variants cover most real cases:
-- equality columns first, then the column used for ordering
CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);
-- partial: index only the rows the hot query touches
CREATE INDEX CONCURRENTLY idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';
-- covering: lets PostgreSQL answer from the index alone
CREATE INDEX CONCURRENTLY idx_orders_customer_covering
ON orders (customer_id, created_at DESC) INCLUDE (total_cents);
An index-only scan also needs an up-to-date visibility map, which autovacuum maintains, so a covering index on a table that never gets vacuumed will disappoint you. CREATE INDEX CONCURRENTLY avoids blocking writes but cannot run inside a transaction; the lock-related details are in Postgres Migrations Without Locks. After creating an index, run the EXPLAIN again and compare the numbers. If the plan did not change, drop the index.
How do I find the queries that deserve attention?
Guessing wastes time. pg_stat_statements aggregates every normalized query, and sorting by total time shows where the database actually spends its day:
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements' (restart required)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT round(total_exec_time::numeric, 0) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
A 200 ms query that runs 50 times a day is not your problem; a 5 ms query that runs five million times is. Pair this with a slow query log (log_min_duration_statement = 500) to catch one-off outliers, and with auto_explain if you want plans captured for them automatically.
Which configuration settings matter?
Fewer than you would think. These are starting points for a dedicated 16 GB server, not rules:
# postgresql.conf
shared_buffers = 4GB # about 25% of RAM
effective_cache_size = 12GB # planner hint, 50-75% of RAM
work_mem = 32MB # per sort or hash node, per query
maintenance_work_mem = 1GB
random_page_cost = 1.1 # for SSD storage
log_min_duration_statement = 500
Be careful with work_mem: it applies to each sort or hash operation in each query, so 200 concurrent queries with a few such nodes each can exhaust memory quickly. That is also why connection count matters. Every PostgreSQL connection is a process, so put PgBouncer in front of the database and keep the server at a few dozen connections rather than hundreds. Transaction pooling does break session-level features such as advisory locks and SET, so test your application first. If one table is simply too large to vacuum and trim comfortably, the next step is partitioning, covered in PostgreSQL Partitioning and Read Replicas.
What are the most common PostgreSQL performance mistakes?
- N+1 queries. Fetching related rows in a loop. Use a join, or one query with
WHERE id = ANY($1). - Unindexed foreign keys. PostgreSQL does not create an index on the referencing column for you, and deletes on the parent table will scan the child.
- Functions on indexed columns.
WHERE lower(email) = 'a@b.com'cannot use a plain index onemail; create an expression index onlower(email). - Over-indexing. Each index costs write time and disk. Find the ones nobody reads:
SELECT s.relname AS table_name,
s.indexrelname AS index_name,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;
- Ignoring autovacuum. Dead rows bloat tables and stale statistics produce bad plans. Watch
n_dead_tupinpg_stat_user_tables.
Statistics reset on a crash or manual reset, and an index may be used only on a replica, so check a full business cycle on every node before dropping anything.
What should I do this week?
Enable pg_stat_statements, let it collect for a day, and take the top five queries by total time. Run EXPLAIN (ANALYZE, BUFFERS) on each and fix only the worst one with a single index. Record the before and after timings in the pull request so the next person sees the method, not just the result. Application-side causes of slow endpoints are covered in Backend Performance Optimization.
Uğur Kaval
AI/ML Engineer & Full Stack Developer specializing in building innovative solutions with modern technologies. Passionate about automation, machine learning, and web development.


