← All tutorials
PostgreSQL Performance

PostgreSQL query performance

A query that runs in 3 ms on your laptop can take 8 seconds in production once the table has ten million rows. The difference is almost never "Postgres is slow" β€” it's a plan you never looked at. This is how to read what Postgres is doing, find the slow part, and fix it methodically.

The core skill. Don't guess, and don't optimise by superstition. Run EXPLAIN (ANALYZE, BUFFERS), read the plan, find the node that dominates the time, change one thing, and measure again. Every tip below is downstream of that loop.

Foundations  How the planner works

SQL is declarative: you say what you want, not how to get it. Postgres' cost-based optimiser decides the "how". Understanding its pipeline tells you where a query can go wrong.

The PostgreSQL query lifecycle A query is parsed, rewritten, then the planner uses table statistics and cost estimates to choose the cheapest of many candidate plans, which the executor runs. ParseSQL β†’ tree Rewriteviews, rules Plan / Optimisecost each candidate,pick the cheapest Executerun the plan Rows πŸ“Š table statistics

The planner assigns a cost (an abstract unit, not milliseconds) to each candidate plan and picks the cheapest. Cost is estimated from table statistics β€” how many rows a table has, how selective a filter is, how values are distributed. Wrong statistics β†’ wrong estimate β†’ wrong plan. That single sentence explains most mystery slow queries.

Mental model: your job isn't to write the fast plan by hand β€” it's to give the planner (a) the right indexes to choose from and (b) the right statistics to choose with. Then verify it actually chose well.

The tool  EXPLAIN & EXPLAIN ANALYZE

EXPLAIN shows the plan the planner chose and its estimates. EXPLAIN ANALYZE actually runs the query and adds the real timings and row counts, so you can compare estimate vs reality.

-- Estimate only (does NOT run the query):
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Run it and show real time, rows, and I/O β€” the one you want:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE customer_id = 42;
ANALYZE really executes the query β€” including INSERT/UPDATE/DELETE. To inspect a write safely, wrap it: BEGIN; EXPLAIN ANALYZE UPDATE …; ROLLBACK;

Read a plan inside-out and bottom-up: the most indented nodes run first and feed their parents. Each node shows:

Index Scan using orders_customer_id_idx on orders
  (cost=0.43..8.45 rows=1 width=64)          ← planner ESTIMATE
  (actual time=0.021..0.024 rows=1 loops=1)  ← REALITY (ANALYZE)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=4                        ← pages read from cache
The #1 tell: compare estimated rows vs actual rows. A large gap (say estimate 1, actual 900,000) means the statistics are misleading the planner β€” that's your root cause, not the scan type. Paste plans into explain.dalibo.com to visualise them.

Vocabulary  Reading the plan β€” scans & joins

Scan nodes β€” how a table is read

NodeWhat it meansGood / bad?
Seq ScanRead every row in the tableFine for small tables or when you need most rows; bad when filtering few rows from a big table (missing index)
Index ScanWalk an index, fetch matching rowsGreat when returning few rows
Index Only ScanAnswer entirely from the index (no table fetch)Best β€” needs a covering index
Bitmap Heap ScanGather many index hits, then read the table in page orderGood for medium-selectivity filters

Join nodes β€” how two tables are combined

JoinBest when
Nested LoopOne side is tiny and the other is indexed on the join key
Hash JoinJoining two large unsorted sets on equality (builds a hash table)
Merge JoinBoth inputs are already sorted on the join key
Red flags in a plan: a Seq Scan on a big table with a selective filter Β· a Nested Loop whose inner side runs thousands of loops Β· rows estimate wildly off from actual Β· a Sort or Hash that spills to disk (Sort Method: external merge Disk: …kB) Β· Rows Removed by Filter in the millions.

Lever 1  Indexes

The single biggest lever. An index is a sorted side-structure that lets Postgres find rows without scanning the whole table β€” at the cost of extra storage and slower writes.

πŸ…±οΈ B-tree (the default)

Use for Equality and range on ordered data: =, <, >, BETWEEN, ORDER BY, and prefix LIKE 'abc%'. Covers the vast majority of needs.

🧩 Composite & covering

Use for Filtering on several columns. Column order matters β€” a (a, b) index helps WHERE a=… AND b=… and WHERE a=…, but not WHERE b=… alone. Add INCLUDE columns to enable an Index Only Scan.

πŸ”¬ Partial & expression

Use for Indexing a slice (WHERE status = 'active') to keep the index small, or indexing a computed value (lower(email)) so a function in your filter is still SARGable.

πŸ—‚οΈ GIN / GiST / BRIN

Use for GIN β†’ full-text search, jsonb, arrays. GiST β†’ geometric / nearest-neighbour. BRIN β†’ huge, naturally-ordered tables (time-series) where a tiny index is enough.

-- Composite index for: WHERE customer_id = ? AND status = ? ORDER BY created_at
CREATE INDEX idx_orders_cust_status
  ON orders (customer_id, status, created_at DESC);

