Przepisy na chore zapytania SQL

Miesiące temu ogłosiliśmy wyjaśnić.tensor.ru - publiczne usługa analizowania i wizualizacji planów zapytań do PostgreSQL.

Od tamtej pory korzystałeś z niej ponad 6000 razy, ale jedną z przydatnych funkcji mogła pozostać niezauważona jest wskazówki strukturalne, które wyglądają mniej więcej tak:

Słuchaj ich, a Twoje prośby staną się „jedwabiście gładkie”. 🙂

Ale poważnie, wiele sytuacji, które sprawiają, że prośba jest powolna i „żarłoczna” pod względem zasobów, są typowe i można je rozpoznać po strukturze i danych planu.

W takim przypadku każdy programista nie będzie musiał samodzielnie szukać opcji optymalizacji, opierając się wyłącznie na własnym doświadczeniu - możemy mu powiedzieć, co się tutaj dzieje, jaka może być tego przyczyna i jak wymyślić rozwiązanie. Co właśnie zrobiliśmy.

Przyjrzyjmy się bliżej tym przypadkom – jak są definiowane i do jakich rekomendacji prowadzą.

Aby lepiej zanurzyć się w temacie, możesz najpierw posłuchać odpowiedniego bloku z mój raport na PGConf.Russia 2020, a dopiero potem przejść do szczegółowej analizy każdego przykładu:

#1: indeks „podsortowanie”

Kiedy powstaje

Pokaż ostatnią fakturę dla klienta „LLC Kolokolchik”.

Jak zidentyfikować

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

zalecenia

Używany indeks rozwiń o pola sortowania.

Przykład:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- отбор по конкретной связи
ORDER BY
  pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;

[patrz na explain.tensor.ru]

Od razu można zauważyć, że indeks odjął ponad 100 rekordów, które następnie wszystkie zostały posortowane, a następnie został tylko jeden.

Naprawimy:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

[patrz na explain.tensor.ru]

Nawet na tak prymitywnej próbce - 8.5x szybciej i 33x mniej odczytów. Efekt będzie tym wyraźniejszy, im więcej „faktów” będziesz mieć dla każdej wartości. fk.

Zauważam, że taki indeks będzie działał jako indeks „przedrostkowy” nie gorszy niż poprzedni dla innych zapytań z fk, gdzie sortowanie według pk nie było i nie jest (możesz przeczytać więcej na ten temat w moim artykule o znajdowaniu nieefektywnych indeksów). W szczególności zapewni normalne wyraźna obsługa klucza obcego przez to pole.

#2: przecięcie indeksu (BitmapAnd)

Kiedy powstaje

Pokaż wszystkie umowy dla klienta „LLC Kolokolchik” zawarte w imieniu „NJSC Lyutik”.

Jak zidentyfikować

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

zalecenia

Utworzyć indeks złożony według pól z obu źródeł lub rozwiń jedno z istniejących pól z drugiego.

Przykład:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре

[patrz na explain.tensor.ru]

Naprawimy:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[patrz na explain.tensor.ru]

Tutaj zysk jest mniejszy, ponieważ Bitmap Heap Scan jest dość skuteczny sam w sobie. Ale w każdym razie 7x szybciej i 2.5x mniej odczytów.

#3: Łączenie indeksów (BitmapOr)

Kiedy powstaje

Pokaż pierwszych 20 najstarszych „własnych” lub nieprzypisanych żądań do przetworzenia, z priorytetem własnych.

Jak zidentyfikować

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

zalecenia

Использовать UNIA [WSZYSTKIE] aby połączyć podzapytania dla każdego z bloków warunkowych LUB.

Przykład:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- свои
  fk_own IS NULL -- ... или "ничьи"
ORDER BY
  pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;

[patrz na explain.tensor.ru]

Naprawimy:

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- сначала "свои" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- потом "ничьи" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо

[patrz na explain.tensor.ru]

Wykorzystaliśmy fakt, że wszystkie 20 potrzebnych rekordów uzyskano od razu w pierwszym bloku, więc drugi, z „droższym” Bitmap Heap Scan, nie został nawet wykonany – w rezultacie 22x szybciej, 44x mniej odczytów!

Bardziej szczegółowa historia tej metody optymalizacji na konkretnych przykładach można przeczytać w artykułach Antywzorce PostgreSQL: szkodliwe JOIN i OR и Antywzorce PostgreSQL: opowieść o iteracyjnym udoskonalaniu wyszukiwania według nazwy lub „optymalizacji tam iz powrotem”.

Wersja uogólniona uporządkowany wybór za pomocą kilku klawiszy (i nie tylko dla pary const / NULL) jest omówiona w artykule SQL HowTo: napisz pętlę while bezpośrednio w zapytaniu lub „Elementary trójdrożny”.

#4: Za dużo czytamy

Kiedy powstaje

Z reguły ma to miejsce, gdy chcesz „dołączyć kolejny filtr” do istniejącego żądania.

„I nie masz tego samego, ale z perłowymi guzikami? " film „Diamentowa ręka”

Na przykład modyfikując powyższe zadanie, pokaż pierwsze 20 najstarszych „krytycznych” żądań do przetworzenia, niezależnie od ich celu.

Jak zidentyfikować

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

zalecenia

Stwórz [bardziej] wyspecjalizowane indeks z klauzulą ​​WHERE lub dołącz dodatkowe pola do indeksu.

