Spatial Indexes

GiST indexes accelerate PostGIS bounding-box operators and distance prefiltering. Create indexes on geometry or geography columns, write queries that use && or ST_DWithin, and maintain stats after bulk spatial loads.

Recipe

CREATE TABLE parcels (
  id    bigserial PRIMARY KEY,
  geom  extensions.geometry(Polygon, 4326) NOT NULL
);
 
CREATE INDEX parcels_geom_gist ON parcels USING gist (geom);
 
-- Index-friendly: bounding box overlap then precise filter
SELECT id
FROM parcels
WHERE geom && extensions.ST_Expand(
  extensions.ST_SetSRID(extensions.ST_MakePoint(-73.99, 40.75), 4326),
  0.01
)
AND extensions.ST_Contains(
  geom,
  extensions.ST_SetSRID(extensions.ST_MakePoint(-73.99, 40.75), 4326)
);

When to reach for this: Seq Scan on spatial filters, map viewport queries, or join of large polygon tables.

Working Example

CREATE TABLE drivers (
  id   uuid PRIMARY KEY,
  loc  extensions.geography(Point, 4326) NOT NULL,
  updated_at timestamptz DEFAULT now()
);
 
CREATE INDEX drivers_loc_gist ON drivers USING gist (loc);
 
-- ST_DWithin uses index for geography
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, updated_at
FROM drivers
WHERE extensions.ST_DWithin(
  loc,
  extensions.ST_SetSRID(extensions.ST_MakePoint(-73.99, 40.75), 4326)::extensions.geography,
  1500  -- meters
)
ORDER BY updated_at DESC
LIMIT 50;
 
ANALYZE drivers;

What this demonstrates:

• GiST on geography for meter-radius queries
• ST_DWithin is the idiomatic indexed proximity filter
• EXPLAIN should show Index Scan using drivers_loc_gist
• ANALYZE after bulk location updates

Deep Dive

GiST vs SP-GiST

Default choice: GiST unless benchmarks show otherwise.

Operators That Use GiST

-- geometry bounding box overlap (indexable)
geom1 && geom2
 
-- ST_DWithin with index (uses && internally for prefilter)
ST_DWithin(geog1, geog2, distance_meters)

Functions that only use exact geometry without bounding prefilter may not use index efficiently (ST_Contains alone on huge table without && guard).

Multi-Column and Partial Indexes

-- Active drivers only
CREATE INDEX drivers_active_loc_gist ON drivers USING gist (loc)
  WHERE updated_at > now() - interval '1 hour';
 
-- Composite btree + gist: filter tenant then spatial
CREATE INDEX parcels_tenant_gist ON parcels (tenant_id);
CREATE INDEX parcels_geom_gist ON parcels USING gist (geom);

Apply tenant_id equality before spatial op for multi-tenant SaaS.

Index Build and Maintenance

CREATE INDEX CONCURRENTLY parcels_geom_gist ON parcels USING gist (geom);
 
-- After bulk shapefile import
REINDEX INDEX CONCURRENTLY parcels_geom_gist;
ANALYZE parcels;
 
SELECT pg_size_pretty(pg_relation_size('parcels_geom_gist'));

Complex polygons produce large GiST trees. Simplify geometries for overview indexes if needed (see performance article).

Query Plan Checklist

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
-- Expect: Index Scan using ..._gist
-- Red flag: Seq Scan + Filter on million rows

Gotchas

• No index on geography cast in WHERE only - Expression not indexed. Fix: Persist geography column or index functional expression explicitly.
• ST_Distance without DWithin - Sorts all rows. Fix: ST_DWithin cap then ORDER BY ST_Distance on small set.
• Wrong SRID in index column - Index used but wrong answers. Fix: SRID constraints on column typmod.
• Tiny bounding box - Over-selective && misses edges. Fix: ST_Expand or appropriate tolerance.
• CREATE INDEX lock - Blocks writes on production. Fix: CONCURRENTLY.
• Stale stats after bulk GIS import - Seq scan chosen. Fix: ANALYZE and consider increased default_statistics_target on geometry columns if supported by workload.

Alternatives

FAQs

Related

• Geometry vs Geography - type choice
• PostGIS Performance - simplify and prefilter
• GIN and GiST - GiST overview
• Index Design Basics - general indexing
• PostGIS Best Practices - validate inputs

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x. NzA2MjAyfDA4MDFvaWdjfG9pLnNlZGl1Z2Vkb2M=