Dlaczego baza „muli”? Krótkie spojrzenie na wydajność
Co naprawdę znaczy „wolna baza danych”
„Wolna baza” to zwykle mieszanka trzech zjawisk: wysokiego opóźnienia pojedynczych zapytań, niskiej przepustowości przy dużym ruchu oraz losowych pików, gdy wszystko nagle zamiera. Każde wymaga trochę innego podejścia.
Latency to czas odpowiedzi pojedynczego zapytania. Użytkownika interesuje głównie to. Na poziomie aplikacji widać to jako wolno ładujące się strony lub API.
Throughput to liczba zapytań na sekundę, które baza jest w stanie bezpiecznie przetworzyć. Zbyt niski throughput wobec ruchu prowadzi do kolejek, time-outów i „płaskich” wykresów CPU przy rosnącym czasie odpowiedzi.
Piki obciążenia powstają, gdy zbiega się kilka ciężkich zapytań, operacja raportowa lub batch i normalny ruch użytkowników. To często ujawnia problemy z indeksami, I/O lub blokadami.
Gdzie najczęściej leży problem z wydajnością
Źródło problemu rzadko jest jedno. Zwykle nakładają się:
Nieoptymalne zapytania – brak limitów, niepotrzebne JOIN-y, warunki po funkcjach, SELECT *.
Złe lub brakujące indeksy – pełne skany dużych tabel, sortowania na dysku, powtarzające się indeksy.
Schemat „z przypadku” – nietrafione typy danych, brak kluczy obcych, kolumny JSON zamiast normalnych relacji.
I/O i sprzęt – wolne dyski, brak pamięci dla buforów, brak rozdzielenia na dyski dla logów i danych.
Sieć i warstwa aplikacji – czatty connection pooling, N+1 zapytań zamiast jednego większego.
Bez analizy planów zapytań i pomiaru trudno zgadnąć, który element jest dominujący. Zaczyna się jednak prawie zawsze od zapytań i indeksów.
„Działa” kontra „działa stabilnie pod obciążeniem”
Aplikacja może mieć szybkie odpowiedzi na środowisku developerskim i jednocześnie dramatycznie zawodzić w produkcji. Dzieje się tak, gdy:
Po pierwsze, dane są małe i planner wybiera zupełnie inne strategie niż przy milionach wierszy. Po drugie, środowisko testowe ma inny rozkład danych (np. tylko „świeże” rekordy), więc selektywność filtrów jest złudna.
Stabilność pod obciążeniem wymaga:
sprawdzania planów na zbliżonym wolumenie danych,
stosowania indeksów pod dominujące ścieżki odczytu/zapisu,
monitorowania: czasów zapytań, blokad, zużycia buforów, operacji dyskowych.
Pojedyncze wolne zapytanie kontra systematyczne przeciążenie
W praktyce spotyka się dwa scenariusze:
1. Pojedyncze krytyczne zapytanie – np. raport, który trwa kilkanaście sekund i blokuje inne operacje. Rozwiązanie: analiza planu, dopasowanie indeksów, czasem zmiana algorytmu (materializacja, pre-aggregacja).
2. Ogólne „zamulenie” – większość zapytań powoli zwalnia, pojawiają się kolejki, rośnie obciążenie I/O. Tutaj zwykle problemem jest kombinacja: zbyt wielu małych, nieoptymalnych zapytań, słabe indeksy i brak regularnego utrzymania (VACUUM, ANALYZE, reindeksacja).
Rozpoznanie, z którym typem problemu masz do czynienia, ustawia priorytety. Przy pojedynczym zapytaniu warto użyć EXPLAIN ANALYZE i dobrać konkretny indeks. Przy ogólnym przeciążeniu najpierw mierzy się top N najwolniejszych lub najczęściej wykonywanych zapytań i szuka powtarzających się wzorców.
Podstawy: jak silniki PostgreSQL i MySQL wykonują zapytania
Pipeline zapytania: parser, planer, executor
W obu systemach droga zapytania SQL jest podobna:
Parser – sprawdza składnię i zamienia zapytanie na drzewo zapytania.
Planiści (planner/optimizer) – generują możliwe strategie wykonania i szacują ich koszt.
Executor – wykonuje wybrany plan krok po kroku, korzystając z buforów, indeksów i dysku.
Najwięcej „magii” dzieje się w etapie planowania. To tam zapada decyzja, czy użyć skanu sekwencyjnego, czy indeksu, czy zastosować hash join, czy nested loop. Planner opiera się na statystykach tabel i indeksów, dlatego ich jakość jest kluczowa dla optymalizacji.
PostgreSQL vs MySQL: jak różnią się planiści
PostgreSQL ma rozbudowany, cost-based planner. Rozważa różne kombinacje joinów, korzysta z zaawansowanych statystyk (histogramy, korelacje), ma wiele typów planów i joinów. Daje to dużą elastyczność, ale też większą wrażliwość na przestarzałe statystyki.
MySQL (InnoDB) ma prostszy planer, historycznie z istotnymi ograniczeniami (np. brak niektórych typów złożonych joinów, uproszczone szacunki). Z czasem wiele z nich zredukowano, ale nadal częściej trzeba mu „pomagać” odpowiednią konstrukcją zapytania i indeksów.
Dla praktyka oznacza to, że w PostgreSQL częściej wystarczy dobrze zaprojektować schemat i statystyki, a planner „sam” wybierze rozsądny plan. W MySQL częściej trzeba zwracać uwagę na kolejność tabel w JOIN, wskazywać indeksy lub dopasowywać zapytanie do możliwości optymalizatora.
Znaczenie statystyk i metadanych
Planiści opierają się na metadanych: liczbie wierszy w tabeli, rozkładzie wartości w kolumnie, korelacji między kolumnami, rozmiarze wierszy, dostępnych indeksach. Bez tego wybór planu jest w zasadzie zgadywaniem.
W PostgreSQL analizy statystyk dokonuje ANALYZE (ręcznie lub przez autovacuum). W MySQL główną rolę odgrywają ANALYZE TABLE i mechanizmy InnoDB, które aktualizują statystyki w tle.
Po zmianach w strukturze danych (np. dużych batchach insert/update/delete, migracjach) brak świeżych statystyk powoduje, że planery wybierają sekwencyjne skany zamiast indeksów, złe kolejności joinów albo tworzą ogromne struktury tymczasowe.
Strategie wykonania: od seq scan do hash join
Podstawowe strategie, które trzeba rozumieć:
Sequential scan – pełne przejście po tabeli. Dobre dla małych tabel lub mało selektywnych filtrów. Katastrofalne przy dużych tabelach i selektywnych warunkach.
Index scan – odczyt tylko tych pozycji z indeksu, które spełniają warunki. Może wymagać skoków po danych (random I/O), szczególnie przy złej lokalności danych.
Index-only scan – planer używa wyłącznie indeksu, bez sięgania do tabeli (gdy wszystkie potrzebne kolumny są w indeksie). Świetne dla typowych list i prostych raportów.
Nested loop join – dla każdej krotki z tabeli zewnętrznej szuka dopasowań w tabeli wewnętrznej (idealnie po indeksie). Bardzo wydajne przy małej tabeli zewnętrznej i indeksie na wewnętrznej.
Hash join (PostgreSQL) – buduje tabelę haszującą z jednej tabeli i przeszukuje ją dla każdej krotki z drugiej. Dobre przy dużych zbiorach i równomiernych rozkładach.
Merge join – łączy dwie posortowane sekwencje. Dobre, gdy plan i tak musi sortować lub gdy dane są wstępnie posortowane przez indeks.
W MySQL zestaw operatorów jest prostszy, większość joinów to warianty nested loop, ale sposób użycia indeksów i kolejność tabel mają duże znaczenie dla kosztu.
Projekt schematu pod wydajność – zanim dodasz pierwszy indeks
Normalizacja kontra denormalizacja
Normalizacja porządkuje dane i eliminuje redundancję. Dobra dla spójności, ale nadmiar poziomów pośrednich (zbyt rozdrobnione tabele) skutkuje lawiną JOIN-ów. To kosztuje pamięć, I/O i czas planowania.
dla tych przypadków celowe „spłaszczenie” danych: dodatkowe kolumny, tabele agregujące, materializowane widoki.
Denormalizacja ma sens tam, gdzie liczba odczytów jest ogromna, a zmiany rzadkie, lub gdzie spójność da się zapewnić logiką aplikacji czy triggerami. Przykład: przechowywanie computed_status obok stanu składowego, zamiast obliczania go za każdym razem z pięciu tabel.
Typy danych i ich wpływ na indeksy
Wybór typu danych to nie tylko kwestia wygody.
Problemy typowe:
Używanie TEXT i VARCHAR bez limitu tam, gdzie wystarczyłby krótki varchar lub enum.
Przechowywanie liczb jako tekstu – brak naturalnego porządku liczbowego, gorsze sortowanie, brak możliwości użycia niektórych optymalizacji.
Nadużywanie JSON/JSONB zamiast normalnych kolumn – trudniejsze indeksowanie i cięższe plany zapytań.
Im wiersz jest szerszy, tym mniej wierszy mieści się w jednej stronie danych, a to oznacza więcej operacji I/O przy skanach i gorszą lokalność odniesień. Szczególnie kluczowy jest rozmiar kluczy głównych i kolumn, po których wykonuje się joiny i sortowania.
Klucze główne: sekwencja czy UUID
W PostgreSQL i MySQL PRIMARY KEY jest domyślnie indeksowany jako BTREE, a w InnoDB jest jednocześnie clustered index, determinując fizyczny układ danych. W PostgreSQL klasteryzacja jest możliwa (CLUSTER), ale nie utrzymuje się automatycznie.
Porównanie zachowania:
Klucze sekwencyjne (INT/BIGINT, SERIAL, IDENTITY) – nowe wiersze lądują na końcu indeksu. Dobre wstawianie (append-only), lepsza lokalność, mniej fragmentacji.
UUID losowe – wstawiania trafiają w losowe miejsca indeksu. W InnoDB powoduje to rozrzucanie danych po stronach, więcej page splitów i gorszą lokalność cache.
UUID bywają wygodne w rozproszonych systemach i przy ekspozycji ID na zewnątrz, ale ich koszt na wydajność jest realny. Częsta praktyka: wewnętrzny, sekwencyjny klucz PK oraz osobna kolumna z UUID (z dodatkowym indeksem, tylko tam, gdzie naprawdę potrzebna).
Relacje i klucze obce a optymalizacja zapytań
Brak kluczy obcych upraszcza wstawianie danych, ale utrudnia plannerowi oszacowanie rozmiarów joinów. Z kluczami obcymi wiadomo, że każde child_id odpowiada realnemu rekordowi w tabeli nadrzędnej, a to poprawia szacunki cardinality.
Relacje wpływają też na indeksowanie:
Kolumna będąca FK prawie zawsze powinna mieć indeks, jeśli jest używana w joinach lub filtrach.
Composite keys (wielokolumnowe PK) trzeba projektować ostrożnie – przekombinowane klucze sklejone z wielu kolumn utrudniają efektywne indeksowanie.
Schemat zaprojektowany z myślą o typowych zapytaniach (np. „po użytkowniku i dacie”) wygrywa z projektem, który powstał tylko pod kątem struktury biznesowej, bez myślenia o wzorcach odczytu.
Planowanie schematu pod konkretne wzorce odczytu i zapisu
Dobry schemat nie jest „uniwersalny”, tylko dopasowany do tego, jak system będzie używany. Kluczowe pytania na starcie:
Jakie są najczęstsze SELECT-y? Po czym filtrują, jak sortują, po czym łączą tabele?
Jakie są wolumeny danych i typowe okna czasowe (ostatni dzień, tydzień, rok)?
Jak intensywny jest zapis w porównaniu do odczytu?
Dla systemu analitycznego sensowne mogą być szerokie tabele faktów z dobrze dobranymi indeksami pod typowe raporty. Dla systemu transakcyjnego – węższe tabele, silna normalizacja, wiele małych indeksów i ograniczanie dużych, złożonych joinów.
Indeksy od podstaw: jak je tworzyć, żeby faktycznie pomagały
Co przyspiesza indeks, a co spowalnia
Indeks przyspiesza odczyty, ale zawsze spowalnia zapisy. Każdy INSERT/UPDATE/DELETE musi zaktualizować wszystkie indeksy na danej tabeli.
Nieefektywny zestaw indeksów oznacza:
wolniejsze wstawianie i aktualizacje,
zwiększone zużycie dysku,
czasem gorsze plany, jeśli planner „widzi” wiele podobnych indeksów.
W praktyce zbyt wiele indeksów szkodzi bardziej niż brak jednego dobrze przemyślanego. Lepiej mieć 3–5 dobrze trafionych indeksów niż 15 losowych, dodanych z nadzieją, że „kiedyś się przydadzą”.
Kiedy indeks ma sens: selektywność i wzorce filtrów
Najprostsze kryteria opłacalności indeksu:
Kolumna (lub zestaw kolumn) pojawia się często w WHERE, JOIN, ORDER BY, GROUP BY.
Selektywność a rozkład danych
Sam fakt, że kolumna występuje w WHERE, nie wystarczy. Indeks opłaca się, gdy filtr eliminuje dużą część wierszy.
Indeks na kolumnie, która ma 2–3 różne wartości w milionach wierszy (np. status: ACTIVE/INACTIVE), zwykle jest słaby dla prostych zapytań typu WHERE status = 'ACTIVE'.
Indeksy na kolumnach o dużej liczbie unikalnych wartości (np. email, user_id, timestamp transakcji) zazwyczaj są dużo skuteczniejsze.
Rzeczywista selektywność zależy też od rozkładu. Warunek WHERE country = 'PL' może być selektywny w jednej bazie, a niemal bezużyteczny w innej, w zależności od danych.
Indeksy a wzorzec dostępu: od równoległych filtrów do sortowania
Indeks ma sens tam, gdzie jest powtarzalny wzorzec. Przykładowo API, które zawsze pobiera zamówienia użytkownika po dacie, „woła” o indeks na (user_id, created_at).
Dla zapytań łączących filtr i sortowanie:
PostgreSQL i MySQL potrafią wykorzystać indeks zarówno do filtracji, jak i do uniknięcia sortowania, jeśli porządek indeksu pasuje do ORDER BY.
Jeśli filtr używa jednej kolumny, a sortowanie innej, często lepszy bywa indeks wielokolumnowy niż dwa osobne.
Typowy antywzorzec: indeks na samej kolumnie sortowania, podczas gdy filtr używa innej kolumny – i tak powstanie kosztowny sort na wyniku.
Wielokolumnowe indeksy: kolejność ma znaczenie
Indeks złożony działa dobrze wtedy, gdy jego prefiks zgadza się z warunkami zapytania.
Założenie: indeks (user_id, created_at).
WHERE user_id = ? AND created_at > ? – indeks wykorzystany w pełni.
WHERE created_at > ? – użyteczny już znacznie mniej, planner w wielu przypadkach wybierze inny plan lub skan sekwencyjny.
Z tego wynika prosta zasada: na początku indeksu umieszczaj kolumny używane w równych warunkach (=, IN), potem w nierównych (>, <, BETWEEN), a na końcu te, które służą głównie sortowaniu.
Indeksy częściowe i warunkowe
W PostgreSQL bardzo pomocny bywa partial index:
CREATE INDEX idx_orders_active
ON orders (user_id, created_at)
WHERE status = 'ACTIVE';
Taki indeks jest mniejszy i bardziej selektywny. Idealny, gdy większość zapytań dotyczy tylko części danych (np. aktywnych rekordów, ostatnich okresów).
W MySQL nie ma bezpośredniego odpowiednika indeksu częściowego, ale podobny efekt daje osobna tabela dla „aktywnych” rekordów lub indeks na wyrażeniu (w nowszych wersjach) przy odpowiednio przygotowanej kolumnie.
Indeksy na wyrażeniach i kolumnach funkcjonalnych
Filtry typu WHERE lower(email) = lower(?) albo WHERE date(created_at) = ? zabijają wiele indeksów, bo funkcja stosowana na kolumnie uniemożliwia prosty odczyt posortowanych danych.
Rozwiązania są dwa:
Utrzymywanie dodatkowej kolumny „przygotowanej do wyszukiwania” (np. email_normalized, created_date) i indeks na niej.
Indeks na wyrażeniu: PostgreSQL – CREATE INDEX idx_email_lower ON users (lower(email));. W MySQL – generated column i indeks na niej.
Prosta refaktoryzacja typu „przenieść lower() do kolumny” potrafi obniżyć czas zapytania z sekund do milisekund.
Indeksy a operatory LIKE i wyszukiwanie tekstowe
Dla wyszukiwania tekstowego klasyczne BTREE łatwo „zabić” złym operatorem.
WHERE name LIKE 'abc%' – indeks może być użyty (prefiks).
WHERE name LIKE '%abc' lub '%abc%' – BTREE jest praktycznie bezużyteczne.
W PostgreSQL dla pełnotekstowego wyszukiwania używa się GIN/GIST na tsvector. W MySQL – FULLTEXT na odpowiednich kolumnach (z ograniczeniami co do typu i silnika).
Jeżeli wyszukiwanie „zawiera” ma być częścią biznesu, lepiej od razu zaplanować odpowiednie indeksy pełnotekstowe lub dedykowany silnik (np. Elastic), zamiast opierać wszystko na LIKE z wildcardami z przodu.
Indeksy unikalne a integralność i wydajność
Indeks unikalny nie tylko wymusza spójność, ale może uprościć plan zapytania.
Planner wie, że warunek po unikalnej kolumnie zwróci co najwyżej jeden rekord – może dobrać szybszą strategię joinu.
W MySQL unikalność często wpływa pozytywnie na wybór indeksu w JOIN-ach.
Z drugiej strony nadmiar unikalnych indeksów na często aktualizowanych tabelach zwiększa koszty wstawiania (każde INSERT musi sprawdzić konflikt) i ryzyko blokad przy konfliktach.
Źródło: Pexels | Autor: panumas nikhomkhai
Typy indeksów i ich zastosowanie w PostgreSQL i MySQL
BTREE – domyślny koń pociągowy
BTREE jest standardem w obu silnikach. Dobrze obsługuje równości, zakresy, sortowanie i większość typowych operatorów (<, <=, >, >=, BETWEEN).
Przykładowe zastosowania:
klucze główne i obce,
filtry po datach i identyfikatorach,
sortowanie po jednej lub kilku kolumnach.
W InnoDB każdy indeks BTREE to osobna struktura, a clustered index (PK) porządkuje fizyczne wiersze. W PostgreSQL wszystkie indeksy są niezależne od fizycznego ułożenia danych.
HASH – kiedyś ciekawostka, dzisiaj nisza
PostgreSQL ma indeksy HASH, ale praktyczne zastosowania są ograniczone. Działają tylko dla równości, nie obsługują zakresów i najczęściej BTREE wypada podobnie lub lepiej.
MySQL w InnoDB nie posiada osobnego typu HASH jako indeksu ogólnego przeznaczenia (HASH jest używany wewnętrznie np. w adaptive hash index). Gdy potrzeba indeksu do równości, BTREE wystarcza, a planner i tak wykorzystuje tylko prosty operator =.
GIN i GIST w PostgreSQL
Dla danych nieprostych (tekst, JSONB, tablice, geometria) PostgreSQL daje dwa istotne typy:
GIN – dobry do wyszukiwania „zawiera” w zbiorach, tekstach, JSONB (np. @>, ?).
GIST – bardziej ogólny, dla danych przestrzennych, zakresów, drzew i innych „dziwnych” typów.
Przykład GIN na JSONB:
CREATE INDEX idx_payload_gin
ON events USING GIN (payload jsonb_path_ops);
Bez takiego indeksu zapytania po kluczach zagnieżdżonych w JSONB zwykle robią pełne skany, niezależnie od ilości danych.
FULLTEXT i przestrzenne indeksy w MySQL
MySQL oferuje specjalne indeksy dla:
FULLTEXT – wyszukiwanie tekstowe po MATCH … AGAINST (InnoDB, MyISAM),
SPATIAL – dane geometryczne z funkcjami przestrzennymi.
FULLTEXT działa dobrze dla scenariuszy „szukaj po słowach kluczowych” w artykułach, opisach, logach. Nie zastąpi zaawansowanego silnika wyszukiwania, ale jest wystarczający dla wielu aplikacji biznesowych.
Indeksy częściowe i BRIN – PostgreSQL w dużej skali
Dla ogromnych tabel czasowych PostgreSQL ma przydatne narzędzia.
Partial indexes – indeks tylko na aktywną część danych (np. ostatni rok).
BRIN – indeksy „skrótowe”, które zapamiętują zakresy wartości dla bloków stron.
BRIN jest dobry tam, gdzie dane są naturalnie posortowane (np. rosnący timestamp), a zapytania operują na wąskich zakresach. Zajmuje mało miejsca i jest tani w utrzymaniu, kosztem nieco gorszej selektywności niż klasyczne BTREE.
Indeksy pokrywające (covering indexes)
Indeks pokrywający to taki, z którego planner potrafi zaspokoić całe zapytanie bez dotykania wiersza tabeli.
PostgreSQL osiąga to przez index-only scan wtedy, gdy widoczność wierszy została odpowiednio „przemieciona” przez VACUUM. MySQL (InnoDB) pozwala jawnie zdefiniować indeks obejmujący dodatkowe kolumny:
CREATE INDEX idx_orders_cover
ON orders (user_id, created_at)
INCLUDE (status, total_amount); -- PostgreSQL 11+
W MySQL odpowiednikiem jest po prostu indeks z dodatkowymi kolumnami w definicji. W obu silnikach chodzi o to samo – minimalizację losowych odczytów z tabeli.
Analiza planów zapytań – EXPLAIN w PostgreSQL i MySQL
Podstawy użycia EXPLAIN
Pierwszy krok przy każdej optymalizacji to sprawdzenie, co baza faktycznie robi.
EXPLAIN
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;
W PostgreSQL można dodać ANALYZE, aby zobaczyć rzeczywiste czasy wykonania i liczbę wierszy:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;
W MySQL używa się EXPLAIN, a dla szczegółów dodatkowo EXPLAIN ANALYZE w nowszych wersjach.
Kluczowe pola w planach PostgreSQL
Najbardziej przydatne elementy:
Node Type – rodzaj operacji (Seq Scan, Index Scan, Hash Join itp.).
Relation Name – na jakiej tabeli pracuje dany krok.
Index Name – który indeks został użyty.
Rows vs Actual Rows – szacunek kontra rzeczywistość.
Filter – warunki zastosowane w danym kroku.
Cost – startup..total, koszt szacunkowy (im wyższy, tym „drożej”).
Duża różnica między Rows a Actual Rows sugeruje problem ze statystykami lub korelacją kolumn.
Kluczowe pola w planach MySQL
Dla MySQL szczególnie interesujące są kolumny z klasycznego EXPLAIN:
type – rodzaj dostępu (ALL, index, range, ref, const, eq_ref). ALL oznacza pełny skan, const/eq_ref – najlepsze przypadki.
key – który indeks został wybrany.
rows – szacowana liczba wierszy do przejrzenia.
Extra – np. „Using where”, „Using index”, „Using temporary”, „Using filesort”.
„Using temporary” i „Using filesort” przy dużych zbiorach często sygnalizują problem z sortowaniem lub grupowaniem bez odpowiedniego indeksu.
Identyfikowanie pełnych skanów i złych joinów
Pełne skany tabel (Seq Scan w PostgreSQL, type=ALL w MySQL) są akceptowalne dla małych tabel lub zapytań bez filtrów. Przy dużych tabelach zwykle to pierwszy podejrzany.
W joinach zwróć uwagę na:
czy tabele łączone po kluczach mają indeksy na kolumnach joinu,
kolejność, w której planner łączy tabele (w MySQL bywa szczególnie istotna),
czy nie powstają gigantyczne struktury tymczasowe do sortowania lub grupowania.
Typowy przypadek z praktyki: zapytanie łączące 5 tabel, gdzie tylko 4 mają indeksy na kluczach obcych. Brak jednego indeksu na „mało ważnej” tabeli powoduje lawinowe powiększenie kosztu całego JOIN.
EXPLAIN ANALYZE: pomiar zamiast zgadywania
PostgreSQL i nowszy MySQL (8+) potrafią wykonać zapytanie i w raporcie EXPLAIN pokazać rzeczywiste czasy. To najlepsze narzędzie do porównywania alternatywnych wersji tego samego zapytania.
Warto (świadomie) wykonywać EXPLAIN ANALYZE na produkcji, ale tylko dla zapytań, które i tak są ciężkie, i z ograniczeniem liczby uruchomień. Plan zawiera wtedy m.in.:
czas każdego węzła (actual time),
liczbę wierszy wejściowych/wyjściowych,
informację o buforach (BUFFERS) w PostgreSQL.
Na tej podstawie łatwo znaleźć konkretny krok planu, który „puchnie”, zamiast zgadywać na oko.
Porównywanie planów po zmianach indeksów i zapytań
Po dodaniu indeksu lub zmianie zapytania dobrze jest porównać stare i nowe plany.
Praktyczne podejście:
zapisać plan (np. w pliku lub narzędziu typu pgAdmin/Workbench),
wykonać zmianę (indeks, refaktoryzacja WHERE/JOIN),
Śledzenie regresji wydajności po zmianach
Po każdej zmianie, która dotyka wydajności (indeks, refaktor zapytania, migracja wersji, zmiana konfiguracji), dobrze jest mieć punkt odniesienia.
Uruchom EXPLAIN (ANALYZE) / EXPLAIN ANALYZE przed zmianą i zachowaj wynik.
Po wdrożeniu powtórz pomiar na tym samym zestawie danych.
Porównaj: czas całkowity, ilość odczytanych wierszy, użyte typy skanów.
Prosty workflow: zrzut planu do pliku, commit zmiany, nowy zrzut, diff. Narzędzia typu auto_explain w PostgreSQL pozwalają też łapać najcięższe zapytania „w locie”.
Planer próbuje najpierw zastosować najbardziej selektywne warunki. Można mu pomóc, pisząc WHERE w sposób przewidywalny.
Unikaj funkcji na lewej stronie warunku indeksowanej kolumny (WHERE date(created_at) = ... zabija BTREE po created_at).
Stosuj zakresy zamiast równości na przekształconych wartościach (WHERE created_at >= '2023-01-01' AND created_at < '2023-02-01' zamiast date(created_at) = '2023-01-01').
Unikaj wzorców typu LIKE '%coś' bez indeksu fulltext lub GIN/GIST – indeks BTREE pomaga tylko przy prefixie ('coś%').
Jeśli filtr jest słabo selektywny (np. status z dwoma możliwymi wartościami), sam indeks po tej kolumnie zwykle nie pomaga – wtedy lepszy jest indeks z kolumną bardziej selektywną lub indeks złożony.
JOIN-y – dobór kluczy i kolejność
JOIN-y na dużych tabelach są zwykle głównym źródłem opóźnień.
Łącz po kolumnach o tym samym typie i tej samej semantyce (klucz główny <-> klucz obcy).
Zapewnij indeks na kluczu obcym po stronie „wiele” w relacji 1:N.
Unikaj JOIN-ów po wyrażeniach (np. ON lower(email) = lower(other_email)) bez osobnego indeksu na wyrażeniu w PostgreSQL.
MySQL jest wrażliwy na kolejność tabel w zapytaniu, choć od wersji 8 planner radzi sobie lepiej. Gdy mimo to uparcie wybiera złą kolejność, można użyć STRAIGHT_JOIN lub hintów, ale lepiej zacząć od indeksów.
Redukcja liczby JOIN-ów
Czasem największa optymalizacja to usunięcie zbędnego JOIN-a.
Kolumny rzadko używane w raportach można pobierać osobnym zapytaniem (np. lazy-load szczegółów w aplikacji).
JOIN tylko po to, by sprawdzić istnienie rekordu, można zastąpić EXISTS lub półzłączeniem (PostgreSQL).
Skrajne przypadki: materializowane widoki / tabele pomocnicze zamiast kaskady JOIN-ów na gorąco.
Przykładowy refaktor: raport łączący 7 tabel, z których 2 dostarczają pola do rzadko oglądanego eksportu. Osobne zapytanie dla eksportu zmniejsza czas generowania standardowego widoku o rząd wielkości.
Sortowanie i GROUP BY pod indeksy
Sortowanie i grupowanie bez indeksów kończy się sortem w pamięci lub na dysku.
Dla ORDER BY a, b przydatny jest indeks BTREE (a, b) w tej samej kolejności.
Dla GROUP BY a, b planner potrafi użyć indeksu do „group aggregate” (szczególnie w PostgreSQL).
Zbędne ORDER BY w podzapytaniach można usuwać, jeśli wynik i tak jest później sortowany.
Sygnatury problemu w MySQL: „Using temporary” + „Using filesort” dla dużych zestawów. W PostgreSQL – węzły Sort/External Sort z dużą liczbą wierszy i odczytów z dysku.
LIMIT, paginacja i „OFFSET problem”
LIMIT chroni przed zwracaniem tysięcy wierszy, ale sam nie redukuje kosztu skanowania, jeśli nie ma dobrego indeksu pod sortowanie.
Klasyczny problem paginacji:
SELECT *
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
Baza musi przetworzyć wszystkie poprzednie wiersze. Rozwiązania:
paginacja oparta na kursorze / kluczu (tzw. keyset pagination): WHERE created_at < ? i ORDER BY created_at DESC LIMIT 20,
zapamiętanie ostatniego klucza (np. id) po stronie aplikacji,
dla raportów – generowanie snapshotów lub eksportów, zamiast głębokiego „przeklikiwania” przez strony.
Keyset pagination zwykle wymaga indeksu z dopasowaną kolejnością (user_id, created_at DESC).
Unikanie antywzorców w WHERE i JOIN
Kilka konstrukcji notorycznie blokuje indeksy.
Warunki OR na różnych kolumnach bez indeksów kompozytowych – lepiej czasem rozbić na UNION ALL dwóch zapytań.
NOT IN, <> na słabo selektywnych kolumnach – często pełny skan i filtry w pamięci.
Porównania z NULL bez zrozumienia logiki trójwartościowej – col = NULL nigdy nie zwróci wierszy.
PostgreSQL radzi sobie lepiej z OR (bitmap index scan), ale przy dużej liczbie gałęzi i tak można skończyć z pełnym skanem.
Agregacje i okna – gdzie kończy się indeks
Indeks pomaga ograniczyć zbiór wejściowy do agregacji, ale sama agregacja zwykle dzieje się już w pamięci.
Jeśli filtr jest selektywny, indeks BTREE po kolumnie filtrowanej mocno pomaga.
Dla częstych, podobnych zapytań agregujących te same dane przydają się tabele zdenormalizowane lub materializowane widoki.
Funkcje okienkowe (np. ROW_NUMBER(), SUM() OVER) rzadko są „załatwiane” samym indeksem – trzeba liczyć się z sortem.
W raportach okresowych sensowne bywa przeliczanie dziennych/miesięcznych agregatów offline i trzymanie ich w prostych tabelach.
Statystyki, aktualizacja i autovacuum – fundament planera
Jak statystyki wpływają na plany zapytań
Planner opiera się na statystykach, żeby oszacować koszty i wybrać algorytm. Błędne statystyki = błędne decyzje.
PostgreSQL trzyma histogramy, most-common-values, korelacje. MySQL utrzymuje mniej bogate statystyki, ale i tak liczy rozkłady wartości i liczność.
Gdy szacunki w EXPLAIN (ANALYZE) mocno odbiegają od „Actual Rows”, najczęściej winne są nieaktualne statystyki.
Brak indeksu oznacza uboższe statystyki – planner musi zgadywać.
Autovacuum i bloat w PostgreSQL
PostgreSQL używa MVCC. Usunięte i nadpisane wiersze zostają fizycznie w tabeli, dopóki VACUUM ich nie posprząta.
Nadmiar martwych wierszy powoduje „bloat” – rośnięcie tabeli i indeksów.
Bloat obniża selektywność, zwiększa ilość odczytywanych stron i psuje plany.
Autovacuum próbuje to kontrolować, ale domyślne ustawienia nie są idealne dla każdej bazy.
Objawy: Seq Scan zamiast Index Scan na indeksowanych kolumnach, pliki tabel rosną szybciej niż liczba logicznych wierszy, rosną czasy prostych zapytań po kilku miesiącach pracy.
Konfiguracja autovacuum pod obciążenie
W silnie modyfikowanych tabelach warto ustawić agresywniejsze progi autovacuum.
ALTER TABLE events
SET (autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_scale_factor = 0.02);
Do tego parametry globalne:
autovacuum_max_workers – ile procesów VACUUM może działać równolegle,
autovacuum_vacuum_cost_limit i related – jak „agresywnie” może pracować.
Czasem i tak potrzebne są ręczne VACUUM (FULL) lub narzędzia typu pg_repack dla mocno rozdętych tabel.
ANALYZE i statystyki w PostgreSQL
ANALYZE aktualizuje statystyki. Autovacuum robi to okresowo, ale przy dużych skokach danych opłaca się wywołać ANALYZE ręcznie.
MySQL: information_schema.TABLES, performance_schema, status InnoDB.
Warto śledzić m.in. liczbę martwych wierszy, czas od ostatniego VACUUM/ANALYZE, wielkość indeksów w stosunku do liczby rekordów oraz listę najwolniejszych zapytań z logów slow query.
Statystyki a „dziwne” plany w praktyce
Częsty przypadek z produkcji: po masowej operacji (import, migracja) prosty SELECT nagle staje się wielokrotnie wolniejszy.
Autovacuum jeszcze nie zdążył – statystyki zakładają starą strukturę danych.
Planner wybiera zły indeks albo pełny skan, bo nie wie o zmianie selektywności.
Ręczne VACUUM ANALYZE problem rozwiązuje. Bez tego można godzinami dłubać w indeksach, podczas gdy wystarcza odświeżenie metadanych.
Bibliografia i źródła
PostgreSQL 16 Documentation: Query Planning. PostgreSQL Global Development Group (2023) – Opis planera zapytań, statystyk, typów skanów i joinów w PostgreSQL
PostgreSQL 16 Documentation: Routine Vacuuming. PostgreSQL Global Development Group (2023) – VACUUM, autovacuum, ANALYZE i wpływ na statystyki oraz wydajność
MySQL 8.0 Reference Manual: Optimization Overview. Oracle (2023) – Przegląd optymalizacji zapytań, indeksów i planera w MySQL InnoDB
