DuckDB Spatial Analytical SQL for GIS

Migrating PostGIS GiST Indexes to R-Tree

A PostGIS GiST index and a DuckDB R-tree solve the same bounding-box pruning problem, but they are reached by the planner in opposite ways: GiST is used automatically the instant a spatial predicate appears, while DuckDB’s R-tree fires only when you write the && overlap operator against a bare geometry column. This walkthrough sits under the PostGIS to DuckDB Spatial migration guide and covers how the index maps across, why a migrated query that “should” use the index silently runs a nested loop, and the rebuild-after-load discipline that gives the new tree good node packing. The recurring surprise is that dropping CREATE INDEX ... USING GIST in favour of USING RTREE is the easy half; making the planner actually choose it is the half that bites.

Root-Cause Analysis: Why the Migrated Index Goes Unused

The index migrates cleanly; the query is what needs rewriting. Four behavioral differences between GiST and DuckDB’s R-tree explain every “the index exists but the plan ignores it” report:

  • Automatic use versus explicit &&. PostGIS registers GiST operator classes, so the planner transparently rewrites ST_Intersects, ST_Contains, and ST_DWithin into an index-backed bounding-box scan. DuckDB’s optimizer keys the R-tree off the && operator specifically — an exact predicate like ST_Intersects alone does not reliably pull in the index, so the bounding-box stage must be written by hand.
  • Two-stage evaluation is explicit. GiST hides the cheap-MBR-then-exact split behind a single ST_Intersects. In DuckDB you spell both stages out: && in the ON/WHERE for the R-tree pre-filter, and the exact ST_ predicate on the survivors. This is the same shape as point-in-polygon optimization.
  • Persistence is tied to the database file. A GiST index lives with the table in the PostgreSQL cluster. A DuckDB R-tree persists inside a .duckdb file, but an index built in an in-memory (:memory:) session vanishes when the process exits. Build against a persistent database if the index must survive.
  • Packing quality depends on build order. Both engines maintain the index under DML, but a tree bulk-loaded over a fully populated table packs far better than one grown row-by-row during the migration insert. After a large load, a rebuild is a correctness-neutral performance win.

Deterministic Configuration

R-tree construction is memory-bandwidth bound, so the only settings that matter for the migration build are the thread count, the memory ceiling, and a fast spill target.

INSTALL spatial;
LOAD spatial;

-- Match physical cores. The R-tree bulk-load saturates memory bandwidth, so
-- oversubscribing hyperthreads raises peak memory without speeding the build.
SET threads = 8;

-- Headroom for the whole tree plus the table pages it reads while packing. Too low →
-- the build spills mid-migration; too high on a shared host → the OS OOM-kills it.
SET memory_limit = '12GB';

-- Spill target for an over-budget build. Local NVMe only; a network mount turns a
-- spilling RTREE construction into an I/O cliff.
SET temp_directory = '/var/lib/duckdb/spill';

Confirm the preconditions before building:

Optimized Execution Pattern: Before and After

Start with the PostGIS pair — a GiST index and a query that relies on the planner to use it automatically:

-- PostGIS: GiST index, and a query that never mentions the index. The planner
-- rewrites ST_Intersects into a bounding-box index scan behind the scenes.
CREATE INDEX idx_boundaries_geom ON boundaries USING GIST (geom);

SELECT p.id, b.name
FROM points p
JOIN boundaries b ON ST_Intersects(b.geom, p.geom);

The DuckDB migration changes both halves. The index declaration swaps GIST for RTREE; the query gains an explicit && stage so the optimizer can route through the tree:

-- DuckDB: R-tree index built AFTER the load. max_node_capacity trades a fatter,
-- shallower tree (fewer levels, more scan per node) against a leaner one.
CREATE INDEX idx_boundaries_geom ON boundaries USING RTREE (geom)
  WITH (max_node_capacity = 128);   -- default suits most layers; raise for very large ones

-- The && bounding-box pre-filter is what reaches the R-tree; exact topology runs
-- only on the survivors it returns.
SELECT p.id, b.name
FROM points p
JOIN boundaries b
  ON b.geom && p.geom                     -- Stage 1: MBR overlap (R-tree scan)
WHERE ST_Intersects(b.geom, p.geom);      -- Stage 2: exact topology on survivors

The annotated difference: USING GIST became USING RTREE, and the single automatic ST_Intersects became an explicit &&-then-ST_Intersects pair. Keep the indexed column (b.geom) bare on one side of && — wrapping it in ST_Transform or any function hides it from the optimizer, and the index is skipped. The comparison below shows the two routes side by side.

