Choosing a Spatial Index Type: GiST vs SP-GiST vs BRIN

Selecting a spatial access method is the first architectural decision in Advanced GiST Indexing & Optimization, and it is one PostGIS engineers frequently get wrong by defaulting to GiST for every geometry column regardless of data shape. The right choice depends on three measurable properties of the table: whether geometries overlap, how the table is written, and how the physical row order relates to position on the map. This page compares the three access methods PostgreSQL exposes to PostGIS — GiST, SP-GiST, and BRIN — across internals, size, build cost, and query behaviour, then distils the trade-offs into a decision matrix you can apply to a meteorological observation platform built on observations, grid_cells, and radar_sweeps.

Prerequisites and Infrastructure Validation

All three access methods ship with a stock PostgreSQL plus PostGIS installation, but the operator classes differ per method. Confirm the extension version and inspect what each method supports before committing to a design.

-- PostGIS 3.x and PostgreSQL 13+ expose GiST, SP-GiST, and BRIN operator classes for geometry
SELECT extname, extversion
FROM pg_extension
WHERE extname = 'postgis';

-- List the operator classes available per access method for the geometry type
SELECT am.amname AS access_method, opc.opcname AS operator_class
FROM pg_opclass opc
JOIN pg_am am ON am.oid = opc.opcmethod
JOIN pg_type t ON t.oid = opc.opcintype
WHERE t.typname = 'geometry'
ORDER BY am.amname, opc.opcname;

You should see gist_geometry_ops_2d and gist_geometry_ops_nd for GiST, spgist_geometry_ops_2d for SP-GiST, and brin_geometry_inclusion_ops_2d for BRIN. If the SP-GiST or BRIN classes are absent, your PostGIS build predates 2.5 and needs upgrading before you can use them.

Confirm the SRID and geometry type of every column you intend to index. A meteorological schema typically looks like this:

SELECT f_table_name, f_geometry_column, type, srid
FROM geometry_columns
WHERE f_table_name IN ('observations', 'grid_cells', 'radar_sweeps');

For this platform, observations stores geometry(Point, 4326) station fixes, grid_cells stores geometry(Polygon, 4326) forecast tiles, and radar_sweeps stores geometry(Point, 4326) reflectivity samples appended in scan-time order. Those three shapes map cleanly onto the three access methods, which is why they make a useful comparison bed.

The Python toolchain for validating index choices is minimal:

psycopg[binary]>=3.1     # or psycopg2-binary>=2.9
sqlalchemy>=2.0
geoalchemy2>=0.14
shapely>=2.0

How Each Access Method Works Internally

GiST — the balanced R-tree

GiST builds a balanced tree in which every node stores the minimum bounding rectangle (MBR) that encloses all geometries beneath it. Child MBRs are allowed to overlap, which is precisely what lets GiST index overlapping polygons, lines, and mixed geometry types. A search descends every branch whose MBR intersects the query box, so overlapping siblings can force the scan down multiple paths. The tree is lossy: an MBR match is only a candidate, and PostgreSQL rechecks the exact geometry against the heap tuple. This two-phase behaviour is the foundation of every tuning decision covered across the parent guide, and it is the reason GiST works for any operator PostGIS defines.

Because it is balanced and self-organising, GiST tolerates updates, deletes, and random insert order without degrading. That resilience is its defining strength and the reason it is the correct default.

SP-GiST — the space-partitioning quadtree

SP-GiST implements space-partitioning trees: quadtrees and k-d trees. For 2D geometry it partitions the coordinate plane into four non-overlapping quadrants at each level. Because the partitions never overlap, a point probe follows exactly one root-to-leaf path instead of fanning out across overlapping branches. That makes SP-GiST both smaller and faster to probe than GiST for disjoint point sets such as observations. The catch is structural: quadtree partitioning assumes each indexed value belongs to a single quadrant, which holds for points but breaks down for polygons that straddle partition boundaries. SP-GiST does support box and polygon operator classes, but the sweet spot is clearly non-overlapping points.

BRIN — the block range summary

