DuckDB Spatial Analytical SQL for GIS

Choosing R-Tree Node Capacity

Node capacity is the one R-tree knob DuckDB exposes and the one most likely to be changed without a measurement — this walkthrough, part of the spatial indexing internals reference, sets out what the setting actually trades, which layer shapes justify moving it, and how to tell from the plan whether a change helped or simply made the tree different.

Root-Cause Analysis: what capacity actually changes

  • Fanout, and therefore depth. A higher capacity puts more child entries in each node, so the tree is shallower and a descent takes fewer hops. A lower capacity does the opposite. Depth is logarithmic in capacity, so the effect on hop count saturates quickly.
  • The precision of each prune. A node envelope encloses every child, so a node holding more children covers more area and excludes less. Larger capacity therefore means fewer, coarser decisions; smaller means more, sharper ones.
  • The cost of a node split. Splitting a full node means partitioning its entries into two, and the algorithms that do this well are superlinear in entry count. A high capacity makes each split more expensive, which matters during the build.
  • Memory locality during traversal. A node is read as a unit, so capacity sets how much is fetched per hop. Very small nodes waste the read; very large ones fetch entries the descent will not follow.
  • Nothing about correctness. Every capacity produces a correct index. The setting is entirely a performance trade, which is why a change to it can only be justified by a measurement rather than by reasoning.

The distinguishing question is whether your queries are selective. A selective query benefits from sharp pruning and therefore from lower capacity; a broad query touches many nodes regardless and benefits from a shallower tree.

What raising node capacity trades away Depth falls and pruning precision falls; split cost and bytes per hop both rise. PROPERTY AS CAPACITY RISES EFFECT tree depth falls, then saturates fewer hops per descent pruning precision falls each node excludes less cost of a node split rises superlinearly a slower build bytes fetched per hop rises more entries read than followed

One reason to raise it, three reasons not to. A measurement decides.

Deterministic Configuration 

INSTALL spatial; LOAD spatial;

-- The build is the memory-sensitive part, and higher capacity makes each
-- split hold more entries at once. Size for the build, not the query.
SET memory_limit = '8GB';
SET threads = 8;                      -- physical cores; splits contend on locks
SET temp_directory = '/var/tmp/duckdb_index';

Optimized Execution Pattern

The pattern is to build both and compare, because the setting has no reasoning that substitutes for a measurement. Building two indexes on the same column is legal and the comparison takes minutes.

-- ANTI-PATTERN: changed on a hunch, with nothing to compare against.
CREATE INDEX idx_parcels_geom ON parcels USING RTREE (geom)
  WITH (max_node_capacity = 256);        -- why 256? nobody remembers
-- PATTERN: two indexes, one query, one comparison. Drop the loser afterwards.
CREATE INDEX idx_geom_default ON parcels USING RTREE (geom);
CREATE INDEX idx_geom_low     ON parcels USING RTREE (geom)
  WITH (max_node_capacity = 32);

-- Run the representative query against each and compare candidate rows,
-- not wall-clock time — the candidate count is what capacity actually changes.
EXPLAIN ANALYZE
SELECT count(*) FROM parcels
WHERE geom && ST_MakeEnvelope(2.20, 48.80, 2.45, 48.95);

The number to compare is the row count the index scan emits, because that is what capacity affects directly. Wall-clock time also reflects cache state and thread scheduling, and on a small table it can move in the opposite direction from the thing being measured.

Which layer shapes justify a change, and which way Uniform small features and broad queries favour higher capacity; mixed extents and selective queries favour lower. LAYER OR QUERY SHAPE DIRECTION WHY uniformly small features higher capacity tiny envelopes still exclude well wildly mixed extents lower capacity large features widen every node broad queries over the extent higher capacity precision is irrelevant when most match highly selective queries lower capacity precision is what the query is asking for

Two of the four are about the data and two about the questions asked of it.

Why the default is usually right

The default exists because it sits near the flat part of the trade-off curve for mixed-extent data, which is what most real layers are. Depth is logarithmic in capacity, so doubling the capacity removes a fraction of a hop from a descent — while halving the pruning precision, which is a linear effect. The asymmetry means the downside of a large change arrives faster than the upside.

