DuckDB vs PostGIS Spatial Join Benchmarks

Benchmarking a point-in-polygon spatial join between DuckDB and PostGIS fairly means putting DuckDB’s R-tree plus && two-stage filter against PostGIS’s GiST index scan under identical inputs — and this walkthrough, part of the performance crossover benchmarks guide, gives a reproducible harness, the metrics that matter, representative numbers, and the plan comparison that explains them.

Root-Cause Analysis of Misleading Join Benchmarks #

A spatial-join benchmark that compares the two engines honestly is surprisingly hard to construct, and almost every “engine X is N× faster” claim about spatial joins dies once you inspect how it was measured. The failure modes are specific to this operation:

• Asymmetric indexing. DuckDB does not auto-index geometry and PostGIS does not either; one side ends up with a bounding-box index and the other with a full scan, and the ratio measures the missing index rather than the engine. Both a DuckDB R-tree and a PostGIS GiST index must exist and be used before a comparison means anything. Porting the index correctly is the subject of migrating GiST indexes to R-tree.
• Predicate placed where the index cannot serve it. In DuckDB the && overlap must sit in the ON/WHERE against the bare indexed column; wrap it in a function and the R-tree is bypassed. In PostGIS the same discipline applies to && and ST_Intersects against a GiST-indexed column. A defeated index turns either engine into an $O(N \times M)$ nested loop.
• Cold cache on one side. The first execution reads from disk; the engine whose files were already cached wins for reasons that have nothing to do with join execution. Warm both engines before timing.
• CRS or predicate drift. Different coordinate systems, or ST_Intersects on one side versus ST_DWithin with a tolerance on the other, make the two engines answer different questions at different costs.

Each of these produces a large, confident, wrong number, which is why plan validation — not the stopwatch — is the load-bearing step of the whole exercise.

There is also a subtler trap specific to the join case: the two engines can prune identically at the bounding-box stage yet disagree on the final match count if their exact predicates differ by a hair. A DuckDB ST_Intersects that treats a boundary-touching point as a match, versus a PostGIS run that filtered such points with a stricter ST_Contains, will produce different result sizes — and a benchmark comparing two different answers is timing two different queries. Always confirm the two engines return the same row count on the same inputs before you compare their speed; a matching count is the precondition that makes the timing ratio meaningful at all.

Deterministic Configuration #

Fix the DuckDB session so the join is measured against a warm cache, an in-memory working set, and a real R-tree. Record these settings with the results.

INSTALL spatial;
LOAD spatial;

-- Match physical cores so the vectorized hash join actually parallelizes.
SET threads = 8;

-- Size above the join's working set so the measured run does not spill;
-- a spilling benchmark measures disk throughput, not the join.
SET memory_limit = '12GB';

-- Parallel R-tree build and scan; benchmark output order is irrelevant here.
SET preserve_insertion_order = false;

-- Remove progress-bar render cost from EXPLAIN ANALYZE timings.
SET enable_progress_bar = false;

Build the R-tree on the polygon layer before timing anything — the bounding-box index is what the two-stage filter routes through, and its internals are documented in R-tree spatial indexing internals.

-- The index the && stage will use. Without it, the join collapses to a full scan.
CREATE INDEX idx_zones_geom ON zones USING RTREE (geom);

-- Prove it registered before trusting a single timing.
SELECT index_name, table_name FROM duckdb_indexes() WHERE table_name = 'zones';

On the PostGIS side, the parity setup is CREATE INDEX idx_zones_geom ON zones USING GIST (geom); followed by ANALYZE zones; so the planner has fresh statistics, with work_mem set high enough that the join does not spill to pgsql_tmp.

Optimized Execution Pattern #

The benchmark query assigns 5M points to 10K polygons. The naive form names the exact predicate once and lets each engine fall into a nested loop; the optimized form exposes the bounding-box stage so the index drives the scan.

-- Before: single exact predicate, no exposed bbox stage → nested loop, O(N × M).
SELECT p.id, z.zone_id
FROM points p
JOIN zones z ON ST_Intersects(z.geom, ST_Point(p.lon, p.lat));

-- After: bbox overlap in ON (index-servable) gates exact topology on survivors only.
SELECT p.id, z.zone_id
FROM points p
JOIN zones z
  ON z.geom && ST_Point(p.lon, p.lat)              -- stage 1: R-tree MBR overlap