BRIN abandons the tree model entirely. Instead of indexing rows, it divides the table’s physical storage into block ranges — by default 128 consecutive 8 KB pages — and stores one summary per range: the bounding box that encloses every geometry in those pages. A query scans the tiny summary list, discards ranges whose bounding box cannot match, and then sequentially rechecks only the surviving ranges. The index is minuscule because it holds one small record per 128 pages rather than one entry per row.

BRIN’s entire value proposition rests on correlation: the summaries only prune well if geometries stored physically close to each other are also close on the map. On radar_sweeps, where each sweep appends a spatially compact burst of samples in acquisition order, that correlation is naturally high. On a randomly shuffled table it collapses to nearly zero, and BRIN degenerates into a full sequential scan. The narrow scenario where BRIN shines — a massive, time-ordered, spatially correlated append-only geometry table — is developed in full in BRIN indexes for append-only spatial tables.

Size, Build Cost, and Query Trade-offs

The three methods occupy different points on a size-versus-precision curve. GiST is precise and large; BRIN is imprecise and tiny; SP-GiST sits in between for point workloads. Measuring the actual footprint on your data removes the guesswork.

-- Build one of each on separate copies and compare footprint
CREATE INDEX idx_obs_gist    ON observations USING gist  (geom);
CREATE INDEX idx_obs_spgist  ON observations USING spgist (geom);
CREATE INDEX idx_sweeps_brin ON radar_sweeps USING brin  (geom) WITH (pages_per_range = 128);

SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan
FROM pg_stat_user_indexes
WHERE indexrelname IN ('idx_obs_gist', 'idx_obs_spgist', 'idx_sweeps_brin');

On a 20-million-row observations table, the GiST index commonly lands near 900 MB, the SP-GiST index near 500 MB, and a BRIN index on a billion-row radar_sweeps table often measures under 5 MB. Those ratios drive the decision as much as raw query latency does, because a small index stays cache-resident and adds negligible write amplification.

Build cost

Build time tracks index complexity. BRIN builds fastest by a wide margin — it makes a single sequential pass computing per-range summaries, so it indexes a billion rows in minutes. GiST and SP-GiST both sort and insert every row into a tree, which is I/O- and CPU-heavy at scale. Raise maintenance_work_mem before building either tree, and always build on live tables with CONCURRENTLY:

SET maintenance_work_mem = '1GB';
CREATE INDEX CONCURRENTLY idx_obs_geom ON observations USING gist (geom);

Query precision

GiST returns a tight candidate set with few false positives, so heap rechecks are cheap. SP-GiST matches GiST precision on point lookups. BRIN returns entire block ranges as candidates, so PostgreSQL rechecks every tuple in each surviving range — potentially hundreds of thousands of rows. That recheck cost is why BRIN is disqualified for interactive point-and-click map queries and only viable when a query touches a contiguous physical slice of a well-correlated table.

The Decision Matrix

The following matrix maps observable table properties to the recommended access method. Read it top to bottom and stop at the first row that matches your dominant query shape.

Two rows deserve emphasis. First, only GiST and SP-GiST support the <-> distance operator that powers ordered nearest-neighbour retrieval, so any KNN endpoint rules BRIN out immediately. Second, the “huge append-only correlated” row is the sole case where BRIN is the best answer rather than a compromise — and that case is common in observation platforms where sensor firehoses land in time order.

For grid_cells (overlapping forecast polygons queried by arbitrary bounding box) the matrix points to GiST. For observations (disjoint station points, read-mostly) SP-GiST is the tighter, faster fit. For radar_sweeps (a billion-row append-only firehose) BRIN wins decisively on size. A single platform legitimately uses all three.

Core Execution Workflow

Step 1 — Build the Method That Matches Each Table

-- grid_cells: overlapping polygons, arbitrary access -> GiST
CREATE INDEX CONCURRENTLY idx_grid_cells_geom
ON grid_cells USING gist (geom);

-- observations: non-overlapping station points, read-mostly -> SP-GiST
CREATE INDEX CONCURRENTLY idx_observations_geom
ON observations USING spgist (geom);

-- radar_sweeps: billion-row append-only firehose in scan order -> BRIN
CREATE INDEX CONCURRENTLY idx_radar_sweeps_geom
ON radar_sweeps USING brin (geom) WITH (pages_per_range = 64);

Step 2 — Confirm the Planner Selects Each Index

