Recepty pre choré SQL dotazy

Pred mesiacmi oznámili sme vysvetliť.tensor.ru - verejný služba na analýzu a vizualizáciu plánov dotazov do PostgreSQL.

Použili ste ho už viac ako 6000 XNUMX-krát, no jedna užitočná funkcia, ktorá mohla zostať nepovšimnutá, je štrukturálne stopy, ktoré vyzerajú asi takto:

Počúvajte ich a vaše požiadavky budú „hladké a hodvábne“. 🙂

Ale vážne, mnohé situácie spôsobujú, že žiadosť je pomalá a náročná na zdroje sú typické a dajú sa rozpoznať podľa štruktúry a údajov plánu.

V tomto prípade každý jednotlivý vývojár nemusí hľadať možnosť optimalizácie sám, spoliehajúc sa výlučne na svoje skúsenosti - môžeme mu povedať, čo sa tu deje, čo by mohlo byť dôvodom a ako pristupovať k riešeniu. To sme urobili.

Pozrime sa na tieto prípady bližšie – ako sú definované a k akým odporúčaniam vedú.

Aby ste sa lepšie ponorili do témy, môžete si najprv vypočuť príslušný blok z moja správa na PGConf.Russia 2020a až potom prejdite na podrobnú analýzu každého príkladu:

#1: index „podtriedenie“

Keď vznikne

Ukážte najnovšiu faktúru pre klienta "LLC Kolokolchik".

Ako identifikovať

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

Odporúčanie

Použitý index rozšíriť o triediace polia.

Príklad:

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;

[pozrite sa na explain.tensor.ru]

Okamžite si môžete všimnúť, že z indexu bolo odčítaných viac ako 100 záznamov, ktoré boli následne všetky zoradené a potom zostal jediný.

Oprava:

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

[pozrite sa na explain.tensor.ru]

Aj na takejto primitívnej vzorke - 8.5-krát rýchlejšie a 33-krát menej čítaní. Čím viac „faktov“ máte pre každú hodnotu, tým je efekt zreteľnejší fk.

Všimol som si, že takýto index bude fungovať ako „predponový“ index nie horšie ako predtým pre iné dotazy s fk, kde zoradiť podľa pk nebolo a nie je (môžete si o tom prečítať viac v mojom článku o hľadaní neúčinných indexov). Vrátane bude poskytovať normálne explicitná podpora cudzieho kľúča na tomto poli.

#2: priesečník indexu (BitmapAnd)

Keď vznikne

Zobraziť všetky zmluvy pre klienta „LLC Kolokolchik“, uzatvorené v mene „NAO Buttercup“.

Ako identifikovať

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

Odporúčanie

vytvoriť zložený index o polia z oboch pôvodných alebo rozšíriť jedno z existujúcich o polia z druhého.

Príklad:

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); -- отбор по конкретной паре

[pozrite sa na explain.tensor.ru]

Oprava:

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

[pozrite sa na explain.tensor.ru]

Odmena je tu menšia, pretože Bitmap Heap Scan je dosť efektívny sám o sebe. Ale aj tak 7-krát rýchlejšie a 2.5-krát menej čítaní.

#3: Zlúčiť indexy (BitmapOr)

Keď vznikne

Zobrazte prvých 20 najstarších „nás“ alebo nepriradených žiadostí na spracovanie, pričom vaša má prioritu.

Ako identifikovať

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

Odporúčanie

použitie ÚNIE [VŠETCI] skombinovať poddotazy pre každý z blokov OR podmienok.

Príklad:

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;

[pozrite sa na explain.tensor.ru]

Oprava:

