PostgreSQL antipattern'id: jutt iteratiivsetest täiustustest nimepõhise otsingu osas ehk "Optimeerimine edasi-tagasi"

Tuhanded müügibüroode juhid üle kogu riigi registreerivad meie CRM-süsteemis iga päev kümneid tuhandeid kontakte — suhtlemise fakte potentsiaalsete või juba koostööd tegevaid kliente. Ja selle kliendi leidmiseks tuleb seda kõigepealt teha, ja soovitavalt väga kiiresti. See toimub kõige sagedamini nime järgi.

Seega ei ole üllatav, et, arutades taas "raskete" päringute üle ühes meie kõige koormatud andmebaasidest — meie enda korporatiivkonto SBiSi, avastasin "tipus" päringu "kiire" nimepõhise otsingu jaoks organisatsioonide kaartide jaoks.

Lisaks paljastas edasine uurimine huvitava näite algsest optimeerimisest ja seejärel jõudluse halvenemisest päringu järjestikuse täiustamise käigus, mida viisid läbi mitmed meeskonnad, kellest igaüks tegutses üksnes parimatest kavatsustest.

0: mida tahtis kasutaja

[КДПВ siit]

Mida mõtleb kasutaja üldiselt, kui räägib "kiirest" otsingust pealkirja järgi? Harva, kui see osutub "ausaks" otsinguks alamsõnna tüüpi ... LIKE '%roos%' — sest siis jäävad tulemused mitte ainult 'Rooslia' ja 'Pood Roos', vaid ka 'Groos' ja isegi 'Vanaema Roos'.

Kasutaja mõistab igapäevaelus, et te tagate talle otsingu sõna alguse järgi pealkirjas ja näitate enam asjakohaseid tulemusi, mis algab sisestatud. Ja teete seda praktiliselt koheselt — alamsõnade sisestamise korral.

1: kitsendame ülesande

Ja kindlasti ei hakka inimene spetsiaalselt sisestama 'roos pood',et iga sõna sooviksite otsida prefiksina. Ei, kasutajal on palju lihtsam reageerida kiiresti esitatud vihjele viimasele sõnale, kui tahtlikult "puuduvad" eelnevad — vaadake, kuidas see toimib igas otsingumootoris.

Üldiselt, õigesti formuleerida ülesande nõuded — enam kui pooled lahendusest. Mõnikord võib tähelepanelik use case'i analüüs oluliselt mõjutada tulemust.

Mida teeb abstraktne arendaja?

1.0: väline otsingumootor

Oi, otsimine on keeruline, ei viitsi sellega tegeleda — lasta devops'il sellega tegeleda! Las nad seadistavad meile välise otsingusüsteemi, nagu Sphinx, ElasticSearch,…

Töötav, kuigi töömahukas sünkroniseerimise ja muudatuste kiirusest. Kuid mitte meie puhul, kuna otsing toimub iga kliendi jaoks ainult tema konto andmete ulatuses. Andmed on piisavalt muutlikud — ja kui praegu haldur sisestab kaardi 'Poekett Roosa', siis 5-10 sekundit hiljem võib ta juba meeles pidada, et unustas seal e-posti aadressi ning soovib seda leida ja parandada.

Seetõttu — laseme otsida „otse andmebaasist”. Õnneks võimaldab PostgreSQL meil seda teha, ja mitte ühe versiooniga — vaatame neid läbi.

1.1: „aus” alamstring

Haakume sõna „alamstring” külge. Just indekseeritud otsingu jaoks alamstringide järgi (ja isegi regulaaravaldiste järgi!) on olemas suurepärane moodul pg_trgm! Ainult pärast seda tuleb õigesti järjestada.

Proovime võtta lihtsuse mõttes sellise tabeli:

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

Laadime sinna 7.8 miljonit reaalselt olemasolevat organisatsiooni ja indekseerime:

LOOJA LAIA pg_trgm;
LOOJA INDICE FIRMADE ÜLEGINA gin(lower(nimi) gin_trgm_ops);

Otsime alamsõna otsimiseks esimesed 10 salvestust:

VALI
  *
KUST
  firmad
KUS
  lower(nimi) ~ ('(^|s)' || 'roos')
KOOS
  lower(nimi) ~ ('^' || 'roos') DESC -- esmalt "alustavad"
, lower(nimi) -- ülejäänud tähestikujärjekorras
PIIRANG 10;

[vaata explain.tensor.ru]

No, selline… 26ms, 31 MB loetud andmeid ja rohkem kui 1.7K filtreeritud salvestust — 10 otsitava jaoks. Kulud on liiga suured, kas ei saaks kuidagi efektiivsemalt?

