ProHoster > Blog > Administrim > Antipatterns PostgreSQL: tregu për përmirësimin e iterativ të kërkimit sipas emrit, ose "Optimizimi atje dhe këtu"

Antipatterns PostgreSQL: tregu për përmirësimin e iterativ të kërkimit sipas emrit, ose "Optimizimi atje dhe këtu"

MijĂ«ra menaxherĂ« nga zyrat e shitjeve nĂ« tĂ« gjithĂ« vendin regjistrojnĂ« nĂ« sistemin tonĂ« CRM çdo ditĂ« dhjetĂ«ra mijĂ«ra kontakte — fakte tĂ« komunikimit me klientĂ«t potencialĂ« ose ata qĂ« tashmĂ« punojnĂ« me ne. Dhe pĂ«r kĂ«tĂ« klient duhet fillimisht ta gjejmĂ«, dhe Ă«shtĂ« e dĂ«shirueshme qĂ« ta bĂ«jmĂ« shumĂ« shpejt. Kjo ndodh shpesh nĂ«pĂ«rmjet emrit.

Prandaj nuk Ă«shtĂ« e çuditshme qĂ«, duke analizuar sĂ«rish "kĂ«rkesat e rĂ«nda" nĂ« njĂ« nga bazat mĂ« tĂ« ngarkuara — llogarinĂ« tonĂ« korporative tĂ« SBIS, kam zbuluar "nĂ« majĂ«" kĂ«rkesĂ«n pĂ«r "kĂ«rkimin e shpejtĂ«" sipas emrit pĂ«r kartat e organizatave.

Dhe hulumtimi i mëtejshëm zbuloi një shembull interesant fillimisht të optimizimit dhe më pas të degradimit të performancës të kërkesës gjatë përmirësimit të saj të vazhdueshëm nga disa ekipe, çdo njëra prej të cilave vepronte ekskluzivisht me qëllime të mira.

0: çfarë donte përdoruesi

Antipatterns PostgreSQL: tregu për përmirësimin e iterativ të kërkimit sipas emrit, ose "Optimizimi atje dhe këtu"[KDPV këtu]

ÇfarĂ« nĂ«nkupton zakonisht pĂ«rdoruesi kur flet pĂ«r "kĂ«rkimin e shpejtĂ«" sipas emrit? Pothuajse kurrĂ« nuk Ă«shtĂ« "kĂ«rkimi i ndershĂ«m" sipas njĂ« nĂ«nstrinje si ... LIKE '%rosĂ«%' — pĂ«rndryshe nĂ« rezultat merren jo vetĂ«m 'Rosalia' dhe 'Dyqan Rosa', por edhe 'GrosĂ«' dhe madje 'ShtĂ«pia e DedĂ« MorosĂ«'.

PĂ«rdoruesi e nĂ«nkupton nĂ« njĂ« nivel tĂ« zakonshĂ«m se ju do t'i siguroni kĂ«rkimin sipas fillimit tĂ« fjalĂ«s nĂ« emĂ«r dhe do tĂ« tregoni mĂ« tĂ« relevuara ato qĂ« fillojnĂ« me ato qĂ« Ă«shtĂ« futur. Dhe do ta bĂ«ni kĂ«tĂ« praktikisht menjĂ«herĂ« — me hyrje pĂ«r substrat.

1: kufizojmë detyrën

Dhe pĂ«r mĂ« tepĂ«r, nuk ka asnjĂ« shans qĂ« njĂ« njeri tĂ« futĂ« veçmas 'roz dyqan', pĂ«r tĂ« kĂ«rkuar çdo fjalĂ« sipas prefiksit. Jo, pĂ«r pĂ«rdoruesin Ă«shtĂ« shumĂ« mĂ« e lehtĂ« tĂ« reagojĂ« ndaj sugjerimit tĂ« shpejtĂ« pĂ«r fjalĂ«n e fundit sesa tĂ« "mos e mbushĂ«" me qĂ«llim fjalĂ«t e mĂ«parshme — shikoni sesi çdo motor kĂ«rkimi e bĂ«n kĂ«tĂ«.

