🥇PostgreSQL antipattern'id: jutt iteratiivse otsingu täiustamisest pealkirja järgi, või "Optimeerimine edasi-tagasi"

Tuhanded müügiülemad üle kogu riigi registreerivad meie CRM-süsteemis igapäevaselt kümneid tuhandeid kontakte — suhtlemise fakte potentsiaalsete või juba meiega töötavate klientidega. Mida aga selle kliendi leidmiseks on kõige parem teha, on soovitav väga kiiresti. See toimub enamasti nime põhjal.

Seetõttu pole üllatav, et vaadates taas "raskete" päringute puhul ühte meie kõige koormatud andmebaasi — meie enda ettevõtte konto SBIS, avastasin, et "tipptaseme" päringu jaoks "kiire" nime järgi otsimiseks organisatsioonide kaardid.

Veelgi enam, edasine uurimine tõi esile huvitava näite esmalt optimeerimisest ja seejärel toimivuse halvenemisest päringu järjestikuse arendamise käigus mitme meeskonna poolt, kes tegutsesid ainul parimate kavatsustega.

0: mida tahtis kasutaja

[KDPV siit]

Mida kasutaja tavaliselt mõtleb, kui räägib "kiirest" otsingust nime järgi? Peaaegu kunagi ei ole see "aus" otsing lõigendiga nagu ... LIKE '%roosa%' — sest siis satub tulemustesse mitte ainult 'Roosalia' ja 'Pood Roosa', vaid ka 'Groosa' ja isegi 'Vanaema Roosa'.

Kasutaja mõistab aga igapäevaselt, et tagate talle otsingu sõna algusele nimedes ja näitate asjakohasemaid tulemusi, mis algavad sisestatud. Ja teete seda praktiivselt koheselt — lõige sisestamise puhul.

1: piirame ülesande

Ja inimesel ei tule kindlasti eraldi sisestada 'roos pood', et iga sõna peab teilt teatud eesliidet otsima. Ei, kasutajale on palju lihtsam reageerida viimase sõna kiirele soovitusele, kui eesmärgipäraselt "puudu jätta" eelnevad — vaadake, kuidas tõhusalt töötab iga otsingumootor.

Tegelikult, õigesti nõuete formuleerimine ülesandele — on juba üle poole lahendusest. Mõnikord võib tähelepanelik use case analüüs oluliselt mõjutada tulemust.

Mida aga abstraktne arendaja teeb?

1.0: väline otsingumootor

Oh, otsing on keeruline, pole üldse tahtmist sellega tegeleda — laseme selle devopsile! Las nad seadistavad meile välist, andmebaasist suhteliselt lahutatud otsingusüsteemi: Sphinx, ElasticSearch,…

Töötav, kuigi muutuste sünkroonimise ja operatiivsuse osas töömahukas variant. Kuid meie puhul mitte, kuna otsimine toimub iga kliendi jaoks ainult tema konto andmete raames. Andmed on piisavalt muutlikud - ja kui juhataja hetkel lisas kaardi, 'Rosa Pood', siis võib 5-10 sekundi pärast meelde tulla, et unustas seal e-posti aadressi märkida ja soovida seda leida ja parandada.

Seega - laseme otsida „otse andmebaasist“. Õnneks võimaldab PostgreSQL meil seda teha, ja mitte ainult ühe variandiga - neid vaatamegi.

1.1: „aus“ alamstring

Haarame kinni sõnast „alamstring“. Just selleks, et teostada indeksipõhist otsingut alamstringide (ja isegi regulaaravaldiste) järgi, on olemas suurepärane pg_trgm moodul! Ainult hiljem tuleb see korralikult järjestada.

Proovime võtta lihtsuse huvides sellise lisa:

LOOMINE TABEL firms(
  id
    seeria
      PRAEGUNE VÕTI
, nimi
    tekst
);

Laeme sinna 7,8 miljonit reaalset organisatsiooni ja indekseeri:

LISA UGAL pg_trgm;
LOOME INDeksi firms KASUTADES gin(lower(nimi) gin_trgm_ops);

Otsime alamstringi otsinguks esimesed 10 kirjet:

VALI
  *
FROM
  firms
WHERE
  lower(nimi) ~ ('(^|s)' || 'roosa')
ORDER BY
  lower(nimi) ~ ('^' || 'roosa') DESC -- esmalt "alustavad"
, lower(nimi) -- ülejäänud tähestiku järgi
LIMIT 10;

[vaata explain.tensor.ru]

Noh, nii... 26ms, 31MB loetud andmeid ja rohkem kui 1,7K filtreeritud kirjeid - 10 otsitava jaoks. Kulu on liiga suur, kas ei saaks kuidagi efektiivsemalt?

