Mga PostgreSQL Antipattern: isang kuwento ng umuulit na pagpipino ng paghahanap ayon sa pangalan, o "Pag-optimize pabalik-balik"

Libu-libong mga tagapamahala mula sa mga tanggapan ng pagbebenta sa buong bansa ang nagtala aming CRM system sampu-sampung libong mga contact araw-araw — mga katotohanan ng komunikasyon sa mga potensyal o umiiral na mga kliyente. At para dito, kailangan mo munang makahanap ng isang kliyente, at mas mabuti nang napakabilis. At ito ay madalas na nangyayari sa pamamagitan ng pangalan.

Samakatuwid, hindi nakakagulat na, muling sinusuri ang "mabibigat" na mga query sa isa sa mga pinaka-load na database - ang sarili nating VLSI corporate account, nakita ko "sa itaas" humiling ng "mabilis" na paghahanap ayon sa pangalan para sa mga kard ng organisasyon.

Bukod dito, ang karagdagang pagsisiyasat ay nagsiwalat ng isang kawili-wiling halimbawa unang pag-optimize at pagkatapos ay pagkasira ng pagganap kahilingan na may sunud-sunod na pagpipino ng ilang mga koponan, na ang bawat isa ay kumilos lamang na may pinakamahusay na layunin.

0: ano ang gusto ng gumagamit?

[KDPV kaya]

Ano ang karaniwang ibig sabihin ng isang user kapag pinag-uusapan nila ang isang "mabilis" na paghahanap ayon sa pangalan? Halos hindi ito naging "tapat" na paghahanap para sa isang substring na tulad ... LIKE '%роза%' - dahil kung gayon ang resulta ay kinabibilangan ng hindi lamang 'Розалия' и 'Магазин Роза'pero 'Гроза' at kahit 'Дом Деда Мороза'.

Ipinapalagay ng gumagamit ang pang-araw-araw na antas na ibibigay mo sa kanya paghahanap sa pamamagitan ng simula ng salita sa pamagat at gawin itong mas may kaugnayan doon nagsisimula sa pumasok. At gagawin mo ito halos agad-agad - para sa interlinear input.

1: limitahan ang gawain

At higit pa rito, hindi partikular na papasok ang isang tao 'роз магаз', upang kailangan mong hanapin ang bawat salita sa pamamagitan ng prefix. Hindi, mas madali para sa isang user na tumugon sa isang mabilis na pahiwatig para sa huling salita kaysa sa sadyang "underspecify" ang mga nauna - tingnan kung paano ito pinangangasiwaan ng anumang search engine.

Sa pangkalahatan, wasto ang pagbabalangkas ng mga kinakailangan para sa problema ay higit sa kalahati ng solusyon. Minsan maingat na pagsusuri ng kaso ng paggamit maaaring makabuluhang makaimpluwensya sa resulta.

Ano ang ginagawa ng abstract developer?

1.0: panlabas na search engine

Oh, mahirap ang paghahanap, wala akong gustong gawin - ibigay natin ito sa mga devops! Hayaan silang mag-deploy ng isang search engine sa labas ng database: Sphinx, ElasticSearch,...

Isang opsyon sa pagtatrabaho, kahit na labor-intensive sa mga tuntunin ng pag-synchronize at bilis ng mga pagbabago. Ngunit hindi sa aming kaso, dahil ang paghahanap ay isinasagawa para sa bawat kliyente sa loob lamang ng balangkas ng data ng kanyang account. At ang data ay may medyo mataas na pagkakaiba-iba - at kung ang tagapamahala ay pumasok na ngayon sa card 'Магазин Роза', pagkatapos pagkatapos ng 5-10 segundo ay maaaring maalala na niya na nakalimutan niyang ipahiwatig ang kanyang email doon at gusto niyang hanapin ito at itama ito.

Samakatuwid - tayo maghanap "direkta sa database". Sa kabutihang palad, pinapayagan tayo ng PostgreSQL na gawin ito, at hindi lamang isang opsyon - titingnan natin ang mga ito.

1.1: "tapat" na substring

Kumapit kami sa salitang "substring". Ngunit para sa paghahanap ng index sa pamamagitan ng substring (at kahit sa pamamagitan ng mga regular na expression!) mayroong isang mahusay module pg_trgm! Pagkatapos lamang ito ay kinakailangan upang ayusin nang tama.

Subukan nating kunin ang sumusunod na plato upang gawing simple ang modelo:

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

Nag-a-upload kami ng 7.8 milyong talaan ng mga tunay na organisasyon doon at ini-index ang mga ito:

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

Hanapin natin ang unang 10 record para sa interlinear na paghahanap:

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

[tingnan sa explain.tensor.ru]

