GiST vs SP-GiST Index Selection

This page is a decision guide for choosing between a PostGIS GIST bounding-box R-tree and an SPGIST space-partitioned index for a spatial dbt model, and for wiring whichever you pick into a post_hook.

When to use this approach

Make the GiST-versus-SP-GiST call deliberately — rather than reaching for USING GIST by reflex — when any of these describe your model:

• Your geometry column is point-heavy and the points barely overlap — dense GPS pings, IoT fixes, event locations. Space partitioning suits non-overlapping data, and this is the one case where SP-GiST routinely wins. If instead you are tuning a proximity join over that point cloud, the access-path shape matters as much as the index type — see optimizing proximity joins.
• You store overlapping polygons — parcels nested inside districts, service areas, administrative tessellations with shared borders. Overlap is exactly what an R-tree bounding-box index is built for, so GiST stays the default here.
• The index build itself is a bottleneck on a large table you rebuild each run. Build time and on-disk size differ enough between the two structures to matter for a table or incremental model. Once you have chosen, the hint that keeps the planner on that index belongs with using spatial index hints in dbt materializations.

If none of these apply, stop here and use GiST — it is the correct general-purpose default and the rest of this guide is about the narrow cases where SP-GiST earns its place.

Prerequisites

• PostGIS 2.5+ for SP-GiST on the geometry type (2D operator class spgist_geometry_ops_2d); PostGIS 3.x recommended. SP-GiST does not index the geography type — GiST is your only choice there.
• dbt-core 1.5+ with dbt-postgres against a PostGIS-enabled database, set up per PostGIS adapter configuration.
• CREATE INDEX grants on the target schema — both index types are declared in a model post_hook, so the run role must be able to build them.
• A single canonical SRID already enforced upstream. Index choice is meaningless if the join casts CRS at execution time; neither structure covers an implicit ST_Transform.
• Permission to run EXPLAIN (ANALYZE, BUFFERS) so you can prove which index the planner actually used.

Step-by-step instructions

1. Profile the geometry distribution before choosing

The choice is driven by one property: how much the bounding boxes of your geometries overlap. R-trees degrade when many entries share overlapping envelopes because a point query must descend several branches; a space-partitioning tree sidesteps that by splitting the plane into disjoint regions. Measure overlap instead of guessing.

-- analysis/geom_overlap_profile.sql
SELECT
  COUNT(*)                                          AS n_rows,
  COUNT(*) FILTER (WHERE GeometryType(geom) = 'POINT') AS n_points,
  AVG(ST_NPoints(geom))                             AS avg_vertices,
  -- rough overlap signal: how many other envelopes each row's bbox touches
  AVG(neighbors)                                    AS avg_bbox_neighbors
FROM (
  SELECT a.geom,
         COUNT(b.geom) AS neighbors
  FROM {{ ref('stg_gps_pings') }} a
  JOIN {{ ref('stg_gps_pings') }} b
    ON a.geom && b.geom AND a.ctid <> b.ctid
  GROUP BY a.geom, a.ctid
) s

Verify: a high n_points fraction with a low avg_bbox_neighbors (envelopes rarely touch) points to SP-GiST; a low point fraction or a high neighbor count — overlapping polygons — points to GiST. If avg_vertices is large, prefer GiST regardless, because SP-GiST partitions on the bounding box and gains little from complex boundaries.

2. Declare a GiST index in a post_hook (the default)

GiST is the general-purpose R-tree PostGIS uses for &&, ST_Intersects, ST_Contains, ST_DWithin, and <-> KNN ordering, on both geometry and geography. Build it after materialization, which in dbt means a post_hook — the index cannot exist before the table it covers.

-- models/marts/fct_service_areas.sql
{{ config(
    materialized='table',
    post_hook="CREATE INDEX IF NOT EXISTS {{ this.identifier }}_geom_gist
               ON {{ this }} USING GIST (geom)"
) }}

SELECT area_id, geom
FROM {{ ref('int_service_area_unions') }}

Verify the index exists and is the R-tree access method you expect:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'fct_service_areas';
-- Expect: "... USING gist (geom)" in indexdef.

