PostgreSQL Antipatterns: Príbeh o opakovanom spresňovaní vyhľadávania podľa názvu alebo „Optimalizácia tam a späť“

Rekordujú tisíce manažérov z obchodných kancelárií po celej krajine náš CRM systém desiatky tisíc kontaktov denne — fakty o komunikácii s potenciálnymi alebo existujúcimi klientmi. A na to musíte najprv nájsť klienta, najlepšie veľmi rýchlo. A to sa deje najčastejšie podľa mena.

Preto nie je prekvapujúce, že opäť raz analyzujeme „ťažké“ dopyty na jednej z najviac zaťažených databáz – našej vlastnej firemný účet VLSI, našiel som "v hornej časti" žiadosť o „rýchle“ vyhľadávanie podľa mena pre organizačné karty.

Navyše ďalšie skúmanie odhalilo zaujímavý príklad najprv optimalizácia a potom zníženie výkonu žiadosť s jej postupným spresňovaním niekoľkými tímami, z ktorých každý konal len s tými najlepšími úmyslami.

0: čo používateľ chcel?

[KDPV preto]

Čo má používateľ zvyčajne na mysli, keď hovorí o „rýchlom“ vyhľadávaní podľa mena? Takmer nikdy sa neukáže, že ide o „poctivé“ hľadanie podreťazca ako ... LIKE '%роза%' - pretože potom výsledok zahŕňa nielen 'Розалия' и 'Магазин Роза'avšak 'Гроза' a dokonca 'Дом Деда Мороза'.

Používateľ na každodennej úrovni predpokladá, že mu poskytnete hľadať podľa začiatku slova v názve a urobte ho relevantnejším začína o zadané. A ty to urobíš takmer okamžite - pre interlineárny vstup.

1: obmedziť úlohu

A ešte viac, človek konkrétne nevstúpi 'роз магаз', takže každé slovo musíte hľadať podľa predpony. Nie, pre používateľa je oveľa jednoduchšie reagovať na rýchlu nápovedu na posledné slovo, ako zámerne „podšpecifikovať“ predchádzajúce – pozrite sa, ako to rieši každý vyhľadávací nástroj.

všeobecne, správne formulovanie požiadaviek na problém je viac ako polovica riešenia. Niekedy starostlivá analýza prípadov použitia môže výrazne ovplyvniť výsledok.

Čo robí abstraktný vývojár?

1.0: externý vyhľadávač

Ach, hľadanie je ťažké, nechcem robiť vôbec nič - dajme to devopsom! Nechajte ich nasadiť externý vyhľadávač k databáze: Sphinx, ElasticSearch,...

Pracovná možnosť, aj keď náročná na prácu z hľadiska synchronizácie a rýchlosti zmien. Ale nie v našom prípade, pretože vyhľadávanie sa vykonáva pre každého klienta iba v rámci údajov o jeho účte. A údaje majú dosť vysokú variabilitu - a ak manažér teraz zadal kartu 'Магазин Роза', potom si po 5-10 sekundách už možno spomenie, že tam zabudol uviesť svoj email a chce ho nájsť a opraviť.

Preto – poďme hľadať „priamo v databáze“. Našťastie nám to PostgreSQL umožňuje, a nielen jednu možnosť – pozrieme sa na ne.

1.1: "čestný" podreťazec

Držíme sa slova „podreťazec“. Ale pre indexové vyhľadávanie podľa podreťazca (a dokonca aj podľa regulárnych výrazov!) je vynikajúci modul pg_trgm! Až potom bude potrebné správne triediť.

Skúsme si zobrať nasledujúcu tabuľku na zjednodušenie modelu:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

Nahrávame tam 7.8 milióna záznamov skutočných organizácií a indexujeme ich:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

Pozrime sa na prvých 10 záznamov pre interlineárne vyhľadávanie:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

[pozrite sa na explain.tensor.ru]

No to je... 26 ms, 31 MB prečítaných údajov a viac ako 1.7 tis. filtrovaných záznamov - pre 10 hľadaných. Režijné náklady sú príliš vysoké, neexistuje niečo efektívnejšie?