Run the query shape each table actually receives, and confirm the plan node names the intended index. Always ANALYZE first so the planner has fresh selectivity estimates:

ANALYZE grid_cells;
ANALYZE observations;
ANALYZE radar_sweeps;

EXPLAIN (ANALYZE, BUFFERS)
SELECT cell_id
FROM grid_cells
WHERE ST_Intersects(
        geom,
        ST_SetSRID(ST_MakeEnvelope(-105.3, 39.5, -104.6, 40.1), 4326)
      );

A GiST plan reads Index Scan using idx_grid_cells_geom. An SP-GiST plan on observations reads Index Scan using idx_observations_geom. A BRIN plan on radar_sweeps reads Bitmap Heap Scan fed by a Bitmap Index Scan on idx_radar_sweeps_geom — BRIN never produces a plain index scan because it must recheck heap tuples in each candidate range. Seeing Seq Scan where you expected an index means the planner mis-estimated selectivity; the remedy is covered under query plan analysis with EXPLAIN.

Step 3 — Drive the Chosen Index From Python

Parameterised queries with explicit SRID keep the planner’s type inference clean regardless of access method. This helper switches between the three tables without changing the binding pattern:

import psycopg
from psycopg.rows import dict_row

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

def cells_in_view(min_lon, min_lat, max_lon, max_lat):
    """Bounding-box query against the GiST-indexed grid_cells polygons."""
    sql = """
        SELECT cell_id, forecast_zone
        FROM grid_cells
        WHERE ST_Intersects(
                geom,
                ST_SetSRID(ST_MakeEnvelope(%s, %s, %s, %s), 4326)
              );
    """
    with psycopg.connect(DSN, row_factory=dict_row) as conn:
        with conn.cursor() as cur:
            cur.execute(sql, (min_lon, min_lat, max_lon, max_lat))
            return cur.fetchall()

def nearest_stations(lon, lat, k=10):
    """KNN against the SP-GiST-indexed observations points via the <-> operator."""
    sql = """
        SELECT station_id, geom <-> ST_SetSRID(ST_MakePoint(%s, %s), 4326) AS dist
        FROM observations
        ORDER BY geom <-> ST_SetSRID(ST_MakePoint(%s, %s), 4326)
        LIMIT %s;
    """
    with psycopg.connect(DSN, row_factory=dict_row) as conn:
        with conn.cursor() as cur:
            cur.execute(sql, (lon, lat, lon, lat, k))
            return cur.fetchall()

The <-> ordering in nearest_stations is exactly why observations uses SP-GiST rather than BRIN: BRIN cannot answer ordered nearest-neighbour queries at all. For the KNN pattern itself see KNN nearest-neighbour queries.

Performance Considerations

Reading the plan for each method

The single most useful diagnostic is whether the index node reports a plausible candidate count. For GiST and SP-GiST, watch Rows Removed by Filter on the recheck — a high value means the MBR or quadrant filter passed too many false positives, usually a sign the query box is too large. For BRIN, watch the ratio of Bitmap Index Scan rows to final rows: if BRIN hands the bitmap heap scan far more blocks than the query needs, correlation has degraded and the table needs a resummarize or a CLUSTER.

-- Correlation drives BRIN quality; below ~0.9 the summaries stop pruning
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'radar_sweeps' AND attname = 'geom';

GUC settings that shift the choice

-- On SSD/NVMe, cheap random reads make GiST and SP-GiST index scans attractive
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_cache_size = '24GB';   -- ~75% of RAM
SELECT pg_reload_conf();

A high random_page_cost biases the planner toward sequential scans and can make it skip a GiST index that would in fact be faster. BRIN, by contrast, is a sequential access method internally, so it is far less sensitive to random_page_cost — another reason it suits spinning disks and cold archival storage where random I/O is expensive.

Build memory

maintenance_work_mem governs how much of a GiST or SP-GiST tree PostgreSQL can assemble in RAM before spilling. Under-provisioning it turns an index build into a disk-thrashing crawl. BRIN builds are memory-light and rarely need tuning.

Common Failure Modes and Fixes

Defaulting to GiST on a firehose table

Symptom: A GiST index on radar_sweeps grows to tens of gigabytes, slows every insert, and never fits in cache.