3. Declare an SP-GiST index for non-overlapping point data

For a validated point cloud with negligible overlap, swap the access method to SPGIST. The only syntactic change is the USING clause; everything else — the post_hook shape, the operator support for &&, ST_DWithin, and KNN — stays the same.

-- models/marts/fct_gps_fixes.sql
{{ config(
    materialized='table',
    post_hook="CREATE INDEX IF NOT EXISTS {{ this.identifier }}_geom_spgist
               ON {{ this }} USING SPGIST (geom)"
) }}

SELECT device_id, recorded_at, geom
FROM {{ ref('stg_gps_pings') }}
WHERE geom IS NOT NULL AND ST_IsValid(geom)

Verify the access method is spgist, and confirm the column is geometry (not geography, which SP-GiST rejects):

SELECT a.amname, format_type(t.atttypid, t.atttypmod) AS geom_type
FROM pg_index i
JOIN pg_class c   ON c.oid = i.indexrelid
JOIN pg_am a      ON a.oid = c.relam
JOIN pg_attribute t ON t.attrelid = i.indrelid AND t.attnum = i.indkey[0]
WHERE c.relname = 'fct_gps_fixes_geom_spgist';
-- Expect amname = 'spgist' and geom_type = 'geometry'.

4. Benchmark both with EXPLAIN before committing

Never decide on theory alone. Build both indexes on a representative copy, run the model’s real predicate under EXPLAIN (ANALYZE, BUFFERS), and compare the index-scan cost and buffer reads. The winner is the plan that touches fewer buffers for the same result.

-- Run once against a GIST copy, once against an SPGIST copy
EXPLAIN (ANALYZE, BUFFERS)
SELECT device_id
FROM fct_gps_fixes
WHERE ST_DWithin(
        geom,
        ST_SetSRID(ST_MakeEnvelope(-122.5, 37.7, -122.3, 37.9), 4326),
        250);
-- Compare "Index Scan using ..._gist"  vs  "Index Scan using ..._spgist":
-- look at actual time, rows removed by filter, and shared buffers hit/read.

Verify: both plans must show an Index Scan (never Seq Scan) and return identical row counts. If one falls back to a sequential scan, the comparison is invalid — fix the predicate or statistics first, then re-run. Keeping the predicate a bare, index-eligible expression is the same discipline covered in using spatial index hints in dbt materializations.

Decision tree

Comparison reference

Gotchas & edge cases

• SP-GiST rejects geography. CREATE INDEX ... USING SPGIST (geog) fails with a “no operator class” error. If your distance work relies on spheroidal geography metres, you are on GiST regardless of overlap. The geometry-vs-geography trade-off itself is upstream of this decision — decide the type first, per choosing the right spatial adapter, then the index.
• Overlap kills the SP-GiST advantage. A point cloud that clusters tightly (many coincident or near-coincident fixes) partitions poorly, and SP-GiST loses its edge over GiST. Measure overlap on your data, not on the platonic “points don’t overlap” assumption.
• Complex polygons favor GiST. SP-GiST indexes the bounding box, so high-vertex geometries gain nothing from partitioning and pay for it in false-positive recheck cost. Keep GiST for tessellations and coastlines.
• Only one index is used per scan. Building both a GiST and an SP-GiST index on the same column wastes write throughput and disk; the planner picks one. Benchmark, keep the winner, and drop the other in the post_hook.
• Stale statistics mask the winner. After a full refresh the table has no fresh ANALYZE, so EXPLAIN costs are unreliable. Run ANALYZE {{ this }} in the post_hook after the index build, before you benchmark.
• 3D/ND queries. SP-GiST’s spatial support is 2D; for N-dimensional operators use the GiST *_ops_nd operator class instead.

FAQ

Related

• Using spatial index hints in dbt materializations — force the planner onto whichever index you chose.
• Optimizing Proximity Joins — the KNN and radius patterns these indexes accelerate.
• Choosing the Right Spatial Adapter — the geometry-vs-geography decision that gates SP-GiST eligibility.

Up: Part of Index Hints for Spatial Queries.