1.2: vyhľadávanie podľa textu? Je to FTS!

V skutočnosti, PostgreSQL poskytuje veľmi výkonný fulltextový vyhľadávač (Full Text Search), vrátane možnosti vyhľadávania prefixov. Skvelá možnosť, nemusíte ani inštalovať rozšírenia! Vyskúšajme:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

[pozrite sa na explain.tensor.ru]

Tu nám trochu pomohla paralelizácia vykonávania dotazov, ktorá skrátila čas na polovicu 11 ms. A to sme museli čítať 1.5-krát menej – celkovo 20MB. Tu však platí, že čím menej, tým lepšie, pretože čím väčší objem čítame, tým vyššia je šanca, že dôjde k vynechaniu vyrovnávacej pamäte, a každá ďalšia stránka údajov načítaná z disku je potenciálnou „brzdou“ požiadavky.

1.3: stále LIKE?

Predchádzajúca požiadavka je dobrá pre každého, ale len ak ju potiahnete stotisíckrát denne, príde 2TB čítať dáta. V najlepšom prípade z pamäte, ale ak máte smolu, tak z disku. Skúsme to teda zmenšiť.

Zapamätajme si, čo chce používateľ vidieť prvé „ktoré začínajú...“. Takže toto je v najčistejšej podobe vyhľadávanie predpony skrz text_pattern_ops! A iba ak „nemáme dosť“ až 10 záznamov, ktoré hľadáme, potom ich budeme musieť dokončiť pomocou vyhľadávania FTS:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

[pozrite sa na explain.tensor.ru]

Vynikajúci výkon - celkom 0.05 ms a o niečo viac ako 100 kB čítať! Len my sme zabudli triediť podľa názvuaby sa používateľ nestratil vo výsledkoch:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[pozrite sa na explain.tensor.ru]

Ach, niečo už nie je také krásne - zdá sa, že existuje index, ale triedenie letí popri ňom... Je to, samozrejme, už mnohonásobne efektívnejšie ako predchádzajúca možnosť, ale...

1.4: „dokončiť súborom“

Existuje však index, ktorý vám umožňuje vyhľadávať podľa rozsahu a stále normálne používať triedenie - obyčajný bstrom!

CREATE INDEX ON firms(lower(name));

Iba žiadosť o to bude musieť byť „zhromaždená ručne“:

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

[pozrite sa na explain.tensor.ru]

Vynikajúce - triedenie funguje a spotreba zdrojov zostáva „mikroskopická“, tisíckrát účinnejšie ako „čisté“ FTS! Zostáva len spojiť to do jednej žiadosti:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

Všimnite si, že sa vykoná druhý poddotaz iba ak prvý vrátil menej, ako sa očakávalo posledná LIMIT počet riadkov. Hovorím o tejto metóde optimalizácie dopytov už napísal predtým.

Takže áno, teraz máme na stole btree aj gin, ale štatisticky to vychádza menej ako 10 % požiadaviek dosiahne realizáciu druhého bloku. To znamená, že s takýmito typickými obmedzeniami vopred známymi pre túto úlohu sme dokázali znížiť celkovú spotrebu zdrojov servera takmer tisíckrát!

1.5*: vystačíme si aj bez súboru

vyššia LIKE Bolo nám zabránené použiť nesprávne triedenie. Dá sa však „nastaviť na správnu cestu“ zadaním operátora USING:

Štandardne sa predpokladá ASC. Okrem toho môžete zadať názov konkrétneho operátora triedenia v klauzule USING. Operátor triedenia musí byť členom menšej alebo väčšej skupiny operátorov B-stromu. ASC zvyčajne ekvivalentné USING < и DESC zvyčajne ekvivalentné USING >.

V našom prípade je „menej“. ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

[pozrite sa na explain.tensor.ru]

2: ako kysnú žiadosti

