Użycie pg_stat_statements do analizy użycia indeksów i filtrów

Indeksy to jak zakładki w książce. Pomagają szybko znaleźć potrzebne dane. Ale co, jeśli dodaliśmy mnóstwo zakładek, a nikt z nich nie korzysta? Albo, co gorsza, kiepsko dobrane zakładki zmuszają nas do przekartkowania książki od początku do końca? Właśnie dlatego trzeba analizować użycie indeksów.

Słabo napisane zapytania mogą ignorować indeksy, co prowadzi do kosztownych sekwencyjnych skanów (Seq Scan). To z kolei spowalnia wykonanie zapytań i zwiększa obciążenie serwera. Naszym celem jest zrozumieć, które zapytania nie korzystają z indeksów i dlaczego.

Jak sprawdzić, czy indeksy są używane?

Sprawdźmy dwa kluczowe problemy:

1. Czy indeksy, które stworzyliśmy, są używane?
2. Jeśli są używane, to czy są skuteczne?

Do tego możemy przeanalizować statystyki zapytań w pg_stat_statements, zwracając uwagę na kilka kolumn:

• rows: liczba wierszy przetworzonych przez zapytanie.
• shared_blks_hit: liczba stron odczytanych z pamięci (a nie z dysku).
• shared_blks_read: liczba stron faktycznie odczytanych z dysku.

Im mniej wierszy przetwarzanych przez zapytanie i im wyższy udział shared_blks_hit względem ogólnej liczby stron, tym lepiej działa nasz indeks.

Przykład analizy indeksowania

Załóżmy, że mamy tabelę studentów:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- Dodajemy indeks po grade_level
CREATE INDEX idx_grade_level ON students(grade_level);

Teraz wrzucamy dane do eksperymentu:

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    'Student ' || generate_series(1, 100000),
    'LastName',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

Odpalamy zapytanie, żeby znaleźć studentów z konkretnym poziomem:

SELECT *
FROM students
WHERE grade_level = 10;

Sprawdzanie w pg_stat_statements

Po kilku wykonaniach zapytania możemy sprawdzić statystyki:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

Interpretacja wyniku:

• rows: Jeśli zapytanie zwraca za dużo wierszy, czy indeks ma sens? Może nie jest potrzebny dla warunków o niskiej selektywności.
• shared_blks_hit i shared_blks_read: Jeśli dużo stron jest czytanych z dysku (shared_blks_read), to albo indeks nie działa, albo dane są poza buforem.

Optymalizacja indeksowania

Stworzenie indeksu to dopiero połowa roboty. Ważne, żeby PostgreSQL faktycznie go używał. Czasem, mimo naszych starań, baza wybiera nie indeks, a sekwencyjne skanowanie całej tabeli. Dlaczego tak się dzieje? Sprawdźmy.

Najpierw zobaczmy, czemu indeks może być ignorowany nawet jeśli wydaje się przydatny. Potem — jakie triki pomogą zmusić bazę, żeby „pamiętała”, że indeks istnieje i używała go jak należy.

Co jeśli indeks nie jest używany?

Czasem PostgreSQL olewa indeks i robi sekwencyjny skan (Seq Scan). Może to wynikać z kilku powodów:

1. Niska selektywność warunku. Jeśli zapytanie zwraca ponad połowę wierszy tabeli, sekwencyjny skan może być szybszy.
2. Typ danych lub funkcje. Jeśli w zapytaniu używasz funkcji na indeksowanej kolumnie, indeks może być zignorowany. Na przykład:

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- Indeks nie jest używany

W takich przypadkach można przepisać zapytanie:

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- Używa indeksu

3. Nieodpowiedni typ indeksu. Na przykład, do pełnotekstowego wyszukiwania lepiej użyć indeksów GIN lub GiST, a nie B-TREE.

4. Błędy w statystykach. Jeśli statystyki są stare, optymalizator może podjąć złą decyzję. Użyj ANALYZE:

   ANALYZE students;
   

Ulepszanie zapytania

Wracając do naszego przykładu. Jeśli indeksowanie nie działa, można zrobić tak:

1. Upewnij się, że zapytanie używa filtrów, które mogą korzystać z indeksu: nie używaj funkcji, konwersji typów itd.
2. Jeśli filtr zwraca dużo wartości, zastanów się, czy indeks jest potrzebny. Jeśli to częste zapytanie, spróbuj zmienić strukturę tabeli albo dodać zmaterializowane widoki.
3. Jeśli jest Seq Scan przez dużą ilość danych, spróbuj podzielić tabelę na partycje (PARTITION BY).

Sprawdzanie skuteczności indeksowania

Po optymalizacji ponownie wykonaj zapytanie i sprawdź statystyki:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

Porównaj metryki przed i po. Powinieneś zobaczyć mniej odczytów z dysku (shared_blks_read) i więcej trafień w pamięć (shared_blks_hit).

Prawdziwe przypadki

1. Złe użycie indeksu

Mamy tabelę produktów z polem tekstowym description:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- Indeks do pełnotekstowego wyszukiwania
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

Jeśli robimy zapytanie:

SELECT *
FROM products
WHERE description ILIKE '%smartphone%';

Indeks nie będzie użyty! Powód: ILIKE nie działa z GIN. Żeby użyć indeksu, trzeba przepisać zapytanie:

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('smartphone');

2. Brak indeksu tam, gdzie jest potrzebny

Załóżmy, że zapytanie:

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

robi sekwencyjny skan (Seq Scan). To może być przez brak indeksu po birth_date. Tworząc indeks:

CREATE INDEX idx_birth_date ON students(birth_date);

i przeliczając statystyki (ANALYZE students), możesz znacznie przyspieszyć wykonanie tego zapytania.