-- Partial + covering: only active orders, answered from the index alone
CREATE INDEX idx_orders_active
  ON orders (customer_id) INCLUDE (total)
  WHERE status = 'active';
Indexes aren't free. Every index slows INSERT/UPDATE/DELETE and consumes disk & cache. Drop unused ones (check pg_stat_user_indexes.idx_scan = 0), and don't index columns you never filter or join on.

Lever 2  Statistics β€” why estimates go wrong

The planner is only as good as its statistics. ANALYZE samples each table and stores distribution info in pg_statistic (readable via pg_stats). Autovacuum normally runs it β€” but after a big bulk load or data shift, stats can be stale.

-- Refresh statistics for one table (cheap, safe to run anytime):
ANALYZE orders;

-- Correlated columns fool the planner; teach it they're related:
CREATE STATISTICS orders_city_state (dependencies)
  ON city, state FROM orders;
ANALYZE orders;
When estimate ≫ or β‰ͺ actual: first run ANALYZE. If a column is skewed, raise its sampling with ALTER TABLE … ALTER COLUMN … SET STATISTICS 1000;. For columns that vary together (city/state, brand/model), add extended statistics so the planner stops multiplying independent probabilities.

Fixes  Common slow-query anti-patterns

Most slow queries are a handful of recurring mistakes. Each below has a tell in the plan and a concrete fix.

1 Β· Non-SARGable filters (function on the column)

Wrapping the indexed column in a function forces a Seq Scan β€” the index can't be used.

-- βœ— index on created_at can't be used
WHERE date(created_at) = '2026-07-30';
WHERE lower(email) = 'a@b.com';   -- unless you indexed lower(email)

-- βœ“ keep the column bare so the index applies
WHERE created_at >= '2026-07-30' AND created_at < '2026-07-31';

2 Β· Leading wildcard & OR

LIKE '%term' can't use a B-tree (use full-text/trigram GIN instead). A chain of ORs across columns often can't be indexed β€” rewrite as UNION or use the right multi-column strategy.

3 Β· SELECT * and fat rows

Fetching columns you don't need blocks Index Only Scans and bloats I/O. Select only what you use.

4 Β· N+1 queries

Running one query per row in application code. The plan for each looks fine β€” the problem is you issued 1,000 of them. Fix with a single JOIN or WHERE id = ANY($1). See concurrency design for the app-side view.

5 Β· OFFSET pagination on deep pages

-- βœ— OFFSET 100000 still scans & discards 100k rows
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

-- βœ“ keyset ("seek") pagination β€” jumps straight there via the index
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
More quick wins

β€’ Implicit type casts (where bigint_col = '42' vs a text param) can disable an index β€” match types.
β€’ Missing join index β€” index foreign-key columns; they're not indexed automatically.
β€’ Sorting without an index that spills to disk β€” add an index matching the ORDER BY.
β€’ Over-wide IN lists β€” pass an array and let Postgres hash it.
β€’ Count(*) on huge tables β€” use an estimate from pg_class.reltuples when approximate is fine.

Observe  Finding & monitoring slow queries

You can't tune what you can't see. These built-ins find the queries worth your time β€” start from the aggregate view, not a hunch.

πŸ“ˆ pg_stat_statements

Start here The extension that aggregates every query by normalised shape β€” total time, calls, mean, rows, cache hit ratio. Sort by total time to find what actually hurts (often a "fast" query run a million times).

🐒 auto_explain & slow log

Catch offenders log_min_duration_statement = 500ms logs slow statements; auto_explain logs their plans automatically β€” so you see the plan from the moment it was slow in production.

πŸ” pg_stat_activity

Right now Live view of running queries β€” spot long-runners, lock waits (wait_event), and idle-in-transaction sessions holding things up.

