Zbieranie statystyk użycia indeksów i tabel

Wyobraź sobie, że twoja baza danych to wielki magazyn. Indeksy to katalogi i spis, który pomaga szybko znaleźć to, czego szukasz. Tabele to same produkty na półkach. Jeśli indeks jest słabo używany, to tak, jakby katalog leżał w najdalszym kącie i nikt go nie otwierał. Jeśli tabela jest mocno używana, ale ma kiepską strukturę albo za dużo danych, to może obciążać nasz magazyn (bazę danych) i spowalniać jego pracę.

Główne zadania analizy:

1. Ocena efektywności użycia indeksów. Na przykład, twój wypasiony indeks leży bezużytecznie? Wywal go!
2. Określenie częstotliwości operacji odczytu i zapisu. Pomaga ogarnąć, które tabele są aktywnie używane.
3. Optymalizacja zapytań. Statystyki pomagają sprawdzić, gdzie można przyspieszyć przetwarzanie danych, dodając lub zmieniając indeksy.

Widoki pg_stat_user_indexes i pg_stat_user_tables

W PostgreSQL są dwa mega przydatne widoki do zbierania statystyk: pg_stat_user_indexes i pg_stat_user_tables. Sprawdźmy je dokładniej.

pg_stat_user_indexes: jak używane są indeksy?

Główne pola:

• relname — nazwa tabeli, do której należy indeks.
• indexrelname — nazwa indeksu.
• idx_scan — ile razy indeks był użyty do wyszukiwania.
• idx_tup_read — liczba wierszy odczytanych przez indeks.
• idx_tup_fetch — liczba faktycznie zwróconych wierszy (po zastosowaniu filtrów).

Przykład zapytania:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_read, 
       idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Co tu robimy?

• sortujemy dane po liczbie wywołań indeksu (idx_scan), żeby zobaczyć, które indeksy są najpopularniejsze.
• jeśli indeks praktycznie nie jest używany (idx_scan = 0), warto się zastanowić: czy w ogóle jest potrzebny?

Praktyczne zastosowanie:

Wdrażasz nową wersję aplikacji i dodałeś nowy indeks. Dzięki pg_stat_user_indexes możesz sprawdzić, czy twoje zapytanie faktycznie zaczęło używać nowego indeksu, czy PostgreSQL dalej wybiera starą ścieżkę, ignorując twoje dzieło optymalizacji.

pg_stat_user_tables: podgląd danych o tabelach

Główne pola:

• relname — nazwa tabeli.
• seq_scan — liczba sekwencyjnych skanów tabeli (bez użycia indeksów).
• seq_tup_read — liczba wierszy zwróconych z tabeli przy sekwencyjnych skanach.
• idx_scan — liczba skanów indeksowych dla tabeli.
• n_tup_ins — liczba wstawionych wierszy
• n_tup_upd — liczba zaktualizowanych wierszy.
• n_tup_del — liczba usuniętych wierszy.

Przykład zapytania:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
ORDER BY sequential_scans DESC;

Co tu widzimy?

• Tabele z dużą liczbą sekwencyjnych skanów (seq_scan) mogą sugerować, że warto dodać indeks.
• Liczba operacji wstawiania, aktualizacji i usuwania pomaga ogarnąć, jak często dane w tabeli się zmieniają.

Praktyczne zastosowanie: Pracujesz z tabelą users, w której trzymasz dane wszystkich użytkowników twojej apki. Dzięki pg_stat_user_tables odkrywasz, że sekwencyjne skanowania (seq_scan) tej tabeli są kosmiczne. To znak: czas zrobić indeksy na najczęściej używanych kolumnach, żeby przyspieszyć zapytania.

Przykład: analiza indeksów i tabel w prawdziwej bazie danych

Załóżmy, że mamy bazę z tabelami orders (zamówienia) i products (produkty). Chcemy sprawdzić, jak efektywnie używane są tabele i indeksy.

Analiza indeksów:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_read, 
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

Widzisz, że indeks orders_customer_id_idx był użyty 50 tysięcy razy, a orders_date_idx tylko 5 razy. Może orders_date_idx nie jest potrzebny.

Analiza tabel:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       seq_tup_read AS tuples_read, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY seq_scan DESC;

Tablica products jest ciągle skanowana sekwencyjnie. To podpowiedź: w katalogu produktów brakuje indeksów.

Typowe błędy i jak ich unikać

Standardowa pułapka dla początkujących — ignorowanie statystyk. Na przykład, dodałeś nowy indeks z myślą: „No, teraz zapytania będą śmigać”, a PostgreSQL go nie używa, bo statystyki nie zostały automatycznie zaktualizowane. Po większych zmianach w tabelach nie zapomnij ręcznie odświeżyć statystyk komendą ANALYZE.

Kolejny częsty błąd — obsesyjne dodawanie indeksów. Pamiętaj, każdy indeks zajmuje miejsce na dysku i spowalnia operacje wstawiania, aktualizacji i usuwania. Używaj statystyk pg_stat_user_indexes, żeby mieć pewność, że indeks jest naprawdę potrzebny, a nie leży bezużytecznie.

Praktyczna wartość wiedzy: gdzie się przyda?

W prawdziwym devie: jeśli baza zamula, najpierw będziesz szukać problemów z tabelami i indeksami.

Na rozmowie o pracę: pytania o optymalizację indeksów to klasyka SQL-interview. Umiesz wyjaśnić pg_stat_user_indexes? Już jesteś w połowie drogi do sukcesu.

W administrowaniu bazami danych: monitoring to codzienność DBA. Bez statystyk o tabelach i indeksach nic nie poprawisz.