Forcing Index Scans When the Planner Chooses Seq Scan

Problem Statement

A radius search over observations that ran in single-digit milliseconds last week suddenly takes seconds, and EXPLAIN reveals the culprit — the planner has abandoned the GiST index for a full sequential scan, a diagnosis that sits at the heart of query plan analysis with EXPLAIN. The index still exists and is valid; the planner has simply calculated that scanning the whole table is cheaper. That calculation is driven by statistics and cost parameters, and when either drifts out of line with reality the planner makes a defensible-but-wrong choice. This page walks through diagnosing why the sequential scan won, applying the proper fixes — fresh statistics, an accurate cost model, a higher statistics target — and using enable_seqscan strictly as a diagnostic to confirm the index really is faster.

Why the Naive Approach Fails

The tempting reaction is to reach for the sledgehammer and disable sequential scans outright:

-- Naive "fix": globally forbid seq scans so the index is always used
SET enable_seqscan = off;

This is not a fix; it is a distortion. enable_seqscan = off does not remove the sequential-scan option — it adds an enormous cost penalty (1e10) to it. Every plan in the session is now skewed, so joins and aggregates that legitimately need a sequential scan get worse, and the moment the setting is reset the original bad plan returns because the underlying cause is untouched. Left in a role or postgresql.conf, it degrades unrelated queries platform-wide. The planner chose the sequential scan for a reason, and until that reason is corrected the choice keeps coming back.

# The symptom: a proximity endpoint that regressed from milliseconds to seconds
import psycopg
from psycopg.rows import dict_row

with psycopg.connect("host=localhost dbname=weather user=app_user", row_factory=dict_row) as conn:
    with conn.cursor() as cur:
        cur.execute(
            """
            EXPLAIN (ANALYZE, BUFFERS)
            SELECT station_id
            FROM observations
            WHERE ST_DWithin(
                    geom::geography,
                    ST_SetSRID(ST_MakePoint(%s, %s), 4326)::geography,
                    %s
                  );
            """,
            (-104.99, 39.74, 3000.0),
        )
        for line in cur.fetchall():
            print(line)   # shows: Seq Scan on observations ... rows=... (mis-estimated)

Production-Ready Implementation

The reliable path is to find why the planner mis-priced the index scan and correct that input. The three dominant causes for spatial queries are stale statistics, a coarse statistics target on a skewed geometry column, and a random_page_cost that assumes spinning disks. Fix them in that order.

-- STEP 1 — Read the estimate vs. reality. A large gap between the planner's
-- "rows=" estimate and the "actual rows" is the signature of stale/coarse stats.
EXPLAIN (ANALYZE, BUFFERS)
SELECT station_id
FROM observations
WHERE ST_DWithin(
        geom::geography,
        ST_SetSRID(ST_MakePoint(-104.99, 39.74), 4326)::geography,
        3000.0
      );

-- STEP 2 — Refresh statistics. After a bulk load the planner runs on old
-- histograms until ANALYZE recomputes them.
ANALYZE observations;

-- STEP 3 — Raise the statistics target on the skewed geometry column so the
-- planner's selectivity estimate for spatial predicates is finer-grained.
ALTER TABLE observations ALTER COLUMN geom SET STATISTICS 500;
ANALYZE observations;

-- STEP 4 — Price random I/O correctly for SSD/NVMe storage. The 4.0 default
-- assumes spinning disks and biases the planner away from GiST index scans.
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_cache_size = '24GB';   -- ~75% of RAM
SELECT pg_reload_conf();

-- STEP 5 — DIAGNOSTIC ONLY. Confirm the index is genuinely faster before
-- trusting the fixes above. SET LOCAL scopes the override to this transaction.
BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT station_id
FROM observations
WHERE ST_DWithin(
        geom::geography,
        ST_SetSRID(ST_MakePoint(-104.99, 39.74), 4326)::geography,
        3000.0
      );
COMMIT;   -- override evaporates with the transaction; never persist it

Step 5 is the disciplined use of enable_seqscan: wrap it in a transaction with SET LOCAL so the override cannot leak past COMMIT, run the index plan once, and compare its actual execution time against the sequential scan’s. If the index plan is faster, the sequential scan was a costing error and steps 2–4 are the durable remedy. If it is slower, the planner was right and forcing the index would have hurt — the query genuinely matches too much of the table and needs a tighter predicate instead.

"""
diagnose_seqscan.py
------------------
Compare the default plan against a diagnostic index-forced plan for one query,
then apply the durable fixes. SET LOCAL keeps the override inside the transaction.
Requires: psycopg[binary]>=3.1
"""
from __future__ import annotations