1.2: tekstipõhine otsing? see on ju FTS!

Tõepoolest, PostgreSQL pakub väga võimsat täisteksti otsingu mehhanismi (Full Text Search), sealhulgas prefiksotsingu võimalusega. Suurepärane valik, isegi laiendusi pole vaja installida! Proovime:

LOOJA INDICE FIRMADE ÜLEGINA gin(to_tsvector('simple'::regconfig, lower(nimi)));

VALI
  *
KUST
  firmad
KUS
  to_tsvector('simple'::regconfig, lower(nimi)) @@ to_tsquery('simple', 'roos:*')
KOOS
  lower(nimi) ~ ('^' || 'roos') DESC
, lower(nimi)
PIIRANG 10;

[vaata explain.tensor.ru]

Siin aitas meile veidi päringute täitmise paralleelisus, vähendades aega kahe korda kuni 11ms. Ja lugeda tuli meil 1.5 korda vähem — ainult 20MB. Siin kehtib vähem on rohkem, kuna mida suurem on meie loetav maht, seda suurem on chance saada cache miss, ja iga ülearune andmeleht, mis loetakse kettalt, on potentsiaalne "peatus" päringu jaoks.

1.3: siiski LIKE?

Kõik eelnevad päringud on head, aga kui seda tõmmata sada tuhat korda päevas, siis juba koguneb 2TB loetud andmeid. Parimal juhul mälu kaudu, aga kui õnne pole, siis kettalt. Nii et proovime seda väiksemaks teha.

Käime üle, mida kasutaja tahab näha esiteks "mis algavad ...". See on ju puhtalt prefiksipäring kasutades text_pattern_ops! Ja ainult siis, kui meil on "puudu" kuni 10 otsitavat kirjet, peame need lugema FTS-päringuga:

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

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('roosa' || '%')
LIMIT 10;

[vaata explain.tensor.ru]

Suurepärased näitajad — vaid 0.05ms ja veidi üle 100KB loetud! Ainult et me unustasime sorteerimise nime järgi, et kasutaja ei eksiks tulemustes:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('roosa' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[vaata explain.tensor.ru]

Oi, midagi on juba vale — katsed, et indeks olemas, aga sortimine käib mööda… See on muidugi palju efektiivsem kui eelmine versioon, kuid…

1.4: "töötama viimistlusfailiga"

Aga on ju olemas indeks, mis võimaldab otsida vahemiku järgi ja sortimist ka normaalselt kasutada — tavaline btree!

Loo indeks: CREATE INDEX ON firms(lower(name));

Kuid päring tuleb "ise kokku panna":

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'roos' AND
  lower(name) <= ('roos' || chr(65535)) -- UTF8 jaoks, ühesüteme jaoks - chr(255)
ORDER BY
  lower(name)
LIMIT 10;

[vaata explain.tensor.ru]

Suurepärane — nii sortimine töötab, kui ka ressursitarbimine on jäänud "mikroskoopiliseks", tuhandeid kordi efektiivsem kui "puhas" FTS! Jäänud on kokku panna ühte päringusse:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'roos' AND
    lower(name) <= ('roos' || chr(65535)) -- UTF8 jaoks, ühesüteme jaoks - chr(255)
  ORDER BY
    lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'roos:*') AND
    lower(name) NOT LIKE ('roos' || '%') -- "alustavad" leidsime juba ülal
  ORDER BY
    lower(name) ~ ('^' || 'roos') DESC -- kasutame sama sortimist, et MITTE minna btree-indekse
  , lower(name)
  LIMIT 10
)
LIMIT 10;

Tõden, et teine alampäring täidetakse ainult juhul, kui esimene tagastas vähem, kui oodati viimasena LIMIT ridade arvu kohta. Sellest optimeerimismeetodist olen ma juba varem kirjutanud.

Noh, meil on nüüd tabelis samaaegselt nii btree kui ka gin, kuid statistika põhjal selgus, et alla 10% päringutest jõuab teise ploki täitmisele. See tähendab, et selliste tuttavate tüüpide piirangute korral suutsime serveri ressursitarbimist vähendada praktiliselt tuhandete kordadeni!

1.5*: saame hakkama ilma viiliga

Ülal LIKE meil takistas vale sortimine. Aga selle saab "õigele teele suunata" USING-operatoori abil:

Vaikimisi eeldatakse ASC. Samuti saab sortimisoperatoori nime määrata lauses USING. Sortimisoperatoor peab olema „väiksem“ või „suurem“ mõne B-puu operatsiooni perekonna liige. ASC tavaliselt samaväärne USING < ja DESC tavaliselt samaväärne USING >.

