Indexing trajectories in PostGIS for space-time range queries

-- Parent table. The partition key must be part of the primary key in a
-- declaratively partitioned table — this trips up most first attempts.
CREATE TABLE fix (
    entity_id  text        NOT NULL,
    t          timestamptz NOT NULL,
    geom       geometry(Point, 32631) NOT NULL,   -- projected, metric
    speed_ms   real,
    quality    smallint,
    PRIMARY KEY (entity_id, t)
) PARTITION BY RANGE (t);

-- One partition per day. Create them ahead of ingestion, not on demand:
-- an INSERT with no matching partition fails rather than creating one.
CREATE TABLE fix_2026_05_14 PARTITION OF fix
    FOR VALUES FROM ('2026-05-14 00:00+00') TO ('2026-05-15 00:00+00');

-- GiST for geometry. Only spatial operators (&&, ST_DWithin, ST_Intersects)
-- use it; ST_Distance(...) < x in the WHERE clause does NOT.
CREATE INDEX fix_2026_05_14_geom_gix
    ON fix_2026_05_14 USING GIST (geom);

-- BRIN on the timestamp: tiny, and effective only because rows inside the
-- partition are in time order. On unclustered data BRIN is useless.
CREATE INDEX fix_2026_05_14_t_brin
    ON fix_2026_05_14 USING BRIN (t) WITH (pages_per_range = 32);

-- Physically order the partition once after load. CLUSTER takes an
-- ACCESS EXCLUSIVE lock, so do it on the day's partition after ingestion
-- completes, never on the live one.
CLUSTER fix_2026_05_14 USING fix_2026_05_14_t_brin;
ANALYZE fix_2026_05_14;

-- The query shape that prunes. Note the partition key appears as a plain
-- range predicate: the planner needs t itself, not a function of t.
EXPLAIN (ANALYZE, BUFFERS)
SELECT entity_id, t, geom
FROM   fix
WHERE  t >= '2026-05-14 14:00+00'
  AND  t <  '2026-05-14 15:00+00'
  AND  ST_DWithin(
          geom,
          ST_SetSRID(ST_MakePoint(587400, 5760200), 32631),
          1200                                    -- metres, because 32631
       );
-- Look for "Subplans Removed" or a single partition in the plan. If every
-- partition appears, the predicate is not prunable — the usual cause is
-- date_trunc('hour', t) or t AT TIME ZONE '...' on the left-hand side.

import psycopg


def assert_pruned(conn: psycopg.Connection, sql: str, params: tuple,
                  max_partitions: int = 2) -> dict:
    """
    Run EXPLAIN ANALYZE and assert the planner pruned to a few partitions.

    A query that returns correct rows while scanning every partition is the
    failure this catches — it is invisible in the results and obvious in the
    plan, and it gets slower every day as the archive grows.

    Raises
    ------
    AssertionError
        If more than max_partitions were scanned, or a sequential scan
        appears on a partition.
    """
    with conn.cursor() as cur:
        cur.execute("EXPLAIN (ANALYZE, FORMAT JSON) " + sql, params)
        plan = cur.fetchone()[0][0]["Plan"]

    seen, seq_scans = set(), []

    def walk(node):
        rel = node.get("Relation Name")
        if rel and rel.startswith("fix_"):
            seen.add(rel)
            if node.get("Node Type") == "Seq Scan":
                seq_scans.append(rel)
        for child in node.get("Plans", []):
            walk(child)

    walk(plan)
    assert len(seen) <= max_partitions, (
        f"scanned {len(seen)} partitions {sorted(seen)[:4]}… — the predicate "
        "is not prunable; check for a function wrapping the partition key"
    )
    assert not seq_scans, f"sequential scan on {seq_scans} — missing or unused index"
    return {"partitions": sorted(seen), "actual_rows": plan.get("Actual Rows")}

def validate_index_health(conn, table: str) -> dict:
    """Correlation is what makes BRIN work; check it rather than assuming it."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT correlation FROM pg_stats WHERE tablename = %s AND attname = 't'",
            (table,),
        )
        row = cur.fetchone()
    corr = float(row[0]) if row and row[0] is not None else 0.0
    # Near 1.0 means physical order matches time order and BRIN prunes well.
    # Below ~0.9, BRIN is close to useless and a B-tree is the better trade.
    assert abs(corr) > 0.9, (
        f"{table}: t correlation {corr:.2f} — re-CLUSTER after load, or the "
        "BRIN index is costing space and saving nothing"
    )
    return {"table": table, "t_correlation": corr}