DuckDB Spatial Analytical SQL for GIS

Python Spatial UDFs and Predicate Pushdown

The two questions that decide how fast a Python-driven spatial workload runs are where the geometry logic executes and whether the optimizer can still see the predicate — and they are the same question asked twice. This page sits inside the Python & DuckDB integration workflows reference and covers the boundary between them: when custom logic genuinely has to leave SQL, how to move it out without destroying vectorisation, and how to confirm that a filter is still being pushed into the scan rather than evaluated afterwards on everything.

The governing observation is that a Python function registered into the engine is not a faster loop — it is a loop with the loop moved somewhere less visible. It still enters the interpreter once per row, it still holds the global interpreter lock while it does, and it additionally makes the surrounding expression opaque to the optimizer, so a predicate that would have been pushed into a Parquet scan is now evaluated after every row has been read and decoded. The cost of a UDF is rarely the UDF.

Runtime Configuration & Memory Guardrails

A workload that mixes SQL and Python needs both sides bounded, and the Python side is the one with no default.

import duckdb

con = duckdb.connect("gis.duckdb")
con.execute("INSTALL spatial; LOAD spatial")

# The engine's budget. A UDF does not respect it — anything the Python side
# allocates is on top of this, which is why the two have to be sized together.
con.execute("SET memory_limit = '6GB'")
con.execute("SET threads = 8")
con.execute("SET temp_directory = '/var/tmp/duckdb_udf'")

# Vectorised Python work materialises whole columns. Bound it explicitly by
# processing in batches rather than fetching the column in one call.
BATCH_ROWS = 200_000

Trade-off Analysis: Raising threads speeds the SQL side and does nothing for a scalar UDF, because the interpreter lock serialises the Python half regardless. On a query dominated by a UDF, extra threads add contention for no throughput — which is a useful diagnostic in itself: if a query does not get faster with more threads, something in it is not vectorised.

Four places custom geometry logic can run, and what each costs SQL is the baseline; a vectorised Python pass over the WKB column is about 9x; a scalar UDF about 60x; a Python loop about 140x. WHERE THE LOGIC RUNS INTERPRETER ENTRIES RELATIVE COST expressed in SQL none 1× — and prunable by an index vectorised over the WKB column one ~9× a scalar Python UDF one per row ~60× — and breaks vectorisation a Python loop over fetched rows one per row, plus allocation ~140×

Two orders of magnitude, decided entirely by how often the interpreter is entered.

Where custom logic should run

Where should this custom geometry logic run? Express it in SQL if the functions exist; restructure as a join or grouping if it needs several geometries; otherwise transform the whole WKB array in one call and join the result back. can SQL express it? ST_ functions already cover a great deal if yes: write it in SQL vectorised, parallel, and the index can prune before it runs does it need several at once? nearest neighbour, dissolve, overlay against another layer if yes: a join or a GROUP BY SQL expresses “several at once” better than a Python structure genuinely per-geometry? a library call with no SQL equivalent at all then: one vectorised pass extract the WKB array, transform it whole, register the result back At no point does the tree arrive at a scalar UDF or a Python loop. Both are available and both are the wrong answer at every branch.

The middle branch is the one most often missed, because an operation that needs several geometries at once feels like it needs a program. Nearest neighbour, dissolve, overlay against another layer — all of them are naturally written as loops over a collection, and all of them are expressed better as a join or a grouping. Reaching for Python there does not just cost performance; it usually costs correctness too, because a hand-written nearest-neighbour loop has to reimplement the pruning the index would have done.

# The pattern for genuinely per-geometry work: one bulk extraction, one
# vectorised transformation, one registration back. The interpreter is
# entered once regardless of row count.
import pyarrow as pa
from shapely import from_wkb, to_wkb

tbl = con.execute("SELECT parcel_id, ST_AsWKB(geom) AS wkb FROM parcels").fetch_arrow_table()

geoms = from_wkb(tbl.column("wkb").to_numpy(zero_copy_only=False))
transformed = some_library_operation(geoms)          # whole array, one call

result = pa.table({"parcel_id": tbl.column("parcel_id"),
                   "wkb_out": pa.array([to_wkb(g) for g in transformed])})
con.register("transformed", result)

con.execute("""
    CREATE OR REPLACE TABLE parcels_out AS
    SELECT p.parcel_id, ST_GeomFromWKB(t.wkb_out) AS geom
    FROM parcels p JOIN transformed t USING (parcel_id)
""")

What a UDF costs the query around it

What a scalar Python UDF costs beyond its own runtime Per-row invocation, loss of vectorisation in the surrounding expression, no pushdown or index eligibility, GIL-limited parallelism, and an opaque plan node. COST WHY IT HAPPENS VISIBLE? per-row invocation the boundary is crossed each time yes — it is the obvious one surrounding expression de-vectorised the whole expression drops to row-at-a-time no no pushdown, no index the optimizer cannot see through it no — until the plan is read GIL-limited parallelism the Python side serialises no an opaque plan node one node replaces several operators in EXPLAIN, yes

