Indexing and Planning on Partitioned Spatial Tables

Problem Statement

The partitions exist, the data is flowing, and the dashboard is still slow. Both of the usual causes are invisible without looking: a partition somewhere has no spatial index, or the query is not pruning and is quietly searching all forty-eight. This page covers the two mechanics that resolve both — attaching indexes to a partitioned spatial table safely, and reading a partitioned plan precisely enough to know which case you are in.

Why the Naive Approach Fails

CREATE INDEX ... ON vehicle_positions USING gist (geom) on a partitioned parent is valid SQL and does what you asked: it builds a GiST index on every partition. It also takes a lock on every partition for the duration of that partition’s build, and there is no CONCURRENTLY form for a partitioned parent. On a pyramid holding four years of data, that is hours during which each partition in turn refuses writes.

Two ways to index a pyramid The parent build locks each partition in turn for the whole three-hour run. The per-partition approach builds each index concurrently with no lock, then attaches them all in a final step measured in milliseconds. Indexing 48 partitions, with and without locks CREATE INDEX on the parent … each partition locked while its index builds writes to the current partition fail for the whole window CONCURRENTLY per partition, then ATTACH … then one ATTACH per index — milliseconds writes continue throughout; the run can be paused between partitions

The second failure is subtler. Because the parent index is only a template, forgetting to attach one partition leaves that partition unindexed while the parent looks complete in every tool that inspects the parent. The month rolls over, queries hit the unindexed partition, and the regression looks like it came from nowhere.

Production-Ready Implementation

A rolling build that is safe to stop and resume, driven from Python:

python
import psycopg

PARENT = "vehicle_positions"


def partitions(conn) -> list[str]:
    with conn.cursor() as cur:
        cur.execute("""
            SELECT c.relname
            FROM pg_class c
            JOIN pg_inherits i ON i.inhrelid = c.oid
            WHERE i.inhparent = %s::regclass
            ORDER BY c.relname
        """, (PARENT,))
        return [r[0] for r in cur.fetchall()]


def has_gist(conn, table: str) -> bool:
    with conn.cursor() as cur:
        cur.execute("""
            SELECT EXISTS (
                SELECT 1 FROM pg_index x
                JOIN pg_class ic ON ic.oid = x.indexrelid
                JOIN pg_am am    ON am.oid = ic.relam
                WHERE x.indrelid = %s::regclass AND am.amname = 'gist'
            )
        """, (table,))
        return cur.fetchone()[0]


def build_indexes(dsn: str) -> list[str]:
    """Build a GiST index on every partition that lacks one. Resumable."""
    built = []
    with psycopg.connect(dsn, autocommit=True) as conn:   # CONCURRENTLY needs autocommit
        for part in partitions(conn):
            if has_gist(conn, part):
                continue
            with conn.cursor() as cur:
                cur.execute(
                    f"CREATE INDEX CONCURRENTLY {part}_geom_idx "
                    f"ON {part} USING gist (geom)"
                )
            built.append(part)
    return built

The idempotence is the important property: the function can be run repeatedly, and each run picks up wherever the last one stopped. A failed concurrent build leaves an invalid index behind, so pair this with the invalid-index sweep from concurrent index builds before re-running.

Attachment is a separate, fast step:

sql
CREATE INDEX vehicle_positions_geom_idx
    ON ONLY vehicle_positions USING gist (geom);   -- template, starts invalid

DO $$
DECLARE part text;
BEGIN
    FOR part IN
        SELECT c.relname FROM pg_class c
        JOIN pg_inherits i ON i.inhrelid = c.oid
        WHERE i.inhparent = 'vehicle_positions'::regclass
    LOOP
        EXECUTE format(
            'ALTER INDEX vehicle_positions_geom_idx ATTACH PARTITION %I',
            part || '_geom_idx');
    END LOOP;
END $$;

Once the last partition index is attached, indisvalid on the parent flips to true by itself. Nothing else marks completion, which is why the verification query below is worth keeping in monitoring rather than running once.

Reading a Partitioned Plan

Three plan shapes correspond to the three pruning outcomes, and telling them apart is a matter of looking in the right place.

