PostgreSQL Antipatterns: yon istwa de rafineman iteratif nan rechèch pa non, oswa "Optimizasyon retounen ak lide"

Dè milye de manadjè ki soti nan biwo lavant atravè peyi a dosye sistèm CRM nou an dè dizèn de milye kontak chak jou — reyalite kominikasyon ak kliyan potansyèl oswa ki deja egziste. Se pou sa, ou dwe premye jwenn yon kliyan, e de preferans trè vit. Ak sa rive pi souvan pa non.

Se poutèt sa, li pa etone ke, yon lòt fwa ankò analize demann "lou" sou youn nan baz done ki pi chaje - pwòp pa nou. VLSI kont antrepriz, mwen te jwenn "nan tèt la" demann pou yon rechèch "rapid" pa non pou kat òganizasyon yo.

Anplis, plis envestigasyon revele yon egzanp enteresan premye optimize ak Lè sa a, degradasyon pèfòmans demann ak rafineman sekans li yo pa plizyè ekip, chak nan yo ki te aji sèlman ak pi bon entansyon yo.

0: kisa itilizatè a te vle?

[KDPV kon sa]

Ki sa yon itilizatè anjeneral vle di lè yo pale sou yon rechèch "rapid" pa non? Li prèske pa janm vire soti nan yon rechèch "onèt" pou yon substring tankou ... LIKE '%роза%' - paske Lè sa a, rezilta a gen ladan pa sèlman 'Розалия' и 'Магазин Роза'Men, 'Гроза' e menm 'Дом Деда Мороза'.

Itilizatè a sipoze nan nivo chak jou ke ou pral ba li rechèch pa kòmansman mo nan tit la epi fè li pi enpòtan ke kòmanse sou antre. Epi ou pral fè li prèske imedyatman - pou antre interlinear.

1: limite travay la

E menm plis konsa, yon moun pa pral espesyalman antre 'роз магаз', pou ou gen pou chèche chak mo pa prefiks. Non, li pi fasil pou yon itilizatè reponn a yon sijesyon rapid pou dènye mo a pase fè espre "sous-espesifye" sa yo anvan yo - gade nan ki jan nenpòt motè rechèch okipe sa a.

Anjeneral kòrèkteman fòmile kondisyon yo pou pwoblèm nan se plis pase mwatye solisyon an. Pafwa analiz ka itilize atansyon ka siyifikativman enfliyanse rezilta a.

Kisa yon pwomotè abstrè fè?

1.0: motè rechèch ekstèn

Oh, rechèch difisil, mwen pa vle fè anyen ditou - ann bay devops li! Kite yo deplwaye yon motè rechèch ekstèn nan baz done a: Sphinx, ElasticSearch,...

Yon opsyon k ap travay, kwake travay-entansif an tèm de senkronizasyon ak vitès nan chanjman. Men, pa nan ka nou an, depi rechèch la te pote soti pou chak kliyan sèlman nan kad done kont li. Ak done yo gen yon varyab jistis segondè - epi si manadjè a gen kounye a antre nan kat la 'Магазин Роза', Lè sa a, apre 5-10 segonn li ka deja sonje ke li bliye endike imel li la epi li vle jwenn li epi korije li.

Se poutèt sa - an n rechèch "dirèkteman nan baz done a". Erezman, PostgreSQL pèmèt nou fè sa, epi pa sèlman yon opsyon - nou pral gade yo.

1.1: "onèt" substring

Nou rete kole sou mo "substring". Men, pou rechèch endèks pa substring (e menm pa ekspresyon regilye!) Gen yon ekselan modil pg_trgm! Se sèlman lè sa a li pral nesesè yo sòt kòrèkteman.

Ann eseye pran plak sa a pou senplifye modèl la:

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

Nou telechaje 7.8 milyon dosye sou òganizasyon reyèl la epi endèks yo:

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

Ann gade pou 10 premye dosye yo pou rechèch entèlineyè:

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

[wè nan eksplike.tensor.ru]

Oke, sa a... 26ms, 31MB li done ak plis pase 1.7K dosye filtre - pou 10 sa yo fouye. Depans anlè yo twò wo, èske pa gen yon bagay ki pi efikas?

