How to Optimize PostgreSQL Query Performance

PostgreSQL is fast by default and forgiving for a long time — which is why performance problems tend to arrive all at once, when a table that was fine at 50,000 rows reaches 5 million. The good news is that the diagnosis is methodical: find the queries that actually cost the most, read their plans, and fix the specific thing the plan shows. Adding indexes at random is how databases end up slow and bloated.

Step 1: find the queries worth fixing

The slowest single query is often not the problem. A 5 ms query that runs 40,000 times a minute costs far more than a 2-second report that runs once an hour. The pg_stat_statements extension tracks every normalised query with its call count and total time — enable it first.

-- postgresql.conf (most managed providers enable this for you)
shared_preload_libraries = 'pg_stat_statements'

-- then, once, in the database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- the queries costing the most in total
SELECT
  round(total_exec_time::numeric, 0)  AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2)   AS mean_ms,
  rows,
  left(query, 120)                    AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Work down that list from the top. It's also worth setting log_min_duration_statement (for example to 500 ms) so individual slow executions are logged with their parameters, which you need to reproduce them.

Step 2: read the plan with EXPLAIN ANALYZE

EXPLAIN shows the plan PostgreSQL intends to use. EXPLAIN ANALYZE actually runs the query and reports what happened — so be careful with it on UPDATE or DELETE (wrap those in a transaction and roll back). Add BUFFERS to see how much data was read.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821
ORDER BY created_at DESC
LIMIT 20;

Limit  (cost=18234.51..18234.56 rows=20 width=24) (actual time=212.4..212.4 rows=20 loops=1)
  ->  Sort  (cost=18234.51..18240.12 rows=2244 width=24) (actual time=212.4..212.4 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Seq Scan on orders  (cost=0.00..18174.80 rows=2244 width=24) (actual time=0.03..211.8 rows=2310 loops=1)
              Filter: (customer_id = 4821)
              Rows Removed by Filter: 998690
              Buffers: shared hit=5412 read=2150
Planning Time: 0.1 ms
Execution Time: 212.5 ms

Read a plan from the most indented line outwards, and look for four things:

• Seq Scan on a large table with a selective filter.Here, a million rows read to return 2,310 — “Rows Removed by Filter: 998690” is the giveaway. That's a missing index.
• Estimated rows far from actual rows. If the planner expected 10 rows and got 100,000, it chose its plan on bad information. Run ANALYZE on the table, and for skewed columns consider raising the statistics target.
• The node where the time jumps. actual timeis cumulative; find the node whose time is much larger than its children's — that's where the work happens.
• Sorts and hashes spilling to disk. “Sort Method: external merge Disk” means work_mem was too small for that operation, or the query sorts far more rows than it needs.

Step 3: choose the right index

For the query above, one composite index removes both the scan and the sort:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

PostgreSQL can now jump straight to that customer's rows, already in order, and stop after 20. The plan becomes an Index Scan under the Limit and the time drops from hundreds of milliseconds to well under one. Always use CONCURRENTLY on a live table; a plain CREATE INDEX blocks writes to the table until it finishes.

Column order in composite indexes

A B-tree index on (a, b) helps queries filtering on a, or on a and b — but not efficiently on b alone. Put columns compared with equality first, then the column used for ranges or sorting. (customer_id, created_at)serves “this customer's recent orders”; (created_at, customer_id)mostly doesn't.

Other index types worth knowing

• Partial indexes index only the rows you query: WHERE status = 'pending' on a table where almost everything is completed gives a tiny, fast index.
• Covering indexes with INCLUDE (total) let a query be answered from the index alone — an Index Only Scan — without visiting the table.
• Expression indexes for queries that filter on a function: ON users (lower(email)) for case-insensitive lookups.
• GIN indexes for JSONB containment, arrays and full-text search.

Don't over-index

Every index slows down every insert and update to that table and takes space. Check for indexes that are never used and drop them:

SELECT relname AS table, indexrelname AS index, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

(Statistics reset when the server restarts or stats are reset, and unique indexes enforce constraints even when never scanned — check before dropping.)

Step 4: fix the query patterns that indexes can't

N+1 queries from the ORM

An ORM loop that loads 50 orders and then lazily fetches each order's customer runs 51 queries. Each is fast; together they're slow, and they barely show up as “slow queries” — they show up as a huge calls count in pg_stat_statements. Fix it with a join or the ORM's eager loading (include in Prisma, within Drizzle's relational queries).

OFFSET pagination on deep pages

LIMIT 20 OFFSET 100000 still reads and discards 100,000 rows. Use keyset (cursor) pagination instead — remember the last row you showed and continue from it:

-- page after the row (created_at = '2026-09-01 10:22', id = 88123)
SELECT id, total, created_at
FROM orders
WHERE (created_at, id) < ('2026-09-01 10:22', 88123)
ORDER BY created_at DESC, id DESC
LIMIT 20;

With an index on (created_at DESC, id DESC), every page is as fast as the first.

Selecting more than you need

SELECT * on a table with large text or JSONB columns drags all of that across the network and prevents index-only scans. Select the columns the code uses.

Exact counts on big tables

SELECT count(*)on a large table has to scan it. If a UI only needs “about 1.2 million results”, the planner's estimate from pg_class.reltuples is instant; if it needs to know whether there are more pages, fetch LIMIT n + 1 rows and check.

Step 5: keep the database healthy

• Autovacuum.PostgreSQL's MVCC leaves dead row versions behind after updates and deletes; autovacuum cleans them up and keeps planner statistics fresh. On heavily updated tables, the defaults are often too lazy — tune per table rather than turning it off. Check pg_stat_user_tables for n_dead_tup and last-vacuum times.
• Connection pooling. Each PostgreSQL connection is a process with real memory overhead. Serverless functions that open a connection per invocation can exhaust max_connectionsunder load. Put a pooler such as PgBouncer (or your provider's built-in pooling) in front.
• Memory settings. shared_buffers around a quarter of RAM is the usual starting point on a dedicated server, and work_mem is per sort or hash operation, per query — raise it carefully.

A checklist

1. Enable pg_stat_statements; rank queries by total time, not mean time.
2. Run EXPLAIN (ANALYZE, BUFFERS) with realistic parameters on production-sized data.
3. Look for big sequential scans, bad row estimates, disk sorts, and the node where time jumps.
4. Add targeted composite, partial or covering indexes — CONCURRENTLY.
5. Fix N+1 queries, deep OFFSET pagination, SELECT * and exact counts.
6. Drop unused indexes; check autovacuum and connection pooling.
7. Re-run the plan and pg_stat_statements to confirm the improvement.

Most early-stage products never need more than this — a sound schema and a handful of well-chosen indexes go a very long way. If your database is becoming the bottleneck as you grow, it's one of the first things covered in an architecture audit.