Seq Scan a Index Scan i Index-Only

Gdy planista potrzebuje wierszy z tabeli, wybiera jedną z trzech metod dostępu, a rekruterzy oczekują wymienienia wszystkich trzech:

• Seq Scan — odczyt każdego wiersza tabeli od początku do końca.
• Index Scan — przejście po indeksie w celu znalezienia pasujących wierszy, a następnie pobranie każdego z nich z tabeli.
• Index-Only Scan — udzielenie odpowiedzi wyłącznie na podstawie indeksu, bez odwoływania się do tabeli.

Zrozumienie, dlaczego planista wybiera każdą z tych metod, jest sednem tej lekcji i gwarantowanym pytaniem dla doświadczonych kandydatów.

Seq Scan odczytuje strony tabeli jedna po drugiej i stosuje ewentualny filtr do każdego wiersza. Indeks nie jest używany.

Może to brzmieć źle, ale często jest to właściwy wybór. Odczyty sekwencyjne są szybkie dla dysku (nie wymagają losowych przeskoków), więc gdy zapytanie zwraca dużą część tabeli, odczytanie wszystkiego jest lepsze niż przeskakiwanie przez indeks miliony razy.

Przykład: skanowanie orders i zachowanie wierszy, dla których amount > 100. Jeśli większość zamówień przekracza 100, skanowanie sekwencyjne jest właściwym wyborem.

Index Scan wykorzystuje strukturę B-tree, aby przejść bezpośrednio do pasujących kluczy, a następnie odczytuje odpowiadające im wiersze ze sterty tabeli.

Ta metoda sprawdza się szczególnie dobrze, gdy filtr jest selektywny i zwraca niewielką część tabeli. Wyszukanie 5 wierszy za pomocą indeksu jest lepsze niż odczytanie 10 milionów.

Plan podaje nazwę użytego indeksu. Każde dopasowanie kosztuje jedno wyszukanie w indeksie oraz jeden odczyt ze sterty (odczyt losowy), dlatego skanowanie za pomocą indeksu traci przewagę, gdy zwraca zbyt wiele wierszy.

Pojedynczym pojęciem, które wpływa na wszystkie te decyzje, jest selektywność: ułamek wierszy zachowywanych przez predykat.

• Wysoka selektywność (pasuje niewiele wierszy, na przykład dla unikalnego identyfikatora) sprzyja użyciu Index Scan.
• Niska selektywność (pasuje wiele wierszy, na przykład dla status IS NOT NULL) sprzyja użyciu Seq Scan.

Przybliżona zasada mówi, że gdy zapytanie zwraca więcej niż około 5–10 procent tabeli, planista często preferuje skanowanie sekwencyjne, ponieważ losowe odczyty ze sterty wymagane przez indeks stają się droższe niż odczytanie wszystkiego po kolei.

Index-Only Scan jest najszybszą z trzech metod. Jeśli każda kolumna potrzebna zapytaniu znajduje się już w indeksie, silnik w ogóle nie odwołuje się do sterty tabeli.

Przykładowe zapytanie wybiera tylko customer_id i filtruje po tej kolumnie, a indeks został utworzony na customer_id. Wszystkie potrzebne dane znajdują się w indeksie, dlatego Postgres raportuje Index Only Scan.

Eliminuje to losowe odczyty ze sterty, które spowalniają zwykłe skanowanie za pomocą indeksu — jest to ogromna korzyść w przypadku szerokich tabel.

Rekruterzy uwielbiają ten niuans. Skanowanie wyłącznie z indeksu nadal musi potwierdzić, że każdy wiersz jest widoczny dla danej transakcji (MVCC), a sam indeks nie przechowuje informacji o widoczności.

Postgres używa mapy widoczności: jeśli strona jest oznaczona jako all-visible, pomija stertę; w przeciwnym razie musi pobrać wiersz ze sterty. Plan pokazuje wtedy Heap Fetches: N.

Dlatego świeżo zaktualizowana tabela może wykazywać wiele pobrań ze sterty i spowalniać skanowanie wyłącznie z indeksu, dopóki VACUUM nie odświeży mapy widoczności.