import psycopg

DSN = "host=localhost dbname=weather user=app_user"

QUERY = """
    SELECT station_id
    FROM observations
    WHERE ST_DWithin(
            geom::geography,
            ST_SetSRID(ST_MakePoint(%(lon)s, %(lat)s), 4326)::geography,
            %(radius)s
          );
"""

def plan_text(cur, force_index: bool) -> str:
    with cur.connection.transaction():
        if force_index:
            cur.execute("SET LOCAL enable_seqscan = off")   # diagnostic scope only
        cur.execute("EXPLAIN (ANALYZE, BUFFERS) " + QUERY,
                    {"lon": -104.99, "lat": 39.74, "radius": 3000.0})
        return "\n".join(r[0] for r in cur.fetchall())

def apply_durable_fixes(cur) -> None:
    cur.execute("ANALYZE observations")
    cur.execute("ALTER TABLE observations ALTER COLUMN geom SET STATISTICS 500")
    cur.execute("ANALYZE observations")

with psycopg.connect(DSN, autocommit=True) as conn:
    with conn.cursor() as cur:
        print("DEFAULT PLAN:\n", plan_text(cur, force_index=False))
        print("INDEX-FORCED PLAN:\n", plan_text(cur, force_index=True))
        apply_durable_fixes(cur)   # then re-check the default plan flips to Index Scan

Configuration and Tuning Knobs

Selectivity is the root of most spatial mis-estimates. PostGIS stores a histogram of geometry bounding boxes, and a coarse target cannot capture dense urban clusters against sparse rural areas — so the planner guesses a radius search will match far more rows than it does, and a sequential scan wins the cost comparison. Confirm the histogram is fresh and inspect the correlation the planner is working from:

SELECT attname, n_distinct, correlation, most_common_vals IS NOT NULL AS has_mcv
FROM pg_stats
WHERE tablename = 'observations' AND attname = 'geom';

SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'observations';

A large n_mod_since_analyze relative to n_live_tup means the table has changed substantially since the last ANALYZE — the single most common trigger for a regressed plan after a bulk ingest.

Verification Steps

Confirm the default plan — with no overrides in effect — now chooses the index:

-- 1. Run WITHOUT any enable_seqscan override. Expect an Index Scan node.
EXPLAIN (ANALYZE, BUFFERS)
SELECT station_id
FROM observations
WHERE ST_DWithin(
        geom::geography,
        ST_SetSRID(ST_MakePoint(-104.99, 39.74), 4326)::geography,
        3000.0
      );

-- 2. Verify the estimate now tracks reality (rows= close to actual rows).
--    A remaining large gap means the statistics target still needs raising.

-- 3. Confirm the index is accumulating scans under real traffic.
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'observations';

A healthy result shows Index Scan using the GiST index in the default plan, an estimated row count within roughly a factor of two of the actual count, and idx_scan climbing as the endpoint serves traffic. If the sequential scan persists despite fresh statistics and a corrected random_page_cost, the query genuinely matches a large fraction of the table — in that case a sequential scan is the right plan, and the fix is a more selective predicate such as a bounding-box pre-filter with the && operator, not forcing the index.

Gotchas Checklist

• enable_seqscan = off is a probe, not a cure. It penalises rather than removes sequential scans and skews every other plan in scope. Confine it to a SET LOCAL inside a transaction and never persist it to a role or config file.

• Stale statistics are the usual culprit after a load. A large COPY or batch insert shifts the geometry histogram; run ANALYZE observations before trusting any plan, and check n_mod_since_analyze when a query regresses.

• The default statistics target is too coarse for skewed geometry. Raise it to 300–500 on the geometry column so the planner’s selectivity estimate for radius and bounding-box predicates reflects real spatial density.

• random_page_cost = 4.0 sabotages index use on SSDs. The default assumes spinning disks. On flash storage it makes the planner over-price GiST random reads and default to a sequential scan; set it near 1.1.

• Sometimes the planner is right. If forcing the index makes the query slower, the predicate matches too much of the table. Tighten the query — add a selective filter or bounding-box pre-check — rather than overriding the planner.

Related Topics

• Query Plan Analysis with EXPLAIN — parent page on reading spatial execution plans end to end
• Advanced GiST Indexing & Optimization — top-level guide to GiST mechanics, cost parameters, and statistics calibration
• Reading EXPLAIN ANALYZE Output for Spatial Joins — interpret the buffer and row metrics referenced above
• Composite Spatial Indexes — a tighter index shape that improves selectivity estimates for multi-predicate queries