(
  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, больше и не надо

[pozrite sa na explain.tensor.ru]

Využili sme, že všetkých 20 požadovaných záznamov bolo prijatých hneď v prvom bloku, takže druhý, s „drahším“ Bitmap Heap Scan, sa nakoniec ani nevykonával. 22x rýchlejšie, 44x menej čítaní!

Podrobnejší príbeh o tejto metóde optimalizácie pomocou konkrétnych príkladov si môžete prečítať v článkoch PostgreSQL Antipatterns: Škodlivé JOINy ​​a OR и PostgreSQL Antipatterns: Príbeh o opakovanom spresňovaní vyhľadávania podľa názvu alebo „Optimalizácia tam a späť“.

Zovšeobecnená verzia usporiadaný výber na základe niekoľkých kľúčov (a nielen páru const/NULL) je diskutovaný v článku SQL HowTo: napíšte while-loop priamo v dotaze alebo "Elementary troch-way".

#4: Čítame veľa nepotrebných vecí

Keď vznikne

Spravidla vzniká, keď chcete „pripojiť ďalší filter“ k už existujúcej požiadavke.

„A ty nemáš rovnaký, ale s perleťovými gombíkmi? " film "Diamantové rameno"

Napríklad úpravou vyššie uvedenej úlohy zobrazte prvých 20 najstarších „kritických“ žiadostí na spracovanie bez ohľadu na ich účel.

Ako identifikovať

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

Odporúčanie

Vytvorte [viac] špecializované index s podmienkou WHERE alebo zahrnúť do indexu ďalšie polia.

Ak je stav filtra pre vaše účely "statický" - to znamená neznamená expanziu zoznam hodnôt v budúcnosti - je lepšie použiť index WHERE. Do tejto kategórie dobre zapadajú rôzne boolovské/enumové stavy.

Ak je podmienka filtrovania môže nadobudnúť rôzne významy, potom je lepšie rozšíriť index o tieto polia - ako v situácii s BitmapAnd vyššie.

Príklad:

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;

[pozrite sa na explain.tensor.ru]

Oprava:

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

[pozrite sa na explain.tensor.ru]

Ako vidíte, filtrovanie z plánu úplne zmizlo a požiadavka sa stala 5 krát rýchlejšie.

#5: riedky stôl

Keď vznikne

Rôzne pokusy o vytvorenie vlastného frontu spracovania úloh, kedy veľké množstvo aktualizácií/zmazaní záznamov v tabuľke vedie k situácii veľkého počtu „mŕtvych“ záznamov.

Ako identifikovať

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

Odporúčanie

Vykonávajte pravidelne ručne VÁKUUM [PLNÉ] alebo dosiahnuť primerane časté školenia autovákuum doladením jeho parametrov vrátane pre konkrétny stôl.

Vo väčšine prípadov sú takéto problémy spôsobené zlým rozložením dotazov pri volaní z obchodnej logiky, ako je uvedené v PostgreSQL Antipatterns: boj s hordami "mŕtvych".

Musíte však pochopiť, že ani VACUUM FULL nemusí vždy pomôcť. V takýchto prípadoch sa oplatí zoznámiť sa s algoritmom z článku DBA: keď zlyhá VACUUM, vyčistíme stôl ručne.

#6: Čítanie od „stredu“ indexu

Keď vznikne

Zdá sa, že sme čítali málo a všetko bolo indexované a nikoho sme nefiltrovali nadbytočne – no aj tak čítame podstatne viac stránok, ako by sme chceli.

Ako identifikovať

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

Odporúčanie

Pozrite sa pozorne na štruktúru použitého indexu a kľúčové polia špecifikované v dotaze – s najväčšou pravdepodobnosťou časť indexu nie je nastavená. S najväčšou pravdepodobnosťou budete musieť vytvoriť podobný index, ale bez predponových polí resp naučiť sa opakovať svoje hodnoty.

Príklad:

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;

[pozrite sa na explain.tensor.ru]

Všetko sa zdá byť v poriadku, dokonca aj podľa indexu, ale je to nejako podozrivé - pre každý z 20 prečítaných záznamov sme museli odpočítať 4 strany údajov, 32 kB na záznam - nie je to odvážne? A názov indexu tbl_fk_org_fk_cli_idx na zamyslenie.

Oprava:

CREATE INDEX ON tbl(fk_cli);

[pozrite sa na explain.tensor.ru]

zrazu - 10-krát rýchlejšie a 4-krát menej na čítanie!

Ďalšie príklady situácií neefektívneho využívania indexov si môžete pozrieť v článku DBA: hľadanie zbytočných indexov.

#7: CTE × CTE

Keď vznikne

V žiadosti skóroval „tučný“ CTE z rôznych stolov a potom sa rozhodol urobiť to medzi nimi JOIN.

Prípad je relevantný pre verzie nižšie ako v12 alebo žiadosti s WITH MATERIALIZED.

Ako identifikovať

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

Odporúčanie

Starostlivo analyzujte žiadosť - a Sú tu vôbec potrebné CTE?? Ak áno, tak použite "slovník" v hstore/json podľa modelu opísaného v PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN.

#8: výmena na disk (dočasný zápis)

Keď vznikne

Jednorazové spracovanie (triedenie alebo unikátnosť) veľkého počtu záznamov sa nezmestí do pamäte na to vyčlenenej.

Ako identifikovať

-> *
   && temp written > 0

Odporúčanie

Ak množstvo pamäte používanej operáciou výrazne nepresahuje špecifikovanú hodnotu parametra work_mem, oplatí sa to opraviť. Môžete okamžite v konfigurácii pre všetkých, alebo môžete prejsť SET [LOCAL] pre konkrétnu požiadavku/transakciu.

Príklad:

SHOW work_mem;
-- "16MB"

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

[pozrite sa na explain.tensor.ru]

Oprava:

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

[pozrite sa na explain.tensor.ru]

Zo zrejmých dôvodov, ak sa používa iba pamäť a nie disk, dopyt sa vykoná oveľa rýchlejšie. Zároveň sa odstráni aj časť záťaže z HDD.

Musíte však pochopiť, že nie vždy budete môcť prideliť veľa a veľa pamäte - jednoducho nebude dosť pre každého.

#9: irelevantná štatistika

Keď vznikne

Naliali veľa do databázy naraz, ale nemali čas to odohnať ANALYZE.

Ako identifikovať

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

Odporúčanie

Vykonajte to ANALYZE.

Táto situácia je podrobnejšie opísaná v PostgreSQL Antipatterns: štatistika je všetko.

#10: „niečo sa pokazilo“

Keď vznikne

Čakalo sa na uzamknutie vyvolané konkurenčnou požiadavkou alebo boli nedostatočné hardvérové ​​zdroje CPU/hypervízora.

Ako identifikovať

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

Odporúčanie

Použite externé monitorovací systém server pre blokovanie alebo abnormálnu spotrebu zdrojov. Už sme hovorili o našej verzii organizácie tohto procesu pre stovky serverov tu и tu.

Zdroj: hab.com