PostgreSQL nie używa indeksu - dlaczego i jak to naprawić

PostgreSQL

PostgreSQL nie używa indeksu - dlaczego i jak to naprawić

W skrócie

"Dodałem indeks, a baza go ignoruje" to jeden z najczęstszych okrzyków przy tuningu. Zwykle nie jest to błąd PostgreSQL, tylko sygnał, że indeks nie pasuje do zapytania albo planer ma powód, by go pominąć. Przechodzimy przez wszystkie typowe przyczyny.

Jak to wygląda w praktyce

Mamy dużą tabelę, zakładamy indeks na kolumnie, po której filtrujemy, i spodziewamy się natychmiastowego przyspieszenia. Uruchamiamy zapytanie - dalej trwa sekundy. W planie z EXPLAIN widzimy Seq Scan zamiast Index Scan, jakby indeksu w ogóle nie było. Czasem jest jeszcze bardziej mylące: to samo zapytanie z jedną wartością korzysta z indeksu, a z inną już nie. Albo indeks działa na maleńkiej tabeli testowej, a na produkcyjnej z milionami wierszy jest pomijany. Pierwsza reakcja to podejrzenie, że indeks się nie utworzył - ale \d tabela pokazuje go czarno na białym. Problem leży gdzie indziej.

Dlaczego tak się dzieje

PostgreSQL ma planer kosztowy: dla każdego zapytania szacuje koszt różnych ścieżek i wybiera najtańszą. Indeks zostaje pominięty, gdy planer nie może go użyć albo gdy uzna odczyt sekwencyjny za tańszy. Najczęstsze przyczyny są cztery. Pierwsza to niezgodność typów - warunek gdzie kolumna_tekstowa = 123 lub porównanie bigint z numeric wymusza konwersję, która zabija dopasowanie do indeksu. Druga to funkcja lub wyrażenie na kolumnie - WHERE lower(email) = ... albo WHERE data::date = ... nie użyje zwykłego indeksu na surowej kolumnie, bo indeks przechowuje wartości oryginalne, nie przetworzone. Trzecia to nieaktualne statystyki - jeśli planer myśli, że warunek pasuje do połowy tabeli, słusznie wybierze Seq Scan; problem w tym, że jego wiedza o rozkładzie danych jest stara. Czwarta to niska selektywność - gdy zapytanie i tak zwraca dużą część tabeli, odczyt sekwencyjny bywa naprawdę szybszy niż skakanie po indeksie, i wtedy planer ma rację. Do tego dochodzą przypadki, gdzie potrzebny jest inny typ indeksu (np. GIN do wyszukiwania w tekście czy JSONB) niż domyślny B-drzewo.

Jak to rozwiązać krok po kroku

1. Najpierw patrzymy na plan: EXPLAIN (ANALYZE, BUFFERS) <zapytanie>;. Sprawdzamy, czy to naprawdę Seq Scan, i jaka jest różnica między wierszami szacowanymi a rzeczywistymi - to od razu zawęża przyczynę.
2. Sprawdzamy zgodność typów. Porównujemy typ kolumny (\d tabela) z typem wartości w warunku. Jeśli filtrujemy kolumnę tekstową liczbą lub odwrotnie, poprawiamy zapytanie tak, by typy się zgadzały (rzutujemy stałą, nie kolumnę), np. WHERE numer = '123' zamiast WHERE numer = 123 dla kolumny tekstowej.
3. Szukamy funkcji na kolumnie. Jeśli w WHERE jest lower(kolumna), kolumna::date, upper(...) itp., zwykły indeks nie zadziała. Albo zdejmujemy funkcję z kolumny, albo tworzymy indeks wyrażeniowy dopasowany do zapytania: CREATE INDEX ON tabela (lower(email));.
4. Odświeżamy statystyki: ANALYZE nazwa_tabeli;. Po większym imporcie czy masowej zmianie danych planer musi na nowo poznać rozkład wartości - to częsty powód pomijania indeksu na świeżo załadowanej tabeli.
5. Oceniamy selektywność. Jeśli zapytanie zwraca dużą część tabeli, Seq Scan jest poprawnym wyborem i nie ma co go zwalczać. Gdy jednak zwraca ułamek, a planer i tak wybiera skan sekwencyjny, sprawdzamy dwie rzeczy: czy indeks pokrywa warunek i czy statystyki są aktualne.
6. Dobieramy właściwy rodzaj indeksu do problemu. Do wyszukiwania fragmentów tekstu, tablic czy JSONB używamy indeksu GIN; do zapytań zakresowych na wielu kolumnach czasem lepszy jest BRIN na danych ułożonych chronologicznie. Domyślny B-drzewo nie obsłuży np. operatora @> na JSONB.
7. Dopiero na koniec, wyłącznie do diagnozy na sesji testowej, możemy tymczasowo zniechęcić planer do skanu sekwencyjnego: SET enable_seqscan = off; i sprawdzić, czy plan z indeksem jest faktycznie szybszy. To narzędzie do sprawdzenia hipotezy, nie do zostawienia na produkcji.

Jak sprawdzić, że zadziałało

Po zmianie ponawiamy EXPLAIN (ANALYZE, BUFFERS) - w planie Seq Scan powinien ustąpić miejsca Index Scan lub Index Only Scan, a łączny czas i liczba czytanych stron wyraźnie spaść. Jeśli robiliśmy indeks wyrażeniowy, dobrym potwierdzeniem jest to, że plan wymienia właśnie ten indeks po nazwie. Gdy używaliśmy SET enable_seqscan = off do testu, wracamy do on i sprawdzamy, że planer teraz z własnej woli wybiera indeks - to znaczy, że naprawiliśmy prawdziwą przyczynę (typ, wyrażenie lub statystyki), a nie tylko zmusiliśmy bazę siłą. Jeśli mimo indeksu planer nadal woli skan sekwencyjny przy zapytaniu zwracającym dużo wierszy, to prawdopodobnie ma rację i indeks po prostu nie jest tu właściwym narzędziem.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5