NĂ« pĂ«rgjithĂ«si, Ă«shtĂ« e drejtĂ« tĂ« formulojmĂ« kĂ«rkesat pĂ«r detyrĂ«n — mĂ« shumĂ« se gjysma e zgjidhjes. NdonjĂ«herĂ« njĂ« analizĂ« e kujdesshme e rastit tĂ« pĂ«rdorimit mund tĂ« ketĂ« njĂ« ndikim tĂ« konsiderueshĂ«m nĂ« rezultat.

ÇfarĂ« bĂ«n zhvilluesi abstrakt?

1.0: motorin e kërkimit të jashtëm

Oh, searching is hard, I don't even want to deal with it — let's hand it off to devops! Let them set up an external search system relative to the database: Sphinx, ElasticSearch,


It's a workable option, although labor-intensive in terms of synchronization and timely changes. But not in our case, since the search is carried out for each client only within the data of their account. And the data has quite high variability — and if now the manager has added a card 'Rose Store', in 5-10 seconds he may remember that he forgot to include the email and want to find and correct it.

So, let's search "directly in the database". Fortunately, PostgreSQL allows us to do this in multiple ways — we will explore them.

1.1: "honest" substring

We focus on the word "substring". Indeed, there is an excellent module pg_trgmfor indexed searching by substring (and even by regular expressions!) Only then we will need to sort it correctly.

Let's take a simple table for our model:

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

We insert 7.8 million records of real organizations and index them:

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

Let's search for the first 10 records for substring search:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|\s)' || 'rose')
ORDER BY
  lower(name) ~ ('^' || 'rose') DESC -- first "those starting with"
, lower(name) -- the rest alphabetically
LIMIT 10;

Antipatterns PostgreSQL: tregu për përmirësimin e iterativ të kërkimit sipas emrit, ose "Optimizimi atje dhe këtu"
[view on explain.tensor.ru]

Well, something like that
 26ms, 31MB of read data and more than 1.7K filtered records — for 10 sought. The overhead is too high, can't we do it more efficiently?

1.2: searching by text? Isn't that FTS!

Indeed, PostgreSQL provides a very powerful full-text search (Full Text Search), including prefix search capabilities. A great option, and no need to install extensions! Let's give it a try:

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', 'rose:*')
ORDER BY
  lower(name) ~ ('^' || 'rose') DESC
, lower(name)
LIMIT 10;

Antipatterns PostgreSQL: tregu për përmirësimin e iterativ të kërkimit sipas emrit, ose "Optimizimi atje dhe këtu"
[view on explain.tensor.ru]

Here, parallel execution of the query helped us a bit, cutting the time in half to 11ms. And we had to read 1.5 times less — just 20MB. KĂ«tu, mĂ« pak Ă«shtĂ« mĂ« mirĂ«, sepse sa mĂ« shumĂ« informacion tĂ« shfletojmĂ«, aq mĂ« tĂ« larta janĂ« shanset pĂ«r tĂ« pasur cache miss, dhe çdo faqe e lexuar e cila Ă«shtĂ« e tepĂ«rt nga disku Ă«shtĂ« njĂ« potencial "ngadalĂ«sim" pĂ«r kĂ«rkesĂ«n.

1.3: A të përdorim LIKE?

Të gjitha kërkesat e mëparshme janë të mira, por nëse e tërheqim atë njëqind mijë herë në ditë, atëherë do të akumulohet 2TB të dhënash të lexuara. Në rastin më të mirë, nga memoria, por nëse nuk kemi fat, atëherë edhe nga disku. Pra, le të provojmë ta bëjmë më të vogël.

Le tĂ« kujtojmĂ« se pĂ«rdoruesi dĂ«shiron tĂ« shohĂ« fillimisht "atĂ« qĂ« fillon me ...". Pra, kjo Ă«shtĂ« nĂ« forma tĂ« pastĂ«r kĂ«rkimi me prefiks nĂ«pĂ«rmjet text_pattern_ops! Dhe vetĂ«m nĂ«se nuk kemi "mjaft" deri nĂ« 10 regjistrat e kĂ«rkuar, atĂ«herĂ« do tĂ« duhet t’i lexojmĂ« ato me kĂ«rkimin FTS:

KRIJO INDIREKT NË firmat(lower(name) text_pattern_ops);

Zgjedh
  *
PĂ«r
  firmat
KU
  lower(name) LIKE ('roza' || '%')
KUFIZO 10;

Antipatterns PostgreSQL: tregu për përmirësimin e iterativ të kërkimit sipas emrit, ose "Optimizimi atje dhe këtu"
[view on explain.tensor.ru]

TĂ« dhĂ«na tĂ« shkĂ«lqyera — vetĂ«m 0.05ms dhe pak mĂ« shumĂ« se 100KB tĂ« lexuara! Por ne harrojmĂ« renditjen sipas emrit, nĂ« mĂ«nyrĂ« qĂ« pĂ«rdoruesi tĂ« mos humbasĂ« nĂ« rezultatet:

Zgjedh
  *
PĂ«r
  firmat
KU
  lower(name) LIKE ('roza' || '%')
RENDIT NGA
  lower(name)
KUFIZO 10;

Antipatterns PostgreSQL: tregu për përmirësimin e iterativ të kërkimit sipas emrit, ose "Optimizimi atje dhe këtu"
[view on explain.tensor.ru]

Ooh, diçka nuk Ă«shtĂ« mĂ« kaq e bukur — duket se ka njĂ« indeks, por renditja po kalon pĂ«rtej tij... Natyrisht, tashmĂ« Ă«shtĂ« shumĂ« mĂ« efektive se opsioni i mĂ«parshĂ«m, por...

1.4: "të përmirësohet me skrap"

Por ekziston njĂ« indeks qĂ« lejon tĂ« kĂ«rkosh nĂ« mĂ«nyrĂ« tĂ« rregullt dhe tĂ« pĂ«rdorĂ«sh renditjen nĂ« mĂ«nyrĂ« tĂ« duhur — indeksi btree!

KRIJO INDIREKT NË firmat(lower(name));

Vetëm se kërkesa për të do të duhet "të ndërtohet manualisht":

Zgjedh
  *
PĂ«r
  firmat
KU
  lower(name) >= 'roza' DHE
  lower(name) <= ('roza' || chr(65535)) -- për UTF8, për një-byte - chr(255)
RENDIT NGA
   lower(name)
KUFIZO 10;

Antipatterns PostgreSQL: tregu për përmirësimin e iterativ të kërkimit sipas emrit, ose "Optimizimi atje dhe këtu"
[view on explain.tensor.ru]

ShkĂ«lqyeshĂ«m — dhe renditja funksionon, dhe konsumi i burimeve ka mbetur "mikroskopik" mĂ« efektive njĂ« mijĂ« herĂ« se "FTS i pastĂ«r"! E vetmja gjĂ« qĂ« duhet tĂ« bĂ«jmĂ« Ă«shtĂ« tĂ« mbledhim nĂ« njĂ« kĂ«rkesĂ« tĂ« vetme:

(
  Zgjedh
    *
  PĂ«r
    firmat
  KU
    lower(name) >= 'roza' DHE
    lower(name) <= ('roza' || chr(65535)) -- për UTF8, për kodet e një-byte - chr(255)
  RENDIT NGA
     lower(name)
  KUFIZO 10
)
UNION ALL
(
  Zgjedh
    *
  PĂ«r
    firmat
  KU
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'roza:*') DHE
    lower(name) NOT LIKE ('roza' || '%') -- "që fillon me" ne e gjetëm më lart
  RENDIT NGA
    lower(name) ~ ('^' || 'roza') DESC -- përdorim të njëjtin renditje, për të mos kaluar në indeksin btree
  , lower(name)
  KUFIZO 10
)
KUFIZO 10;

Vërej se, nënkërkesa e dytë ekzekutohet vetëm nëse e para kthen më pak nga sa pritej të fundit KUFIZO numri i rreshtave. Për këtë mënyrë optimizimi të kërkesave kam shkruar më parë.