One of the five is the reason people avoid UDFs. The other four are why they should.

The second and third rows are what make a UDF expensive out of proportion to its own body. An expression containing one is evaluated row at a time in its entirety, so the ST_Intersects next to it stops being a SIMD kernel; and a WHERE clause containing one cannot be pushed into a Parquet scan, so every row is read and decoded before the filter that would have eliminated it runs. A UDF in a projection is expensive. A UDF in a predicate is expensive and prevents the pruning that would have made the query cheap.

-- ANTI-PATTERN: the UDF is in the predicate, so nothing can be pruned and
-- every row in every row group is read and decoded before it is discarded.
SELECT parcel_id FROM read_parquet('parcels.parquet')
WHERE my_python_check(geom);

-- PATTERN: filter with what the engine understands first, and let the
-- expensive opaque check run only on what survives.
SELECT parcel_id FROM read_parquet('parcels.parquet')
WHERE land_use = 'residential'                -- pruned at the row group
  AND bbox_xmin >= 2.20 AND bbox_xmax <= 2.45 -- pruned at the row group
  AND my_python_check(geom);                  -- runs on a small fraction

Execution Plan Validation

Pushdown is visible in the plan, and its absence is the single most useful signal in this whole area. Two things to look for: whether the filter appears attached to the scan node or as a separate operator above it, and how many rows the scan emits compared with how many the file holds.

-- Expect the attribute and bbox predicates to appear ON the scan node, with
-- the UDF as a separate FILTER above it. If the UDF's predicate has been
-- pulled down into the scan, the plan is lying — it cannot be evaluated there.
EXPLAIN ANALYZE
SELECT count(*) FROM read_parquet('parcels.parquet')
WHERE land_use = 'residential' AND my_python_check(geom);

Diagnostic — a query that ignores extra threads: run the same statement at SET threads = 2 and SET threads = 8 and compare. A vectorised query improves markedly; one dominated by a Python UDF barely moves, because the interpreter lock serialises that half regardless. It is a cruder measurement than a profiler and it needs nothing installed.

Performance Trade-offs

The honest position is that a UDF is sometimes the right answer. When an operation has no SQL expression, when the row count is small, and when the alternative is a substantially more complex pipeline, a scalar UDF that costs an order of magnitude on ten thousand rows is a perfectly reasonable trade. What makes it a problem is applying that judgement, formed on ten thousand rows, to ten million.

The middle option — a vectorised pass over the WKB column, joined back — is where most real cases land, and it is worth understanding why it is only about nine times the SQL baseline rather than sixty. The interpreter is entered once. The per-geometry work happens in compiled code inside the library. What remains is a single-threaded pass over every row and two bulk transfers across the boundary, and those are proportional but not catastrophic.

Trade-off Analysis: The vectorised pass has one property the UDF does not: it cannot be pruned. A UDF at least runs where the query put it, so filtering before it reduces its input; a bulk extraction pulls the whole column out before Python sees any of it. On a query that filters away 95% of the rows, running a UDF after the filter can beat extracting the whole column — which is the one case where the slower-per-row option wins overall.

Keeping the Predicate Visible

Pushdown is a property of shape, not of intent, and the shapes that defeat it are unremarkable enough to survive review. A predicate is pushable when the optimizer can recognise a column on one side of a simple comparison against a constant. Anything that obscures that — a cast, a function, an expression combining two columns, a correlated subquery — makes the predicate opaque, and an opaque predicate is evaluated after the rows have been read rather than instead of reading them.

The distinction matters most on a Parquet scan, where a pushable predicate can eliminate row groups before decompression and an opaque one cannot eliminate anything at all. On a local file that is a difference in CPU; on object storage it is a difference in how many megabytes cross the network, which is usually the whole cost of the query.

-- Four predicates, same intent, two of them pushable.
WHERE year = 2024                          -- pushable: bare column, constant
WHERE year BETWEEN 2020 AND 2024           -- pushable: a range on a bare column
WHERE year::VARCHAR = '2024'               -- opaque: the cast hides the column
WHERE extract(year FROM loaded_at) = 2024  -- opaque: a function hides it

The remedy for the third and fourth is nearly always to materialise the derived value as a column at write time. A year column costs four bytes per row and makes every query against it prunable; deriving it per query costs a full scan every time. That is the same trade the partitioning and file layout reference makes for the bbox columns, and for the same reason: statistics exist for stored columns and not for expressions.

Trade-off Analysis: Materialising derived columns adds storage and adds a place for the derivation to drift out of step with its source. Against that, it is the only way to make the value prunable, and the drift is preventable with an assertion in the load. The cases where it is not worth it are derivations that are cheap to compute and rarely filtered on — and in practice, if a value is worth filtering on it is worth storing.

Ordering predicates so the opaque one runs last

Where an opaque predicate is unavoidable, its position relative to the others decides how much work it does. DuckDB will generally evaluate cheaper predicates first within a filter, but the reliable way to guarantee it is to make the pushable ones do their job at the scan, so the opaque one only ever sees survivors.