1.2: rechèch pa tèks? Se FTS!

Vreman vre, PostgreSQL bay yon trè pwisan motè rechèch tèks konplè (Rechèch Tèks konplè), ki gen ladan kapasite nan rechèch prefiks. Yon opsyon ekselan, ou pa menm bezwen enstale ekstansyon! Ann eseye:

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;

[wè nan eksplike.tensor.ru]

Isit la paralèlizasyon nan ekzekisyon demann te ede nou yon ti kras, koupe tan an nan mwatye a 11ms. E nou te oblije li 1.5 fwa mwens - nan total 20MB. Men, isit la, mwens, pi bon an, paske pi gwo volim nou li, se pi gwo chans pou jwenn yon miss kachèt, ak chak paj siplemantè nan done li nan disk la se yon potansyèl "fren" pou demann lan.

1.3: toujou LIKE?

Demann anvan an bon pou tout moun, men sèlman si ou rale li yon santèn mil fwa pa jou, li pral vini 2TB li done. Nan pi bon ka a, nan memwa, men si w pa gen chans, Lè sa a, soti nan disk. Se konsa, ann eseye fè li pi piti.

Ann sonje sa itilizatè a vle wè premye "ki kòmanse ak...". Se konsa, sa a se nan fòm pi bon kalite li yo rechèch prefiks avèk èd la text_pattern_ops! Epi sèlman si nou "pa gen ase" jiska 10 dosye nou ap chèche, Lè sa a, nou pral oblije fini li yo lè l sèvi avèk rechèch FTS:

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

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

[wè nan eksplike.tensor.ru]

Ekselan pèfòmans - total 0.05ms ak yon ti kras plis pase 100KB li! Se sèlman nou bliye triye pa nonpou itilizatè a pa pèdi nan rezilta yo:

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

[wè nan eksplike.tensor.ru]

Oh, yon bagay pa tèlman bèl ankò - li sanble tankou gen yon endèks, men klasman an vole sot pase li ... Li, nan kou, se deja anpil fwa pi efikas pase opsyon anvan an, men ...

1.4: "fini ak yon dosye"

Men, gen yon endèks ki pèmèt ou fè rechèch pa ranje epi toujou itilize klasman nòmalman - btree regilye!

CREATE INDEX ON firms(lower(name));

Se sèlman demann lan pou li pral dwe "kolekte manyèlman":

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

[wè nan eksplike.tensor.ru]

Ekselan - klasman an ap travay, ak konsomasyon resous rete "mikwoskopik", dè milye de fwa pi efikas pase "pi" FTS! Tout sa ki rete se mete li ansanm nan yon sèl demann:

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

Remake byen ke dezyèm subquery la egzekite sèlman si premye a tounen mwens pase espere Denye LIMIT kantite liy. Mwen pale sou metòd sa a nan optimize rechèch deja ekri avan.

Se konsa, wi, kounye a nou gen tou de btree ak djin sou tab la, men estatistikman li vire soti sa mwens pase 10% nan demann rive nan ekzekisyon an nan dezyèm blòk la. Sa vle di, ak limit sa yo tipik li te ye davans pou travay la, nou te kapab redwi konsomasyon total resous sèvè pa prèske mil fwa!

1.5*: nou ka fè san yon fichye

Anwo a LIKE Nou te anpeche itilize klasman kòrèk. Men, li ka "mete sou bon chemen an" lè w presize operatè USING la:

Pa default li sipoze ASC. Anplis de sa, ou ka presize non yon operatè sòt espesifik nan yon kloz USING. Operatè sòt la dwe yon manm nan mwens pase oswa pi gran pase nan kèk fanmi operatè B-tree. ASC anjeneral ekivalan USING < и DESC anjeneral ekivalan USING >.

Nan ka nou an, "mwens" se ~<~:

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

[wè nan eksplike.tensor.ru]

2: ki jan demann vire tounen