Saktë, tani kemi në tabelë njëkohësisht btree dhe gin, por statistikat tregojnë se më pak se 10% e kërkesave arrijnë në ekzekutimin e bllokut të dytë. Pra, me këto kufizime të njohura përpara, arritëm të reduktojmë konsumimin e burimeve të serverit pothuajse në mijëra herë!

1.5*: do ta kalojmë pa dorëzë

Më lart LIKE na pengoi përdorimi i renditjes së gabuar. Por mund ta 'orientojmë në rrugën e duhur' me ndihmën e operatorit USING:

Për parazgjedhje, nënkuptohet ASC. Për më tepër, mund të caktoni emrin e operatorit specifik të renditjes në propozimin USING. Operatorët e renditjes duhet të jenë anëtarë të familjes së operatorëve B-tree të 'më pak' ose 'më shumë'. ASC zakonisht është e barabartë me USING < dhe DESC zakonisht është e barabartë me USING >.

Në rastin tonë 'më pak' është ~<~:

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

Antipatterns PostgreSQL: tregu për përmirësimin e iterativ të kërkimit sipas emrit, ose "Optimizimi atje dhe këtu"
[view on explain.tensor.ru]

2: si 'fermentohen' kërkesat

Tani e lĂ«mĂ« kĂ«rkesĂ«n tonĂ« tĂ« 'nastohet' pĂ«r gjashtĂ« muaj deri nĂ« njĂ« vit, dhe me habi e gjejmĂ« pĂ«rsĂ«ri nĂ« 'top' me treguesit e konsumit tĂ« pĂ«rditshĂ«m tĂ« 'pĂ«rmirĂ«simit' tĂ« memories (buffers shared hit) nĂ« 5.5TB — domethĂ«nĂ« mĂ« shumĂ« se sa ishte nĂ« fillim.

Jo, sigurisht, dhe biznesi ynĂ« Ă«shtĂ« rritur, si dhe ngarkesa Ă«shtĂ« rritur, por jo aq shumĂ«! KĂ«shtu qĂ«, Ă«shtĂ« diçka kĂ«tu qĂ« nuk Ă«shtĂ« nĂ« rregull — le tĂ« merremi me kĂ«tĂ«.

2.1: lindja e paginimit

Në një moment, një ekip tjetër zhvilluesish dëshiron të bëjë të mundur që nga kërkimi i shpejtë të 'kalohet' në regjistrin me të njëjtat por rezultate të zgjeruara. Dhe cila është regjistri pa navigim faqe për faqe? Le ta shtojmë!

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

Tani ishte e mundur të shfaqnim regjistrin e rezultateve të kërkimit me 'ngarkim faqe për faqe' pa ndonjë vështirësi për zhvilluesin.

