SpatiaLite & GeoPackage Automation with Python

How to Benchmark a Spatial Query Regression

Assert the query plan, not the elapsed time. EXPLAIN QUERY PLAN tells you whether the R-tree is being used, and that assertion is stable on a two-row…

Assert the query plan, not the elapsed time. EXPLAIN QUERY PLAN tells you whether the R-tree is being used, and that assertion is stable on a two-row fixture, deterministic across machines, and fails for exactly one reason. Wall-clock benchmarks belong in a separate suite with a large committed fixture, where they can be compared against a recorded baseline instead of a threshold someone guessed.

This page belongs to the Testing & CI for Spatial Pipelines guide.

Why This Matters

The regression this catches is specific and common: a query that used to hit the spatial index and now does not. It happens when someone rewrites a search_frame subquery as a direct ST_Intersects, when a bulk load drops the index and nothing rebuilds it, or when a layer is queried in a reference system other than the one it is stored in.

The symptom is not an error. The query returns the right rows, slowly — imperceptibly on a test fixture, and painfully on a field device with half a million parcels. Timing tests are the obvious response and the wrong one, because on a small fixture the difference is microseconds and on shared CI hardware the noise is larger than the signal.

Prerequisites

  • Python 3.9+ with sqlite3, and mod_spatialite for the SpatiaLite idiom
  • A container with a spatial index, per How to Create a Spatial Index in SQLite with Python
  • A separate large fixture if you want timing comparisons as well
  • pytest markers, so the slow suite is excluded by default

Primary Method

python
# Assert that the planner reaches the R-tree — stable on any fixture size
import re
import sqlite3


def query_plan(conn: sqlite3.Connection, sql: str, params=()) -> list[str]:
    return [row[-1] for row in conn.execute("EXPLAIN QUERY PLAN " + sql, params)]


def uses_spatial_index(conn, sql, params=(), table="parcels", geom="geom") -> bool:
    """True when the plan mentions the R-tree virtual table for this layer."""
    plan = " | ".join(query_plan(conn, sql, params))
    pattern = rf"\b(idx_{table}_{geom}|rtree_{table}_{geom})\b"
    return re.search(pattern, plan) is not None


def test_bbox_query_uses_the_index(parcels_master):
    conn = sqlite3.connect(f"file:{parcels_master}?mode=ro", uri=True)
    sql = """
        SELECT p.fid FROM parcels p
        JOIN rtree_parcels_geom r ON r.id = p.fid
        WHERE r.maxx >= ? AND r.minx <= ? AND r.maxy >= ? AND r.miny <= ?
    """
    assert uses_spatial_index(conn, sql, (0, 10, 0, 10)), (
        "plan did not reach the R-tree:\n  " + "\n  ".join(query_plan(conn, sql, (0, 10, 0, 10)))
    )

Including the plan in the assertion message is what makes a failure actionable. The plan is three or four lines and says exactly what the planner chose instead.

Why a plan assertion beats a timing assertionA plan assertion is deterministic, works on a two-row fixture, is unaffected by shared CI hardware, and fails for exactly one reason. A timing assertion needs a large fixture to produce a measurable difference, varies with machine load, needs a threshold somebody guessed, and fails for many reasons including none of them being the index. Both are checking the same regression.plan assertionworks on a two-row fixturedeterministic across machinesunaffected by CI loadfails for exactly one reasontiming assertionneeds a large fixturevaries with machine loadthreshold is a guessfails for many reasonsboth are checking the same regression, and only one of them can live in the fast suite
Timing is not useless — it belongs in a separate suite against a stable fixture, where its noise can be managed.

Step-by-Step Walkthrough

1. Capture the plan for the query you care about

python
conn = sqlite3.connect("parcels.gpkg")
conn.enable_load_extension(True)
conn.load_extension("mod_spatialite")

