Why Small Tables Get Sequential Scans #

You add an index in a migration, run EXPLAIN on your development database, and see Seq Scan. The index is valid, the predicate matches it exactly, and a colleague suggests the index “is not working”. On the production copy, with a million times more rows, the same query uses the index immediately. Nothing is wrong on either side: the planner is making the correct choice for each table’s size.

The general crossover between access paths is described in sequential vs index scans. This page covers the end of that curve where tables are tiny — and the testing mistakes it causes.

The Cost-Model Condition #

A sequential scan of a table costs roughly pages × seq_page_cost + rows × cpu_tuple_cost plus filter evaluation. An index scan costs an index descent — at least one page per B-tree level, costed at random_page_cost for pages not assumed cached — plus a heap fetch for each matching row, plus per-tuple CPU costs.

For a table that fits in one or a few pages, the sequential scan reads those pages and is done. The index scan must read at least the index root and leaf pages and then the heap page anyway. With random_page_cost at 4.0 and seq_page_cost at 1.0, one extra index page already makes the index scan more expensive than reading the whole table. The planner therefore chooses a sequential scan for small tables even when the predicate is extremely selective, and it is right to: the difference is microseconds either way.

Two further effects make development databases misleading:

The planner can see a table’s current size even without statistics, because it checks the number of pages at plan time. What it cannot know on a tiny table is how the query will behave when the table is large.

The crossover by table size For a query selecting one row, the index scan costs about the same at every table size, roughly 8 cost units. The sequential scan costs 1 unit at one page, 10 units at ten pages, and 12,000 units at one hundred thousand rows. The index wins only once the table spans more than a handful of pages. Seq Scan, 1 page 1.4 Index Scan, any size ≈ 8.4 Seq Scan, 10 pages 12 Seq Scan, 1,000 pages 1,250 cost units; the index scan's cost barely changes as the table grows

Annotated EXPLAIN Evidence #

Development database, 400 rows in 3 pages:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM coupons WHERE code = 'SPRING26';
Seq Scan on coupons  (cost=0.00..8.00 rows=1 width=64) (actual time=0.01..0.09 rows=1 loops=1)
  Filter: (code = 'SPRING26'::text)
  Rows Removed by Filter: 399
  Buffers: shared hit=3
Execution Time: 0.11 ms
-- 3 pages: reading the whole table costs less than an index descent plus a heap fetch

Forcing the index to compare:

BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM coupons WHERE code = 'SPRING26';
ROLLBACK;
Index Scan using coupons_code_idx on coupons  (cost=0.27..8.29 rows=1 width=64)
                                               (actual time=0.02..0.02 rows=1 loops=1)
  Index Cond: (code = 'SPRING26'::text)
  Buffers: shared hit=2
Execution Time: 0.04 ms
-- index usable and valid; estimated 8.29 vs 8.00 — a rounding-error difference
Small table, correct sequential scan Three fields are annotated. Buffers shared hit equal to 3 shows the whole table is three pages. The index scan cost of 8.29 against 8.00 shows the plans are essentially equal. Execution times under a millisecond in both plans show nothing to fix. Seq Scan … Buffers: shared hit=3 the whole table is 3 pages Index Scan cost=0.27..8.29 vs 8.00 near-identical estimates Execution Time: 0.11 ms vs 0.04 ms nothing worth optimising prove an index works with enable_seqscan off, not by expecting it on tiny data

Step-by-Step Resolution #

  1. Check the table’s size before worrying.

    SELECT relpages, reltuples, pg_size_pretty(pg_relation_size(oid))
FROM pg_class WHERE relname = 'coupons';

  2. Prove the index is usable in a rolled-back transaction with enable_seqscan = off. If an index scan appears, the index matches the predicate; the planner simply prefers the sequential scan at this size.

  3. Test plans on realistic data volumes. Generate enough rows with a production-like distribution:

    INSERT INTO coupons (code, campaign_id)
SELECT 'C' || g, (random() * 5000)::int
FROM generate_series(1, 5000000) g;
ANALYZE coupons;

  4. Or copy production statistics into a staging environment. PostgreSQL 18 adds functions to export and import relation and column statistics (pg_restore_relation_stats, pg_restore_attribute_stats), which let a small staging database plan as if it held production data.

  5. Analyze after loading test data. An unanalyzed table plans on defaults; see stale statistics after bulk loads.

  6. Assert plans in tests only on realistic data. Plan-shape assertions against tiny fixtures fail or pass for the wrong reason.

Should you worry about this Seq Scan? Starting from a sequential scan that ignores an index. If the table is only a few pages and the query is fast, no action is needed. If forcing the index with enable_seqscan off still shows no index scan, the index does not match the predicate and must be fixed. If the table is large and the scan is slow, investigate estimates and costs. Seq Scan ignores an index — what now? table ≤ a few pages, fast no action correct plan at this size index never used, even forced fix the index predicate does not match large table, slow check estimates see scan thresholds size first, then eligibility, then cost parameters

Before and After #

-- BEFORE: "the index is not used" on development data (400 rows)
Seq Scan on coupons  Buffers: shared hit=3                    Execution Time: 0.11 ms

-- AFTER: 5,000,000 realistic rows, ANALYZE
Index Scan using coupons_code_idx  Index Cond: (code = 'SPRING26')  Buffers: shared hit=4   Execution Time: 0.05 ms

The same effect in production #

Small tables exist in production too — lookup tables of countries, plans, feature flags — and they will be sequentially scanned in joins and filters forever, correctly. The case worth watching is a table that is small at plan time and large at execution time: a staging table truncated and reloaded within a job, a temporary table populated after the query that reads it was prepared, or a partition created empty and filled during the day. Because the planner reads the table’s page count when it plans, a plan built while the table was tiny can be reused after the table has grown, particularly by prepared statements and PL/pgSQL functions. That pattern appears as a sequential scan of millions of rows chosen by a plan built for dozens; the lifecycle behind it is described in plan invalidation after DDL and ANALYZE. Running ANALYZE after loading such a table both updates statistics and invalidates cached plans that depend on it.

Common Pitfalls #

Dropping an index because development ignores it. Production may depend on it. Diagnostic signal: an index “unused” only in a small environment. Fix: check pg_stat_user_indexes.idx_scan in production, as in finding unused indexes safely.

Tuning random_page_cost to make tiny-table plans use indexes. It shifts every plan on the server. Diagnostic signal: configuration changed to satisfy a test. Fix: test on realistic volumes.

Plan assertions in unit tests. Fixtures with dozens of rows produce sequential scans by design. Diagnostic signal: flaky plan tests after data changes. Fix: assert plans only in performance tests with production-scale data.

Forgetting temp tables are never auto-analyzed. Autovacuum cannot see other sessions’ temporary tables. Diagnostic signal: poor plans involving a large temporary table. Fix: ANALYZE the temporary table after populating it.

Frequently Asked Questions #

Why is PostgreSQL not using my index on a small table? #

Because reading the whole table — often a single page — is cheaper than descending an index and then fetching the heap page. The planner’s choice is correct at that size; the same query will use the index once the table is large enough.

How do I check that an index can be used at all? #

Run the query inside a transaction with SET LOCAL enable_seqscan = off. If an index scan appears, the index matches the predicate and the planner simply preferred the sequential scan for cost reasons.

How can I test production plans on a development database? #

Load data at realistic volume and distribution and analyze it, or copy production statistics into the test database where your PostgreSQL version supports statistics import. Plans from tiny tables do not predict plans on large ones.

Up: Sequential vs Index Scans