PostgreSQL Antipatterns: 'n Verhaal van iteratiewe verfyning van soektog op naam, of "Optimaliseer heen en weer"

Duisende bestuurders van verkoopskantore regoor die land maak reg ons CRM-stelsel tienduisende kontakte daagliks — feite van kommunikasie met potensiële of reeds werk met ons kliënte. En vir hierdie kliënt moet jy eers vind, en verkieslik baie vinnig. En dit gebeur meestal by die naam.

Daarom is dit nie verbasend dat ons weereens "swaar" navrae op een van die mees gelaaide databasisse ontleed nie - ons eie VLIS korporatiewe rekening, Ek het "in die top" gevind navraag vir "vinnige" soektog op naam vir besigheidskaartjies.

Verder het verdere ondersoek 'n interessante voorbeeld aan die lig gebring optimering eers, dan prestasie agteruitgang versoek met die konsekwente voltooiing daarvan deur verskeie spanne, wat elkeen uitsluitlik uit die beste bedoelings opgetree het.

0: wat wou die gebruiker hê

[KDPV vandaar]

Wat bedoel die gebruiker gewoonlik as hy praat van 'n "vinnige" soektog op naam? Dit blyk amper nooit 'n "regverdige" substring-soektog te wees nie ... LIKE '%роза%' - die resultaat is immers nie net nie 'Розалия' и 'Магазин Роза'Maar 'Гроза' selfs 'Дом Деда Мороза'.

Die gebruiker bedoel op huishoudelike vlak dat jy hom sal voorsien soek aan die begin van 'n woord in die titel en wys meer relevant wat begin by ingeskryf. En doen dit amper onmiddellik - met subskripsie-invoer.

1: beperk die taak

En meer nog, 'n persoon sal nie spesifiek voorstel nie 'роз магаз'sodat jy die voorvoegsel vir elke woord moet soek. Nee, dit is baie makliker vir 'n gebruiker om op 'n vinnige wenk vir die laaste woord te reageer as om die voriges doelbewus te "onderskryf" - kyk hoe enige soekenjin dit uitwerk.

Oor die algemeen, korrek om die vereistes vir die probleem te formuleer is meer as die helfte van die oplossing. Soms versigtig gebruik geval analise kan die uitkoms aansienlik beïnvloed..

Wat doen 'n abstrakte ontwikkelaar?

1.0: eksterne soekenjin

O, soek is moeilik, jy wil glad nie iets doen nie - kom ons gee dit vir devops! Laat hulle 'n soekenjin buite die databasis vir ons ontplooi: Sphinx, ElasticSearch, ...

'n Werkende, alhoewel tydrowende opsie in terme van sinchronisasie en doeltreffendheid van veranderinge. Maar nie in ons geval nie, aangesien die soektog vir elke kliënt slegs binne die raamwerk van sy rekeningdata uitgevoer word. En die data het 'n redelik hoë veranderlikheid - en as nou die bestuurder 'n kaart ingevoer het 'Магазин Роза', dan kan hy na 5-10 sekondes reeds onthou dat hy vergeet het om die e-pos daar te spesifiseer en dit wil vind en regmaak.

Daarom - kom ons soek "direk in die databasis". Gelukkig laat PostgreSQL ons toe om dit te doen, en meer as een opsie - ons sal dit oorweeg.

1.1: "eerlike" substring

Ons klou aan die woord "substring". Maar presies vir indeks soek deur substring (en selfs deur gereelde uitdrukkings!) Daar is 'n uitstekende pg_trgm module! Eers dan sal dit nodig wees om korrek te sorteer.

Kom ons probeer om die volgende teken vir die eenvoud van die model te neem:

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

Ons laai 7.8 miljoen rekords van regte organisasies daar op en indekseer dit:

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

Kom ons soek die eerste 10 rekords vir substring-soektog:

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

[kyk na explain.tensor.ru]

Wel, sulke... 26 ms, 31 MB lees data en meer as 1.7K gefiltreerde rekords - vir 10 gesoek. Die bokoste is te hoog, is dit moontlik om iets meer doeltreffend te doen?