WHERE ST_Intersects(z.geom, ST_Point(p.lon, p.lat)); -- stage 2: exact on the survivors

The only change is predicate placement, and it is the entire benchmark: && against the bare z.geom is index-servable, so DuckDB replaces the nested loop with an R-tree scan that hands a small candidate set to ST_Intersects. The identical rewrite applies to PostGIS. Both engines now run the same two-stage algorithm — the comparison is finally between their execution of that algorithm, not between an indexed plan and an unindexed one. The predicate-placement rules generalize in the spatial joins and proximity filters reference.

Diagnostic Queries & Plan Validation #

Capture the measured plan on both engines and confirm the intended access path before recording any timing. On DuckDB, EXPLAIN ANALYZE must show the R-tree-driven scan feeding a hash join, not a nested loop over the full polygon set.

EXPLAIN ANALYZE
SELECT p.id, z.zone_id
FROM points p
JOIN zones z
  ON z.geom && ST_Point(p.lon, p.lat)
WHERE ST_Intersects(z.geom, ST_Point(p.lon, p.lat));

Read three things: the join operator (HASH_JOIN healthy, NESTED_LOOP_JOIN a defeated index), the spill indicator (must be empty on the peak-throughput run), and actual-versus-estimated rows at the join (drift beyond ~30% means stale statistics or a non-selective bounding-box stage). On PostGIS, EXPLAIN (ANALYZE, BUFFERS) must show an Index Scan using idx_zones_geom rather than a Seq Scan, and Buffers: shared hit rather than read on a warm run.

Representative numbers for 5M points against 10K polygons on a single 8-core node with an SSD, both engines warm and indexed:

The gap is not the index — both prune identically — it is what happens after pruning: DuckDB feeds the surviving candidates through a vectorized hash join and SIMD topology kernels over whole vectors, while PostGIS walks the survivors one tuple at a time through the nested loop. DuckDB’s higher resident memory is the flip side of holding the working set in columnar buffers; PostGIS’s lower figure reflects its server buffer pool and its per-tuple, low-footprint execution. Neither number is “better” in isolation — they describe different execution models, which is the whole point of the performance crossover benchmarks framing.

Geometry Validation & Fallback Routing #

Invalid polygons skew a join benchmark silently: a self-intersecting zone can make ST_Intersects throw on some engines, return corrupt matches on others, and inflate or deflate the survivor count so the two systems no longer agree on the answer. Validate the polygon layer once, before indexing, on both sides.

-- Quarantine invalid geometry before it poisons the benchmark and the index.
SELECT zone_id FROM zones WHERE NOT ST_IsValid(geom);

-- Repair deterministically, then rebuild the R-tree so MBRs match the fixed geometry.
UPDATE zones SET geom = ST_MakeValid(geom) WHERE NOT ST_IsValid(geom);
DROP INDEX IF EXISTS idx_zones_geom;
CREATE INDEX idx_zones_geom ON zones USING RTREE (geom);

Two fallbacks keep a large run comparable rather than crashing. When the point layer will not fit alongside the polygons and index within memory_limit, chunk the probe side into batches and union the counts, so DuckDB streams rather than spilling — a spilling run measures the disk, not the join. When edge noise matters and you want to benchmark a tolerance join instead of strict intersection, swap ST_Intersects for ST_DWithin with an identical distance on both engines so the comparison stays honest:

-- Tolerance variant: keep the && pre-filter so the R-tree still drives the scan.
SELECT p.id, z.zone_id
FROM points p
JOIN zones z
  ON z.geom && ST_Point(p.lon, p.lat)
WHERE ST_DWithin(z.geom, ST_Point(p.lon, p.lat), 0.0001);  -- same tolerance on PostGIS

Confirm both engines return the identical match count before trusting any timing ratio — a benchmark where the two systems disagree on the answer is measuring a bug, not performance.

Related #

See also

• Migrating GiST indexes to R-tree — porting the exact index this benchmark depends on.
• Spatial joins and proximity filters — the predicate-placement rules that keep both plans two-stage.
• R-tree spatial indexing internals — how the bounding-box index driving DuckDB’s scan is built.

Up: DuckDB vs PostGIS vs GeoPandas Performance · Migrating to DuckDB Spatial