Często pojawia się również czwarta metoda: Bitmap Heap Scan. Planista wybiera ją, gdy predykat pasuje do większej liczby wierszy, niż opłaca się obsłużyć za pomocą zwykłego skanowania indeksu, ale do mniejszej liczby niż w przypadku całej tabeli.

Najpierw tworzy bitmapę lokalizacji pasujących wierszy na podstawie indeksu (Bitmap Index Scan), a następnie pobiera strony sterty w kolejności fizycznej, a nie losowej. Uporządkowane pobieranie jest znacznie tańsze niż rozproszone odczyty wykonywane przez zwykłe skanowanie za pomocą indeksu.

Klasyczne pytanie rekrutacyjne brzmi: Dodałem indeks, ale plan nadal wykonuje Seq Scan — dlaczego? Typowe przyczyny:

• Predykat nie jest selektywny, więc skanowanie jest rzeczywiście tańsze.
• Kolumna jest opakowana funkcją: WHERE lower(email) = ... nie może użyć zwykłego indeksu na email.
• Niezgodność typów wymusza niejawne rzutowanie, które uniemożliwia użycie indeksu.
• Nieaktualne statystyki — należy uruchomić ANALYZE.
• Tabela jest mała, więc skanowanie kilku stron jest szybsze niż ponoszenie narzutu związanego z indeksem.

Załóżmy, że tabela orders ma indeks na created_at, ale to zapytanie nadal wykonuje skanowanie sekwencyjne:

Winowajcą jest DATE(created_at). Opakowanie kolumny w funkcję oznacza, że nie można użyć indeksu utworzonego na surowej wartości created_at. Rozwiązaniem jest przepisanie zapytania jako predykatu zakresowego, który pozostawia kolumnę bez modyfikacji, albo utworzenie indeksu wyrażeniowego na DATE(created_at).

Na rozmowę rekrutacyjną warto zapamiętać następujące porównanie:

• Seq Scan — najlepszy przy zwracaniu dużej części wierszy; sekwencyjne operacje wejścia-wyjścia.
• Index Scan — najlepszy przy selektywnych wyszukiwaniach; przejście po indeksie oraz losowe odczyty ze sterty.
• Bitmap Heap Scan — przy pośredniej liczbie dopasowań; indeks tworzy bitmapę, a następnie wykonywane są uporządkowane odczyty ze sterty.
• Index-Only Scan — najszybszy, gdy indeks zawiera wszystkie potrzebne kolumny, a strony są oznaczone jako all-visible.

Planista wybiera metodę na podstawie szacowanego kosztu, zależnego głównie od selektywności i statystyk.

Aby udowodnić coś podczas pracy programistycznej, można tymczasowo wpłynąć na wybór planisty: SET enable_seqscan = off; wymusza preferowanie indeksów, dzięki czemu można porównać plany.

Jest to metoda diagnostyczna, a nie rozwiązanie do zastosowania na produkcji. Na rozmowie rekrutacyjnej należy wspomnieć, że prawdziwymi rozwiązaniami są lepsze statystyki, odpowiedni indeks lub przepisanie predykatu, a nie globalne wyłączanie funkcji planisty.

Zapytanie wybiera tylko email i filtruje po email, a na tej kolumnie istnieje indeks B-tree. Plan pokazuje Index Only Scan. Dlaczego jest to szybsze niż zwykłe skanowanie za pomocą indeksu?

Najważniejsze informacje o metodach dostępu:

• Seq Scan sprawdza się przy zapytaniach o niskiej selektywności, a Index Scan przy zapytaniach selektywnych.
• Index-Only Scan pomija stertę, gdy indeks zawiera wszystkie potrzebne kolumny; należy zwracać uwagę na Heap Fetches i mapę widoczności.
• Bitmap Heap Scan stanowi rozwiązanie pośrednie, pobierając strony sterty w kolejności fizycznej.
• Planista podejmuje decyzję na podstawie selektywności i statystyk; funkcje stosowane na kolumnach, niezgodności typów i nieaktualne statystyki powodują ignorowanie indeksu.