Sigurisht, nĂ« tĂ« vĂ«rtetĂ«, pĂ«r çdo faqe tĂ« re tĂ« dhĂ«nash, lexohen gjithnjĂ« e mĂ« shumĂ« (tĂ« gjitha nga hera e kaluar qĂ« do t'i heqim, plus 'bishtin' e nevojshĂ«m) — qĂ« do tĂ« thotĂ« se kjo Ă«shtĂ« njĂ« antipatĂ«r dhe mĂ« e drejtĂ« do tĂ« ishte tĂ« fillonim kĂ«rkimin nĂ« iteracionin e ardhshĂ«m nga kyçja e mbajtur nĂ« ndĂ«rfaqe, por pĂ«r kĂ«tĂ« do tĂ« flasim njĂ« herĂ« tjetĂ«r.

2.2: dëshira për egzotizëm

Në një moment zhvilluesi dëshiron të pasurojë zgjedhjen e rezultateve me të dhëna nga tabela tjetër, për të cilin të gjithë kërkesa e mëparshme u dërgua në CTE:

ME Q AS (
  ...
  LIMIT  + 10
)
SELECT
  *
, (SELECT ...) sub_query -- një pyetje për tryezën e lidhur
FROM
  q
LIMIT 10 OFFSET ;

Madje, ashtu — nuk Ă«shtĂ« keq, sepse pyetja e brendshme llogaritet vetĂ«m pĂ«r 10 regjistrimet qĂ« kthehen, po sikur tĂ« mos ishte...

2.3: DISTINCT pa kuptim dhe pa mëshirë

Diku gjatĂ« kĂ«tij procesi tĂ« evoluimit nga nĂ«npyetja e 2-tĂ« u humb NOT LIKE kushti. ËshtĂ« e qartĂ« se pas kĂ«saj UNION ALL filloi tĂ« kthente disa regjistrime dy herĂ« — nĂ« fillim tĂ« gjetur sipas fillimit tĂ« vargut, dhe pastaj pĂ«rsĂ«ri — sipas fillimit tĂ« fjalĂ«s sĂ« parĂ« tĂ« atij vargu. NĂ« limit, tĂ« gjitha regjistrimet e nĂ«npyetjes 2 mund tĂ« pĂ«rputhen me regjistrimet e para.

ÇfarĂ« bĂ«n zhvilluesi nĂ« vend tĂ« kĂ«rkimit tĂ« arsyes?.. Jo njĂ« pyetje!

  • do ta zgjerojmĂ« dyfish madhĂ«sinĂ« e grupeve fillestare
  • do tĂ« aplikojmĂ« DISTINCT, nĂ« mĂ«nyrĂ« qĂ« tĂ« kemi vetĂ«m njĂ« ekzekutim tĂ« çdo rreshti

ME Q AS (
  ( ... LIMIT  + 10)
  UNION ALL
  ( ... LIMIT  + 10)
  LIMIT  + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET ;

Pra, është e qartë se rezultati, në fund, është pikërisht i njëjtë, por shansi për të 'shkëputur' në nënpyetjen e 2-të CTE është rritur ndjeshëm, dhe pa këtë, lexohet qartë më shumë.

Por kjo nuk Ă«shtĂ« trishtimi mĂ« i madh. Sepse zhvilluesi kĂ«rkoi tĂ« seleksionojĂ« DISTINCT jo sipas tĂ« dhĂ«nave specifike, por direkt sipas tĂ« gjithĂ« fushave regjistrimet, kĂ«shtu qĂ« automatikisht ra nĂ« fushĂ«n sub_query — rezultati i nĂ«npyetjes. Tani, pĂ«r tĂ« realizuar DISTINCT, baza duhej tĂ« kryente tashmĂ« jo 10 nĂ«npyetje, por tĂ« gjitha + 10!

2.4: kooperimi mbi të gjitha!

Ja si jetonin zhvilluesit — pa u shqetĂ«suar, sepse nĂ« regjistrin 'tĂ« rrisin' N tĂ« rĂ«ndĂ«sishme nĂ« mĂ«nyrĂ« kronike pĂ«r tĂ« pĂ«rshpejtuar marrjen e secilĂ«s 'faqe' tĂ« re pĂ«r pĂ«rdoruesin, qartĂ« nuk kishin durim.

Derisa iu erdhi zhvilluesit nga njĂ« departament tjetĂ«r, dhe nuk deshĂ«n tĂ« shfrytĂ«zojnĂ« kĂ«tĂ« metodĂ« tĂ« pĂ«rshtatshme pĂ«r kĂ«rkimin iterativ — dmth marrim njĂ« copĂ« nga njĂ« grup, filtrojmĂ« sipas kushteve shtesĂ«, vizatojmĂ« rezultatin, pastaj copĂ«n tjetĂ«r (gjĂ« qĂ« nĂ« rastin tonĂ« arrihet duke rritur N), dhe kĂ«shtu deri sa ta mbushim ekranin.

NĂ« pĂ«rgjithĂ«si, nĂ« ekzemplarin e kapur N arriti vlera gati nĂ« 17K, dhe brenda njĂ« dite u ekzekutuan 'nĂ« zinxhir' jo mĂ« pak se 4K kĂ«rkesa tĂ« tilla. TĂ« fundit prej tyre e skanuan me guxim tashmĂ« sipas 1GB memorjeje nĂ« çdo iteracion


Përveç kësaj

Antipatterns PostgreSQL: tregu për përmirësimin e iterativ të kërkimit sipas emrit, ose "Optimizimi atje dhe këtu"

Burimi: habr.com

Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS đŸ”„ Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS | ProHoster