-- The 10 queries costing the most total time across the whole DB:
SELECT substring(query, 1, 60) AS query,
       calls,
       round(total_exec_time) AS total_ms,
       round(mean_exec_time, 2) AS mean_ms,
       round(100.0 * shared_blks_hit /
             nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Practice  The tuning loop

Optimisation is a disciplined loop, not a bag of tricks. Change one thing at a time and re-measure:

  1. Find the costly query β€” pg_stat_statements by total time, not gut feel.
  2. Reproduce with realistic data volume (a plan on 1k rows lies about 10M rows).
  3. Explain β€” EXPLAIN (ANALYZE, BUFFERS); find the node dominating time.
  4. Diagnose β€” estimate vs actual gap? wrong scan? disk spill? missing index?
  5. Fix one thing β€” add/adjust an index, ANALYZE, rewrite the predicate, or restructure the query.
  6. Re-measure β€” confirm the plan changed and got faster; watch write cost.
  7. Repeat until it meets the target, then move to the next query.
Golden rule: one change per measurement. If you add an index and rewrite the query at once, a speedup tells you nothing about which one helped β€” or whether one masked a regression in the other.

End to end  Worked example: 4.2 s β†’ 0.3 ms

Let's walk the whole loop on one real query. An orders table has 10 million rows. The app shows a customer their most recent shipped orders:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM   orders
WHERE  customer_id = 42
  AND  status = 'shipped'
ORDER BY created_at DESC
LIMIT 20;

Step 1 Β· Read the plan (before)

Limit  (cost=461889..461889 rows=20)
       (actual time=4218.6..4218.9 rows=20 loops=1)
  -> Sort  (cost=461889..462017 rows=51200 width=20)
          (actual time=4218.7..4218.8 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: external merge  Disk: 41960kB   ← spilled to disk
        -> Seq Scan on orders
             (cost=0..449021 rows=51200 width=20)
             (actual time=0.03..4102.5 rows=48730 loops=1)
             Filter: ((customer_id = 42) AND (status = 'shipped'))
             Rows Removed by Filter: 9951270          ← scanned ~10M, kept 48k
             Buffers: shared read=83334
Planning Time: 0.20 ms
Execution Time: 4219.4 ms

Step 2 Β· Diagnose

Three red flags, read bottom-up:

Symptom in the planWhat it tells you
Seq Scan + Rows Removed by Filter: 9,951,270No usable index β€” Postgres reads all 10M rows to keep 48k. This is 97% of the time.
Sort Method: external merge Β· Disk: 41 MBThe ORDER BY can't be answered by an index, so it sorts 48k rows on disk.
Buffers: shared read=83334~650 MB read from disk (not cache) β€” pure I/O waste.

Root cause: no index supports this filter or its ordering. (Estimate 51,200 vs actual 48,730 is close, so statistics are fine here β€” the problem is a missing index, not bad stats.)

Step 3 Β· Fix one thing

Create a composite index whose columns match the equality filters first, then the sort column in the sort direction. That lets one index satisfy the filter and return rows already ordered.

CREATE INDEX idx_orders_cust_status_created
  ON orders (customer_id, status, created_at DESC);

ANALYZE orders;   -- refresh stats so the planner sees the new index

Why this exact order? customer_id and status are equality predicates, so they go first (any order between them). created_at DESC goes last so the index is already in the ORDER BY order β€” Postgres reads the first 20 entries and stops, no sort needed.

Step 4 Β· Re-measure (after)

Limit  (cost=0.56..8.63 rows=20)
       (actual time=0.031..0.28 rows=20 loops=1)
  -> Index Scan using idx_orders_cust_status_created on orders
       (cost=0.56..20640 rows=51200 width=20)
       (actual time=0.029..0.26 rows=20 loops=1)
       Index Cond: ((customer_id = 42) AND (status = 'shipped'))
       Buffers: shared hit=5                    ← 5 pages, all cached
Planning Time: 0.14 ms
Execution Time: 0.31 ms   ← ~13,000Γ— faster
What changed: Seq Scan β†’ Index Scan (no more reading 10M rows), the on-disk Sort disappeared (the index is pre-sorted), and I/O dropped from 83,334 disk reads to 5 cached pages. LIMIT 20 now stops after 20 index entries. Result: 4,219 ms β†’ 0.31 ms.
Then check the cost: re-run the write path β€” this index adds a little work to every INSERT/UPDATE on orders. Here it's an easy trade; confirm it's worth it and that no near-duplicate index already exists.

Cheat sheet

QuestionShort answer
First move on a slow query?EXPLAIN (ANALYZE, BUFFERS) β€” read it bottom-up.
#1 diagnostic signal?Estimated rows vs actual rows β€” a big gap = bad statistics.
Seq Scan on a big table?Likely a missing/unusable index β€” or a non-SARGable filter.
Function on an indexed column?Kills the index β€” rewrite or add an expression index.
Composite index column order?Left-to-right; leftmost columns must appear in the filter.
Deep pagination?Keyset (WHERE id > ?), not big OFFSET.
Which queries to tune?pg_stat_statements ordered by total time.
Estimates look wrong?Run ANALYZE; raise statistics target; add extended stats.
Golden rule?Change one thing, then re-measure.

Knowledge check

Six questions. Pick an answer for each, then check your score.

1. What's the difference between EXPLAIN and EXPLAIN ANALYZE?

2. In a plan, the single most useful signal that statistics are misleading the planner is…

3. Why does WHERE date(created_at) = '2026-07-30' often force a Seq Scan?

4. You have an index on (customer_id, status). Which query can use it?

5. Deep pagination with a large OFFSET is slow because…

6. Which tool best tells you which queries are worth optimising?

Next steps

🧡 Concurrency design

The app side β€” connection pools, N+1, idempotency & capacity.

πŸ—„οΈ Vector databases

pgvector & friends β€” when your queries are similarity search.

☸️ Cloud native

Where the DB runs β€” scaling, replicas & observability.

πŸ““ All tutorials

The hub β€” LLMs, agents, data & infra.