Benchmarking spatial joins in DuckDB

An aggregate FILTER made this DuckDB spatial query 84× faster than a plain WHERE, with the same exact count and no index or precomputation.

Benchmarks ran on a 10-core, 32 GB MacBook Pro M2.

I found the difference while testing a local DuckDB replica of open geodata: 158 million building footprints in an 83 GiB file. I wanted to count buildings inside Columbus, Ohio. The exact answer is 319,513; the city boundary is an 18,239-vertex multipolygon. The slowdown came from how DuckDB planned the spatial predicate.

Initial Columbus timings:

Each timed query is a single CREATE TEMP TABLE ... AS SELECT statement; I did not create a candidate table first and then query it. The bbox prune is a materialized stage within each query. The 35.4 s and 0.423 s figures are full query timings; 0.065 s is a separate bbox-only baseline. If the bbox prune is not materialized first, the planner can run the spatial test before applying the bbox filter, testing all 158 million rows. On Houston, that took 203 s instead of 1.3 s for the same 637,209 buildings.

EXPLAIN shows the difference. The WHERE form becomes a SPATIAL_JOIN and re-parses the polygon for each row (about 61 µs per call). Aggregate FILTER avoids that rewrite; the predicate uses GEOS's prepared-geometry path instead (about 0.7 µs per call).

WHERE  : UNGROUPED_AGGREGATE -> SPATIAL_JOIN(condition: st_intersects) -> SEQ_SCAN x2
FILTER : UNGROUPED_AGGREGATE(count_star() FILTER (WHERE ...))
                              -> PROJECTION -> CROSS_PRODUCT -> SEQ_SCAN x2

The pattern holds from a tiny village to a 9,238-vertex county; each pair returns the same count:

Returning rows instead of a count

For endpoints that return rows rather than a count, aggregate FILTER is not a direct fit. These alternatives all return the same 319,513 rows:

IS TRUE is the simplest drop-in. A ::BOOLEAN cast (37.4 s) and filtering a projected predicate in an outer query (33.3 s) remain slow.

The same pattern holds for point-in-polygon: count(*) FILTER (WHERE ST_Contains(poly, point)) took 0.033 s for 28,862 points, versus 4.09 s with WHERE — 124× slower.

The practical rule

IS TRUE changes the predicate shape the optimizer sees. Like MATERIALIZED, it is load-bearing: a future DuckDB version could optimize through it and bring back the slow plan. Check EXPLAIN after upgrades.

Keep the bbox prune to limit the exact test to candidates. An RTree index did not help: 35.1 s versus 33.7 s, and EXPLAIN showed no RTree scan.

Queries

These are the complete statements behind the two timings:

-- Full WHERE query: 35.4 s, result 319,513.
CREATE OR REPLACE TEMP TABLE where_result AS
WITH tgt AS (
  SELECT geometry AS geom, xmin, xmax, ymin, ymax, cx0, cx1, cy0, cy1
  FROM census_places WHERE geoid = '3918000'
), cand AS MATERIALIZED (
  SELECT b.geometry AS g FROM buildings b, tgt t
  WHERE b.cx BETWEEN t.cx0 AND t.cx1 AND b.cy BETWEEN t.cy0 AND t.cy1
    AND b.xmax >= t.xmin AND b.xmin <= t.xmax
    AND b.ymax >= t.ymin AND b.ymin <= t.ymax
)
SELECT count(*) AS buildings FROM cand c, tgt t
WHERE ST_Intersects(c.g, t.geom);

-- Full aggregate-FILTER query: 0.423 s, same result 319,513.
CREATE OR REPLACE TEMP TABLE filter_result AS
WITH tgt AS (
  SELECT geometry AS geom, xmin, xmax, ymin, ymax, cx0, cx1, cy0, cy1
  FROM census_places WHERE geoid = '3918000'
), cand AS MATERIALIZED (
  SELECT b.geometry AS g FROM buildings b, tgt t
  WHERE b.cx BETWEEN t.cx0 AND t.cx1 AND b.cy BETWEEN t.cy0 AND t.cy1
    AND b.xmax >= t.xmin AND b.xmin <= t.xmax
    AND b.ymax >= t.ymin AND b.ymin <= t.ymax
)
SELECT count(*) FILTER (WHERE ST_Intersects(c.g, t.geom)) AS buildings
FROM cand c, tgt t;

Co-Authored by DeepSeek Alpha 4.1 | Edited by GPT-6 Luna | Coded in Freebuff