Meie puhul on „väiksem“ ~<~:

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

[vaata explain.tensor.ru]

2: kuidas päringud "hapnevad"

Nüüd jätame oma päringu „setitama“ pooleks aastaks kuni aastaks ja avastame üllatusega, et see on taas „tipus“ kogupäevase „mälupotentsiaali“ näitajatega (jagatud puhvri tabamused) 5.5TB — see on veelgi rohkem kui algselt.

Ei, loomulikult on meie äri kasvanud ja koormus suurenenud, kuid mitte nii palju! See tähendab, et midagi on siin valesti — lähme vaatame.

2.1: leheküljenduse sünn

Mõnes järgnevas hetkes soovis teise arendustiimi liige luua võimaluse kiirelt subtekstist „hüppamiseks“ registrisse, mis sisaldab samu, kuid laiendatud tulemusi. Aga mis register on leheküljendamata? Lähme lisame!

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

Nüüd sai arendajale muretseda, et näidata otsingu tulemuste registrit „leheküljendusega“ allalaadimiseks.

Muidugi, tegelikult loetakse iga järgmise andmelehe jaoks üha rohkem ja rohkem. (eelmiste kordade kõik, mida jätame kõrvale, pluss vajalik „sabake”) — see tähendab, et see on ühemõtteliselt vale muster. Õigem oleks algatada otsing järgmise iteratsiooni juures mäletatud võtme põhjal, aga sellest räägime mõni teine kord.

2.2: tahaks natuke eksootikat

Mõnel hetkel soovis arendaja mitmekesistada tulemustes olevat valikut teise tabeli andmetega, mille jaoks kogu eelmine päring saadeti CTE-sse:

WITH q AS (
  ...
  LIMIT  + 10
)
SELECT
  *
, (SELECT ...) sub_query -- mingi päring seotud tabelisse
FROM
  q
LIMIT 10 OFFSET ;

Ja isegi sel moel — pole paha, kuna sisemine päring arvutatakse vaid 10 tagastatud kirje jaoks, kui mitte…

2.3: DISTINCT mõttetu ja halastamatu

Kusagil selle evolutsiooni käigus teise alampäringu kadus ära NOT LIKE tingimus. On selge, et pärast seda UNION ALL hakkas tagastama mõningaid kirjeid kaks korda — esmalt leitud stringi alguse järgi, seejärel veel kord — selle stringi esimese sõna alguse järgi. Lõpptulemusena võisid kõik teise alampäringu kirjed kattuda esimese kirjetega.

Mida arendaja teeb, selle asemel et otsida põhjust?.. Ei mingit küsimust!

• kaks korda suurendame algsete valikute mahtu
• rakendame DISTINCT, et ainult ühe eksemplari iga rea jaoks sobiks

WITH q AS (
  ( ... LIMIT  + 10)
  UNION ALL
  ( ... LIMIT  + 10)
  LIMIT  + 10
)
SELECT DISTINCT
  *
, (SELECT ...) alampäring
FROM
  q
LIMIT 10 OFFSET ;

See tähendab, et tulemus on lõppkokkuvõttes täpselt sama, aga võimalus "mööda lasta" 2. alampäringus CTE on oluliselt kõrgem, ja ilma selleta ka loetakse kindlasti rohkem.

Aga see ei ole kõige hullem. Kuna arendaja palus valida DISTINCT mitte konkreetsetelt, vaid kohe kõikidelt väljadelt kirjed, siis sinna automaatselt sattus ka väli alampäring — alampäringu tulemus. Nüüd, et teostada DISTINCT, pidi baasis juba tegema mitte 10 alampäringut, vaid kõik + 10!

2.4: koostöö on kõik!

Nii magasid arendajad - ei kurtnud, sest registris "kohandada" tähtsate väärtuste N saavutamiseks, kui iga järgmise "lehe" hankimise aeglasemaks muutmine oli selgelt liiga palju kannatust nõudnud.

Kuni neid tulid arendajad teisest osakonnast ja soovisid kasutada sellist mugavat meetodit iteratiivseks otsimiseks — see, we take a piece from a sample, filter it by additional conditions, draw the result, then the next piece (which in our case is achieved by increasing N), and so on until we fill the screen.

In general, in the instance caught N reached values of nearly 17K, and in total over the day, at least 4K such requests were executed ‘in sequence’. The last of them were confidently scanned already by 1GB of memory at each iteration…

Kokku

Allikas: habr.com