PostgreSQL Antipatterns: Příběh o iterativním zpřesňování vyhledávání podle názvu nebo „optimalizace tam a zpět“

Tisíce manažerů z prodejních kanceláří po celé zemi opravují náš CRM systém desítky tisíc kontaktů denně — fakta o komunikaci s potenciálními nebo již spolupracujícími klienty. A pro tohoto klienta musíte nejprve najít a nejlépe velmi rychle. A to se děje nejčastěji jménem.

Proto není divu, že opět analyzujeme „těžké“ dotazy na jedné z nejzatíženějších databází – naší vlastní firemní účet VLIS, našel jsem "nahoře" dotaz na "rychlé" vyhledávání podle jména pro vizitky.

Navíc další vyšetřování odhalilo zajímavý příklad nejprve optimalizace, poté snížení výkonu žádost s jejím důsledným vyplňováním několika týmy, z nichž každý jednal výhradně z nejlepších úmyslů.

0: co uživatel chtěl

[KDPV proto]

Co uživatel obvykle myslí, když mluví o „rychlém“ vyhledávání podle jména? Téměř nikdy to nedopadne jako „spravedlivé“ hledání podřetězců ... LIKE '%роза%' - koneckonců, výsledek není jen 'Розалия' и 'Магазин Роза'Ale 'Гроза' a dokonce i 'Дом Деда Мороза'.

Uživatel znamená na úrovni domácnosti, že mu poskytnete hledat na začátku slova v názvu a ukázat relevantnější co začíná v vstoupil. A udělej to téměř okamžitě - se vstupem dolního indexu.

1: omezit úkol

A ještě víc, člověk konkrétně nepředstaví 'роз магаз'takže musíte hledat předponu pro každé slovo. Ne, pro uživatele je mnohem snazší reagovat na rychlou nápovědu k poslednímu slovu, než záměrně „podzadávat“ předchozí – podívejte se, jak to řeší jakýkoli vyhledávač.

obecně, správně formulovat požadavky na problém je více než polovinou řešení. Někdy pečlivá analýza případu použití může výrazně ovlivnit výsledek..

Co dělá abstraktní vývojář?

1.0: externí vyhledávač

Ach, hledání je těžké, vůbec se vám nechce něco dělat – devopům to dejte! Nechte je nasadit pro nás externí vyhledávač k databázi: Sphinx, ElasticSearch, ...

Funkční, i když časově náročná možnost z hlediska synchronizace a efektivity změn. Ale ne v našem případě, protože vyhledávání se provádí pro každého klienta pouze v rámci údajů o jeho účtu. A data mají poměrně vysokou variabilitu - a pokud nyní vedoucí zadal kartu 'Магазин Роза', pak si po 5-10 sekundách už může vzpomenout, že tam zapomněl uvést email a chce to najít a opravit.

Proto – pojďme hledat "přímo v databázi". Naštěstí nám to PostgreSQL umožňuje a více než jednu možnost - zvážíme je.

1.1: "čestný" podřetězec

Lpíme na slově „podřetězec“. Ale přesně pro indexové vyhledávání podle podřetězců (a dokonce podle regulárních výrazů!) existuje vynikající modul pg_trgm! Teprve pak bude nutné správně třídit.

Zkusme si pro jednoduchost modelu vzít takovou desku:

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

Nahrajeme tam 7.8 milionu záznamů skutečných organizací a indexujeme je:

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

Podívejme se na prvních 10 záznamů pro vyhledávání podřetězců:

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

[podívejte se na explain.tensor.ru]

No takový... 26 ms, 31 MB přečtená data a více než 1.7K filtrovaných záznamů - pro 10 prohledaných. Režie je příliš vysoká, je možné udělat něco efektivnějšího?

1.2: textové vyhledávání? to je FTS!

Ve skutečnosti PostgreSQL poskytuje velmi výkonný fulltextový vyhledávač (Full Text Search), včetně možnosti vyhledávání prefixů. Skvělá volba, nemusíte ani instalovat rozšíření! Zkusme to:

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;

[podívejte se na explain.tensor.ru]

Zde nám trochu pomohla paralelizace provádění dotazu, která zkrátila čas na polovinu 11 ms. Ano, a museli jsme číst 1.5krát méně – celkem 20MB. A zde platí, že čím méně – tím lépe, protože čím větší objem odečteme, tím vyšší je šance na vynechání mezipaměti a každá další stránka dat přečtená z disku je potenciální „brzdou“ požadavku.

1.3: stále LIKE?

Předchozí požadavek je dobrý pro všechny, ale jen když ho zatáhnete stotisíckrát za den, tak to poběží 2TB číst data. V lepším případě - z paměti, ale pokud nemáte štěstí, tak z disku. Zkusme to tedy zmenšit.

Pamatujte, co chce uživatel vidět první "který začíná...". Je tedy ve své nejčistší podobě. vyhledávání prefixů přes text_pattern_ops! A pouze pokud „nemáme dostatek“ až 10 požadovaných záznamů, budeme je muset přečíst pomocí vyhledávání FTS:

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

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

[podívejte se na explain.tensor.ru]

Vynikající výkon - celkem 0.05 ms a něco málo přes 100 kB číst! Prostě jsme zapomněli Seřaď dle jménaaby se uživatel ve výsledcích neztratil:

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

[podívejte se na explain.tensor.ru]