-- The opaque check is the last thing in the chain, and by the time it runs
-- the row-group statistics and the bbox comparison have already removed the
-- overwhelming majority of the input.
SELECT parcel_id
FROM read_parquet('s3://lake/parcels/**/*.parquet', hive_partitioning = true)
WHERE region = 'west'                          -- directory pruning
  AND year = 2024                              -- directory pruning
  AND land_use = 'residential'                 -- row-group statistics
  AND bbox_xmin >= 2.20 AND bbox_xmax <= 2.45  -- row-group statistics
  AND my_python_check(geom);                   -- opaque, on what is left

Edge Cases & Anti-Patterns

A UDF that closes over a large Python object. The object is captured per invocation context and can be re-serialised more often than expected. Keep UDFs pure and small; pass data in through the arguments or a registered table.

Returning geometry from a UDF as WKT. The engine has to parse it back on every row, which adds a second per-row cost on top of the first. Return WKB.

A UDF used as a GROUP BY key. This forces the grouping to evaluate the function per row and prevents any hash-aggregate optimisation the engine would otherwise apply. Materialise the key into a column first.

Assuming the UDF sees a whole vector. A scalar UDF sees one value. If the registration API offers a vectorised form, use it — it is the difference between the third and second rows of the cost table above.

Filtering in Python after fetching. This is the loop with an extra step: every row crosses the boundary and most are then discarded. Push the filter into SQL even when the final operation cannot be, which is what replacing Python loops with set-based SQL works through in detail.

Query Regression Analysis

Pushdown is exactly the kind of property that regresses silently: a refactor moves a predicate, a column gets wrapped in a cast, and a query that read 3% of a file starts reading all of it while still returning the right answer. Asserting it in a test is cheap because the plan carries the number.

def assert_pushdown(con, sql: str, max_rows_scanned: int) -> None:
    """Fail when a query starts reading more of the file than it used to.
    The row count emitted by the scan node is the thing that regresses; the
    result is identical either way, which is why runtime alone will not catch it."""
    plan = con.execute("EXPLAIN ANALYZE " + sql).fetchall()[0][1]
    scanned = _rows_at_scan_node(plan)          # parse the scan node's output count
    assert scanned <= max_rows_scanned, (
        f"pushdown regression: scan emitted {scanned} rows, budget {max_rows_scanned}"
    )

assert_pushdown(con, """
    SELECT count(*) FROM read_parquet('parcels.parquet')
    WHERE land_use = 'residential' AND my_python_check(geom)
""", max_rows_scanned=120_000)

The budget is set from a healthy run rather than from theory, and it is generous — the point is to catch an order-of-magnitude change, not to pin the number. A test that fails when the data grows by 10% gets disabled; one that fires when a predicate stops being pushed is the one worth having.

One sentence that summarises the whole page

Everything above reduces to a single rule with two clauses: run the logic where the data already is, and keep the predicate in a form the optimizer can read. The first clause is what stops a per-row boundary crossing; the second is what stops the query reading rows it was never going to keep. Every anti-pattern in this reference violates one or the other, and most violate both — a Python loop over fetched rows crosses the boundary for every row and fetches rows a pushable filter would have eliminated.

Frequently Asked Questions

Is a Python UDF ever the right choice?

Yes, when the operation has no SQL expression, the row count is modest, and the alternative is a materially more complex pipeline. What makes UDFs a problem is applying a judgement formed on ten thousand rows to ten million, and not noticing that the cost is superlinear in the surrounding query rather than linear in the function.

Why does my query not get faster with more threads?

Usually because something in it is not vectorised, and a scalar Python UDF is the most common cause — the interpreter lock serialises that half however many engine threads exist. Comparing runtime at two and eight threads is a crude but effective test for the presence of a per-row bottleneck.

Does a UDF in a WHERE clause prevent pushdown?

Yes, for that predicate, and the consequence is larger than it sounds: the rows the UDF would have rejected are still read from the file and decoded first. Put the predicates the engine understands — attribute filters, bbox comparisons — alongside it so they can prune, and let the opaque one run on what survives.

Should a UDF return WKB or a Shapely object?

WKB. Returning a Python object means the engine has to convert it on every row, and returning WKT means it has to re-parse text. WKB is the representation both sides already speak, and it is the only one that does not add a second per-row cost on top of the first.

How do I know whether pushdown is happening at all?

Read the row count the scan node emits in EXPLAIN ANALYZE and compare it against the file’s row count. Runtime alone will not tell you — a query that reads the whole file and one that reads 3% of it return the same answer, and on a warm cache they can take comparable time on a small dataset and then diverge by two orders of magnitude in production.

Can I vectorise a UDF instead of avoiding it?

Where the driver offers a vectorised registration form, yes, and it removes the largest part of the cost — the interpreter is entered once per batch rather than once per row. What it does not remove is opacity: the optimizer still cannot see through the function, so a predicate involving it still cannot be pushed into a scan.

See also

Up: Python & DuckDB Integration Workflows