Problemy nadmiarowego indeksowania

Indeksy — to bez dwóch zdań świetny sposób, żeby twoje bazy danych działały szybciej, ale jak to mówią, „lepsze jest wrogiem dobrego”. Nie każdy indeks jest przydatny, a ich nadmiar może bardziej zaszkodzić niż pomóc. Brzmi dziwnie, ale tak właśnie jest. Sprawdźmy to razem.

Wyobraź sobie wielką bibliotekę, gdzie do szukania książek masz kilka katalogów — na przykład według autora, gatunku, roku wydania. Każdy taki katalog pomaga szybko znaleźć książkę. Ale jeśli katalogów będzie za dużo — na przykład dla każdego słowa z tytułu albo każdej drobnej cechy — zamiast pomocy masz chaos: szukanie trwa dłużej, katalogi zajmują mnóstwo miejsca, a bibliotekarz musi ciągle aktualizować te wszystkie listy.

W bazie danych indeksy działają podobnie: pomagają szybko znaleźć potrzebne dane, ale jeśli jest ich za dużo, aktualizowanie ich przy dodawaniu lub zmianie rekordów staje się ciężką robotą. I miejsce na dysku też cierpi. Do tego, gdy indeksów jest za dużo, system może się po prostu pogubić, którego użyć.

Więc, jak z katalogami w bibliotece, z indeksami nie można przesadzać — lepiej mieć kilka potrzebnych i skutecznych niż dziesiątki bezużytecznych.

Pobawmy się w "detektywów PostgreSQL". Wyobraź sobie, że dodałeś trzy indeksy do jednej kolumny. Zrobiłeś to, bo myślałeś, że to poprawi wydajność. Ale wyobraź sobie tylko:

• Jeśli twoja tabela to wielka lista studentów, a indeksów jest trzy, każde dodanie nowego studenta do tabeli oznacza trzy operacje aktualizacji indeksów. Nie brzmi jak "przyspieszenie", prawda?
• A jeśli masz 10 takich tabel, każda przeładowana indeksami? Wydajność całej bazy danych leci na łeb na szyję.

Jak sprawdzić, czy masz problem z nadmiarowym indeksowaniem?

Pierwsze, co warto zrobić, żeby się zorientować, to spojrzeć na istniejące indeksy. W PostgreSQL możesz to zrobić komendą:

\d nazwa_tabeli

Ta komenda pokaże ci tabelę, jej kolumny i powiązane indeksy. Jeśli widzisz masę indeksów przypisanych do jednej tabeli, to już sygnał ostrzegawczy.

Kolejne przydatne narzędzie — widok systemowy pg_stat_user_indexes. Pokazuje, jak aktywnie używane są indeksy, co pozwala wyłapać "martwy balast":

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0;

Jeśli idx_scan równa się 0, to znaczy, że indeks ani razu nie był użyty w zapytaniach. Taki indeks to oczywisty kandydat do usunięcia.

Przykład nadmiarowego indeksowania

Wyobraźmy sobie tabelę z użytkownikami:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

I mamy trzy indeksy:

-- Indeks na email
CREATE INDEX idx_users_email ON users (email);

-- Indeks na username
CREATE INDEX idx_users_username ON users (username);

-- Indeks na created_at
CREATE INDEX idx_users_created_at ON users (created_at);

Teraz zobaczmy typowe zapytania, które wykonujemy:

1. Wyszukiwanie użytkownika po email.
2. Wyszukiwanie użytkownika po username.
3. Sortowanie użytkowników po created_at.

Wydaje się, że indeksy są przydatne. Ale jest haczyk: jeśli te zapytania są rzadkie (powiedzmy, raz w tygodniu), tworzenie indeksów nie ma sensu. Co więcej, jeśli część tych indeksów nigdy nie jest używana, tylko obciążają operacje insert i update.

Dla przykładu: załóżmy, że mamy takie dane w tabeli users:

Jeśli zapytania po username praktycznie nie są wykonywane, indeks idx_users_username ani razu nie jest używany (idx_scan = 0) i można go spokojnie usunąć dla optymalizacji.

Czyli indeks to świetne narzędzie, ale trzeba go używać z głową. Lepiej mieć kilka potrzebnych i dobrze wykorzystywanych indeksów niż dużo zbędnych.

Jak unikać nadmiarowego indeksowania

1. Analiza używanych indeksów. Jak już wspomnieliśmy, sprawdź statystyki użycia indeksów przez pg_stat_user_indexes. Jeśli indeks prawie nie jest używany, pewnie można go usunąć:

DROP INDEX IF EXISTS nazwa_indeksu;

2. Twórz indeksy tylko dla często używanych zapytań. Zanim dodasz indeks, zadaj sobie pytania:

• Czy ta kolumna często pojawia się w WHERE, ORDER BY, GROUP BY?
• Czy tabela ma dużo danych?
• Czy zapytanie naprawdę działa zbyt wolno bez indeksu?

Jeśli choć na jedno z tych pytań odpowiesz "nie", tworzenie indeksu może być zbędne.

3. Używaj indeksów złożonych. Jeśli często używasz kilku kolumn w jednym zapytaniu, zamiast tworzyć osobne indeksy na każdą kolumnę, stwórz indeks złożony:

CREATE INDEX idx_users_email_username ON users (email, username);

To przyspieszy zapytania, które filtrują po email i username jednocześnie.

4. Regularnie przeglądaj istniejące indeksy. Wraz z rozwojem bazy twoje zapytania mogą się zmieniać. To, co było przydatne rok temu, dziś może być zbędne. Co jakiś czas sprawdzaj swoje indeksy i usuwaj te, których już nie używasz.

Minimalizacja indeksów na przykładzie

Wróćmy do naszej tabeli users. Zamiast trzech osobnych indeksów możemy zoptymalizować je tak:

• Usuń osobny indeks na created_at, jeśli sortowanie po tej kolumnie zdarza się rzadko.
• Zamiast dwóch osobnych indeksów na email i username stwórz indeks złożony:

CREATE INDEX idx_users_email_username ON users (email, username);

Podsumowanie: jaki jest sekret balansu?

Jak w wielu aspektach programowania, tu działa zasada minimalizmu: "Im mniej, tym lepiej". Nie musisz indeksować każdej kolumny tylko dlatego, że możesz. Zastanów się, po co ci indeks i czy naprawdę poprawi wydajność zapytań. Bądź pragmatyczny i pamiętaj, że dobry programista to nie ten, kto dodaje indeksy na prawo i lewo, tylko ten, kto rozumie ich wpływ i używa ich skutecznie.

Teraz, mając to narzędzie w ręku, możesz zapobiec katastrofie nadmiarowego indeksowania i sprawić, że twoja baza danych będzie szybka jak gepard, a nie powolna jak żółw z bagażem zbędnych indeksów.