Teraz necháme našu požiadavku „variť“ šesť mesiacov alebo rok a sme prekvapení, že ju opäť nájdeme „navrchu“ s ukazovateľmi celkového denného „čerpania“ pamäte (zdieľaný prístup vyrovnávacej pamäte) V 5.5TB - teda ešte viac, ako bolo pôvodne.

Nie, samozrejme, naše podnikanie sa rozrástlo a naše pracovné zaťaženie sa zvýšilo, ale nie o rovnakú sumu! To znamená, že tu je niečo rybie - poďme na to.

2.1: zrod stránkovania

V určitom momente chcel iný vývojový tím umožniť „skočiť“ z rýchleho dolného indexu do registra s rovnakými, ale rozšírenými výsledkami. Čo je to register bez navigácie po stránkach? Poďme na to!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

Teraz bolo možné zobraziť register výsledkov vyhľadávania s načítaním „stránka po stránke“ bez akéhokoľvek stresu pre vývojárov.

Samozrejme, v skutočnosti pre každú nasledujúcu stranu údajov sa číta viac a viac (všetko z predchádzajúceho obdobia, ktoré vyradíme, plus potrebný „chvost“) - to znamená, že ide o jasný antivzor. Správnejšie by však bolo začať vyhľadávanie pri ďalšej iterácii z kľúča uloženého v rozhraní, ale o tom inokedy.

2.2: Chcem niečo exotické

V určitom okamihu vývojár chcel diverzifikovať výslednú vzorku údajmi z inej tabuľky, pre ktorú bola CTE odoslaná celá predchádzajúca požiadavka:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

A aj tak to nie je zlé, pretože poddotaz sa vyhodnocuje iba pre 10 vrátených záznamov, ak nie ...

2.3: DISTINCT je nezmyselné a nemilosrdné

Niekde v procese takéhoto vývoja od 2. poddotazu stratil NOT LIKE stav. Je jasné, že po tomto UNION ALL sa začali vracať niektoré záznamy dvakrát - najprv sa nachádza na začiatku riadku a potom znova - na začiatku prvého slova tohto riadku. V limite sa všetky záznamy 2. poddotazu mohli zhodovať so záznamami prvého.

Čo robí vývojár namiesto hľadania príčiny?... Bez pochýb!

• dvojnásobnú veľkosť originálne vzorky
• aplikovať DISTINCTzískať iba jednotlivé inštancie každého riadku

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

To znamená, že je jasné, že výsledok je nakoniec úplne rovnaký, ale šanca „vletieť“ do 2. poddotazu CTE sa výrazne zvýšila a aj bez toho jednoznačne čitateľnejšie.

Ale to nie je to najsmutnejšie. Keďže vývojár požiadal o výber DISTINCT nie pre konkrétne, ale pre všetky polia naraz záznamy, potom tam bolo automaticky zahrnuté pole sub_query — výsledok subdotazu. Teraz vykonať DISTINCT, databáza sa už musela spustiť nie 10 poddotazov, ale všetky <2 * N> + 10!

2.4: spolupráca predovšetkým!

Vývojári teda žili ďalej - neobťažovali sa, pretože používateľ zjavne nemal dostatok trpezlivosti na „úpravu“ registra na významné hodnoty N s chronickým spomalením prijímania každej nasledujúcej „stránky“.

Až kým za nimi neprišli vývojári z iného oddelenia a nechceli použiť takýto pohodlný spôsob pre iteratívne vyhľadávanie - to znamená, že vezmeme kúsok z nejakej vzorky, prefiltrujeme ho podľa dodatočných podmienok, nakreslíme výsledok, potom ďalší kúsok (čo v našom prípade dosiahneme zvýšením N) a tak ďalej, až kým nevyplníme sito.

Vo všeobecnosti v ulovenom exemplári N dosiahol hodnoty takmer 17Ka len za jeden deň bolo vykonaných najmenej 4 XNUMX takýchto požiadaviek „v reťazci“. Posledných z nich smelo naskenovali 1 GB pamäte na iteráciu...

Celkom

Zdroj: hab.com