🥇PostgreSQL Antipatterns: usa ka istorya sa iterative refinement sa pagpangita pinaagi sa ngalan, o "Optimization back and forth"

Liboan ka mga manedyer gikan sa mga opisina sa pagbaligya sa tibuok nasud nga rekord atong CRM nga sistema napulo ka libo nga mga kontak kada adlaw — mga kamatuoran sa komunikasyon sa mga potensyal o kasamtangan nga mga kliyente. Ug alang niini, kinahanglan una nimo nga makit-an ang usa ka kliyente, ug labi ka dali. Ug kini kasagaran mahitabo pinaagi sa ngalan.

Busa, dili ikatingala nga, sa makausa pa nag-analisar sa "bug-at" nga mga pangutana sa usa sa labing puno nga mga database - ang atong kaugalingon VLSI corporate account, nakit-an nako "sa taas" hangyo alang sa usa ka "dali" nga pagpangita sa ngalan alang sa mga kard sa organisasyon.

Dugang pa, ang dugang nga imbestigasyon nagpadayag usa ka makapaikag nga pananglitan una nga pag-optimize ug pagkahuman pagkadaot sa pasundayag hangyo uban ang sunud-sunod nga pagpino niini sa daghang mga koponan, nga ang matag usa milihok lamang nga adunay labing kaayo nga katuyoan.

0: unsay gusto sa user?

[KDPV gikan dinhi]

Unsa ang kasagarang ipasabot sa usa ka tiggamit kon sila maghisgot mahitungod sa usa ka "dali" nga pagpangita pinaagi sa ngalan? Kini hapit dili mahimong usa ka "matinud-anon" nga pagpangita alang sa usa ka substring nga sama ... LIKE '%роза%' - tungod kay ang resulta naglakip dili lamang 'Розалия' и 'Магазин Роза'apan 'Гроза' ug bisan pa 'Дом Деда Мороза'.

Ang tiggamit nagtuo sa adlaw-adlaw nga lebel nga imong ihatag kaniya pangitaa pinaagi sa pagsugod sa pulong sa titulo ug himoa nga mas may kalabotan kana nagsugod sa nisulod. Ug buhaton nimo kini hapit dayon - alang sa interlinear input.

1: limitahan ang buluhaton

Ug labaw pa, ang usa ka tawo dili espesipikong mosulod 'роз магаз', aron kinahanglan nimong pangitaon ang matag pulong pinaagi sa prefix. Dili, mas sayon alang sa usa ka user ang pagtubag sa usa ka dali nga pahibalo alang sa katapusang pulong kaysa sa tinuyo nga "underspecify" ang mga nauna - tan-awa kung giunsa kini pagdumala sa bisan unsang search engine.

General husto ang paghimo sa mga kinahanglanon alang sa problema labaw pa sa katunga sa solusyon. Usahay mainampingon nga paggamit sa pagtuki sa kaso makaimpluwensya kaayo sa resulta.

Unsa ang gibuhat sa usa ka abstract developer?

1.0: eksternal nga search engine

Oh, lisud ang pagpangita, dili ko gusto nga buhaton ang bisan unsa - ihatag kini sa mga devops! Tugoti sila nga mag-deploy og search engine sa gawas sa database: Sphinx, ElasticSearch,...

Usa ka opsyon sa pagtrabaho, bisan pa sa labor-intensive sa mga termino sa pag-synchronize ug katulin sa mga pagbag-o. Apan dili sa among kaso, tungod kay ang pagpangita gihimo alang sa matag kliyente sulod lamang sa gambalay sa datos sa iyang account. Ug ang datos adunay medyo taas nga pagkalainlain - ug kung ang manedyer nakasulod na sa kard 'Магазин Роза', unya human sa 5-10 ka segundos basin mahinumdoman na niya nga nakalimot siya sa pagpaila sa iyang email didto ug gusto niyang pangitaon ug tul-iron.

Busa - atong pangitaa ang "direkta sa database". Maayo na lang, gitugotan kami sa PostgreSQL nga buhaton kini, ug dili lang usa nga kapilian - tan-awon namon sila.

1.1: "matinud-anon" nga substring

Nagkupot kami sa pulong nga "substring". Apan alang sa pagpangita sa indeks pinaagi sa substring (ug bisan sa regular nga mga ekspresyon!) Adunay usa ka maayo kaayo module pg_trgm! Unya ra kinahanglan ang paghan-ay sa husto.

Atong sulayan ang pagkuha sa mosunod nga plato aron pasimplehon ang modelo:

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

Nag-upload kami og 7.8 ka milyon nga mga rekord sa tinuod nga mga organisasyon didto ug gi-index kini:

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

Pangitaon nato ang unang 10 ka rekord para sa interlinear nga pagpangita:

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

[tan-awa sa explain.tensor.ru]

Aw, mao na... 26ms, 31MB basaha ang datos ug labaw pa sa 1.7K nasala nga mga rekord - para sa 10 ka gipangita. Taas kaayo ang gasto sa overhead, wala bay mas episyente?

1.2: pangita pinaagi sa text? FTS na nga!