1.2: tekstipõhine otsing? see on FTS!

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

LOOME INDeksi firms KASUTADES gin(to_tsvector('simple'::regconfig, lower(nimi)));

VALI
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(nimi)) @@ to_tsquery('simple', 'roosa:*')
ORDER BY
  lower(nimi) ~ ('^' || 'roosa') DESC
, lower(nimi)
LIMIT 10;

[vaata explain.tensor.ru]

Siin aitas natuke paralleelne päringu täitmine, vähendades aega poole võrra kuni 11ms. Ja pidime lugema 1,5 korda vähem - kõigest 20MB. Ja siin kehtib, et mida vähem - seda parem, sest mida suurem maht me tõmbame, seda suurem on võimalus saada vahemälu vahelejätmiseks, ja iga liigne loetud andmefaili leht - potentsiaalne „pidurdus“ päringu jaoks.

1.3: ikkagi LIKE?

Kõik eelmine päring on hea, aga kui seda tõmmata sada tuhat korda päevas, siis mahtude jälgimine võib hakata ulatuma juba 2TB läbitud andmed. Parimal juhul - mälust, aga kui ei vea, siis ka kettalt. Nii et proovime seda väiksemaks teha.

Käidame meeles, et kasutaja tahab näha esmalt "mis algab ...". Noh, see on ju tõeline prefiksiga otsing kasutades text_pattern_ops! Ja ainult juhul, kui meil "ei piisa" 10 otsitavast kirjest, tuleb neid lugeda FTS-otsinguga:

Loo indeks ettevõtete kohta (lower(name) text_pattern_ops);

VALI
  *
KINNITUS
  ettevõtete
KUS
  lower(name) LIKE ('roos' || '%')
PIIRANG 10;

[vaata explain.tensor.ru]

Suurepärased näitajad - kokku 0,05 ms ja veidi üle 100 KB loetud! Ainult me unustasime nimede järgi sorteerimise, et kasutaja ei eksiks tulemuste seas:

VALI
  *
KINNITUS
  ettevõtete
KUS
  lower(name) LIKE ('roos' || '%')
SORTEERI
  lower(name)
PIIRANG 10;

[vaata explain.tensor.ru]

Oi, midagi on juba vähem ilus - tundub, et indeks on olemas, aga sorteerimine jätab teda kõrvale... See on kindlasti juba oluliselt efektiivsem kui eelmine variant, aga...

1.4: "töötleme käsitsi"

Aga on ju indeks, mis võimaldab otsida ka vahemikus ja kasutada sorteerimist normaalselt - tavaline btree!

Loo indeks ettevõtete kohta (lower(name));

Aga päring tuleb "koguda käsitsi":

VALI
  *
KINNITUS
  ettevõtete
KUS
  lower(name) >= 'roos' JA
  lower(name) <= ('roos' || chr(65535)) -- UTF8 jaoks, ühesügavuse puhul - chr(255)
SORTEERI
   lower(name)
PIIRANG 10;

[vaata explain.tensor.ru]

Suurepärane - ja sortimine töötab, ja ressursikasutus jäi "mikroskoopiliseks", tuhandetes kordi efektiivsem kui "puhas" FTS! Jäänud on koguda ühte päringusse:

(
  VALI
    *
  KINNITUS
    ettevõtete
  KUS
    lower(name) >= 'roos' JA
    lower(name) <= ('roos' || chr(65535)) -- UTF8 jaoks, ühesügavuse puhul - chr(255)
  SORTEERI
     lower(name)
  PIIRANG 10
)
UNION ALL
(
  VALI
    *
  KINNITUS
    ettevõtete
  KUS
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'roos:*') JA
    lower(name) NOT LIKE ('roos' || '%') -- "mis algavad" oleme juba leitud üleval
  SORTEERI
    lower(name) ~ ('^' || 'roos') DESC -- kasutame sama sortimist, et mitte minna btree-indeksisse
  , lower(name)
  PIIRANG 10
)
PIIRANG 10;

Täheldan, et teine alamküsitlus käivitatakse ainult siis, kui esimene tagastas oodatust vähem rida. LIMIT Sellest optimeerimise meetodist olen ma juba varem kirjutanud.

Nii on, meil on nüüd tabelil samaaegselt btree ja gin, kuid statistiliselt selgus, et alla 10% päringutest jõuavad teise ploki täitmise juurde. See tähendab, et selliste ette teadaolevate tüüpiliste piirangute puhul suudame me vähendada serveri kogukulutusi praktiliselt tuhandetes kordades!

1.5*: saame hakkama ilma viimistlemiseta

Üleval LIKE meile segas vale sorteerimine. Kuid selle saab "õigele teele suunata" USING- operaatori näitamisega:

Tavaliselt eeldatakse ASC. Lisaks on võimalik määrata konkreetse sorteerimisoperaatori nimi lauses USING. Sorteerimisoperaator peab olema "väiksem" või "suurem" mõnest B-puu opsioonide perekonnast. ASC tavaliselt on samaväärne USING < ja DESC tavaliselt on samaväärne USING >.

Kohapeal "väiksem" on see ~<~:

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

[vaata explain.tensor.ru]

2: kuidas päringud "hapenduvad"

Nüüd jätame meie päringu "seisma" kuue kuu kuni aastani, ja avastame üllatusega, et see on taas "tipphetkes" kogutud päevase "mälu tõstmise" näitajatega (buffers shared hit) 5.5TB — seega veelgi rohkem kui algselt.

Muidugi on meie äri kasvanud ja koormus on suurenenud, kuid mitte nii palju! Siis on midagi kahtlast — uurime lähemalt.

2.1: lehepteo sünn

Mõnes etapis tahtis teine arendajate meeskond võimaldada kiire altotsingu kaudu "hüppamist" registrisse, mis sisaldas samu, kuid laiendatud tulemusi. Aga milline register ilma lehe navigeerimiseta? Loodame selle ära teha!

( ... LIMIT  + 10)
UNION ALL
( ... LIMIT  + 10)
LIMIT 10 OFFSET ;

Nüüd oli arendajal võimalik hõlpsasti näidata otsingutulemuste registrit "lehe-tüüpi" alalaadimisega.

Muidugi, tegelikult kui iga järgmise lehe andmeid loetakse üha rohkem (kõik eelmisest korrast, mida me kõrvale viskame, pluss vajalik "sabajupp") — see on selgelt vale muster. Õigem oleks olnud käivitada otsing järgmise iteratsiooni juurest, salvestatud võtmega, aga sellest räägime teisel korral.

2.2: tahaks eksotikat

Mõnes etapis soovis arendaja mitmekesistada tulemuste valikut andmetega teise tabeli seest, milleks kogu eelnev päring saadeti CTE-sse:

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

Ja isegi nii — mitte halb, kuna siseriikide päring arvutatakse ainult 10 tagastatud kirje jaoks, kui just ei olnud...

2.3: DISTINCT mõttetu ja halastamatu

Kusagil sellise evolutsiooni protsessis kadusid 2. alampäringust kadus NOT LIKE tingimus. Selge on see, et pärast seda UNION ALL hakkas tagasi tooma mõningad kirjed kaks korda — esialgu leitud aluse järgi, ja siis veel kord — vastavalt esimese sõna algusele selle reas. Limiteeritud, kõik teise aluses küsimuse kirjed võisid kattuda esimese kirjedega.

Mida teeb arendaja põhjuseta otsimise asemel?.. Pole küsimust!

kordame suurust kahekordseks algsete valimite
rakendame DISTINCT, et saada igast reast ainult ühekordseid eksemplare

WITH q AS (
  ( ... LIMIT  + 10)
  UNION ALL
  ( ... LIMIT  + 10)
  LIMIT  + 10
)
SELECT DISTINCT
  *
, (SELECT ...) alussügavus
FROM
  q
LIMIT 10 OFFSET ;

Seega on selge, et tulemus on lõpuks ikka sama, kuid võimalus "läpata" teises aluses CTE-s on palju suurem, ja ilma selleta loetakse selgelt rohkem.

Aga see ei ole kõige halvem. Kuna arendaja palus valida STDEV mitte konkreetse, vaid kohe kõikide väljade järgi kirjed, siis pääses automaatselt ka väli sub_query — alusküsimuse tulemus. Nüüd, et täita STDEV, pidi andmebaas tegema juba mitu 10 alusküsimust, vaid kõik + 10!

2.4: koostöö on kõige üle!

Nii nad elavad, arendajad — ei muretse, sest registris "teha" oluliseks väärtusteks N pideva edasiviimise juures igas järgmises "leheküljas" oli kasutajal ilmselt kannatust puudu.

Kuni teise osakonna arendajad tulid nende juurde ja soovisid kasutada nii mugavat meetodit iteratiivseks otsimiseks — st võtame mingist valimist tükikese, filtreerime lisatingimuste järgi, joonistame tulemuse, siis järgmise tükikese (mida meie puhul saavutatakse N suurendamise teel), ja nii edasi, kuni täidame ekraani.

Ühesõnaga, püütud eksemplaris N saavutas väärtusi peaaegu 17K, ja viimase 24 tunni jooksul tehti "ahelas" mitte vähem kui 4K sellist küsimust. Viimased neist skaneerisid julgelt 1GB mälu igal iteratsioonil…