Jeśli warunek filtrowania jest „statyczny” dla twoich zadań - to znaczy nie obejmuje rozbudowy lista wartości w przyszłości - lepiej użyć indeksu WHERE. Różne statusy logiczne/wyliczeniowe dobrze pasują do tej kategorii.

Jeśli warunek filtracji może przyjmować różne wartości, 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 "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

[patrz na explain.tensor.ru]

Naprawimy:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[patrz na explain.tensor.ru]

Jak widać, filtrowanie z planu całkowicie zniknęło, a żądanie stało się 5 razy szybciej.

# 5: rzadki stół

Kiedy powstaje

Różne próby stworzenia własnej kolejki przetwarzania zadań, gdy duża ilość aktualizacji/usunięć rekordów w tabeli prowadzi do sytuacji dużej ilości „martwych” rekordów.

Jak zidentyfikować

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

zalecenia

Ręcznie przeprowadzaj regularnie PRÓŻNIA [PEŁNA] lub osiągnąć odpowiednio częste przetwarzanie autopróżnia poprzez dostrojenie jego parametrów, m.in dla konkretnego stołu.

W większości przypadków takie problemy są spowodowane złym układem zapytań wywoływanych z logiki biznesowej, na przykład omówionych w PostgreSQL Antywzorce: walka z hordami „martwych”.

Ale musimy zrozumieć, że nawet VACUUM FULL nie zawsze może pomóc. W takich przypadkach powinieneś zapoznać się z algorytmem z artykułu. DBA: gdy przechodzi VACUUM, czyścimy stół ręcznie.

#6: czytanie ze „środka” indeksu

Kiedy powstaje

Wygląda na to, że trochę czytali, a wszystko zostało zindeksowane i nikogo dodatkowo nie filtrowali - ale mimo to przeczytano znacznie więcej stron, niż byśmy chcieli.

Jak zidentyfikować

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

zalecenia

Przyjrzyj się dokładnie strukturze użytego indeksu i kluczowym polom podanym w zapytaniu - najprawdopodobniej część indeksu nie ustawiona. Najprawdopodobniej będziesz musiał utworzyć podobny indeks, ale bez pól prefiksu lub naucz się iterować ich wartości.

Przykład:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;

[patrz na explain.tensor.ru]

Niby wszystko w porządku, nawet jeśli chodzi o indeks, ale jakoś podejrzanie - za każdy z 20 odczytanych rekordów trzeba było odjąć 4 strony danych, po 32 KB na rekord - czy to nie odważne? Tak i nazwa indeksu tbl_fk_org_fk_cli_idx prowadzi do myślenia.

Naprawimy:

CREATE INDEX ON tbl(fk_cli);

[patrz na explain.tensor.ru]

Nagle - 10 razy szybciej i 4 razy mniej do czytania!

Więcej przykładów nieefektywnego wykorzystania indeksów znajdziesz w artykule DBA: znajdź bezużyteczne indeksy.

#7: CTE × CTE

Kiedy powstaje

Modny uzyskał „gruby” CTE z różnych stołów, a następnie postanowił zrobić między nimi JOIN.

Sprawa dotyczy wersji poniżej v12 lub żądań z WITH MATERIALIZED.

Jak zidentyfikować

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

zalecenia

Dokładnie przeanalizuj prośbę czy CTE są tu w ogóle potrzebne? Jeśli tak, to zastosuj „słownik” w hstore/json według wzoru opisanego w art Antywzorce PostgreSQL: Słownik Hit Heavy JOIN.

#8: zamiana na dysk (zapis tymczasowy)

Kiedy powstaje

Jednorazowe przetwarzanie (sortowanie lub unikatowanie) dużej liczby rekordów nie mieści się w przeznaczonej na to pamięci.

Jak zidentyfikować

-> *
   && temp written > 0

zalecenia

Jeśli ilość pamięci używanej przez operację nie przekracza znacznie ustawionej wartości parametru pamięć_robocza, należy to poprawić. Możesz od razu w konfiguracji dla wszystkich lub możesz przejść SET [LOCAL] dla konkretnego żądania/transakcji.

Przykład:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[patrz na explain.tensor.ru]

Naprawimy:

SET work_mem = '128MB'; -- перед выполнением запроса

[patrz na explain.tensor.ru]

Z oczywistych względów, jeśli używana jest tylko pamięć, a nie dysk, to zapytanie zostanie wykonane znacznie szybciej. W tym samym czasie część obciążenia jest również usuwana z dysku twardego.

Ale musisz zrozumieć, że przydzielenie dużej ilości pamięci też nie zawsze będzie działać - po prostu nie wystarczy dla wszystkich.

#9: Nieistotne statystyki

Kiedy powstaje

Do bazy wlano dużo od razu, ale nie mieli czasu, aby ją odpędzić ANALYZE.

Jak zidentyfikować

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

zalecenia

Spędź tyle samo ANALYZE.

Ta sytuacja została dokładniej opisana w PostgreSQL Antywzorce: statystyki są na czele wszystkiego.

#10: „coś poszło nie tak”

Kiedy powstaje

Blokada czekała na konkurencyjne żądanie lub nie było wystarczających zasobów sprzętowych procesora/hiperwizora.

Jak zidentyfikować

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

zalecenia

Użyj zewnętrznego system monitorujący serwera do blokowania lub nieprawidłowego zużycia zasobów. Mówiliśmy już o naszej wersji organizacji tego procesu dla setek serwerów. tutaj и tutaj.

Źródło: www.habr.com