π ±οΈ 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.
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.
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.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 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.
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;
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
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.| Node | What it means | Good / bad? |
|---|---|---|
| Seq Scan | Read every row in the table | Fine for small tables or when you need most rows; bad when filtering few rows from a big table (missing index) |
| Index Scan | Walk an index, fetch matching rows | Great when returning few rows |
| Index Only Scan | Answer entirely from the index (no table fetch) | Best β needs a covering index |
| Bitmap Heap Scan | Gather many index hits, then read the table in page order | Good for medium-selectivity filters |
| Join | Best when |
|---|---|
| Nested Loop | One side is tiny and the other is indexed on the join key |
| Hash Join | Joining two large unsorted sets on equality (builds a hash table) |
| Merge Join | Both inputs are already sorted on the join key |
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.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.
Use for Equality and range on ordered data: =, <, >, BETWEEN, ORDER BY, and prefix LIKE 'abc%'. Covers the vast majority of needs.
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.
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.
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';
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.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;
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.Most slow queries are a handful of recurring mistakes. Each below has a tell in the plan and a concrete fix.
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';
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.
Fetching columns you don't need blocks Index Only Scans and bloats I/O. Select only what you use.
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.
-- β 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;
β’ 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.
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.
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).
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.
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;
Optimisation is a disciplined loop, not a bag of tricks. Change one thing at a time and re-measure:
pg_stat_statements by total time, not gut feel.EXPLAIN (ANALYZE, BUFFERS); find the node dominating time.ANALYZE, rewrite the predicate, or restructure the query.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;
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
Three red flags, read bottom-up:
| Symptom in the plan | What it tells you |
|---|---|
Seq Scan + Rows Removed by Filter: 9,951,270 | No usable index β Postgres reads all 10M rows to keep 48k. This is 97% of the time. |
| Sort Method: external merge Β· Disk: 41 MB | The 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.)
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.
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
LIMIT 20 now stops after 20 index entries. Result: 4,219 ms β 0.31 ms.INSERT/UPDATE on orders. Here it's an easy trade; confirm it's worth it and that no near-duplicate index already exists.| Question | Short 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. |
Six questions. Pick an answer for each, then check your score.
The app side β connection pools, N+1, idempotency & capacity.
pgvector & friends β when your queries are similarity search.
Where the DB runs β scaling, replicas & observability.
The hub β LLMs, agents, data & infra.