Consolidate timed_belief indexes: pin and reorder the primary key, drop redundant indexes

[Flix6x]

Context

While profiling #2332 (narrowing the timed_belief value columns to float4, since closed) against a realistic dataset, it became clear that the storage and performance opportunity on this table is not in the value columns at all — it is in its indexes. timed_belief typically carries several times more index than heap, and a large share of that is either redundant or badly ordered.

Everything below was measured on a representative dataset; figures are given as ratios rather than absolutes.

1. The primary key's column order is incidental, not designed

timely_beliefs' TimedBeliefDBMixin marks five columns primary_key=True and lets SQLAlchemy derive the order from declaration order. That means attributes declared on the subclass are collected first, then the mixin's — so the key's shape depends on which columns a host happens to redeclare, and it changes if anyone adds a column override.

Two consequences:

• A create_all() schema can disagree with a migrated one. FlexMeasures' migrations build one order; the ORM produces another. Deployments built each way have differently-shaped primary keys, and nothing catches it.
• The resulting order is poor. It does not lead with sensor_id, which nearly every query filters on, so the key cannot serve those queries at all.

Fix: pin the order explicitly with a PrimaryKeyConstraint, and choose it deliberately:

(sensor_id, source_id, event_start, belief_horizon, cumulative_probability)

• sensor_id first — virtually every query filters on a single sensor.
• source_id second — makes (sensor_id, source_id, event_start, belief_horizon) a prefix of the key, so a separate composite index on exactly those columns becomes redundant (see §2).
• cumulative_probability last — it is very nearly a constant (0.5 for every deterministic belief), so it contributes no selectivity wherever it sits.
• sensor_id and source_id adjacent — both are 4-byte integers, so keeping them together avoids alignment padding in every index tuple. Measured at ~15% of the index's size, which was the single most surprising result of the exercise.

The column set is unchanged, so uniqueness semantics are identical and on_conflict_do_update (which takes index_elements as a set) is unaffected.

Crucially, a reorder rebuilds an index but does not rewrite the table. No heap pages are touched, so it can be done nearly online with CREATE UNIQUE INDEX CONCURRENTLY followed by ADD CONSTRAINT ... USING INDEX, taking only a brief catalog-only exclusive lock. It is also fully reversible.

→ PR #2378 does this.

2. A large composite index becomes redundant

Deployments that have added a composite index on (sensor_id, source_id, event_start, belief_horizon) — a natural thing to add, precisely because the primary key does not lead with sensor_id — no longer need it once the key is reordered, since it is a strict prefix. In the dataset profiled this was among the largest indexes on the table and by far the most frequently used, so dropping it is the single biggest win here, and the queries that relied on it get faster, because they are served by a key that no longer needs a second structure maintained alongside it on every insert.

The migration in #2378 drops it, but only after verifying its definition matches, so an index that merely shares the name is left alone.

3. Two single-column indexes are entirely redundant

timely_beliefs declares index=True on both event_start and sensor_id, producing two single-column indexes. Both are fully covered by composite indexes the same mixin creates:

• (event_start) → covered by (event_start, sensor_id, source_id) INCLUDE (belief_horizon)
• (sensor_id) → covered by (sensor_id, event_start)

In the profiled deployment neither had ever been used for a single index scan, while every other index on the table had scan counts in the hundreds of thousands to millions — so this is not a case of young statistics. Together they accounted for a meaningful fraction of the table's index footprint, for no benefit.

This one needs an upstream change, since the index=True flags live in timely_beliefs — dropping them only in a FlexMeasures migration would recreate exactly the ORM/database divergence §1 is meant to cure. Opened upstream as SeitaBV/timely-beliefs#244.

Before dropping, each deployment should confirm on its own database:

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)), idx_scan
  FROM pg_stat_user_indexes WHERE relname = 'timed_belief'
 ORDER BY pg_relation_size(indexrelid) DESC;

SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

and check read replicas, which keep their own counters and may be using an index the primary is not.

4. Is the event_start-leading search index still earning its keep? — measured

