Finding Unused Spatial Indexes

Problem Statement

Spatial indexes are large and expensive to maintain, and a surprising number of them are never used. They accumulate from abandoned features, from experiments nobody cleaned up, and from copies of a table where somebody indexed both the geometry and its centroid. Each one costs write throughput on every insert and space in the buffer cache that the useful indexes wanted. This page finds them safely, as a companion to detecting GiST index bloat — the other half of keeping index storage honest.

Why the Naive Approach Fails

The query everyone runs is a good start and a poor conclusion:

sql
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

It ignores three things that each cause a bad drop.

Three ways a zero is not a zero An index reporting zero scans may simply have had its statistics reset yesterday, may be scanned heavily on a read replica whose counters the primary never sees, or may exist to enforce a unique constraint that no query scans but every insert relies on. idx_scan = 0 has three innocent explanations statistics were reset a crash, a failover or an explicit pg_stat_reset stats_reset read it from pg_stat_database need weeks, not hours used on a replica each instance counts its own scans the reporting workload is invisible from the primary poll every instance it enforces something a unique constraint is checked on every insert without a scan being recorded check indisunique first Rule out all three before a drop. The third one turns a cleanup into a data-integrity incident.

Production-Ready Implementation

The full query, with the context that makes the number interpretable:

sql
WITH reset AS (
    SELECT stats_reset FROM pg_stat_database WHERE datname = current_database()
)
SELECT
    s.schemaname || '.' || s.indexrelname                AS index,
    s.relname                                            AS table,
    am.amname                                            AS method,
    s.idx_scan,
    pg_size_pretty(pg_relation_size(s.indexrelid))       AS size,
    i.indisunique                                        AS enforces_unique,
    (SELECT count(*) FROM pg_constraint c
      WHERE c.conindid = s.indexrelid)                   AS backs_constraint,
    (SELECT stats_reset FROM reset)                      AS stats_since
