DuckDB Spatial Analytical SQL for GIS

Verifying Predicate Pushdown into Parquet Scans

A query that reads the whole file and one that reads three per cent of it return exactly the same answer, which is why pushdown regresses without anyone noticing — this walkthrough, part of the Python spatial UDFs and predicate pushdown reference, covers the numbers in a scan node that reveal what was actually read, the ordinary refactors that silently remove pushdown, and how to assert the property in a test so a change costing two orders of magnitude fails the build rather than the quarter.

Root-Cause Analysis: why pushdown disappears without a symptom

Pushdown is a property of the shape of a predicate rather than of its meaning, and every way of losing it preserves the meaning exactly.

  • The predicate stopped being recognisable. A cast, a function, or an expression combining two columns leaves the optimizer with nothing it can compare against a stored statistic. The filter still runs; it runs after the rows are in memory.
  • The literal stopped being a literal. A constant is known when the scan is planned and can prune. A scalar subquery is not, so the planner assumes the worst and reads everything.
  • The column stopped being a column. A value derived in the query — a year extracted from a timestamp, a distance computed between two points — has no statistics, because statistics exist for stored columns and not for expressions.
  • The file stopped being prunable. A rewrite in arrival order, or a compaction that produced one enormous row group, leaves statistics that span the whole range and therefore exclude nothing.
  • The predicate moved. A condition relocated into a join clause, a view, or an application-side filter is applied at a different point in the plan, and only one of those points is the scan.

The distinguishing question is not how long the query took — on a warm cache over a small file the two cases are indistinguishable — but how many rows the scan node emitted. That number is in the plan, and it is the only signal that is stable across machines.

Five places the same filter can end up Directory pruning, row-group statistics, page statistics, post-decode filtering and client-side filtering, from free to most expensive. WHERE THE FILTER LANDS WHEN IT RUNS COST directory (partition column) before any file is opened free row group (sorted column) after the footer, before decompress very cheap page statistics within a chunk, where present cheap post-decode FILTER after rows are read and decoded the whole scan client side, in Python after rows cross the boundary the scan plus the transfer

The same logical condition can land in any of these. How it is written decides which.

Deterministic Configuration 

import duckdb

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

# Pushdown is about I/O, so cache state has to be controlled or the
# measurement measures the cache instead.
con.execute("SET enable_object_cache = false")
con.execute("SET threads = 4")

Optimized Execution Pattern

The anti-pattern is measuring runtime, because runtime conflates pruning with caching, parallelism and whatever else the machine was doing.

-- ANTI-PATTERN: timing it. Over a warm cache and a small file this reports
-- the same number whether the scan read 3% of the data or all of it.
.timer on
SELECT count(*) FROM read_parquet('parcels.parquet') WHERE land_use = 'residential';
-- PATTERN: read what the scan actually did, against what the file contains.
-- The ratio is independent of cache state, thread count and machine load.
SELECT sum(row_group_num_rows) AS rows_in_file,
       count(DISTINCT row_group_id) AS groups_in_file
FROM parquet_metadata('parcels.parquet');

EXPLAIN ANALYZE
SELECT count(*) FROM read_parquet('parcels.parquet') WHERE land_use = 'residential';

A selective predicate that prunes well emits a small multiple of the answer size from the scan. One that is not pushed emits the entire file and lets a FILTER operator above the scan do the work that the statistics should have done for free.

Reading pushdown out of a scan node Files read, row groups read, rows emitted, the filters attached to the scan, and any FILTER operator above it. WHAT TO READ WHAT IT REVEALS GOOD LOOKS LIKE files read vs total directory pruning a small fraction row groups read vs total statistics pruning a small fraction rows emitted vs rows in groups page and decode filters close to the answer size filters attached to the scan what was pushed your selective predicates a FILTER operator above it what was not only the opaque ones

Five numbers, all in one node, and none of them is runtime.

Where the numbers come from

parquet_metadata reports the file’s own structure — one row per column chunk, with its row group, row count and statistics — so the denominators are available without reading any data at all. The numerators come from the plan. Together they turn a vague impression that a query “feels slow” into a ratio that can be recorded, compared across runs, and asserted against in a test.

One subtlety is worth knowing: the scan node’s emitted-row count already reflects page-level filtering wherever the writer emitted page statistics, so it can be lower than the row count of the surviving row groups. That makes the ratio a slight understatement of how much was read rather than an overstatement, which is the safe direction for a budget to err in.