Well, iyon ay... 26ms, 31MB magbasa ng data at higit sa 1.7K na-filter na tala - para sa 10 na hinanap. Ang mga gastos sa overhead ay masyadong mataas, hindi ba may mas mahusay?

1.2: maghanap sa pamamagitan ng text? Ito ay FTS!

Sa katunayan, ang PostgreSQL ay nagbibigay ng napakalakas buong text search engine (Buong Paghahanap ng Teksto), kabilang ang kakayahang mag-prefix ng paghahanap. Isang mahusay na opsyon, hindi mo na kailangang mag-install ng mga extension! Subukan Natin:

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;

[tingnan sa explain.tensor.ru]

Narito ang parallelization ng query execution ay nakatulong sa amin ng kaunti, pagputol ng oras sa kalahati sa 11ms. At kailangan naming magbasa ng 1.5 beses na mas kaunti - sa kabuuan 20MB. Ngunit dito, mas kaunti ang mas mahusay, dahil mas malaki ang volume na binabasa natin, mas mataas ang pagkakataong makakuha ng cache miss, at bawat karagdagang pahina ng data na nabasa mula sa disk ay isang potensyal na "preno" para sa kahilingan.

1.3: LIKE pa rin?

Ang nakaraang kahilingan ay mabuti para sa lahat, ngunit kung hinila mo ito ng isang daang libong beses sa isang araw, ito ay darating 2TB basahin ang data. Sa pinakamagandang kaso, mula sa memorya, ngunit kung hindi ka mapalad, pagkatapos ay mula sa disk. Kaya't subukan nating gawin itong mas maliit.

Tandaan natin kung ano ang gustong makita ng gumagamit una "na nagsisimula sa...". Kaya ito ay nasa pinakadalisay nitong anyo paghahanap ng prefix sa tulong text_pattern_ops! At kung kami ay "walang sapat" hanggang sa 10 talaan na aming hinahanap, pagkatapos ay kailangan naming tapusin ang pagbabasa sa mga ito gamit ang paghahanap sa FTS:

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

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

[tingnan sa explain.tensor.ru]

Napakahusay na pagganap - kabuuan 0.05ms at higit pa sa 100KB basahin mo! Kami lang ang nakalimutan ayusin ayon sa pangalanupang ang user ay hindi mawala sa mga resulta:

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

[tingnan sa explain.tensor.ru]

Naku, may hindi na ganoon kaganda - parang may index, ngunit ang pag-uuri ay lumipas dito... Ito, siyempre, ay maraming beses na mas epektibo kaysa sa nakaraang opsyon, ngunit...

1.4: "tapusin gamit ang isang file"

Ngunit mayroong isang index na nagbibigay-daan sa iyo upang maghanap ayon sa saklaw at gumagamit pa rin ng normal na pag-uuri - regular na btree!

CREATE INDEX ON firms(lower(name));

Ang kahilingan lamang para dito ang kailangang "manu-manong kolektahin":

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

[tingnan sa explain.tensor.ru]

Mahusay - gumagana ang pag-uuri, at ang pagkonsumo ng mapagkukunan ay nananatiling "microscopic", libu-libong beses na mas epektibo kaysa sa "purong" FTS! Ang natitira na lang ay pagsama-samahin ito sa isang kahilingan:

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

Tandaan na ang pangalawang subquery ay naisakatuparan lamang kung ang una ay bumalik nang mas mababa kaysa sa inaasahan ang huli LIMIT bilang ng mga linya. Pinag-uusapan ko ang paraang ito ng pag-optimize ng query nagsulat na dati.

Kaya oo, mayroon na kaming parehong btree at gin sa mesa, ngunit ayon sa istatistika, lumalabas iyon wala pang 10% ng mga kahilingan ang nakakaabot sa pagpapatupad ng pangalawang bloke. Iyon ay, sa mga tipikal na limitasyon na kilala nang maaga para sa gawain, nagawa naming bawasan ang kabuuang pagkonsumo ng mga mapagkukunan ng server ng halos isang libong beses!

1.5*: magagawa natin nang walang file

Mas mataas LIKE Pinigilan kaming gumamit ng maling pag-uuri. Ngunit maaari itong "itakda sa tamang landas" sa pamamagitan ng pagtukoy sa USING operator:

Sa pamamagitan ng default ito ay ipinapalagay ASC. Bilang karagdagan, maaari mong tukuyin ang pangalan ng isang partikular na operator ng pag-uuri sa isang sugnay USING. Ang operator ng pag-uuri ay dapat na miyembro ng mas mababa o mas malaki kaysa sa ilang pamilya ng mga operator ng B-tree. ASC karaniwang katumbas USING < и DESC karaniwang katumbas USING >.

Sa aming kaso, ang "mas mababa" ay ~<~:

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

[tingnan sa explain.tensor.ru]

2: kung paano nagiging maasim ang mga kahilingan

