Które indeksy są nieużywane i jak je znaleźć

PostgreSQL

Które indeksy są nieużywane i jak je znaleźć

W skrócie

Indeksy przyspieszają odczyty, ale nie są darmowe. Każdy z nich powiększa bazę, wydłuża czas zapisów i daje VACUUM więcej pracy. W dojrzałych bazach część indeksów zostaje po eksperymentach, po nieaktualnych zapytaniach albo powstała jako duplikaty. PostgreSQL na szczęście liczy, ile razy każdy indeks został użyty, więc nie musimy zgadywać - wystarczy zajrzeć do właściwego widoku statystyk i podjąć decyzję na twardych liczbach.

Jak to wygląda w praktyce

Objawem pośrednim są wolne zapisy do tabel z wieloma indeksami oraz baza, która rośnie szybciej, niż wynikałoby to z samych danych. Przy sprawdzeniu rozmiarów okazuje się, że indeksy ważą tyle co dane albo więcej. Często widać też pary indeksów, które różnią się tylko kolejnością kolumn albo indeks na pojedynczej kolumnie, która i tak jest pierwsza w indeksie złożonym. Użytkownik nie widzi bezpośrednio nieużywanego indeksu, ale odczuwa jego skutki: dłuższe operacje zapisu, dłuższy VACUUM i większe zużycie miejsca na dysku. Dopiero zajrzenie do statystyk pokazuje, które indeksy nigdy nie posłużyły do żadnego skanu.

Dlaczego tak się dzieje

PostgreSQL zbiera statystyki użycia obiektów w tak zwanych widokach statystyk skumulowanych. Dla indeksów kluczowy jest pg_stat_user_indexes, a w nim kolumna idx_scan, która zlicza, ile razy dany indeks posłużył planerowi do skanu. Jeśli licznik stoi na zerze mimo długiego czasu działania serwera, indeks praktycznie nie jest wykorzystywany do odczytów. Trzeba tylko pamiętać o dwóch pułapkach. Po pierwsze, licznik jest kasowany przy pg_stat_reset() oraz po odtworzeniu bazy, więc zero może oznaczać po prostu świeży start statystyk. Po drugie, indeks może wyglądać na nieużywany, a mimo to pilnować unikalności klucza głównego lub ograniczenia unique - takiego nie usuwamy, nawet jeśli nie służy do wyszukiwania.

Jak to rozwiązać krok po kroku

1. Sprawdź, jak długo zbierają się statystyki, żeby zero miało sens: SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();. Jeśli reset był wczoraj, poczekaj z decyzją.
2. Wylistuj kandydatów: SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS rozmiar FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;.
3. Odsiej indeksy wspierające klucze i ograniczenia. Sprawdź w pg_index flagi indisunique i indisprimary - takich nie ruszamy, bo pilnują poprawności danych.
4. Poszukaj duplikatów i indeksów zawartych w innych. Indeks na kolumnie a jest zbędny, jeśli istnieje indeks na kolumnach a oraz b w tej kolejności, bo ten drugi obsługuje także zapytania po samym a.
5. Zanim skasujesz, wykonaj miękki test: DROP INDEX CONCURRENTLY zbedny_indeks; zdejmuje indeks bez blokowania zapisów do tabeli. Wariant CONCURRENTLY jest ważny na produkcji.
6. Jeśli chcesz zachować ostrożność, zamiast kasować możesz najpierw zdjąć indeks wariantem CONCURRENTLY na środowisku testowym pod obciążeniem zbliżonym do produkcji i obserwować, czy plany zapytań się nie pogarszają.
7. Po usunięciu zaplanuj ponowny przegląd za jakiś czas, bo wzorce zapytań się zmieniają i indeks nieużywany dziś może być potrzebny po zmianie kodu aplikacji.

Jak sprawdzić, że zadziałało

Po usunięciu indeksów porównaj rozmiar tabeli i jej indeksów przez SELECT pg_size_pretty(pg_indexes_size('nazwa_tabeli')); - powinien spaść. Zmierz też czas typowej operacji zapisu do tej tabeli; przy wielu skasowanych indeksach INSERT i UPDATE przyspieszają. Najważniejsze jest jednak potwierdzenie, że nie zepsuliśmy odczytów: wykonaj kluczowe zapytania z EXPLAIN ANALYZE i upewnij się, że planer nie przeszedł niespodziewanie na wolny skan sekwencyjny. Jeśli zapisy są szybsze, baza mniejsza, a odczyty niezmienione, usunięcie było trafne.

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