PostgreSQL indexes and EXPLAIN ANALYZE: reading 400,000 rows to show 20
There is an index, yet the biggest merchant's dashboard is still slow. How to read an EXPLAIN ANALYZE plan, what's inside a B-tree, and how one composite index fixes the query, with real plans.
Picture a payment system with a merchant dashboard. The landing page shows the latest 20 successful payments. The code is trivial, the ORM sends a single query, and locally the page opens instantly. In production most merchants don't notice anything either. Only the biggest one complains: their dashboard is slow, and at peak hours the database CPU graph climbs.
Same code, same query, and there is an index on merchant_id. So what's wrong? In this post we'll answer that with EXPLAIN ANALYZE: how to read the numbers in a plan, what actually lives inside a B-tree index, when an index doesn't help at all, and finally how to fix the query with the right index.
The problem: one query, two speeds
The model is as ordinary as it gets:
# billing/models.py
from django.db import models
class Merchant(models.Model):
name = models.CharField(max_length=100)
class Payment(models.Model):
class Status(models.TextChoices):
PENDING = "pending", "Pending"
PAID = "paid", "Paid"
REFUNDED = "refunded", "Refunded"
merchant = models.ForeignKey(
Merchant, on_delete=models.PROTECT, related_name="payments"
)
status = models.CharField(max_length=16, choices=Status.choices)
amount = models.DecimalField(max_digits=12, decimal_places=2)
created_at = models.DateTimeField(auto_now_add=True)
The dashboard selector:
# billing/selectors.py
from billing.models import Payment
def latest_paid(merchant_id: int):
return (
Payment.objects
.filter(merchant_id=merchant_id, status=Payment.Status.PAID)
.order_by("-created_at")[:20]
)
Django sends roughly this SQL to PostgreSQL:
SELECT "billing_payment"."id", "billing_payment"."merchant_id",
"billing_payment"."status", "billing_payment"."amount",
"billing_payment"."created_at"
FROM "billing_payment"
WHERE ("billing_payment"."merchant_id" = 42
AND "billing_payment"."status" = 'paid')
ORDER BY "billing_payment"."created_at" DESC
LIMIT 20;
merchant is a ForeignKey, and Django creates an index on ForeignKey columns automatically (db_index=True by default). So there is an index on billing_payment.merchant_id. At first glance everything looks fine.
To get real numbers, let's generate synthetic data: 2 million payments, about 20% of them belonging to one large merchant (id = 42), 80% in the paid state. Real systems have this kind of skew all the time: a couple of big customers and thousands of small ones.
INSERT INTO billing_merchant (name)
SELECT 'merchant ' || g FROM generate_series(1, 5000) AS g;
-- 2M payments: ~20% belong to merchant 42, ~80% are 'paid'
INSERT INTO billing_payment (merchant_id, status, amount, created_at)
SELECT
CASE WHEN random() < 0.2 THEN 42 ELSE 1 + (random() * 4999)::int END,
CASE WHEN r < 0.80 THEN 'paid'
WHEN r < 0.97 THEN 'pending'
ELSE 'refunded' END,
(random() * 1000)::numeric(12, 2),
now() - random() * interval '365 days'
FROM (SELECT random() AS r FROM generate_series(1, 2000000)) AS s;
VACUUM ANALYZE billing_payment;
All plans in this post were captured on this data with PostgreSQL 16. Your milliseconds will differ — what matters is the ratio between them and the shape of the plan.
What's inside a B-tree
Before reading a plan, you need a mental model of the index itself. In PostgreSQL, CREATE INDEX builds a B-tree by default. It's a balanced tree made of 8 KB pages: a root page at the top, internal pages in the middle, leaf pages at the bottom.
[ root: 2500 ]
/ \
[ 900 | 1700 ] [ 3300 | 4100 ] internal pages
/ | \ / | \
[1..899] [900..1699] [1700..] [2500..] [3300..] [4100..] leaf pages
<-> <-> <-> <-> <-> (linked list)
leaf entry: key = 42 -> TID (block 1834, item 7) -> row in the heap
The important parts:
- Keys in leaf pages are sorted, and each entry is a key plus a TID (the physical address of the row in the table: block number and position within it).
- Neighbouring leaves are linked. So for
BETWEEN,>/<andORDER BY, PostgreSQL descends to the right spot once and then walks the leaves sequentially. It can walk them backwards too, which is why aDESCsort can also use the index. - Tree depth grows logarithmically: even with millions of rows, it's only a handful of pages from root to leaf.
- The index has no row visibility (MVCC) information. For every TID it finds, PostgreSQL still has to visit the table itself (the heap) to check whether the row is visible to the current transaction and to read the remaining columns. These heap visits are random reads, and they are what makes index access expensive.
That's why PostgreSQL has several ways to read a table and picks one by cost every time:
| Method | How it works | Good for |
|---|---|---|
| Seq Scan | Reads the whole table page by page | Most of the rows are needed; small tables |
| Index Scan | Takes TIDs from the index and visits the heap for each; output comes in index order | Few rows, especially with ORDER BY ... LIMIT |
| Bitmap Index Scan + Bitmap Heap Scan | Collects all matching TIDs into a bitmap first, then reads heap pages in physical order | A moderate number of rows; combining several indexes with AND/OR |
| Index Only Scan | If every needed column is in the index, skips the heap for pages marked all-visible in the visibility map | Covering indexes, recently vacuumed tables |
The planner estimates cost from statistics (pg_stats, collected by ANALYZE: most common values, histograms, n_distinct) and settings (seq_page_cost = 1, random_page_cost = 4 by default). A random read is assumed to cost four times a sequential one, so skipping the index when many rows are needed is perfectly normal behaviour.
Reading EXPLAIN ANALYZE output
EXPLAIN shows only the plan and the estimates. EXPLAIN ANALYZE actually runs the query and adds real time and real row counts to every node. BUFFERS shows how many pages were touched (since PostgreSQL 18 it's on by default with ANALYZE; on 16–17 add it explicitly). You can call it straight from Django:
from billing.selectors import latest_paid
qs = latest_paid(42)
print(qs.query) # approximate SQL, parameters are not quoted
print(qs.explain(analyze=True, buffers=True))
Because the query really executes, only check UPDATE/DELETE inside a transaction you roll back:
-- EXPLAIN ANALYZE really executes the statement.
-- For UPDATE/DELETE always wrap it in a transaction and roll back:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE billing_payment SET status = 'refunded' WHERE id = 1;
ROLLBACK;
Now the plan for the large merchant (secondary lines trimmed):
Limit (cost=28546.98..28549.32 rows=20 width=35) (actual time=117.443..119.435 rows=20 loops=1)
Buffers: shared hit=1008 read=15993 written=1
-> Gather Merge (cost=28546.98..59875.34 rows=268510 width=35) (actual time=117.441..119.430 rows=20 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=27546.96..27882.60 rows=134255 width=35) (actual time=112.583..112.592 rows=15 loops=3)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 27kB
-> Parallel Bitmap Heap Scan on billing_payment (cost=4789.98..23974.48 rows=134255 width=35) (actual time=13.114..102.134 rows=106657 loops=3)
Recheck Cond: (merchant_id = 42)
Filter: ((status)::text = 'paid'::text)
Rows Removed by Filter: 26632
-> Bitmap Index Scan on billing_payment_merchant_id_6d2f0c1a (cost=0.00..4709.43 rows=402800 width=0) (actual time=12.912..12.912 rows=399869 loops=1)
Index Cond: (merchant_id = 42)
Planning Time: 0.380 ms
Execution Time: 119.498 ms
How to read it:
- Read the tree inside out. The deepest node marked with
->runs first and feeds its parent. Here the order is: Bitmap Index Scan → Bitmap Heap Scan → Sort → Gather Merge → Limit. cost=A..Bis the planner's estimate in arbitrary units, not milliseconds.Ais the cost before the first row can be returned (startup),Bis the cost to return all rows.rowsis the estimated row count,widththe average row size in bytes.actual time=A..Bis real time in milliseconds, but per loop.rowsis per loop too.loops=3means the node ran three times (here: the leader plus two parallel workers), so the real row count is106657 × 3 ≈ 320 thousand.- Compare estimated and actual
rows. Here it's 402800 vs 399869 — very close, the statistics are good. If they're off by tens or hundreds of times, the planner is probably making bad choices; start withANALYZE. Rows Removed by Filteris rows that were read and then thrown away. One of the strongest signals that the index doesn't cover the condition.Bufferscounts 8 KB pages.hitmeans found in shared buffers,readmeans fetched from outside (OS cache or disk). Roughly 17 thousand pages, about 130 MB of data — for 20 rows.
The diagnosis is now clear. The index only serves merchant_id = 42 and returns almost 400 thousand TIDs. The heap scan reads all of those rows, drops about 80 thousand on status, sorts the remaining 320 thousand by created_at and keeps the 20 newest. Neither the status condition nor the ORDER BY benefits from the index.
The same query for a small merchant:
Limit (cost=1151.95..1152.00 rows=20 width=35) (actual time=0.799..0.802 rows=20 loops=1)
-> Sort (cost=1151.95..1152.59 rows=256 width=35) (actual time=0.798..0.799 rows=20 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 27kB
-> Bitmap Heap Scan on billing_payment (cost=6.88..1145.14 rows=256 width=35) (actual time=0.115..0.763 rows=241 loops=1)
Recheck Cond: (merchant_id = 1777)
Filter: ((status)::text = 'paid'::text)
Rows Removed by Filter: 68
-> Bitmap Index Scan on billing_payment_merchant_id_6d2f0c1a (cost=0.00..6.82 rows=319 width=0) (actual time=0.075..0.075 rows=309 loops=1)
Index Cond: (merchant_id = 1777)
Execution Time: 0.824 ms
Same plan shape, but the index returns only 309 rows and the whole thing finishes in under a millisecond. That's why everything is fast locally: there's no "large merchant" there.
What matters is not whether an index exists but how many rows the query reads and how many it throws away.
Rows Removed by Filterand a bigrowsunderSortare the first places to look.
The fix: a composite index shaped like the query
The core rule for a composite index: equality (=) columns first, then the sort or range column. Our query has merchant_id = ?, status = ? and ORDER BY created_at DESC, so the index should be (merchant_id, status, created_at DESC). In that index all paid payments of merchant 42 sit next to each other in the leaves, already sorted by created_at. PostgreSQL descends to that spot, reads the first 20 entries and stops.
# billing/models.py
class Payment(models.Model):
# ... fields as above ...
class Meta:
indexes = [
models.Index(
fields=["merchant", "status", "-created_at"],
name="pay_merch_status_created_idx",
),
]
makemigrations generates a plain AddIndex, which runs a plain CREATE INDEX. A plain CREATE INDEX takes a SHARE lock on the table: reads keep working, but INSERT/UPDATE/DELETE wait until the index is built. For a payments table that's unacceptable. So swap the operation in the generated migration for AddIndexConcurrently:
# billing/migrations/0008_payment_merchant_status_created.py
from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models
class Migration(migrations.Migration):
atomic = False # CREATE INDEX CONCURRENTLY cannot run inside a transaction
dependencies = [
("billing", "0007_previous"),
]
operations = [
AddIndexConcurrently(
"payment",
models.Index(
fields=["merchant", "status", "-created_at"],
name="pay_merch_status_created_idx",
),
),
]
The migration runs this SQL:
CREATE INDEX CONCURRENTLY "pay_merch_status_created_idx"
ON "billing_payment" ("merchant_id", "status", "created_at" DESC);
The result:
Limit (cost=0.43..5.36 rows=20 width=35) (actual time=0.045..0.104 rows=20 loops=1)
Buffers: shared hit=15 read=11
-> Index Scan using pay_merch_status_created_idx on billing_payment (cost=0.43..79470.67 rows=322213 width=35) (actual time=0.043..0.100 rows=20 loops=1)
Index Cond: ((merchant_id = 42) AND ((status)::text = 'paid'::text))
Buffers: shared hit=15 read=11
Planning Time: 0.233 ms
Execution Time: 0.124 ms
What changed:
- The
Sortnode is gone: rows come out of the index already in order. - No
Filter, noRows Removed by Filter: both conditions are inIndex Cond. - The Index Scan's total cost is 79470, but the
Limitcosts 5.36. The planner knowsLIMIT 20stops the scan early and picks the plan with the small startup cost. Buffers: 26 pages instead of 17 thousand.
Note that Buffers is a more reliable metric than execution time here. Time depends on cache state and server load; the number of pages touched tells you directly how much work the query does.
When an index doesn't help
Low selectivity
Let's add a separate index on status. paid is 80% of the table:
-- index on (status) exists; 'paid' is ~80% of the table
Seq Scan on billing_payment (cost=0.00..41667.00 rows=1591800 width=35) (actual time=0.017..300.312 rows=1600569 loops=1)
Filter: ((status)::text = 'paid'::text)
Rows Removed by Filter: 399431
-- same index, 'refunded' is ~3% of the table
Bitmap Heap Scan on billing_payment (cost=663.75..18071.58 rows=59267 width=35) (actual time=7.013..81.979 rows=59797 loops=1)
Recheck Cond: ((status)::text = 'refunded'::text)
-> Bitmap Index Scan on billing_payment_status_idx (cost=0.00..648.93 rows=59267 width=0) (actual time=3.815..3.817 rows=59797 loops=1)
Index Cond: ((status)::text = 'refunded'::text)
The index exists, yet for paid the planner deliberately ignores it: reading the whole table sequentially is cheaper than 1.6 million random hops through TIDs. For refunded (3%) the same index is used. The planner knows the distribution from most_common_vals in pg_stats. Takeaway: if you only ever search for a rare value, a partial index (models.Index(..., condition=Q(status="refunded"))) makes more sense than a full index on such a column.
A function on the column
A B-tree is sorted by the column's own values. If the condition is applied to an expression of the column rather than the column itself, a plain index won't work. In Django this is easy to miss because the ORM adds the function for you:
User.objects.filter(email__iexact="User777@Example.com").first()
-- what Django sends to PostgreSQL:
SELECT "accounts_user"."id", "accounts_user"."email" -- and the other columns
FROM "accounts_user"
WHERE UPPER("accounts_user"."email"::text) = UPPER('User777@Example.com')
ORDER BY "accounts_user"."id" ASC
LIMIT 1;
-- email has a UNIQUE index, but the plan is:
Parallel Seq Scan on accounts_user (cost=0.00..6802.00 rows=1042 width=30) (actual time=62.314..95.115 rows=0 loops=3)
Filter: (upper((email)::text) = 'USER777@EXAMPLE.COM'::text)
Rows Removed by Filter: 166666
email has a UNIQUE index, but it's built on email values, not on UPPER(email). The fix is an expression index:
# accounts/models.py
from django.contrib.auth.models import AbstractUser
from django.db import models
from django.db.models.functions import Upper
class User(AbstractUser):
email = models.EmailField(unique=True)
class Meta:
indexes = [
models.Index(Upper("email"), name="user_email_upper_idx"),
]
Index Scan using user_email_upper_idx on accounts_user (cost=0.42..8.44 rows=1 width=30) (actual time=0.031..0.032 rows=1 loops=1)
Index Cond: (upper((email)::text) = 'USER777@EXAMPLE.COM'::text)
Execution Time: 0.039 ms
The same applies to the __date, __year and __month lookups: Django turns them into AT TIME ZONE ... and ::date. In those cases, rewrite the condition as a range on the column itself:
from datetime import date, datetime, timedelta
from zoneinfo import ZoneInfo
# Wrong: becomes ("created_at" AT TIME ZONE 'Asia/Tashkent')::date = '2026-05-01'
Payment.objects.filter(merchant_id=42, created_at__date=date(2026, 5, 1))
# Right: a plain range on the column itself, the index can be used
start = datetime(2026, 5, 1, tzinfo=ZoneInfo("Asia/Tashkent"))
Payment.objects.filter(
merchant_id=42,
created_at__gte=start,
created_at__lt=start + timedelta(days=1),
)
The leading column is missing
An index on (merchant_id, status, created_at) won't help WHERE status = 'paid' much, because the leaves are sorted by merchant_id first. PostgreSQL 18 added skip scan for B-tree, which sometimes helps when the leading column has few distinct values, but don't design indexes around it. The rule also works the other way: since the new index starts with merchant_id, it can serve queries on merchant_id alone as well.
A leading wildcard
A B-tree can't help LIKE '%foo%' (Django's __contains/__icontains). For __startswith, if the database collation isn't C, you need an index with varchar_pattern_ops. Django creates one automatically for a CharField with db_index=True or unique=True (its name ends in _like). Substring search needs pg_trgm with a GIN index, which deserves its own post.
Production pitfalls
EXPLAIN ANALYZEruns the query. Running a heavy query that way in production means running it one more time. When you can, do it on a replica or a copy with production-sized data.- Dev data lies about the plan. On a 1,000-row table the planner rightly picks a Seq Scan and you conclude the index isn't needed. The plan depends on data volume and distribution.
- Stale statistics. After a large import or migration, autovacuum may not have run
ANALYZEyet. If estimated and actualrowsdiverge sharply, runANALYZE table_name;manually. CONCURRENTLYcan fail, for example when building a unique index hits a duplicate. That leaves anINVALIDindex behind: it's still maintained on writes but never used for reads. Drop it withDROP INDEX CONCURRENTLYand create it again.- Every index slows down writes and takes disk space. Find unused indexes via
pg_stat_user_indexes, but remember the stats are per server and count from the last reset: an index used by queries on a replica can showidx_scan = 0on the primary. - The first run hits a cold cache. Run it a few times and look at
Buffers, not just the time.
To decide which query to look at first, pg_stat_statements is the most convenient tool:
-- requires shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round(total_exec_time::numeric) AS total_ms,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- indexes that are never used (on this server, since the last stats reset)
SELECT relname, indexrelname, idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC
LIMIT 20;
Sorting by total_exec_time matters: a 50 ms query called thousands of times on every page load costs the database more than a report that runs for 2 seconds once a day.
Checklist
- Find slow queries with
pg_stat_statementsorlog_min_duration_statement; don't guess. - Run
qs.explain(analyze=True, buffers=True)on data close to production. - Read the plan inside out; compute
actual rows × loops. - Look for
Rows Removed by Filter, a largeSort, and gaps between estimated and actualrows. - Composite index:
=columns first, then theORDER BYor range column. - Function on the column (
iexact,__date) — an expression index or a rewritten query. - Add indexes with
AddIndexConcurrentlyandatomic = False. - After the change, re-check the plan and compare
Buffers.
Takeaway
The reason a query is slow is almost always written in its plan: EXPLAIN ANALYZE shows exactly where many rows are read and where many are thrown away. Once you think of a B-tree as sorted keys plus TIDs, it becomes easy to predict which index will help which query. Design indexes for a concrete query shape rather than a column, and verify the result with a plan, not a guess.
// comments
Loading comments...