sql = """
    SELECT p.fid, p.parcel_ref FROM parcels p
    WHERE p.ROWID IN (
        SELECT ROWID FROM SpatialIndex
        WHERE f_table_name = 'parcels'
          AND search_frame = BuildMbr(?, ?, ?, ?, 27700)
    )
"""
for line in query_plan(conn, sql, (400000, 300000, 401000, 301000)):
    print(line)

A healthy plan names the index virtual table. An unhealthy one says SCAN parcels, and that single word is the regression.

2. Assert on the plan shape, not its exact text

Plan text varies between SQLite versions — SCAN TABLE parcels became SCAN parcels, and the details of subquery reporting have shifted more than once. Match on the index name, which is stable, rather than on the surrounding words.

python
# Stable: the index table name appears in the plan
assert re.search(r"\bidx_parcels_geom\b", plan_text)

# Fragile: exact plan wording, which changes between SQLite releases
assert "SEARCH TABLE idx_parcels_geom USING PRIMARY KEY" in plan_text

3. Add a negative assertion

Asserting the index is used is only half the check. Asserting a full scan is not present catches the case where the plan mentions the index for one part of the query and scans for another — a real pattern when a query has both a spatial and an attribute predicate.

python
def test_no_full_scan_of_the_feature_table(parcels_master):
    plan = " | ".join(query_plan(conn, sql, params))
    assert "SCAN parcels" not in plan, f"full table scan present:\n{plan}"

4. Count rows examined for a size-independent signal

Where the plan alone is ambiguous, SQLite’s status counters give a direct measure that is still deterministic — it does not depend on how fast the machine is, only on how much work was done.

python
# Rows examined is a better proxy for cost than elapsed time
import sqlite3

conn.execute("PRAGMA count_changes = OFF")
before = conn.execute("SELECT * FROM pragma_stats").fetchall()  # if available

cur = conn.execute(sql, params)
rows = cur.fetchall()

# A bounding-box query over a small window must not have examined
# a number of rows comparable to the table size.
total = conn.execute("SELECT count(*) FROM parcels").fetchone()[0]
assert len(rows) < total * 0.1, (
    f"query returned {len(rows)} of {total} rows — the window may be wrong, "
    "or the filter is not selective"
)
Three signals, ordered by how stable they areThe query plan is fully deterministic and works on any fixture size, making it the right check for the fast suite. Rows examined is deterministic but needs a fixture large enough for the number to be meaningful. Elapsed time varies with machine load and needs both a large fixture and a recorded baseline, which is why it belongs in a separate suite.query plan — fully deterministicany fixture size · any machine · belongs in the fast suiterows examined — deterministic, needs volumemachine-independent, but meaningless on a three-row fixtureelapsed time — needs volume and a baselinevaries with load · separate suite, compared against a recorded run
Each row down the list buys more realism and loses determinism; only the top one belongs on every commit.

5. Keep timing in its own suite, against a baseline

When you do want timing — and for a real performance investigation you do — record a baseline rather than asserting a threshold.

python
# tests/bench/test_query_timing.py
import json, time
from pathlib import Path

BASELINE = Path(__file__).parent / "baseline.json"


@pytest.mark.slow
def test_bbox_query_has_not_regressed(large_parcels, request):
    conn = sqlite3.connect(f"file:{large_parcels}?mode=ro", uri=True)

    best = min(_time_once(conn) for _ in range(7))   # best of 7, not the mean
    baseline = json.loads(BASELINE.read_text()).get("bbox_query_s")

    if baseline is None or request.config.getoption("--update-baseline"):
        BASELINE.write_text(json.dumps({"bbox_query_s": best}, indent=2))
        pytest.skip("baseline recorded")

    assert best < baseline * 2.0, (
        f"query took {best:.4f}s against a baseline of {baseline:.4f}s"
    )

Best-of-seven rather than the mean, and a factor-of-two tolerance rather than a percentage: both concessions to the fact that shared CI hardware is noisy, and both keep the test from failing for reasons that have nothing to do with the code.

Verification

The plan assertion must be shown to fail when the index is absent.