Ngayon ay iniiwan namin ang aming kahilingan na "simmer" sa loob ng anim na buwan o isang taon, at nagulat kaming makita itong muli "sa tuktok" na may mga tagapagpahiwatig ng kabuuang pang-araw-araw na "pumping" ng memorya (buffer shared hit) sa 5.5TB - iyon ay, higit pa kaysa sa orihinal.

Hindi, siyempre, ang aming negosyo ay lumago at ang aming trabaho ay tumaas, ngunit hindi sa parehong halaga! Nangangahulugan ito na may hindi kapani-paniwala dito - alamin natin ito.

2.1: ang kapanganakan ng paging

Sa isang punto, gusto ng isa pang development team na gawing posible na "tumalon" mula sa isang mabilis na paghahanap ng subscript patungo sa registry na may pareho, ngunit pinalawak na mga resulta. Ano ang isang registry na walang page navigation? Ating sirain ito!

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

Ngayon ay posible nang ipakita ang registry ng mga resulta ng paghahanap na may "page-by-page" na naglo-load nang walang anumang stress para sa developer.

Siyempre, sa katunayan, para sa bawat kasunod na pahina ng data ay parami nang parami ang binabasa (lahat mula sa nakaraang oras, na itatapon namin, kasama ang kinakailangang "buntot") - iyon ay, ito ay isang malinaw na antipattern. Ngunit magiging mas tama na simulan ang paghahanap sa susunod na pag-ulit mula sa susi na nakaimbak sa interface, ngunit tungkol doon sa ibang pagkakataon.

2.2: Gusto ko ng kakaiba

Sa ilang mga punto gusto ng developer pag-iba-ibahin ang resultang sample gamit ang data mula sa isa pang talahanayan, kung saan ang buong nakaraang kahilingan ay ipinadala sa CTE:

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

At kahit na, hindi masama, dahil ang subquery ay sinusuri lamang para sa 10 ibinalik na mga tala, kung hindi ...

2.3: DISTINCT ay walang katuturan at walang awa

Sa isang lugar sa proseso ng naturang ebolusyon mula sa 2nd subquery naligaw NOT LIKE kalagayan. Malinaw na pagkatapos nito UNION ALL nagsimulang bumalik ilang mga entry dalawang beses - unang natagpuan sa simula ng linya, at pagkatapos ay muli - sa simula ng unang salita ng linyang ito. Sa limitasyon, maaaring tumugma ang lahat ng record ng 2nd subquery sa mga record ng una.

Ano ang ginagawa ng isang developer sa halip na hanapin ang dahilan?.. Walang tanong!

• doble ang laki orihinal na mga sample
• ilapat ang DISTINCTupang makakuha lamang ng mga iisang pagkakataon ng bawat 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>;

Iyon ay, malinaw na ang resulta, sa huli, ay eksaktong pareho, ngunit ang pagkakataon na "lumipad" sa 2nd CTE subquery ay naging mas mataas, at kahit na wala ito, malinaw na mas nababasa.

Ngunit hindi ito ang pinakamalungkot na bagay. Dahil hiniling ng developer na pumili DISTINCT hindi para sa mga partikular, ngunit para sa lahat ng mga patlang nang sabay-sabay records, pagkatapos ay ang sub_query field — ang resulta ng subquery — ay awtomatikong isinama doon. Ngayon, upang maisagawa DISTINCT, ang database ay kailangang isagawa na hindi 10 subquery, ngunit lahat <2 * N> + 10!

2.4: kooperasyon higit sa lahat!

Kaya, nabuhay ang mga developer - hindi sila nag-abala, dahil ang gumagamit ay malinaw na walang sapat na pasensya upang "ayusin" ang pagpapatala sa makabuluhang mga halaga ng N na may talamak na pagbagal sa pagtanggap ng bawat kasunod na "pahina".

Hanggang sa dumating sa kanila ang mga developer mula sa ibang departamento at gustong gumamit ng ganitong maginhawang paraan para sa umuulit na paghahanap - iyon ay, kumuha kami ng isang piraso mula sa ilang sample, i-filter ito sa pamamagitan ng karagdagang mga kondisyon, iguhit ang resulta, pagkatapos ay ang susunod na piraso (na sa aming kaso ay nakamit sa pamamagitan ng pagtaas ng N), at iba pa hanggang sa mapunan namin ang screen.

Sa pangkalahatan, sa nahuli na ispesimen Naabot ng N ang mga halaga na halos 17K, at sa loob lamang ng isang araw hindi bababa sa 4K ng mga naturang kahilingan ang naisakatuparan "sa kahabaan ng kadena". Ang huli sa kanila ay matapang na na-scan ni 1GB ng memorya bawat pag-ulit...

Sa kabuuan

Pinagmulan: www.habr.com