Query performanceintermediateFree in full

Slow query from a sequential scan on a large table

A lookup that used to be instant now reads the whole table. Prove it is a sequential scan, then bring it back with the right index, before and after on a 40,000-row lab table.

Problem

What you're actually looking at

The symptom as it shows up on a real server.

A query filtering a large table by one column has quietly gotten slower as the table grew. Nothing in the SQL changed; the plan did. It is the most common performance ticket a Postgres DBA ever sees.

Meridian Freight's consignments table has 40,000 rows in this lab. A dispatcher looks up one courier's parcels by courier_id. With no index on that column the planner has to read every row, so a query returning ~100 rows still touches all 40,000.

Simple terms

Picture a phone book with no alphabetical order. To find one courier's parcels, PostgreSQL has to read the whole book cover to cover, all 40,000 rows, even though only about 100 match. That full read is called a "sequential scan", and it keeps getting slower as the table grows. An index is the alphabetical tab down the side: it lets the database jump straight to the matching rows instead of checking every one.

Before you start

  • • Ability to run EXPLAIN (ANALYZE, BUFFERS) on the slow query.
  • • No maintenance window needed, CREATE INDEX CONCURRENTLY does not block writes.

How to identify it

  • ›EXPLAIN shows a Seq Scan on a big table with a large "Rows Removed by Filter" count.
  • ›In EXPLAIN ANALYZE the estimated and actual row counts are close, so this is a missing-index problem, not a bad-estimate problem.
  • ›pg_stat_user_tables shows seq_scan climbing while idx_scan stays at zero for the table.
  • ›pg_stat_statements ranks the query high by total_exec_time (see the observability recipes).

Pitfalls to avoid

  • ✕Do not set enable_seqscan = off in production, a seq scan is the right plan when a query returns a large fraction of the table.
  • ✕Do not add an index without checking the table's write ratio; every index is maintained on each INSERT, UPDATE and DELETE.
  • ✕Do not index around a type mismatch (WHERE courier_id = '17' on an integer column), fix the predicate instead.
  • ✕Do not trust estimated rows alone, compare estimate against actual in EXPLAIN ANALYZE before deciding the plan is wrong.

Trace it

Run these against the affected server. To build a copy of this scenario instead, see “Reproduce it in a lab” below.

  1. 01

    Read the plan, not the clock

    A Seq Scan that discards ~39,900 rows to return ~100 is the signature. courier_id = 17 is in-domain for g%400 (0..399), so the filter matches 100 rows and still visits the whole heap.

    SQL
    SET max_parallel_workers_per_gather = 0;
    EXPLAIN (ANALYZE, BUFFERS)
    SELECT * FROM consignments WHERE courier_id = 17;
    Lab-captured output
                                                        QUERY PLAN                                                     
    -------------------------------------------------------------------------------------------------------------------
     Seq Scan on consignments  (cost=0.00..881.00 rows=100 width=44) (actual time=0.018..4.210 rows=100.00 loops=1)
       Filter: (courier_id = 17)
       Rows Removed by Filter: 39900
       Buffers: shared hit=381
     Planning:
       Buffers: shared hit=42
     Planning Time: 0.120 ms
     Execution Time: 4.250 ms
    (8 rows)

Resolution approach

  1. 1.Build the index the filter needs, using CREATE INDEX CONCURRENTLY in production so writes keep flowing.
  2. 2.If an index already exists but is ignored, look for a type mismatch in the predicate and refresh statistics with ANALYZE.
  3. 3.After fast growth, run ANALYZE first and re-check the plan before adding anything.
  4. 4.Always confirm the win with EXPLAIN ANALYZE afterwards, never assume the index helped.

Stop it recurring

  1. 01

    Add the B-tree index and re-measure

    Create the courier_id index, refresh stats, then re-run the same probe. The plan becomes a Bitmap Heap Scan driven by a Bitmap Index Scan, a handful of buffers instead of the full heap.

    SQL
    CREATE INDEX idx_consignments_courier ON consignments (courier_id);
    ANALYZE consignments;
    SET max_parallel_workers_per_gather = 0;
    EXPLAIN (ANALYZE, BUFFERS)
    SELECT * FROM consignments WHERE courier_id = 17;
    Lab-captured output
                                                                   QUERY PLAN                                                               
    ----------------------------------------------------------------------------------------------------------------------------------------
     Bitmap Heap Scan on consignments  (cost=5.06..140.80 rows=100 width=44) (actual time=0.045..0.180 rows=100.00 loops=1)
       Recheck Cond: (courier_id = 17)
       Heap Blocks: exact=100
       Buffers: shared hit=103
       ->  Bitmap Index Scan on idx_consignments_courier  (cost=0.00..5.04 rows=100 width=0) (actual time=0.030..0.030 rows=100.00 loops=1)
             Index Cond: (courier_id = 17)
             Index Searches: 1
             Buffers: shared hit=3
     Planning:
       Buffers: shared hit=48
     Planning Time: 0.180 ms
     Execution Time: 0.220 ms
    (12 rows)

Reproduce it in a lab

Builds the scenario above on a throwaway database so you can practise the fix. Skip this if you are working a live incident.

  1. 01

    Lab setup (run this first)

    Creates and seeds a 40,000-row `consignments` table with courier_id in 0..399. Index on id only, no courier_id index, so the baseline probe is a real Seq Scan.

    SQL
    DROP TABLE IF EXISTS consignments CASCADE;
    CREATE TABLE consignments AS
    SELECT g AS id,
           (g % 400) AS courier_id,
           1.0::numeric AS rate,
           g AS zone_id,
           (g % 100) AS k,
           (g % 100) AS bucket
    FROM generate_series(1, 40000) g;
    CREATE INDEX ON consignments (id);
    -- No index on courier_id yet, the baseline probe must be a Seq Scan.
    ANALYZE consignments;

Verify you're done

EXPLAIN SELECT * FROM consignments WHERE courier_id = 17;  -- after the index + ANALYZE: Bitmap/Index Scan, not Seq Scan
Related errors57014

Related errors

SQLSTATEs this runbook resolves

The error pages that send an on-call engineer here.

More in this category

Other Query performance runbooks

Neighbouring incidents that share the same diagnostic surface.

All Query performance runbooks →

Connected

How this connects to the rest of the library

A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.

Open in the interactive map →
ShareLinkedInX

Need the full procedure?

Pro runbooks finish the incident path

Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.

Unlock runbooks with ProSign in
FollowSubstackLinkedInnew errors · lab notes · hiring loops