How PostGIS GiST and DuckDB R-tree indexes are reached by the planner Two vertical paths side by side. On the left, the PostGIS path: an ST_Intersects query flows into a planner box that automatically recognizes the GiST operator class, which flows into a GiST index scan, then an exact recheck, then results. On the right, the DuckDB path: the same query must be written with an explicit ampersand-ampersand overlap operator, which flows into an R-tree index scan, then an explicit exact ST_Intersects on the survivors, then results. An annotation notes that in DuckDB the two-stage split is written by hand, not inferred. PostGIS · GiST ST_Intersects(b.geom, p.geom) planner auto-rewrite GiST operator class GiST index scan MBR + recheck fused results DuckDB · R-tree b.geom && p.geom (explicit) RTREE_INDEX_SCAN MBR pre-filter only ST_Intersects (survivors) exact, written by hand results

Diagnostic Queries & Plan Validation

Never trust that the R-tree is used because it exists. First confirm it registered, then confirm the planner reached it.

-- Confirm the index migrated and is the type you expect.
SELECT index_name, table_name, is_unique
FROM duckdb_indexes()
WHERE table_name = 'boundaries';

-- Confirm the planner actually routes through it.
EXPLAIN ANALYZE
SELECT p.id, b.name
FROM points p
JOIN boundaries b ON b.geom && p.geom
WHERE ST_Intersects(b.geom, p.geom);

Read the plan for these signals:

  • RTREE_INDEX_SCAN appears. This is the proof the && predicate reached the tree. If instead you see a NESTED_LOOP_JOIN over the full boundary set, the index was skipped — the usual cause is a wrapped indexed column or a missing &&.
  • Survivor count is a small fraction of the cross product. The R-tree stage should hand a tiny candidate set to ST_Intersects. If the exact stage still processes near-$O(N \times M)$ rows, the MBR filter is not selective — re-check that both inputs share a CRS.
  • Actual versus estimated rows within ~30% at the scan node. Wide drift right after a migration usually means statistics were never gathered on the freshly loaded table; the deeper index-internals behind these numbers are in the spatial indexing internals reference.

If the index does not appear in the plan, the fastest triage is to check the three things in order: is the column bare on one side of &&, does duckdb_indexes() list the index, and do both geometries share one coordinate system.

One structural difference is worth internalizing because it changes how you reason about every migrated query. In PostGIS the recheck step — re-testing the exact predicate on the rows the MBR stage returned — is fused inside the GiST scan and invisible in most plans. DuckDB deliberately surfaces it: the RTREE_INDEX_SCAN returns only bounding-box candidates, and the exact ST_Intersects you wrote in the WHERE clause is a separate, visible operator above it. That visibility is an asset during migration, because it lets EXPLAIN ANALYZE attribute time to the pruning stage versus the topology stage independently. If the topology operator dominates total runtime, the MBR stage under it is not selective enough, and the fix is upstream — usually a CRS mismatch or a non-selective envelope — not a tweak to the exact predicate itself.

Rebuild Strategy & Fallback Routing

The migration load is the moment to (re)build the tree. Whether you inserted rows through a COPY or an INSERT ... SELECT, drop any partially-grown index and rebuild it once over the settled table so the nodes pack tightly:

-- Rebuild after the bulk load for optimal node packing. A tree grown incrementally
-- during the migration insert is fragmented; one built over the full table is not.
DROP INDEX IF EXISTS idx_boundaries_geom;
CREATE INDEX idx_boundaries_geom ON boundaries USING RTREE (geom);

-- Persist the index into the database file (a no-op on :memory:, which cannot keep it).
CHECKPOINT;

Because the R-tree indexes bounding boxes, invalid geometry produces a valid-looking envelope that still breaks the exact stage. Repair before you build, then rebuild so the tree reflects the corrected shapes — the same gate the parent migration guide applies to the whole load:

-- Repair first, then index, so MBRs match the fixed geometry.
UPDATE boundaries SET geom = ST_MakeValid(geom) WHERE NOT ST_IsValid(geom);
DROP INDEX IF EXISTS idx_boundaries_geom;
CREATE INDEX idx_boundaries_geom ON boundaries USING RTREE (geom);

For tables that keep taking bulk appends after the initial migration — a monthly parcel refresh, say — an incrementally-maintained R-tree gradually loses packing quality, so schedule a periodic rebuild rather than relying on in-place maintenance. The cadence and cost of that rebuild, including how to size it against write volume, are the whole subject of the R-tree index rebuild strategies reference, and the query-side patterns that depend on a healthy tree are covered in spatial joins and proximity filters.

When a rebuild itself breaches memory on a very large layer, chunk the load: build the index on a partitioned subset, validate, then union the partitions, rather than forcing the entire tree through one over-budget build.

See also

Up: PostGIS to DuckDB Spatial migration