Postgres pod listami: partial index zamiast tłustego indeksu na całą tabelę

Masz tabelę ticketów na milion wierszy. Inbox admina zawsze pyta o status = 'open'. Dodałeś CREATE INDEX ON tickets (created_at). Lista nadal muli, a EXPLAIN pokazuje seq scan albo tłusty indeks pełen zamkniętych spraw.

Postgres ma na to precyzyjne narzędzie: indeks częściowy (partial index). Indeksuje tylko wiersze spełniające predykat, więc pod listy filtrujące status albo soft-delete bywa mniejszy i tańszy w utrzymaniu. Oficjalna dokumentacja: Partial Indexes.

Problem: pełny indeks na gorący filtr

Hipotetyczny schemat: skrzynka supportu. Większość wierszy jest zamknięta. Prawie każde zapytanie listy interesuje się otwartymi.

id           bigserial PRIMARY KEY,
  org_id       bigint NOT NULL,
  status       text NOT NULL CHECK (status IN ('open', 'closed')),
  created_at   timestamptz NOT NULL DEFAULT now(),
  subject      text NOT NULL
);

-- Pełny indeks: każdy wiersz, w tym lata zamkniętych ticketów
CREATE INDEX tickets_created_at_idx ON tickets (created_at);

Typowa lista:

FROM tickets
WHERE status = 'open'
  AND org_id = 42
ORDER BY created_at DESC
LIMIT 50;

Planner może odrzucić sensowną ścieżkę indeksową przy złej selektywności albo użyć tickets_created_at_idx i dopiero potem odfiltrować zamknięte. W obu wariantach płacisz za utrzymanie wpisów dla wierszy, których inbox nie czyta.

Rozwiązanie: partial index tylko na gorące wiersze

Ten sam pomysł co w przykładzie „unbilled orders” z dokumentacji: w indeksie zostaw gorący podzbiór.

ON tickets (org_id, created_at DESC)
  WHERE status = 'open';

Indeks trzyma wpisy wyłącznie dla otwartych ticketów. Zamknięte go nie rozdymują. Update open → closed usuwa wiersz z tego indeksu; odwrotna zmiana go dodaje. To zachowanie predykatu indeksu częściowego z dokumentacji, nie magia.

Sprawdź plan (kształt poglądowy; koszty zależą od statystyk):

SELECT id, subject, created_at
FROM tickets
WHERE status = 'open'
  AND org_id = 42
ORDER BY created_at DESC
LIMIT 50;

Chcesz zobaczyć tickets_open_org_created_idx (Index Scan albo Bitmap Index Scan), a nie seq scan po całej tabeli. Jak czytać plany: Using EXPLAIN.

Pułapki: zapytanie musi implikować predykat

Postgres użyje indeksu częściowego tylko wtedy, gdy udowodni, że WHERE zapytania implikuje predykat indeksu. Dopasowanie dzieje się w czasie planowania. Dokumentacja jest szczera: nie ma wyrafinowanego „twierdzeniowca”. Działają proste implikacje nierówności (x < 1 implikuje x < 2); poza tym predykat zwykle musi pojawić się w zapytaniu w tej samej formie.

To może użyć indeksu:

To nie może, nawet jeśli akurat wszystkie pozostałe wiersze są otwarte:

-- planner nie założy, że status = 'open'

A przygotowany parametr w stylu WHERE amount < $1 nie dopasuje się do indeksu częściowego WHERE amount < 100, bo $1 nie implikuje tego limitu dla każdej możliwej wartości. Soft-delete rządzi się tym samym: jeśli indeks ma WHERE deleted_at IS NULL, zapytanie też musi ten warunek mieć (albo coś, co planner uzna za jego implikację).

Trzymaj tekst predykatu zsynchronizowany z filtrami, które naprawdę wysyła aplikacja. Warstwa ORM, która przepisuje status = 'open' na inną ekspresję, potrafi po cichu wyrzucić indeks z planu.

Bonus: częściowa unikalność

Drugie udokumentowane zastosowanie: unikalność na podzbiorze. Kształt z docs, pod aplikację webową: co najwyżej jedna aktywna subskrypcja na użytkownika, dowolna liczba anulowanych.

ON subscriptions (user_id)
  WHERE status = 'active';

To jest constraint, nie tylko trik wydajnościowy. Drugi aktywny wiersz kończy się naruszeniem unikalności przy insert/update.

Trade-off: kiedy nie sięgać po indeksy częściowe

Dokumentacja ostrzega przed ścianą niepokrywających się indeksów częściowych (WHERE category = 1, = 2, … = N) jako domowym zamiennikiem partycjonowania. Lepiej jeden indeks złożony (category, data) albo prawdziwe partycjonowanie tabel, gdy tabela jest naprawdę duża. Planner nie rozumie, że te indeksy się wykluczają, więc marnuje pracę na testowanie każdego z osobna.

I jeszcze: jeśli otwarte tickety to większość tabeli, indeks częściowy niewiele daje. Wygrana pojawia się, gdy gorący filtr wybiera mały ułamek wierszy i ten filtr jest stabilny w workloadzie.

Werdykt

1. Wybierz jeden endpoint listy, który zawsze filtruje tak samo (status, soft-delete, is_read = false).
2. Dodaj indeks częściowy, którego WHERE pasuje do tego filtra, a kolumny do ORDER BY / równości.
3. Odpal EXPLAIN (ANALYZE, BUFFERS) na stagingu z rozmiarem zbliżonym do produkcji. Potwierdź, że w planie jest nazwa indeksu.
4. Jeśli plan go ignoruje, najpierw sprawdź brzmienie predykatu, potem statystyki (ANALYZE), zanim dodasz kolejne indeksy.
Indeksy częściowe nie są srebrną kulą. Są precyzyjnym narzędziem do nudnego przypadku każdej aplikacji CRUD: większość wierszy jest zimna, UI pyta tylko o gorące, a Ty indeksowałeś jedne i drugie.