1.2: tekssoektog? dit is FTS!

Inderdaad, PostgreSQL bied 'n baie kragtige volteks soekenjin (Voltekssoektog), insluitend met die moontlikheid van voorvoegselsoektog. Goeie opsie, jy hoef nie eers uitbreidings te installeer nie! Kom ons probeer:

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;

[kyk na explain.tensor.ru]

Parallellisering van die uitvoering van die navraag het ons 'n bietjie hier gehelp, wat die tyd met die helfte verminder het 11 ms. Ja, en ons moes 1.5 keer minder lees – in totaal 20MB. En hier hoe minder - hoe beter, want hoe groter die volume wat ons aftrek, hoe groter is die kanse om 'n kasmis te kry, en elke ekstra bladsy data wat van die skyf gelees word, is 'n potensiële "rem" vir die versoek.

1.3: hou nog steeds van?

Die vorige versoek is goed vir almal, maar net as jy dit honderdduisend keer per dag trek, dan sal dit loop 2TB lees data. Op sy beste - uit die geheue, maar as jy nie gelukkig is nie, dan vanaf die skyf. So kom ons probeer om dit kleiner te maak.

Onthou wat die gebruiker wil sien eerste "wat begin met ...". Dit is dus in sy suiwerste vorm. voorvoegsel soek met die hulp text_pattern_ops! En slegs as ons tot 10 vereiste rekords "nie genoeg het nie", sal ons dit moet lees deur die FTS-soektog te gebruik:

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

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

[kyk na explain.tensor.ru]

Uitstekende prestasie - totaal 0.05ms en net meer as 100KB lees! Ons het net vergeet sorteer volgens naamsodat die gebruiker nie in die resultate verdwaal nie:

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

[kyk na explain.tensor.ru]

O, iets is nie meer so mooi nie - dit blyk dat daar 'n indeks is, maar sorteer vlieg verby dit ... Natuurlik is dit al baie keer meer doeltreffend as die vorige weergawe, maar ...

1.4: "maak klaar met 'n lêer"

Maar daar is 'n indeks wat jou toelaat om volgens reeks te soek, en dit is normaal om sortering te gebruik - gereelde btree!

CREATE INDEX ON firms(lower(name));

Slegs die versoek daarvoor sal "handmatig saamgestel" moet word:

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

[kyk na explain.tensor.ru]

Uitstekend - en sorteerwerk, en hulpbronverbruik bly "mikroskopies", duisende kere meer effektief as "suiwer" FTS! Dit bly om in 'n enkele versoek in te samel:

(
  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;

Let daarop dat die tweede subnavraag uitgevoer word slegs as die eerste een minder as verwag teruggekeer het laaste LIMIT aantal lyne. Oor hierdie manier om navrae te optimaliseer, I reeds voorheen geskryf.

So ja, ons het nou btree en gin op dieselfde tyd op die tafel, maar statisties het dit geblyk dat minder as 10% van versoeke bereik die uitvoering van die tweede blok. Dit wil sê, met sulke tipiese beperkings vir die taak wat vooraf bekend is, kon ons die totale verbruik van bedienerhulpbronne met byna 'n duisend keer verminder!

1.5*: doen sonder 'n lêer

hoër LIKE ons is verhinder om die verkeerde sortering te gebruik. Maar dit kan "op die regte pad gestel" word deur die GEBRUIK-operateur te spesifiseer:

Die verstek is ASC. Daarbenewens kan jy die naam van 'n spesifieke sorteeroperateur in die klousule spesifiseer USING. Die sorteeroperateur moet 'n "minder as" of "groter as" lid van 'n familie van B-boom operateurs wees. ASC gewoonlik ekwivalent USING < и DESC gewoonlik ekwivalent USING >.

In ons geval is "minder". ~<~:

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

[kyk na explain.tensor.ru]

2: hoe versoeke versuur word

Nou laat ons ons versoek om vir ses maande of 'n jaar te "brou", en met verbasing vind ons dit weer "in die top" met aanwysers van die totale daaglikse "pomp" van geheue (buffers gedeelde treffer) in 5.5TB - dit wil sê selfs meer as wat dit oorspronklik was.

Nee, natuurlik, en ons besigheid het gegroei, en die werklading het toegeneem, maar nie met dieselfde hoeveelheid nie! So, iets is nie skoon hier nie – kom ons vind dit uit.

2.1: die geboorte van paging

Op 'n stadium wou 'n ander ontwikkelingspan dit moontlik maak om in die register te "spring" vanaf 'n vinnige subskripsiesoektog met dieselfde, maar uitgebreide resultate. En watter register sonder paginering? Kom ons skroef dit aan!

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

Nou was dit moontlik vir die ontwikkelaar om die register van soekresultate te wys met 'n "bladsy-tipe" laai sonder om te beur.

Natuurlik, in werklikheid, meer en meer word gelees vir elke volgende bladsy data (alles van die vorige keer, wat ons sal weggooi, plus die gewenste "stert") - dit wil sê, dit is 'n ondubbelsinnige anti-patroon. En dit sal meer korrek wees om die soektog by die volgende iterasie te begin vanaf die sleutel wat in die koppelvlak gestoor is, maar meer daaroor 'n ander keer.

2.2: eksotiese wil hê

Op 'n stadium wou die ontwikkelaar diversifiseer die resulterende steekproef met data vanaf 'n ander tabel, waarvoor die hele vorige navraag na die CTE gestuur is:

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

En selfs so, nie sleg nie, want die subnavraag word slegs vir 10 teruggestuurde rekords geëvalueer, indien nie ...

2.3: ONDERSKYDE sinneloos en genadeloos

Iewers in die proses van so 'n evolusie vanaf die 2de subnavraag verlore NOT LIKE toestand. Dit is duidelik dat na hierdie UNION ALL begin terugkeer sommige inskrywings twee keer - eers aan die begin van die reël gevind, en dan weer - aan die begin van die eerste woord van hierdie reël. In die limiet kan alle rekords van die 2de subnavraag ooreenstem met die rekords van die eerste.

Wat doen 'n ontwikkelaar in plaas daarvan om na 'n rede te soek?.. Nie 'n vraag nie!

• dubbel die grootte aanvanklike monsters
• DISTINCT oplêom slegs enkele gevalle van elke ry te kry

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>;

Dit wil sê, dit is duidelik dat die resultaat op die ou end presies dieselfde is, maar die kans om na die 2de CTE-subnavraag te "vlieg" het baie hoër geword, en selfs daarsonder, lees duidelik meer.

Maar dit is nie die hartseerste ding nie. Aangesien die ontwikkelaar gevra het om te kies DISTINCT nie vir spesifiek nie, maar vir alle velde gelyktydig rekords, dan word die sub_query-veld outomaties ingesluit - die resultaat van die subnavraag. Nou, om uit te voer DISTINCT, die databasis moes reeds uitgevoer word nie 10 subnavrae nie, maar almal <2 * N> + 10!

2.4: samewerking bo alles!

So, die ontwikkelaars het geleef - hulle het nie bedroef nie, want in die register "opskroef" tot beduidende N-waardes met 'n chroniese verlangsaming om elke volgende "bladsy" te kry, het die gebruiker duidelik nie die geduld gehad nie.

Totdat ontwikkelaars van 'n ander departement na hulle gekom het, en nie so 'n gerieflike metode wou gebruik nie vir iteratiewe soektog - dit wil sê, ons neem 'n stuk uit een of ander monster, filter volgens bykomende toestande, teken die resultaat, dan die volgende stuk (wat in ons geval bereik word deur N te verhoog), en so aan totdat ons die skerm vul.

Oor die algemeen, in 'n gevang eksemplaar N het amper 17K bereik, en binne net 'n dag is ten minste 4K sulke versoeke "langs die ketting" uitgevoer. Die laaste van hulle het met vrymoedigheid al verby geskandeer 1 GB geheue per iterasie...

In totaal

Bron: will.com