PostGIS Spatial Indexing: Why Your Queries Are Doing Sequential Scans

2026-03-12

PostGIS Spatial Indexing: Why Your Queries Are Doing Sequential Scans

The Problem

PostGIS stores geometry data as binary blobs that can represent anything from a single point to a multi-polygon with thousands of vertices. When a query asks "find all restaurants within 1 km of this location," PostgreSQL needs to compare the query geometry against every row in the table. Without a spatial index, that means deserializing and testing every geometry in the table -- a sequential scan that gets worse linearly with table size.

A location-based application with 5 million points of interest and no spatial index on the geometry column will force PostgreSQL to compute geometric relationships against all 5 million rows for every single proximity query. A query that should take 2ms with an index takes 3 seconds without one. At 100 concurrent users, the database is doing 500 million geometry comparisons per second and falling further behind.

The core issue is how GiST (Generalized Search Tree) indexes work with spatial data. A GiST index on a geometry column builds a hierarchy of bounding boxes -- each geometry is enclosed in its minimal bounding rectangle, and these rectangles are grouped into larger rectangles up through the tree. When a query asks for geometries within a region, the index eliminates entire subtrees by checking whether their bounding boxes could possibly overlap the query region. Instead of testing 5 million geometries, the index narrows it to a few hundred candidates.

But here is where developers get caught. Not all spatial functions can use this bounding-box filtering. ST_DWithin(geom, point, 1000) is index-aware -- it translates internally to a bounding-box overlap check, then refines with exact distance. ST_Distance(geom, point) < 1000 is not -- it computes the exact distance for every row first, then filters. Same logical question, same result set, but one uses the index and the other forces a sequential scan. This single distinction is responsible for most spatial query performance problems.

How to Detect It

Start by identifying geometry columns that lack spatial indexes entirely:

-- Find geometry columns without GiST spatial indexes
SELECT
    gc.f_table_schema,
    gc.f_table_name,
    gc.f_geometry_column,
    gc.type,
    gc.srid
FROM geometry_columns gc
WHERE NOT EXISTS (
    SELECT 1
    FROM pg_indexes pi
    WHERE pi.tablename = gc.f_table_name
      AND pi.indexdef LIKE '%gist%'
      AND pi.indexdef LIKE '%' || gc.f_geometry_column || '%'
);

Any row returned here is a geometry column with no spatial index -- every spatial query against it will do a full table scan.

Next, check whether existing spatial indexes are actually being used:

-- Check spatial index scan counts and sizes
SELECT
    indexrelname,
    idx_scan,
    idx_tup_read,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE indexrelname LIKE '%gist%'
   OR indexrelname LIKE '%spatial%'
   OR indexrelname LIKE '%geom%'
ORDER BY idx_scan DESC;

An index with idx_scan = 0 is never being used. Either the queries are written in a way that bypasses the index (the ST_Distance problem), or the index was created on the wrong column. Both warrant investigation.

You can confirm the sequential scan on a specific query with EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM points_of_interest
WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326), 0.01);

If you see Seq Scan instead of Index Scan using idx_pois_geom, the index is either missing or the query is not written to use it.

How myDBA.dev Shows It

myDBA.dev's PostGIS monitoring page provides a complete inventory of geometry columns, spatial indexes, and their health status. The spatial index panel shows which geometry columns have GiST indexes, which are missing them, and the scan statistics for each existing index. Columns flagged as missing an index are highlighted immediately -- no need to run detection queries manually.

PostGIS monitoring page showing spatial index health

The Queries page complements this by showing actual spatial query performance. Queries using PostGIS functions are identified and sorted by execution time, making it easy to spot the ST_Distance pattern -- a spatial query with high total time and a sequential scan plan is the classic symptom. Clicking into the query reveals the EXPLAIN plan, confirming whether the planner is using a GiST index scan or falling back to a sequential scan.

Queries page showing spatial query execution plans

How to Fix It

Create GiST indexes on every geometry and geography column that participates in spatial queries:

-- GiST index on a geometry column
CREATE INDEX CONCURRENTLY idx_pois_geom
    ON points_of_interest USING gist (geom);

-- GiST index on a geography column (same syntax)
CREATE INDEX CONCURRENTLY idx_pois_geog
    ON points_of_interest USING gist (geog);

CONCURRENTLY is essential in production -- a normal CREATE INDEX on a large spatial table can take minutes and blocks all writes during the build.

The more impactful fix is rewriting queries to use index-aware functions. The most common offender:

-- BAD: ST_Distance computes exact distance for every row (sequential scan)
SELECT * FROM points_of_interest
WHERE ST_Distance(geom, ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)) < 0.01;

-- GOOD: ST_DWithin uses bounding-box filtering first (GiST index scan)
SELECT * FROM points_of_interest
WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326), 0.01);

Both return the same rows, but ST_DWithin uses the GiST index to eliminate candidates before computing exact distances. On a 5-million-row table, this is the difference between scanning 5 million geometries and scanning a few hundred.

The same principle applies to other spatial predicates. ST_Intersects, ST_Contains, and ST_Within are all index-aware -- they use the bounding-box operator (&&) internally. ST_Distance used in a WHERE clause is not. If you need distance-based filtering, always use ST_DWithin. If you need the actual distance value for sorting or display, compute it after filtering:

-- Filter with ST_DWithin (uses index), then compute distance for sorting
SELECT *, ST_Distance(geom, query_point) AS distance
FROM points_of_interest
WHERE ST_DWithin(geom, query_point, 0.01)
ORDER BY distance;

For tables with mixed spatial and non-spatial predicates, a compound GiST index with btree_gist can help:

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX CONCURRENTLY idx_pois_category_geom
    ON points_of_interest USING gist (category, geom);

This allows the planner to use a single index scan for queries that filter on both category and spatial proximity.

How to Prevent It

Build an automated check into your deployment pipeline that flags geometry columns without spatial indexes:

-- Alert on unindexed geometry columns
SELECT count(*) AS unindexed_geometry_columns
FROM geometry_columns gc
WHERE NOT EXISTS (
    SELECT 1
    FROM pg_indexes pi
    WHERE pi.tablename = gc.f_table_name
      AND pi.indexdef LIKE '%gist%'
      AND pi.indexdef LIKE '%' || gc.f_geometry_column || '%'
);

Monitor spatial query patterns for sequential scans using pg_stat_statements:

-- Find slow spatial queries that may be missing index usage
SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
WHERE query ~* 'st_distance|st_dwithin|st_contains|st_intersects'
ORDER BY mean_exec_time DESC
LIMIT 10;

Any query with st_distance in the WHERE clause and a high mean_exec_time is almost certainly doing a sequential scan. The fix is always the same: replace ST_Distance < threshold with ST_DWithin. Establish this as a code review rule -- ST_Distance in a WHERE clause is a spatial code smell that should be flagged automatically.