python
def test_plan_assertion_catches_a_missing_index(parcels, tmp_path):
    """Drop the index; the plan assertion must notice."""
    conn = sqlite3.connect(parcels)
    conn.enable_load_extension(True)
    conn.load_extension("mod_spatialite")
    conn.execute("SELECT DisableSpatialIndex('parcels', 'geom')")
    conn.execute("DROP TABLE IF EXISTS idx_parcels_geom")
    conn.commit()

    assert not uses_spatial_index(conn, SQL, PARAMS), (
        "the assertion passed with no index present — it is not checking anything"
    )

That negative test is the whole reason to trust the positive one, and it costs a fixture copy and two statements.

How a query loses its indexThree ways a working indexed query stops using the index. A rewrite that replaces the bounding-box subquery with a direct spatial predicate leaves the planner nothing to work with. A bulk load that drops the triggers and is never followed by a rebuild leaves the index present but empty. A query issued in a different reference system than the layer is stored in cannot match the stored bounding boxes at all. All three return correct rows, slowly.a rewritesubquery replaced bya direct predicateplanner cannot infer a boxa bulk loadtriggers dropped for speedrebuild never ranindex present but emptya CRS mismatchquery in another systemboxes cannot matchindex skipped entirelyall three return correct rowswhich is why only a plan assertion notices
None of the three is a correctness bug, so no assertion about results will ever catch them.

Alternative Approaches or Edge Cases

sqlite3_analyzer and ANALYZE. Running ANALYZE populates statistics the planner uses, and a container without them can produce a worse plan than the same container with them. If your production containers are analysed, analyse the fixture too — otherwise the test measures a planner working with less information than the real one has.

GeoPackage versus SpatiaLite idioms. The two express a bounding-box query differently: SpatiaLite through the SpatialIndex virtual table and a search_frame, GeoPackage through a join on the rtree_ table’s coordinate columns. The plan assertion needs to know which, so detect the container flavour rather than hard-coding one.

Queries with attribute predicates too. A query filtering on both geometry and an attribute may legitimately use the attribute index instead, and that can be the better plan. Assert that some index is used and that no full scan of the feature table appears, rather than insisting on the spatial one specifically.

Troubleshooting

The plan mentions the index but the query is still slow

Cause: The index is being used and is not selective — a bounding box covering most of the layer returns most of the layer. Fix: This is not an index problem. Check the window; if it is genuinely large, the cost is in the exact predicate applied to the survivors, and reducing vertex count helps more than anything about the index.

The assertion fails only on the large fixture

Cause: The planner chose differently with statistics present, or the small fixture is small enough that a scan is genuinely cheaper and SQLite knows it. Fix: Run ANALYZE on both fixtures so the planner has comparable information, and prefer asserting on the large fixture where the choice is meaningful.

Plan text differs between developer machines

Cause: Different SQLite versions wording the plan differently. Fix: Match on the index name only, as in step 2, and pin the SQLite version as described in Pinning GDAL, PROJ and GEOS Versions in CI.

Frequently Asked Questions

Is it worth benchmarking at all if plans are asserted?

Yes, for a different purpose. Plan assertions catch a structural regression; benchmarks catch a gradual one — a layer growing past the point where the current approach works, or a change that keeps the index and doubles the per-row cost. Run them nightly against a stable fixture, compare against a recorded baseline, and treat a move as something to investigate rather than as a build failure.

How large should the benchmark fixture be?

Large enough that the operation takes tens of milliseconds rather than microseconds, which for a bounding-box query usually means a hundred thousand features or so. Beyond that the numbers get more stable and the suite gets slower, and the stability gain flattens quickly. Commit it, with a regeneration script, so the baseline stays comparable across runs.

Should the plan assertion run against production containers?

Running it as a periodic check against a copy of a production container is genuinely useful, because it catches the case where the code is unchanged and the data lost its index. That is a monitoring concern rather than a testing one, and it fits naturally alongside the stale-index check in Data Quality Gates & Monitoring.