Sa tinuud, ang PostgreSQL naghatag usa ka kusgan kaayo full text search engine (Full Text Search), lakip ang abilidad sa prefix search. Usa ka maayo nga kapilian, dili nimo kinahanglan nga mag-install mga extension! Atong sulayan:

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;

[tan-awa sa explain.tensor.ru]

Dinhi ang parallelization sa query execution nakatabang kanamo gamay, pagputol sa oras sa katunga sa 11ms. Ug kinahanglan namon nga basahon ang 1.5 ka beses nga mas gamay - sa kinatibuk-an 20MB. Apan dinhi, mas gamay, mas maayo, tungod kay ang mas dako nga volume nga atong gibasa, mas taas ang kahigayonan nga makakuha og cache miss, ug ang matag dugang nga pahina sa data nga mabasa gikan sa disk usa ka potensyal nga "preno" alang sa hangyo.

1.3: LIKE gihapon?

Ang nauna nga hangyo maayo alang sa tanan, apan kung gibira nimo kini usa ka gatos ka libo ka beses sa usa ka adlaw, kini moabut 2TB pagbasa sa datos. Sa labing maayo nga kaso, gikan sa panumduman, apan kung dili ka swerte, unya gikan sa disk. Busa atong sulayan nga himoon kini nga mas gamay.

Atong hinumdoman kung unsa ang gusto nga makita sa tiggamit una "nga nagsugod sa...". Busa kini anaa sa labing putli nga porma niini pagpangita sa prefix sa panabang text_pattern_ops! Ug kung kami "walay igo" hangtod sa 10 nga mga rekord nga among gipangita, nan kinahanglan namon nga tapuson ang pagbasa niini gamit ang pagpangita sa FTS:

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

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

[tan-awa sa explain.tensor.ru]

Maayo kaayo nga performance - total 0.05ms ug mas gamay sa 100KB basaha! Kami ra ang nakalimot paghan-ay sa ngalanaron ang tiggamit dili mawala sa mga resulta:

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

[tan-awa sa explain.tensor.ru]

Oh, adunay usa ka butang nga dili na kaayo matahum - ingon og adunay usa ka indeks, apan ang paghan-ay nga molabay niini ... Kini, siyempre, sa daghang mga higayon nga mas epektibo kaysa sa miaging kapilian, apan...

1.4: "paghuman gamit ang file"

Apan adunay usa ka indeks nga nagtugot kanimo sa pagpangita pinaagi sa gidak-on ug gigamit gihapon ang paghan-ay nga normal - regular nga btree!

CREATE INDEX ON firms(lower(name));

Ang hangyo lamang alang niini kinahanglan nga "manual nga kolektahon":

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

[tan-awa sa explain.tensor.ru]

Maayo kaayo - ang paghan-ay nga mga buhat, ug ang pagkonsumo sa kapanguhaan nagpabilin nga "microscopic", linibo ka pilo nga mas epektibo kay sa “putli” nga FTS! Ang nahabilin mao ang paghiusa niini sa usa ka hangyo:

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

Timan-i nga ang ikaduhang subquery gipatuman lamang kon ang una mibalik nga ubos pa kay sa gipaabot ang katapusan LIMIT gidaghanon sa mga linya. Naghisgot ko bahin niini nga pamaagi sa pag-optimize sa pangutana gisulat na kaniadto.

Mao nga oo, kami karon adunay duha nga btree ug gin sa lamesa, apan sa istatistika kini nahimo ubos sa 10% sa mga hangyo makaabot sa pagpatuman sa ikaduhang block. Kana mao, uban sa ingon nga kasagaran nga mga limitasyon nga nahibal-an daan alang sa buluhaton, nakahimo kami sa pagpakunhod sa kinatibuk-ang konsumo sa mga kapanguhaan sa server sa hapit usa ka libo ka beses!

1.5*: mahimo namong walay file

Ibabaw sa LIKE Gipugngan kami sa paggamit sa dili husto nga paghan-ay. Apan kini mahimong "ibutang sa husto nga dalan" pinaagi sa pagtino sa USING operator:

Pinaagi sa default kini gituohan ASC. Dugang pa, mahimo nimong ipiho ang ngalan sa usa ka piho nga operator sa usa ka clause USING. Ang klase nga operator kinahanglang usa ka membro sa mas ubos o mas dako kay sa pipila ka pamilya sa mga operator sa B-tree. ASC kasagaran katumbas USING < и DESC kasagaran katumbas USING >.

Sa among kaso, ang "gamay" mao ~<~:

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

[tan-awa sa explain.tensor.ru]

2: sa unsang paagi ang mga hangyo nahimong maaslom

Karon gibiyaan namo ang among hangyo nga "simmer" sulod sa unom ka bulan o usa ka tuig, ug natingala kami nga makita kini pag-usab "sa ibabaw" nga adunay mga timailhan sa kinatibuk-ang adlaw-adlaw nga "pagbomba" sa panumduman (buffers mipakigbahin hit) sa 5.5TB - nga mao, labaw pa kay sa orihinal.

Dili, siyempre, ang among negosyo miuswag ug ang among trabaho misaka, apan dili sa parehas nga kantidad! Kini nagpasabot nga adunay usa ka butang nga dili maayo dinhi - atong mahibal-an kini.