Ach, něco už není tak krásné - zdá se, že existuje rejstřík, ale třídění kolem něj letí ... Samozřejmě je již mnohonásobně efektivnější než předchozí verze, ale ...

1.4: "dokončit souborem"

Existuje však index, který vám umožňuje vyhledávat podle rozsahu, a je normální používat třídění - pravidelný bstrom!

CREATE INDEX ON firms(lower(name));

Pouze požadavek na něj bude muset být „sestaven ručně“:

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

[podívejte se na explain.tensor.ru]

Vynikající - a třídění funguje a spotřeba zdrojů zůstává "mikroskopická", tisíckrát účinnější než "čisté" FTS! Zbývá shromáždit v jedné žádosti:

(
  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šimněte si, že se provede druhý poddotaz pouze pokud první vrátil méně, než se očekávalo poslední LIMIT počet řádků. O tomto způsobu optimalizace dotazů I už psal dříve.

Takže ano, teď máme na stole btree a gin zároveň, ale statisticky to vyšlo méně než 10 % požadavků dosáhne provedení druhého bloku. To znamená, že s takovými typickými omezeními pro úlohu, která byla předem známa, jsme byli schopni snížit celkovou spotřebu zdrojů serveru téměř tisíckrát!

1.5*: obejdete se bez souboru

Nahoře LIKE bylo nám zabráněno použít špatné řazení. Lze jej však „nastavit na správnou cestu“ zadáním operátoru USING:

Výchozí nastavení je ASC. Kromě toho můžete v klauzuli zadat název konkrétního operátoru řazení USING. Operátor řazení musí být "menší než" nebo "větší než" členem nějaké rodiny operátorů B-stromu. ASC obvykle ekvivalentní USING < и DESC obvykle ekvivalentní USING >.

V našem případě je „méně“. ~<~:

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

[podívejte se na explain.tensor.ru]

2: jak kyselé žádosti

Nyní necháme náš požadavek „vařit“ šest měsíců nebo rok a s překvapením jej opět nacházíme „nahoře“ s ukazateli celkového denního „napumpování“ paměti (sdílený přístup do vyrovnávacích pamětí) V 5.5TB - tedy ještě více, než bylo původně.

Ne, samozřejmě, naše podnikání se rozrostlo a pracovní zátěž se zvýšila, ale ne o stejnou částku! Takže tady něco není čisté - pojďme na to přijít.

2.1: zrod stránkování

V určitém okamžiku chtěl jiný vývojový tým umožnit „skočit“ do registru z rychlého vyhledávání v dolním indexu se stejnými, ale rozšířenými výsledky. A jaký registr bez stránkování? Jdeme na to!

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

Nyní bylo pro vývojáře možné zobrazit registr výsledků vyhledávání s načítáním „typu stránky“ bez namáhání.

Samozřejmě, ve skutečnosti pro každou další stránku dat se čte stále více (vše z předchozí doby, co vyřadíme, plus požadovaný „ocásek“) - to je jednoznačný anti-vzor. A správnější by bylo zahájit vyhledávání při další iteraci z klíče uloženého v rozhraní, ale o tom jindy.

2.2: chci exotiku

V určitém okamžiku vývojář chtěl diverzifikovat výsledný vzorek daty z jiné tabulky, pro kterou byl odeslán celý předchozí dotaz do CTE:

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

A i tak to není špatné, protože poddotaz je vyhodnocen pouze pro 10 vrácených záznamů, pokud ne ...

2.3: VÝRAZNÉ nesmyslné a nemilosrdné

Někde v procesu takového vývoje od 2. dílčího dotazu ztracený NOT LIKE stav. Je jasné, že po tomto UNION ALL se začal vracet některé záznamy dvakrát - nejprve nalezen na začátku řádku a poté znovu - na začátku prvního slova tohoto řádku. V limitu se všechny záznamy 2. poddotazu mohly shodovat se záznamy prvního.

Co dělá vývojář místo toho, aby hledal důvod?... To není otázka!

• dvojnásobnou velikost počáteční vzorky
• uložit DISTINCTzískat pouze jednotlivé instance každého řádku

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ýsledek je nakonec úplně stejný, ale šance „vletět“ do 2. poddotazu CTE se výrazně zvýšila a i bez něj jasně čtěte více.

Ale to není to nejsmutnější. Protože vývojář požádal o výběr DISTINCT ne pro konkrétní, ale pro všechny obory najednou záznamy, pak je automaticky zahrnuto pole sub_query - výsledek subdotazu. Nyní k provedení DISTINCT, databáze se již musela spustit ne 10 poddotazů, ale všechny <2 * N> + 10!

2.4: spolupráce především!

Vývojáři tedy žili - netruchlili, protože v registru „našroubovali“ na významné hodnoty N s chronickým zpomalením získávání každé další „stránky“ uživatel zjevně neměl trpělivost.

Dokud k nim nepřišli vývojáři z jiného oddělení a nechtěli tak pohodlnou metodu použít pro iterativní vyhledávání - to znamená, že vezmeme kousek z nějakého vzorku, přefiltrujeme podle dalších podmínek, nakreslíme výsledek, pak další kus (což je v našem případě dosaženo zvýšením N) a tak dále, dokud nevyplníme obrazovku.

Obecně v uloveném exempláři N dosáhl téměř 17Ka za pouhý den bylo „podél řetězce“ provedeno nejméně 4K takových požadavků. Poslední z nich už směle prohlédl 1 GB paměti na iteraci...

Celkem

Zdroj: www.habr.com