Kilka miesięcy temu — publiczny do PostgreSQL.
W tym czasie korzystaliście już z niego ponad 6000 razy, ale jedna z przydatnych funkcji mogła pozostać niezauważona — to podpowiedzi strukturalne, które wyglądają mniej więcej tak:
Słuchajcie ich, a wasze zapytania „staną się gładkie i jedwabiste”. 🙂
A tak na poważnie, wiele sytuacji, które sprawiają, że zapytanie jest wolne i „żerne” w zasoby, jest typowych i mogą być rozpoznawane na podstawie struktury i danych planu..
W takim przypadku każdy pojedynczy programista nie musi samodzielnie szukać sposobu na optymalizację, opierając się wyłącznie na swoim doświadczeniu — możemy mu podpowiedzieć, co się dzieje, w czym może być problem i jak można podejść do rozwiązania. Co zrobiliśmy.
Przyjrzyjmy się bliżej tym przypadkom — jak są definiowane i jakie rekomendacje z nich wynikają.
Aby lepiej wgłębić się w temat, najpierw można posłuchać odpowiedniego fragmentu z a potem przejść do szczegółowego omówienia każdego przykładu:
#1: индексная «недосортировка»
Kiedy występuje
Pokaż ostatni rachunek dla klienta „OOO Kołokołczik”.
Jak rozpoznać
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Zalecenia
Używany indeks rozszerzyć polami sortowania.
Przykład:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "faktów"
, (random() * 1000)::integer fk_cli; -- 1K różnych kluczy obcych
CREATE INDEX ON tbl(fk_cli); -- indeks dla klucza obcego
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- selekcja po konkretnej relacji
ORDER BY
pk DESC -- chcemy tylko jeden „ostatni” wpis
LIMIT 1; 
Od razu można zauważyć, że z indeksu odczytano ponad 100 rekordów, które następnie zostały wszystkie posortowane, a potem pozostawiono tylko jeden.
Naprawiamy:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- dodano klucz sortowania
Nawet na tak prymitywnej próbce — 8,5 razy szybciej i 33 razy mniej odczytów.Efekt będzie tym bardziej widoczny, im więcej będziecie mieć „faktów” dla każdej wartości fk..
Zauważam, że taki indeks będzie działał jako „prefiksowy” nie gorzej niż poprzedni i w innych zapytaniach z fk., gdzie sortowania po pk nie było i nie ma (więcej na ten temat można przeczytać ). W tym również zapewni normalne wsparcie dla jawnego klucza obcego po tym polu.
#2: пересечение индексов (BitmapAnd)
Kiedy występuje
Pokaż wszystkie umowy dla klienta „LLC Dzwoneczek”, zawarte w imieniu „NAO Lutek”.
Jak rozpoznać
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanZalecenia
Utwórz indeks złożony na polach z obu źródeł lub rozszerzyć jeden z istniejących o pola z drugiego.
Przykład:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "faktów"
, (random() * 100)::integer fk_org -- 100 różnych kluczy obcych
, (random() * 1000)::integer fk_cli; -- 1K różnych kluczy obcych
CREATE INDEX ON tbl(fk_org); -- indeks dla klucza obcego
CREATE INDEX ON tbl(fk_cli); -- indeks dla klucza obcego
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- wybór według konkretnej pary 
Naprawiamy:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Tutaj zysk jest mniejszy, ponieważ Bitmap Heap Scan jest wystarczająco wydajny sam w sobie. Mimo to 7 razy szybciej i 2,5 razy mniej odczytów.
#3: объединение индексов (BitmapOr)
Kiedy występuje
Pokaż pierwsze 20 najstarszych „swoich” lub nieprzypisanych zgłoszeń do przetworzenia, przy czym swoje mają priorytet.
Jak rozpoznać
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanZalecenia
Użyć UNION [ALL] do łączenia podzapytań według każdego z bloków OR warunków.
Przykład:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "faktów"
, CASE
WHEN random() < 1::real/16 THEN NULL -- z prawdopodobieństwem 1:16 wpis „remis”
ELSE (random() * 100)::integer -- 100 różnych kluczy obcych
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- indeks z "wydaje się odpowiednią" sortowaniem
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- swoje
fk_own IS NULL -- ... lub „remisy”
ORDER BY
pk
, (fk_own = 1) DESC -- najpierw „swoje”
LIMIT 20;
Naprawiamy:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- najpierw „swoje” 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- potem „remisy” 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- ale łącznie - 20, więcej nie potrzeba 
Skorzystaliśmy z tego, że wszystkie 20 potrzebnych zapisów zostało już pobranych w pierwszym bloku, dlatego drugi, z bardziej „kosztownym” Bitmap Heap Scan, nawet nie został wykonany — w efekcie 22 razy szybciej, 44 razy mniej odczytów!
Bardziej szczegółowe omówienie tej metody optymalizacji na konkretnych przykładach można przeczytać w artykułach i .
Ogólny wariant usystematyzowanego wyboru według wielu kluczy (a nie tylko według pary const/NULL) został omówiony w artykule .
#4: читаем много лишнего
Kiedy występuje
Zazwyczaj pojawia się przy chęci „dołożyć jeszcze jeden filtr” do już istniejącego zapytania.
„A czy nie macie takiego samego, ale z perłowymi guzikami?» x/f „Diamentowa ręka”
Na przykład, modyfikując powyższe zadanie, pokazujemy pierwsze 20 najstarszych „krytycznych” zgłoszeń do przetworzenia, niezależnie od ich przypisania.
Jak rozpoznać
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × wierszy 80% przeczytanego
&& pętli × RRbF > 100 -- a przy tym więcej niż 100 rekordów łącznie
Zalecenia
Stwórz [bardziej] specjalizowany indeks z warunkiem WHERE lub dołącz dodatkowe pola do indeksu.
Jeśli warunek filtrowania jest „statyczny” dla Twoich zadań — czyli nie przewiduje rozszerzenia listy wartości w przyszłości — lepiej użyć indeksu WHERE. W tę kategorię dobrze wpisują się różne statusy boolean/enum.
Jeśli jednak warunek filtrowania może przyjmować różne wartości, to lepiej rozszerzyć indeks o te pola — jak w sytuacji z BitmapAnd powyżej.
Przykład:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "faktów"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 różnych kluczy obcych
END fk_own
, (random() < 1::real/50) critical; -- 1:50, że zgłoszenie jest "krytyczne"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20; 
Naprawiamy:
CREATE INDEX ON tbl(pk)
WHERE critical; -- dodano "statyczny" warunek filtrowania
Jak widać, filtracja z planu całkowicie zniknęła, a zapytanie stało się 5 razy szybsze.
#5: разреженная таблица
Kiedy występuje
Różnorodne próby stworzenia własnej kolejki przetwarzania zadań, gdy duża ilość aktualizacji/usunięć rekordów w tabeli prowadzi do sytuacji wielkiej ilości „martwych” rekordów.
Jak rozpoznać
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& pętli × (wiersze + RRbF) 64
Zalecenia
Regularnie przeprowadzać ręcznie VACUUM [FULL] lub uzyskać odpowiednią częstotliwość jego działania poprzez odpowiednią konfigurację jego parametrów, w tym .
W większości przypadków podobne problemy są spowodowane słabym układem zapytań w wywołaniach z logiki biznesowej, jak te omówione w .
Ale należy rozumieć, że nawet VACUUM FULL nie zawsze może pomóc. W takich przypadkach warto zapoznać się z algorytmem z artykułu .
#6: чтение с «середины» индекса
Kiedy występuje
Wydaje się, że przeczytano nieco, wszystko na indeksie, i nikogo dodatkowo nie filtrowano — a mimo to przeczytano znacznie więcej stron, niż by się chciało.
Jak rozpoznać
-> Index [Only] Scan [Backward]
&& pętli × (wiersze + RRbF) 64
Zalecenia
Uważnie przyjrzyj się strukturze używanego indeksu i kluczowym polom określonym w zapytaniu — najprawdopodobniej część indeksu nie została określona. Najprawdopodobniej będziesz musiał stworzyć podobny indeks, ale bez prefiksowych pól lub .
Przykład:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "faktów"
, (random() * 100)::integer fk_org -- 100 różnych kluczy obcych
, (random() * 1000)::integer fk_cli; -- 1K różnych kluczy obcych
CREATE INDEX ON tbl(fk_org, fk_cli); -- wszystko prawie tak jak w #2
-- tylko że oddzielny indeks po fk_cli uznaliśmy za zbędny i usunęliśmy
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- a fk_org nie zostało określone, chociaż jest wcześniej w indeksie
LIMIT 20; 
Wydaje się, że wszystko jest w porządku, nawet według indeksu, ale jakoś podejrzanie — na każdą z 20 odczytanych rekordów trzeba było wyczytać 4 strony danych, 32KB na zapis — czy to nie jest za dużo? A nazwa indeksu tbl_fk_org_fk_cli_idx nasuwa refleksje.
Naprawiamy:
CREATE INDEX ON tbl(fk_cli); 
Nagle — 10 razy szybciej i 4 razy mniej do czytania!
Inne przykłady nieefektywnego wykorzystania indeksów można zobaczyć w artykule .
#7: CTE × CTE
Kiedy występuje
W zapytaniu podaliśmy "grube" CTE z różnych tabel, a potem postanowiliśmy zrobić między nimi JOIN.
Przypadek aktualny dla wersji poniżej v12 lub zapytań z WITH MATERIALIZED.
Jak rozpoznać
-> CTE Scan
&& pętle > 10
&& pętle × (wiersze + RRbF) > 10000
-- zbyt duża iloczynowa kombinacja CTE
Zalecenia
Dokładnie przeanalizować zapytanie — a ? Если все-таки да, то zastosować "słownikowanie" w hstore/json zgodnie z modelem opisanym w .
#8: swap на диск (temp written)
Kiedy występuje
Jednorazowa obróbka (sortowanie lub unikalność) dużej ilości rekordów nie mieści się w przydzielonej pamięci.
Jak rozpoznać
-> *
&& pamięć tymczasowa zapisana > 0Zalecenia
Jeśli przekroczona ilość używanej pamięci przez operację niewiele przewyższa ustaloną wartość parametru , warto go skorygować. Można to zrobić od razu w konfiguracji dla wszystkich, a można przez SET [LOCAL] dla konkretnego zapytania/transakcji.
Przykład:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1; 
Naprawiamy:
SET work_mem = '128MB'; -- przed wykonaniem zapytania 
Z oczywistych powodów, jeśli używana jest tylko pamięć, a nie dysk, zapytanie będzie wykonywane dużo szybciej. Przy tym część obciążenia z HDD jest również usuwana.
Ale należy zrozumieć, że przydzielanie bardzo dużo pamięci także nie jest możliwe — po prostu nie wystarczy jej dla wszystkich.
#9: неактуальная статистика
Kiedy występuje
Do bazy wpłynęło od razu dużo, ale nie zdążyliśmy przetworzyć ANALYZE.
Jak rozpoznać
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& stosunek >> 10Zalecenia
Należy to przeprowadzić ANALYZE.
W tej sytuacji szczegóły opisano w .
#10: «что-то пошло не так»
Kiedy występuje
Wystąpiło oczekiwanie na blokadę nałożoną przez konkurencyjne zapytanie lub zabrakło zasobów sprzętowych CPU/hypervisora.
Jak rozpoznać
-> *
&& (hit wspólny / 8K) + (odczyt wspólny / 1K) < czas / 1000
-- hit RAM = 64MB/s, odczyt HDD = 8MB/s
&& czas > 100ms -- mało odczytano, ale zbyt długo
Zalecenia
Użyj zewnętrznej systemu monitorowania serwerów pod kątem blokad lub nienormatywnego zużycia zasobów. O naszym sposobie organizacji tego procesu dla setek serwerów już opowiadaliśmy i .
Źródło: habr.com