2.1: ang pagkatawo sa paging

Sa usa ka punto, ang laing development team gusto nga himoong posible ang "paglukso" gikan sa usa ka dali nga pagpangita sa subscript ngadto sa rehistro nga adunay parehas, apan gipalapdan nga mga resulta. Unsa ang usa ka rehistro nga wala’y nabigasyon sa panid? Ato nang gub-on!

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

Karon posible nga ipakita ang rehistro sa mga resulta sa pagpangita nga adunay "panid-sa-panid" nga pagkarga nga wala’y bisan unsang kapit-os alang sa developer.

Siyempre, sa pagkatinuod, para sa matag sunod-sunod nga panid sa datos nagkadaghan ang gibasa (tanan gikan sa miaging panahon, nga atong isalikway, plus ang gikinahanglan nga "ikog") - nga mao, kini mao ang usa ka tin-aw nga antipattern. Apan mas husto ang pagsugod sa pagpangita sa sunod nga pag-uli gikan sa yawe nga gitipigan sa interface, apan bahin niana sa laing higayon.

2.2: Gusto nako ang usa ka butang nga lahi

Sa usa ka punto gusto sa developer pag-diversify sa resulta nga sample gamit ang datos gikan sa laing lamesa, diin ang tibuok naunang hangyo gipadala ngadto sa CTE:

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

Ug bisan pa, dili kini daotan, tungod kay ang subquery gisusi lamang alang sa 10 nga gibalik nga mga rekord, kung dili ...

2.3: DISTINCT walay salabotan ug walay kaluoy

Sa usa ka dapit sa proseso sa maong ebolusyon gikan sa 2nd subquery nawala NOT LIKE kahimtang. Klaro nga pagkahuman niini UNION ALL nagsugod pagbalik pipila ka mga entries kaduha - una nga nakit-an sa sinugdanan sa linya, ug unya pag-usab - sa sinugdanan sa unang pulong niini nga linya. Sa limitasyon, ang tanan nga mga rekord sa 2nd subquery mahimong motugma sa mga rekord sa una.

Unsa ang buhaton sa usa ka developer imbes nga mangita sa hinungdan?.. Walay pangutana!

doble ang gidak-on orihinal nga mga sample
i-apply ang DISTINCTaron makakuha lamang og usa ka higayon sa matag linya

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 ato pa, klaro nga ang resulta, sa katapusan, parehas ra, apan ang higayon nga "molupad" sa 2nd CTE subquery nahimong labi ka taas, ug bisan kung wala kini, klaro nga mas mabasa.

Apan dili kini ang labing makapasubo nga butang. Tungod kay ang developer mihangyo sa pagpili DISTINCT dili alang sa mga piho, apan alang sa tanan nga mga natad sa usa ka higayon mga rekord, dayon ang sub_query field - ang resulta sa subquery - awtomatik nga gilakip didto. Karon, ipatuman DISTINCT, ang database kinahanglan nga ipatuman na dili 10 ka subquery, apan tanan <2 * N> + 10!

2.4: kooperasyon labaw sa tanan!

Mao nga, ang mga nag-develop nagpadayon - wala sila maghasol, tungod kay ang tiggamit klaro nga wala’y igong pasensya sa "pag-adjust" sa rehistro sa hinungdanon nga mga kantidad sa N nga adunay kanunay nga paghinay sa pagdawat sa matag sunod nga "panid".

Hangtud nga ang mga developers gikan sa laing departamento miadto kanila ug gusto nga mogamit sa ingon nga sayon nga paagi para sa iterative search - sa ato pa, nagkuha kami usa ka piraso gikan sa pipila nga sample, gisala kini pinaagi sa dugang nga mga kondisyon, idrowing ang resulta, dayon ang sunod nga piraso (nga sa among kaso nakab-ot pinaagi sa pagdugang N), ug uban pa hangtod mapuno namon ang screen.

Sa kinatibuk-an, sa nakuha nga specimen N nakaabot sa mga kantidad nga hapit 17K, ug sa usa lang ka adlaw labing menos 4K sa maong mga hangyo ang gipatuman "sa kadena". Ang katapusan kanila maisugon nga gi-scan ni 1GB nga panumduman matag pag-uli...

Total

Source: www.habr.com

Mga Antipattern sa PostgreSQL: usa ka istorya sa iterative refinement sa pagpangita pinaagi sa ngalan, o "Pag-optimize pabalik-balik"

0: unsay gusto sa user?

1: limitahan ang buluhaton

1.0: eksternal nga search engine

1.1: "matinud-anon" nga substring

1.2: pangita pinaagi sa text? FTS na nga!

1.3: LIKE gihapon?

1.4: "paghuman gamit ang file"

1.5*: mahimo namong walay file

2: sa unsang paagi ang mga hangyo nahimong maaslom

2.1: ang pagkatawo sa paging

2.2: Gusto nako ang usa ka butang nga lahi

2.3: DISTINCT walay salabotan ug walay kaluoy

2.4: kooperasyon labaw sa tanan!

Total

Idugang sa usa ka comment Отменить ответ