What each pruning outcome looks like in EXPLAIN Three annotated plan excerpts. With plan-time pruning the Append node lists one child. With run-time pruning it lists all children but most report never executed. With no pruning every child reports real rows and timings, and the total time is the sum. Look at the Append node's children Append (actual rows=412 loops=1) -> Index Scan on vehicle_positions_2026_08 (actual rows=412) plan-time pruning — one child listed, 47 never appear Append (actual rows=412 loops=1) -> Index Scan on vehicle_positions_2024_01 (never executed) run-time pruning — all children listed, most never executed Append (actual rows=412 loops=1) -> Index Scan on vehicle_positions_2024_01 (actual rows=0 time=8.1) no pruning — every child really ran, and cost real time to return nothing

The third case is the one to hunt for. A child that reports actual rows=0 alongside a non-zero time did genuine work to discover it had nothing to contribute — forty-seven of those are where the missing milliseconds went.

EXPLAIN without ANALYZE cannot distinguish the second case from the third, because “never executed” is a run-time observation. When diagnosing a partitioned query, always run the analyze form.

Planning time grows with partition count even when pruning works Planning time for the same pruned query at four partition counts: 0.2 milliseconds at 12 partitions, 0.6 at 48, 3.1 at 365 and 14 at 1,460. Execution time stays flat at about 4 milliseconds throughout, so planning eventually dominates. Planning cost by partition count 12 0.2 ms planning · 4 ms execution 48 0.6 ms · still irrelevant 365 3.1 ms · now comparable to execution 1,460 14 ms planning At four years of daily partitions the planner costs more than the query. Prefer monthly.

Configuration and Tuning Knobs

enable_partition_pruning defaults to on and should stay there. It exists mainly so you can turn it off to confirm that pruning is what is helping.

plan_cache_mode matters for prepared statements. With the default auto, PostgreSQL may switch to a generic plan after five executions, and a generic plan cannot prune at plan time — the query silently moves from the first case to the second. That is usually fine, but if planning time is negligible and pruning is everything, plan_cache_mode = force_custom_plan on that session keeps plan-time pruning.

constraint_exclusion is the legacy mechanism and applies to inheritance-based partitioning, not declarative. Leaving it at partition is correct; changing it will not affect a declaratively partitioned table.

Verification Steps

sql
-- every partition has a GiST index, and the parent template is valid
SELECT c.relname AS partition,
       EXISTS (
           SELECT 1 FROM pg_index x
           JOIN pg_class ic ON ic.oid = x.indexrelid
           JOIN pg_am am ON am.oid = ic.relam
           WHERE x.indrelid = c.oid AND am.amname = 'gist'
       ) AS has_gist
FROM pg_class c
JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE i.inhparent = 'vehicle_positions'::regclass
ORDER BY 2, 1;

SELECT indisvalid FROM pg_index
WHERE indexrelid = 'vehicle_positions_geom_idx'::regclass;

Then confirm pruning on the query that matters, with real parameter values substituted rather than placeholders — a plan for $1 tells you about the generic case, not about the query your application runs.

Partition-wise joins, briefly

When two tables are partitioned on the same key with matching bounds, PostgreSQL can join them partition by partition instead of joining the whole appended sets. For a spatial join between, say, positions and events both partitioned by month, that turns one enormous join into forty-eight small ones, each of which fits comfortably in work_mem.

It is off by default because it increases planning time, and it applies only when the partition bounds match exactly. Turn it on for the session that runs such a join rather than globally:

sql
SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;

Gotchas Checklist

  • CREATE INDEX CONCURRENTLY cannot target a partitioned parent. It works only on individual partitions, which is why the rolling build exists.
  • A detached partition keeps its indexes. Re-attaching it later re-uses them, so a partition detached for archival and re-attached does not need rebuilding — but the parent template must be re-attached too.
  • ANALYZE on the parent does not always cascade. After a bulk load into one partition, analyse that partition explicitly or its statistics stay empty and its estimates stay wrong.
  • Partition-wise joins are off by default. enable_partitionwise_join can be a large win when joining two tables partitioned the same way, and does nothing otherwise; it is off because it increases planning time.
  • Index names must be unique across the database. With forty-eight partitions that means a naming convention, and the convention should encode the partition so a slow-plan investigation can find the index from the plan text.