Fix: For an append-only, spatially correlated table, replace it with BRIN. The index shrinks by three or four orders of magnitude and insert throughput recovers because BRIN adds almost no per-row write cost.

DROP INDEX idx_radar_sweeps_gist;
CREATE INDEX idx_radar_sweeps_geom
ON radar_sweeps USING brin (geom) WITH (pages_per_range = 64);

Using SP-GiST on overlapping polygons

Symptom: An SP-GiST index on grid_cells returns correct results but probes slowly, because straddling polygons force wide descents through the quadtree.

Fix: Move overlapping-polygon columns back to GiST. SP-GiST’s advantage exists only for non-overlapping data; on overlapping geometry it forfeits its one strength.

BRIN degrades after out-of-order backfill

Symptom: A BRIN index that was fast at launch begins scanning far too many block ranges after a bulk historical backfill inserted rows out of spatial order.

Fix: Physical order no longer matches spatial order, so the summaries overlap. Rebuild locality with CLUSTER on a companion GiST index, or drop and recreate the BRIN after loading, then resummarize:

SELECT brin_summarize_new_values('idx_radar_sweeps_geom');

SRID mismatch disables every method

Symptom: No matter which index exists, the planner chooses Seq Scan.

Fix: A predicate that wraps the indexed column in ST_Transform makes every access method ineligible. Store and query in the same SRID (4326 here), and transform the query literal rather than the column.

Verification

Confirm each table is served by the intended method and that the index is actually used under real traffic:

-- 1. Which access method backs each spatial index?
SELECT t.relname AS table_name, i.relname AS index_name, am.amname AS method
FROM pg_index x
JOIN pg_class i  ON i.oid = x.indexrelid
JOIN pg_class t  ON t.oid = x.indrelid
JOIN pg_am    am ON am.oid = i.relam
WHERE t.relname IN ('observations', 'grid_cells', 'radar_sweeps')
ORDER BY t.relname;

-- 2. Are the indexes being scanned after a workload run?
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname IN ('observations', 'grid_cells', 'radar_sweeps')
ORDER BY idx_scan DESC;

-- 3. Compare footprints to confirm BRIN's size advantage held up
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname IN ('observations', 'grid_cells', 'radar_sweeps');

A healthy result shows GiST behind grid_cells, SP-GiST behind observations, BRIN behind radar_sweeps, non-zero idx_scan counts on each, and a BRIN size measured in megabytes against gigabytes for the trees.

Frequently Asked Questions

Is GiST always the safe default for PostGIS geometry columns?

For most tables, yes. GiST handles overlapping geometries, mixed types, and arbitrary access patterns, and every PostGIS operator has a GiST operator class. Reach for SP-GiST or BRIN only when the data shape or write pattern clearly favours them, because those methods trade generality for size or speed in narrow cases.

When does SP-GiST beat GiST for point data?

SP-GiST wins when the column holds non-overlapping points such as weather-station coordinates. Its quadtree partitions space without overlapping bounding boxes, so probes descend a single path. That produces a smaller index and faster point-in-region lookups than the R-tree GiST builds, though it cannot index overlapping polygons well.

How small is a BRIN index compared to GiST?

BRIN stores only a bounding summary per block range, so it is often several thousand times smaller than a GiST index on the same column. A GiST index on a billion-row radar table can reach tens of gigabytes, while its BRIN counterpart fits in a few megabytes and stays resident in cache.

Can a BRIN index replace GiST for interactive map queries?

No. BRIN returns block ranges, not exact rows, so PostgreSQL rechecks every tuple in each candidate range. On low-selectivity or randomly ordered data that recheck is far slower than a GiST index scan. BRIN only pays off on large, append-only tables whose physical order tracks the spatial dimension.

Related Topics

• Advanced GiST Indexing & Optimization — parent guide to GiST mechanics, operator classes, and the full indexing lifecycle
• BRIN Indexes for Append-Only Spatial Tables — the deep dive on pages_per_range, correlation, and BRIN’s limits
• Partial GiST Indexes — shrink a GiST tree by indexing only the rows you query
• Composite Spatial Indexes — combine geometry with scalar columns using btree_gist
• Index-Only Scan Strategies — eliminate heap fetches with INCLUDE and the visibility map