Koulye a, nou kite demann nou an "mitone" pou sis mwa oswa yon ane, epi nou sezi jwenn li ankò "nan tèt la" ak endikatè nan total "ponpe" chak jou nan memwa (tanpon pataje frape) an 5.5TB - se sa ki, menm plis pase sa li te orijinèlman.

Non, nan kou, biznis nou an te grandi ak kantite travay nou an te ogmante, men se pa nan menm kantite lajan an! Sa vle di ke yon bagay se pwason isit la - an n kalkile li soti.

2.1: nesans paging

Nan kèk pwen, yon lòt ekip devlopman te vle fè li posib pou "sote" soti nan yon rechèch abònman rapid nan rejis la ak rezilta yo menm, men elaji. Ki sa ki se yon rejis san navigasyon paj? Ann vise li!

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

Koulye a, li te posib yo montre rejis la nan rezilta rechèch ak "paj-pa-paj" chaje san okenn estrès pou pwomotè a.

Natirèlman, an reyalite, pou chak paj ki vin apre nan done yo pi plis ak plis li (tout soti nan tan anvan an, ke nou pral jete, plis "ke" ki nesesè yo) - se sa ki, sa a se yon antimodèl klè. Men, li ta pi kòrèk pou kòmanse rechèch la nan pwochen iterasyon nan kle ki estoke nan koòdone a, men sou sa yon lòt fwa.

2.2: Mwen vle yon bagay ekzotik

Nan kèk pwen pwomotè a te vle divèsifye echantiyon an ki kapab lakòz ak done soti nan yon lòt tab, pou tout demann anvan an te voye bay CTE:

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

E menm si sa, li pa move, depi sou rechèch la evalye sèlman pou 10 dosye retounen, si se pa ...

2.3: DISTINCT se san sans ak san pitye

Yon kote nan pwosesis evolisyon sa yo soti nan 2yèm subquery la te pèdi NOT LIKE kondisyon. Li klè ke apre sa UNION ALL te kòmanse retounen kèk antre de fwa - premye jwenn nan kòmansman an nan liy lan, ak Lè sa a ankò - nan kòmansman an nan premye mo a nan liy sa a. Nan limit la, tout dosye nan 2yèm subquery a ka matche ak dosye yo nan premye a.

Kisa yon pwomotè fè olye pou yo chèche kòz la? .. Pa gen kesyon!

• double gwosè a echantiyon orijinal yo
• aplike DISTINCTpou jwenn sèlman yon sèl egzanp nan chak liy

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

Sa vle di, li klè ke rezilta a, nan fen a, se egzakteman menm bagay la, men chans pou "vole" nan 2yèm CTE subquery a te vin pi wo, e menm san sa a, klèman plis lizib.

Men, sa a se pa bagay ki pi tris la. Depi pwomotè a mande yo chwazi DISTINCT pa pou moun espesifik, men pou tout jaden an menm tan dosye, Lè sa a, jaden an sub_query - rezilta a nan subquery a - te otomatikman enkli la. Koulye a, pou egzekite DISTINCT, baz done a te oblije egzekite deja pa 10 sous-rekèt, men tout <2 * N> + 10!

2.4: koperasyon pi wo a tout!

Se konsa, devlopè yo te viv sou - yo pa t deranje, paske itilizatè a klèman pa t 'gen ase pasyans pou "ajiste" rejis la nan valè N enpòtan ak yon ralentissement kwonik nan resevwa chak "paj" ki vin apre.

Jiskaske devlopè ki soti nan yon lòt depatman te vin jwenn yo epi yo te vle sèvi ak yon metòd pratik konsa pou rechèch iteratif - se sa ki, nou pran yon moso nan kèk echantiyon, filtre li pa kondisyon adisyonèl, trase rezilta a, Lè sa a, moso nan pwochen (ki nan ka nou an reyalize nan ogmante N), ak sou sa jiskaske nou ranpli ekran an.

An jeneral, nan echantiyon an kenbe N rive nan valè prèske 17K, ak nan yon sèl jou omwen 4K nan demann sa yo te egzekite "sou chèn lan". Dènye nan yo te avèk fòs konviksyon analize pa 1GB memwa pou chak iterasyon...

Nan total

Sous: www.habr.com