Diagnostic Queries & Plan Validation

Two checks, run together, separate the three things that can be wrong: the predicate, the file, or neither.

-- Check 1 — is the predicate pushable? A pushable one appears in the scan
-- node's filter list; an opaque one appears in a FILTER above it.
EXPLAIN
SELECT count(*) FROM read_parquet('parcels.parquet')
WHERE land_use = 'residential' AND year::VARCHAR = '2024';   -- one of these is not

-- Check 2 — is the file prunable? A file whose row groups each span the whole
-- value range cannot be pruned however the predicate is written.
SELECT count(DISTINCT row_group_id)                     AS groups,
       min(min_value)                                   AS overall_min,
       max(max_value)                                   AS overall_max,
       avg(max_value::DOUBLE - min_value::DOUBLE)       AS avg_group_span
FROM parquet_metadata('parcels.parquet')
WHERE path_in_schema = 'year';

If avg_group_span approaches the overall range, the file was written in an order that makes pruning impossible, and no amount of predicate rewriting will help — the fix is a re-sort on write, as sorting writes with Hilbert curves sets out for the spatial case.

Four refactors that silently remove pushdown A cast, an un-inlined view, a scalar subquery in place of a literal, and a two-column expression each make a predicate opaque without changing the result. REFACTOR WHY IT BREAKS PUSHDOWN RESULT CHANGES? a cast added to the column the column is hidden no moved into a view or CTE if the planner does not inline it no a scalar subquery for the literal the value is unknown at plan time no two columns in one expression no single statistic can evaluate it no

None of the four changes an answer. All four change how much of the file is read.

Geometry Validation & Fallback Routing

The assertion worth keeping is a budget on rows scanned rather than on elapsed time, because the budget is stable across machines and the time is not.

import re

def rows_scanned(con, sql: str) -> int:
    """The scan node's emitted-row count, pulled out of an EXPLAIN ANALYZE plan.
    Deliberately crude — plan formatting changes between versions, so this
    looks for the first row count under the scan rather than parsing the tree."""
    plan = con.execute("EXPLAIN ANALYZE " + sql).fetchall()[0][1]
    block = plan.split("PARQUET_SCAN", 1)[-1]
    m = re.search(r"(\d[\d,]*) Rows", block)
    return int(m.group(1).replace(",", "")) if m else -1

BUDGET = 150_000        # recorded from a healthy run; generous on purpose
scanned = rows_scanned(con, """
    SELECT count(*) FROM read_parquet('parcels.parquet')
    WHERE land_use = 'residential' AND year = 2024
""")
assert 0 <= scanned <= BUDGET, f"pushdown regression: scanned {scanned}, budget {BUDGET}"

The budget is deliberately loose. Its job is to catch an order-of-magnitude change, not to pin an exact figure — a test that fails when the dataset grows ten per cent gets disabled within a month, and one that fires when a predicate stops being pushed is the one worth keeping.

Frequently Asked Questions

Why not just measure the runtime?

Because runtime conflates pruning with cache state, thread count and machine load. Over a warm cache and a small file, reading three per cent and reading everything can take the same time — and then diverge by two orders of magnitude in production, where the file is larger and the cache is cold.

What ratio should I expect?

It depends entirely on the selectivity of the predicate, which is why the useful form is a budget recorded from a healthy run rather than a universal figure. What matters is the order of magnitude: a query that used to emit a hundred thousand rows from the scan and now emits ten million has lost its pushdown whatever the absolute numbers are.

Does a cast really disable it?

Yes, and it is the single most common cause. The optimizer matches a bare column against a stored statistic; a cast produces an expression, and no statistic exists for an expression. The same applies to any function wrapping the column, however trivial that function is.

Can a spatial predicate be pushed?

Not directly, because geometry statistics are over serialised bytes and mean nothing spatially. What is pushable is a comparison against plain numeric bbox columns written alongside the geometry, which is why that pattern recurs throughout partitioning and file layout.

The predicate is pushable and nothing is pruned — now what?

Then the file is the problem rather than the query. Check the average per-group span of the filtered column: if each row group covers the whole value range, the write order made pruning impossible, and the fix is a re-sort rather than anything in the SQL.

Should this be a test or a dashboard?

A test. The value of the measurement is that it fails on the commit that introduces the regression, when the cause is still obvious. A dashboard tells you that something changed at some point in the last month, which is a far more expensive way to learn the same fact.

Up: Python Spatial UDFs and Predicate Pushdown