How to Optimize PostgreSQL Query Performance in 2026: Indexes, EXPLAIN, and Tuning

Most PostgreSQL performance problems come down to a handful of causes: a missing index, an index the planner cannot use, a query pulling far more rows than it needs, or statistics that no longer reflect the data. This guide covers how to find which one you have and fix it, in the order that finds problems fastest.

Step 1: Find the Slow Queries

Do not guess. Enable pg_stat_statements, which records execution statistics for every query shape.

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
-- then restart, and in your database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Highest total time — usually where the real wins are
SELECT
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total time rather than mean. A query taking 20ms but running 100,000 times an hour costs far more than one taking 2 seconds twice a day, and it is usually easier to fix.

Also log slow queries so you catch what happens in production but not in testing.

-- postgresql.conf
log_min_duration_statement = 500   -- log anything over 500ms
log_lock_waits = on
log_temp_files = 0                 -- log every temp file (indicates spilling)

Step 2: Read EXPLAIN ANALYZE Properly

Always use EXPLAIN (ANALYZE, BUFFERS). Plain EXPLAIN shows the planner’s estimate; ANALYZE actually runs the query and reports reality, and the gap between the two is often the bug.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= now() - interval '7 days'
  AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;

Read the output from the innermost node outward, and look for four things.

Estimated versus actual rows. rows=10 ... actual rows=48000 means the planner was badly wrong, and every decision it made downstream is suspect. Usually stale statistics.

Sequential scans on large tables. Fine on small tables and a red flag on large ones with a selective filter.

Rows removed by filter. A high number means the database read far more rows than it returned — the classic missing-index signature.

External merge or disk-based sort. The sort spilled to disk because work_mem was too small for it.

-- Refresh statistics when estimates are wrong
ANALYZE orders;

-- Collect more detail on a column with skewed distribution
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Step 3: Index the Right Way

Column order in a composite index matters enormously. The rule: equality columns first, then range columns, then columns you sort by.

-- For: WHERE status = 'pending' AND created_at >= ... ORDER BY created_at DESC
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC);

With status first, the index narrows to matching rows and then reads them already ordered by created_at, so the sort disappears entirely. Reverse the order and the index becomes far less useful.

Partial indexes are dramatically smaller when you only ever query a subset.

-- If 98% of orders are completed and you only query pending ones
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at DESC)
  WHERE status = 'pending';

Covering indexes let the query be answered from the index alone, avoiding the table entirely.

CREATE INDEX CONCURRENTLY idx_orders_lookup
  ON orders (user_id, created_at DESC)
  INCLUDE (total, status);
-- EXPLAIN then shows "Index Only Scan"

Always build indexes on live systems with CONCURRENTLY, which does not block writes.

Step 4: Why an Index Is Being Ignored

A common frustration — the index exists and the planner will not use it. Usual causes:

A function is applied to the column. This makes the index unusable.

-- ❌ Index on email cannot be used
WHERE lower(email) = 'user@example.com';

-- ✅ Index the expression instead
CREATE INDEX CONCURRENTLY idx_users_email_lower ON users (lower(email));

Type mismatch. Comparing a bigint column to a string forces a cast that defeats the index. Match the types in your query parameters.

Leading wildcard in LIKE. LIKE '%term' cannot use a B-tree index. Use a trigram index for that.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
  ON products USING gin (name gin_trgm_ops);
-- now ILIKE '%widget%' can use an index

The query is not selective. If a filter matches a large fraction of the table, a sequential scan is genuinely faster and the planner is correct. Not every “unused index” is a bug.

Step 5: Fix N+1 Queries

The most common application-level cause of database load, and usually invisible in slow-query logs because each individual query is fast.

-- 1 query for orders, then one per order for the user: 101 round trips
SELECT * FROM orders LIMIT 100;
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;   -- ... and so on

-- ✅ One query
SELECT o.*, u.email, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
LIMIT 100;

In an ORM, this means eager loading — includes in Active Record, selectinload in SQLAlchemy, include in Prisma. Detect the pattern by counting queries per request in development; a sudden jump with list size is the signature.

Step 6: Pagination That Scales

OFFSET gets slower the deeper you page, because the database must generate and discard every skipped row.

-- ❌ Reads and throws away 100,000 rows
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- ✅ Keyset pagination — constant time at any depth
SELECT * FROM posts
WHERE (created_at, id) < ($1, $2)   -- values from the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

Including id as a tiebreaker makes the ordering total, which prevents rows being skipped or repeated when timestamps collide.

Step 7: Configuration Worth Changing

Defaults are conservative and assume very modest hardware.

random_page_cost is the most impactful and least known. The default of 4.0 assumes random reads are four times more expensive than sequential ones, which was true for mechanical drives. On SSDs it makes the planner avoid indexes it should use.

-- Test the effect on a single session before changing it globally
SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Step 8: Bloat and Vacuum

PostgreSQL keeps old row versions until vacuum reclaims them. Heavy update or delete traffic causes bloat, which makes scans read more pages than necessary.

-- Which tables are bloated and when were they last vacuumed?
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Rebuild a bloated table without a long exclusive lock
VACUUM (ANALYZE, VERBOSE) orders;

-- For severe bloat, rebuild indexes concurrently
REINDEX INDEX CONCURRENTLY idx_orders_status_created;

For tables with heavy write traffic, make autovacuum more aggressive on that table specifically rather than globally.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

Finding Unused and Duplicate Indexes

Every index slows writes and consumes space. Audit them periodically.

-- Indexes that have never been scanned
SELECT
  schemaname, relname AS table_name, indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Check uptime before acting — an index unused since a restart last week may be essential for a monthly report.

Conclusion

Optimising PostgreSQL is a sequence, not a guess: find the expensive queries with pg_stat_statements sorted by total time, read EXPLAIN (ANALYZE, BUFFERS) for estimate-versus-actual gaps and rows removed by filter, add composite indexes with equality columns first and range columns after, eliminate N+1 patterns with eager loading, replace deep OFFSET with keyset pagination, and set random_page_cost appropriately for SSDs. Then keep statistics current and autovacuum tuned, because a well-indexed database with stale statistics still produces bad plans.