That is also why the productive direction, when there is one, is usually downward rather than upward. A selective query over a layer with mixed extents is the case where the default genuinely underperforms, and lowering the capacity sharpens exactly the decisions that query depends on. Raising it helps a narrower set of cases and helps them by less.

Diagnostic Queries & Plan Validation

The diagnostic is the same number in both cases: candidate rows emitted per genuine match. It is stable across machines and it isolates the property capacity controls.

-- Candidates per match, for a representative window. Lower is a better tree.
WITH window AS (SELECT ST_MakeEnvelope(2.20, 48.80, 2.45, 48.95) AS w)
SELECT
    count(*) FILTER (WHERE geom && (SELECT w FROM window))            AS candidates,
    count(*) FILTER (WHERE ST_Intersects(geom, (SELECT w FROM window))) AS matches
FROM parcels;

A ratio that improves by less than about twenty per cent is noise for this purpose — capacity is a second-order setting, and a change that small is not worth carrying as a non-default. A ratio that improves by a factor is worth keeping and worth writing down.

Three problems that are not about capacity Fragmentation, an unused index, and expensive exact topology each look like poor tuning and each has a different fix. SYMPTOM ACTUAL CAUSE FIX candidates ≈ the whole table fragmentation from churn rebuild the index no index scan in the plan the predicate is not index-eligible rewrite the predicate few candidates, still slow exact topology dominates simplify the geometry

Check all three before changing a setting that was never the constraint.

Recording the decision

A non-default capacity is a piece of tuning that will outlive whoever set it, and the failure mode is a value nobody can justify surviving into a schema where it no longer helps. If a change is kept, record three things alongside it: the query it was measured against, the candidates-per-match ratio before and after, and the date. That turns an unexplained number into a decision someone can revisit when the data changes.

The corollary is that a change which cannot be described that way should not be kept. Reverting to the default costs one index rebuild and removes a variable from every future investigation, which is usually worth more than a marginal improvement nobody can reproduce.

Geometry Validation & Fallback Routing

If neither capacity helps, the constraint is elsewhere, and the two candidates are the geometry itself and the query shape.

-- Is the cost in the index or in the topology? Compare the envelope-only
-- count against the exact count for the same window: if the envelope stage
-- is already selective, the remaining time is vertex maths, not tree traversal.
SELECT
    avg(ST_NPoints(geom))                    AS avg_vertices,
    max(ST_NPoints(geom))                    AS max_vertices,
    count(*)                                 AS rows
FROM parcels;
-- A high average here means simplification will beat any index tuning.

Frequently Asked Questions

Should I change node capacity at all?

Usually not. The default sits near the flat part of the trade-off curve for mixed-extent data, which is what most layers are, and the gains from moving it are second-order. The cases that justify a change are a layer with unusually uniform or unusually skewed feature sizes, and even then the change should follow a measurement rather than precede one.

Which direction is more likely to help?

Downward. Lower capacity sharpens pruning, which is what a selective query over mixed-extent data actually needs. Raising it removes a fraction of a hop from a descent — a logarithmic gain — while halving pruning precision, which is linear, so the downside arrives faster than the upside.

What should I measure?

Candidate rows emitted by the index scan per genuine match. That number isolates the property capacity controls and is stable across machines, whereas wall-clock time also reflects cache state and scheduling and can move the opposite way on a small table.

Can I have two indexes with different capacities?

Yes, and building both is the honest way to compare them, since there is no reasoning that substitutes for a measurement here. Drop the loser afterwards — carrying both doubles the maintenance cost of every write for no benefit.

My index emits nearly the whole table. Is capacity the problem?

Almost certainly not. That symptom is fragmentation from churn, where node envelopes have widened until they all overlap the query window. Rebuild first; if the ratio recovers, capacity was never the issue.

Does capacity affect build time?

Yes, and in the direction people do not expect: higher capacity makes each node split more expensive, because partitioning a larger entry set well is superlinear. On a large bulk build that can be a noticeable fraction of the total, which is another reason not to raise it without cause.

Up: Spatial Indexing Internals