Sekwencyjne skanowanie dużej tabeli - kiedy to problem

PostgreSQL

Sekwencyjne skanowanie dużej tabeli - kiedy to problem

W skrócie

Seq Scan w planie zapytania budzi odruchowy niepokój, ale nie zawsze jest winowajcą. Kluczowe jest odróżnienie sytuacji, w której skan sekwencyjny jest optymalny, od tej, w której zdradza brakujący indeks. Wyjaśnimy, kiedy naprawdę mamy problem i jak krok po kroku doprowadzić do szybkiego dostępu indeksowego.

Jak to wygląda w praktyce

Proste zapytanie z warunkiem WHERE na dużej tabeli trwa sekundy albo dłużej, mimo że zwraca kilka wierszy. Gdy sprawdzisz plan poleceniem EXPLAIN, widzisz na górze Seq Scan on nazwa_tabeli zamiast oczekiwanego dostępu przez indeks. Baza czyta całą tabelę, filtruje ją w locie i odrzuca prawie wszystko. Im tabela większa, tym gorzej, a obciążenie dysku rośnie przy każdym takim zapytaniu. Zastanawiasz się, dlaczego planista nie skorzystał z indeksu, choć wynik jest wąski.

Dlaczego tak się dzieje

Planista PostgreSQL wybiera Seq Scan w dwóch przypadkach. Pierwszy jest w pełni uzasadniony: gdy zapytanie i tak zwróci dużą część tabeli, sekwencyjny odczyt jest szybszy niż skakanie po indeksie, bo czyta dysk po kolei. Drugi przypadek to problem: planista sięga po Seq Scan, bo nie ma indeksu na kolumnie z warunku albo z jakiegoś powodu nie może go użyć.

Powodów, dla których indeks nie zostaje użyty mimo jego istnienia, jest kilka. Warunek może opakowywać kolumnę funkcją, na przykład WHERE lower(email) = ..., przez co zwykły indeks na kolumnie przestaje pasować. Typ danych w warunku może nie zgadzać się z typem kolumny, wymuszając konwersję. Statystyki mogą być nieaktualne, więc planista błędnie szacuje, że wynik jest szeroki. Bywa też, że tabela jest po prostu na tyle mała, że skan całości jest tańszy - i wtedy Seq Scan jest poprawną decyzją, której nie ma sensu zwalczać.

Jak to rozwiązać krok po kroku

1. Zacznij od pełnej diagnozy planem z rzeczywistym wykonaniem: EXPLAIN (ANALYZE, BUFFERS) twoje_zapytanie;. Zobaczysz nie tylko wybrany plan, ale i realny czas oraz liczbę odczytanych bloków, co pokazuje skalę problemu.
2. Porównaj szacowaną liczbę wierszy (rows) z faktyczną (actual rows). Duża rozbieżność to sygnał, że statystyki są nieaktualne - wykonaj ANALYZE nazwa_tabeli; i sprawdź plan ponownie.
3. Jeśli po aktualizacji statystyk warunek nadal filtruje wąsko, a indeksu brak, załóż go: CREATE INDEX ON nazwa_tabeli (kolumna);. Dla dużych tabel na produkcji użyj wariantu CREATE INDEX CONCURRENTLY, żeby nie blokować zapisów podczas budowy.
4. Gdy warunek opakowuje kolumnę funkcją, dopasuj do niego indeks funkcyjny, na przykład CREATE INDEX ON nazwa_tabeli (lower(email));. Indeks musi odpowiadać dokładnie wyrażeniu z WHERE, inaczej planista go nie wykorzysta.
5. Upewnij się, że typy się zgadzają. Porównanie kolumny tekstowej z liczbą albo odwrotnie wymusza konwersję, która blokuje indeks. Podawaj literały w typie zgodnym z kolumną.
6. Jeśli zapytanie zwraca sporą część tabeli i Seq Scan jest naprawdę tańszy, zaakceptuj go - to poprawny wybór. Optymalizacji szukaj wtedy w ograniczeniu zakresu danych albo w innym podejściu do zapytania, a nie w wymuszaniu indeksu.

Jak sprawdzić, że zadziałało

Po zmianach ponownie uruchom EXPLAIN (ANALYZE, BUFFERS) dla tego samego zapytania. Sukces poznasz po tym, że w planie zamiast Seq Scan pojawi się Index Scan lub Index Only Scan, a wartość actual time spadnie o rzędy wielkości. Zwróć uwagę na sekcję Buffers: liczba odczytanych bloków przy dostępie indeksowym powinna być znacznie mniejsza niż przy pełnym skanie, co jest twardym dowodem, że baza nie czyta już całej tabeli. Jeśli mimo indeksu planista dalej wybiera Seq Scan, sprawdź, czy warunek nie opakowuje kolumny funkcją i czy typy się zgadzają - dopiero zgodność tych elementów pozwala planiście sięgnąć po indeks.

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