FROM pg_stat_user_indexes s
JOIN pg_index i  ON i.indexrelid = s.indexrelid
JOIN pg_class c  ON c.oid = s.indexrelid
JOIN pg_am am    ON am.oid = c.relam
WHERE s.idx_scan < 50
  AND NOT i.indisunique
  AND NOT EXISTS (SELECT 1 FROM pg_constraint pc WHERE pc.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

Note the threshold of 50 rather than 0: an index scanned a handful of times over a month is functionally unused, and using a small non-zero threshold catches indexes that got one accidental scan from a manual query.

Collecting from every instance is what makes the result trustworthy:

python
import psycopg

INSTANCES = {
    "primary":   "postgresql://…/app",
    "replica-1": "postgresql://…/app",
    "replica-2": "postgresql://…/app",   # the analytics replica
}


def unused_across_instances(min_scans: int = 50) -> list[tuple[str, dict]]:
    """An index is a candidate only if it is unused on every instance."""
    per_instance: dict[str, dict[str, int]] = {}

    for name, dsn in INSTANCES.items():
        with psycopg.connect(dsn) as conn, conn.cursor() as cur:
            cur.execute("""
                SELECT schemaname || '.' || indexrelname, idx_scan
                FROM pg_stat_user_indexes
            """)
            per_instance[name] = dict(cur.fetchall())

    all_indexes = set().union(*(d.keys() for d in per_instance.values()))
    candidates = []
    for idx in sorted(all_indexes):
        scans = {inst: per_instance[inst].get(idx, 0) for inst in INSTANCES}
        if max(scans.values()) < min_scans:
            candidates.append((idx, scans))
    return candidates

An index used four thousand times a day on the analytics replica and never on the primary appears in per_instance with a large number under one key — and is correctly excluded.

Prove it before dropping it

Retiring an index in four reversible steps Four stages. Observe for at least one full business cycle. Make the index invisible to the planner for a trial period, which is instantly reversible. Wait through a reporting cycle with alerting in place. Only then drop it concurrently. Every step before the last one is reversible in seconds 1 · observe a full month, all instances 2 · hide it indisvalid = false planner ignores it 3 · wait one reporting cycle, latency alerts armed 4 · drop CONCURRENTLY the only one-way step UPDATE pg_index SET indisvalid = false WHERE indexrelid = 'idx_name'::regclass; Editing a system catalogue directly is a deliberate, documented exception — it is the only way to hide an index from the planner without dropping it, and setting the flag back restores it instantly.

Configuration and Tuning Knobs

track_counts must be on for any of this to work; it is by default, and a database with it disabled reports zero for everything, which looks exactly like a database full of unused indexes.

Statistics retention is a function of how often the instance restarts or resets. Recording pg_stat_database.stats_reset alongside every collection makes a short observation window visible rather than silently misleading.

A monitoring job that snapshots idx_scan daily turns this from an investigation into a trend. An index whose scan count has been flat for six weeks is a much stronger candidate than one that merely reads zero today.

What the audit found across one database Twenty-two spatial indexes audited: fourteen actively used, three used only on the analytics replica, two enforcing constraints despite low scan counts, and three genuinely unused, together holding 4.1 gigabytes. Twenty-two spatial indexes, four verdicts actively used 14 — keep used on a replica only 3 — keep, and note why enforcing a constraint 2 — keep, scan count is irrelevant genuinely unused 3 — 4.1 GB and write overhead for nothing The two middle rows are what a naive idx_scan query would have dropped.

Verification Steps

sql
-- what would dropping this index actually reclaim?
SELECT pg_size_pretty(pg_relation_size('idx_parcels_centroid'::regclass));

-- and is anything at all depending on it?
SELECT conname, contype FROM pg_constraint
WHERE conindid = 'idx_parcels_centroid'::regclass;

-- after hiding it, confirm no plan regressed
SELECT queryid, calls, round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
WHERE query ILIKE '%parcels%'
ORDER BY total_exec_time DESC LIMIT 10;

Comparing that last result before and after step two is the actual proof. If no statement’s mean time moved, the index was not contributing, and the drop is safe.

Gotchas Checklist

  • Never drop an index during an incident. The cleanup is a scheduled, reversible activity; doing it under pressure removes the ability to observe the effect.
  • DROP INDEX CONCURRENTLY cannot run inside a transaction. Same constraint as the build, same autocommit requirement.
  • Recreating a dropped GiST index takes as long as the original build. “We can just recreate it” is true and expensive — hours on a large table, which is why the hide-first step exists.
  • Partitioned tables have one index per partition. The unused-index query returns them individually; decide about the whole set, not one partition.
  • An index on a rarely-used but critical path is not unused. A disaster-recovery query run twice a year still needs its index the day it runs.

Retiring an Index Without Losing the Ability to Undo It

Identifying an unused index is the easy half. The half that goes wrong is the drop, because a spatial index that took forty minutes to build is not something you want to recreate under pressure at the exact moment you discover it was load-bearing after all.

The safe sequence has four steps and takes a week of calendar time, almost none of it work.

First, record the definition before touching anything. SELECT indexdef FROM pg_indexes WHERE indexname = '...' returns the exact statement that recreates it; store that in the migration alongside the drop, not in a chat message. A reversible migration that carries its own CREATE INDEX CONCURRENTLY in the down path turns a bad decision into a twenty-minute recovery instead of an incident.

Second, confirm the zero is real across the whole cycle. idx_scan resets when statistics are reset and when the server is rebuilt from a base backup, so check stats_reset on pg_stat_database before trusting a low count. An index that reads zero over three days of statistics collected since Tuesday tells you nothing about the monthly billing job.

Third, check the index is not doing structural work. An index backing a unique constraint, an exclusion constraint, or a foreign key will show few or no scans while still being mandatory. pg_constraint.conindid identifies these, and they should be excluded from the candidate list mechanically rather than remembered.

Fourth, drop it concurrently and watch the plans, not the dashboards. DROP INDEX CONCURRENTLY avoids the exclusive lock, and the queries you expect to be affected should be captured with EXPLAIN before and after. A plan that changes from an index scan to a sequential scan on a table of any size is the signal to restore, and having the create statement already written is what makes restoring a non-event.