Jak zindeksować zapytanie za pomocą `WHERE field IS NULL`?

Mam tabelę z dużą ilością wstawek, ustawiając jedno z pól ( uploaded_at) na NULL. Następnie okresowe zadanie wybiera wszystkie krotki WHERE uploaded_at IS NULL, przetwarza je i aktualizuje, ustawiając uploaded_atna bieżącą datę.

Jak powinienem indeksować tabelę?

Rozumiem, że powinienem użyć częściowego indeksu, takiego jak:

CREATE INDEX foo ON table (uploaded_at) WHERE uploaded_at IS NULL

Lub coś w tym rodzaju. Jestem trochę zdezorientowany, jeśli poprawne jest indeksowanie na polu, które jest zawsze NULL. Lub jeśli poprawne jest użycie indeksu b-drzewa. Hash wygląda na lepszy pomysł, ale jest przestarzały i nie jest replikowany przez przesyłanie strumieniowe replikacji w trybie gotowości. Wszelkie porady będą mile widziane.

Eksperymentowałem trochę z następującymi indeksami:

"foo_part" btree (uploaded_at) WHERE uploaded_at IS NULL
"foo_part_id" btree (id) WHERE uploaded_at IS NULL

a narzędzie do planowania zapytań zawsze wybiera foo_partindeks. explain analysedaje również nieco lepszy wynik dla foo_partindeksu:

Index Scan using foo_part on t1  (cost=0.28..297.25 rows=4433 width=16) (actual time=0.025..3.649 rows=4351 loops=1)
   Index Cond: (uploaded_at IS NULL)
 Total runtime: 4.060 ms

vs

Bitmap Heap Scan on t1  (cost=79.15..6722.83 rows=4433 width=16) (actual time=1.032..4.717 rows=4351 loops=1)
   Recheck Cond: (uploaded_at IS NULL)
   ->  Bitmap Index Scan on foo_part_id  (cost=0.00..78.04 rows=4433 width=0) (actual time=0.649..0.649 rows=4351 loops=1)
 Total runtime: 5.131 ms

W tym szczególnym przypadku faktycznie zaindeksowana kolumna nie ma znaczenia dla podanego zapytania. Możesz wybrać dowolną kolumnę. Wybrałbym coś innego uploaded_at, co jest bezużyteczne. Część kolumny, która może być przydatna dla innych zapytań i nie jest większa niż 8 bajtów, najlepiej.

CREATE INDEX foo ON table bar (some_col) WHERE uploaded_at IS NULL;

Jeśli nie masz przypadku użycia żadnej innej kolumny, nadal najlepiej jest trzymać się bezużytecznego uploaded_at, więc nie wprowadzaj dodatkowych kosztów utrzymania indeksu i ograniczeń dla GORĄCYCH aktualizacji. Więcej:

• Czy indeks złożony jest również przydatny w przypadku zapytań dotyczących pierwszego pola?

Lub użyj stałej jako wyrażenia indeksu, jeśli nie masz zastosowania w żadnej innej kolumnie indeksu. Lubić:

CREATE INDEX baz ON table bar ((TRUE)) WHERE uploaded_at IS NULL;

Wymagane nawiasy. To także utrzymuje indeks w minimalnym rozmiarze. Ale chociaż kolumna indeksu nigdy nie jest większa niż 8 bajtów (tak jest w przypadku timestamp), to i tak ma ona minimalny rozmiar. Związane z:

• Uzyskaj oszacowania liczby z pg_class.reltuples dla danych warunków