timely_beliefs' search_session_idx, (event_start, sensor_id, source_id) INCLUDE (belief_horizon), is one of the largest indexes on the table but is used an order of magnitude less often than the sensor-leading composite.

This section originally guessed that adopting the materialized view would make it droppable. Measured against a realistic dataset, that guess was wrong, and the answer is more useful than expected.

Warm timings for most-recent-beliefs on one sensor over a month:

	relative
via the materialized view	fastest, ~12× faster than the direct aggregate
direct aggregate, search_session_idx present	baseline
direct aggregate, search_session_idx dropped	~1.3× faster than baseline

Two conclusions:

• The reordered primary key already displaces this index for sensor-filtered work. Dropping it makes that query faster, because the planner stops choosing it over the better key. So §1, not the materialized view, is what makes it redundant here.
• The materialized view is a latency win, not a storage one. It is ~12× faster for this path, but it carries its own heap plus indexes, and a concurrent refresh takes minutes. Adopting it for disk would be close to a wash.

That leaves the question of what search_session_idx still serves. Measured, it is load-bearing, and this index should be kept.

An earlier revision of this section argued it looked droppable, on the grounds that timely_beliefs' search_session takes sensor as a required positional argument, FlexMeasures has a single call site, and there is no raw SQL against timed_belief — so no belief search can omit a sensor filter, and the only time-range-without-sensor queries are two CLI delete commands.

That reasoning was wrong. It treated "every search filters on a sensor" as meaning an event_start-leading index has nothing left to do, but multi-sensor searches (sensor_id IN (...), which are common) still prefer it. Measured warm, on a query over several sensors and a one-month window:

	with search_session_idx	without
multi-sensor + time range	baseline	~37× slower, and it falls back to a sequential scan
multi-sensor most-recent-beliefs	baseline	~1.5× slower

Without the index the planner chooses neither the reordered primary key nor (sensor_id, event_start), because at that selectivity a sequential scan beats several separate range scans. So the index is what keeps these queries off a full table scan.

Conclusion: keep it. The storage it occupies is buying something real. §1's reordered key displaces it for single-sensor work, which is why dropping it made those queries slightly faster, but that result does not generalise to the multi-sensor case.

5. DataSource.sensors should not read the beliefs table at all — #2381

Reordering the primary key to lead with sensor_id (§1) leaves nothing leading with source_id, so DataSource.sensors degrades to a sequential scan.

An index on source_id would fix it, but that is the wrong shape of answer: the question is about a relation bounded by sensors × sources — a few thousand rows at most — and would be paid for with a large permanent index. Its mirror, Sensor.data_sources, has the same problem in the other direction: it is served by the key today, but still by reaching belief rows to enumerate a handful of sources.

Tracked in #2381, implemented in #2382: a small sensor_data_source summary table maintained by a statement-level trigger, which serves both accessors as a lookup. Deliberately a superset — pairs are added on insert and not removed on delete, because deciding whether a pair went stale needs exactly the scan it exists to avoid.

Suggested sequence

1. Pin and reorder the primary key, and drop the indexes it makes redundant — Pin and reorder the timed_belief primary key #2378 (this also carries the migration for §2 and §3, now that Stop declaring redundant single-column indexes SeitaBV/timely-beliefs#244 is released in 4.2.0).
2. Summarise sensor/source pairs so DataSource.sensors stops scanning beliefs — Summarise which sources recorded for which sensors #2382, which depends on Pin and reorder the timed_belief primary key #2378 and targets its branch.
3. Adopt the most-recent-beliefs materialized view (Speed up queries via materialized view #1671) on its own merits as a latency improvement, not a storage one.

Steps 1 and 2 are sequential; 3 is a larger piece of work and no longer a prerequisite for anything else here. Dropping search_session_idx was considered and rejected on measurement (§4).

Why this and not float4

For comparison, narrowing both value columns to float4 (#2332) would have saved a low single-digit percentage of this table's total on-disk footprint, in exchange for a full-table rewrite under an exclusive lock and irreversible precision loss. The index work above is worth several times that, is reversible, and is mostly doable online.