Mga Recipe para sa Sick SQL Query

Mga buwan na nakalipas inihayag namin explain.tensor.ru - pampubliko serbisyo para sa pag-parse at pag-visualize ng mga query plan sa PostgreSQL.

Nagamit mo na ito nang higit sa 6000 beses, ngunit ang isang madaling gamiting tampok na maaaring hindi napansin ay mga pahiwatig sa istruktura, na mukhang ganito:

Makinig sa kanila, at ang iyong mga kahilingan ay “magiging makinis at malasutla.” 🙂

Ngunit seryoso, maraming mga sitwasyon na gumagawa ng isang kahilingan na mabagal at gutom sa mapagkukunan ay tipikal at maaaring makilala ng istraktura at data ng plano.

Sa kasong ito, ang bawat indibidwal na developer ay hindi kailangang maghanap ng isang pagpipilian sa pag-optimize sa kanyang sarili, umaasa lamang sa kanyang karanasan - maaari naming sabihin sa kanya kung ano ang nangyayari dito, kung ano ang maaaring maging dahilan, at kung paano lumapit sa isang solusyon. Iyon ang ginawa namin.

Tingnan natin ang mga kasong ito - kung paano tinukoy ang mga ito at kung anong mga rekomendasyon ang hahantong sa mga ito.

Upang mas mahusay na isawsaw ang iyong sarili sa paksa, maaari mo munang makinig sa kaukulang bloke mula sa ang aking ulat sa PGConf.Russia 2020, at pagkatapos lamang magpatuloy sa isang detalyadong pagsusuri ng bawat halimbawa:

#1: index "undersorting"

Kapag umusbong

Ipakita ang pinakabagong invoice para sa kliyente na "LLC Kolokolchik".

Paano makilala

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

Rekomendasyon

Ginamit ang index palawakin gamit ang mga patlang ng pag-uuri.

Halimbawa:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- отбор по конкретной связи
ORDER BY
  pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;

[tingnan sa explain.tensor.ru]

Mapapansin mo kaagad na higit sa 100 mga tala ang ibinawas mula sa index, na pagkatapos ay lahat ay pinagsunod-sunod, at pagkatapos ay ang isa lamang ang naiwan.

Pagwawasto:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

[tingnan sa explain.tensor.ru]

Kahit na sa gayong primitive na sample - 8.5 beses na mas mabilis at 33 beses na mas kaunting pagbabasa. Ang mas maraming "katotohanan" na mayroon ka para sa bawat halaga, mas malinaw ang epekto fk.

Tandaan ko na ang naturang index ay gagana bilang isang "prefix" na index na hindi mas masahol kaysa dati para sa iba pang mga query na may fk, kung saan ayusin ayon sa pk wala at wala (maaari kang magbasa ng higit pa tungkol dito sa aking artikulo tungkol sa paghahanap ng mga hindi epektibong index). Kasama, ito ay magbibigay ng normal tahasang suporta sa foreign key sa larangang ito.

#2: index intersection (BitmapAnd)

Kapag umusbong

Ipakita ang lahat ng mga kasunduan para sa kliyente na "LLC Kolokolchik", natapos sa ngalan ng "NAO Buttercup".

Paano makilala

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Rekomendasyon

lumikha pinagsama-samang index sa pamamagitan ng mga field mula sa parehong orihinal o palawakin ang isa sa mga umiiral nang may mga field mula sa pangalawa.

Halimbawa:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре

[tingnan sa explain.tensor.ru]

Pagwawasto:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[tingnan sa explain.tensor.ru]

Ang kabayaran dito ay mas maliit, dahil ang Bitmap Heap Scan ay lubos na epektibo sa sarili nitong. Pero kahit na 7 beses na mas mabilis at 2.5 beses na mas kaunting pagbabasa.

#3: Pagsamahin ang mga index (BitmapOr)

Kapag umusbong

Ipakita ang unang 20 pinakamatandang "kami" o hindi nakatalagang mga kahilingan para sa pagproseso, na ang sa iyo ang priyoridad.

Paano makilala

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Rekomendasyon

Gumamit UNION [LAHAT] upang pagsamahin ang mga subquery para sa bawat isa sa OR-block ng mga kundisyon.

Halimbawa:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- свои
  fk_own IS NULL -- ... или "ничьи"
ORDER BY
  pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;

[tingnan sa explain.tensor.ru]

Pagwawasto:

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- сначала "свои" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- потом "ничьи" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо

[tingnan sa explain.tensor.ru]

Sinamantala namin ang katotohanan na ang lahat ng 20 kinakailangang rekord ay agad na natanggap sa unang bloke, kaya ang pangalawa, na may mas "mahal" na Bitmap Heap Scan, ay hindi man lang naisakatuparan - sa huli 22x na mas mabilis, 44x na mas kaunting mga pagbabasa!

Isang mas detalyadong kuwento tungkol sa paraan ng pag-optimize na ito gamit ang mga tiyak na halimbawa mababasa sa mga artikulo Mga PostgreSQL Antipattern: mga nakakapinsalang JOIN at OR и Mga PostgreSQL Antipattern: isang kuwento ng umuulit na pagpipino ng paghahanap ayon sa pangalan, o "Pag-optimize pabalik-balik".

Pangkalahatang bersyon iniutos na pagpili batay sa ilang mga susi (at hindi lamang ang const/NULL pares) ay tinalakay sa artikulo SQL HowTo: pagsusulat ng while loop nang direkta sa query, o "Elementary three-step".

#4: Marami tayong nababasa na hindi kailangan

Kapag umusbong

Bilang isang panuntunan, ito ay nangyayari kapag gusto mong "maglakip ng isa pang filter" sa isang umiiral nang kahilingan.

"At wala kang pareho, ngunit na may mga pindutan ng ina-ng-perlas? " pelikulang "The Diamond Arm"

Halimbawa, ang pagbabago sa gawain sa itaas, ipakita ang unang 20 pinakalumang "kritikal" na kahilingan para sa pagproseso, anuman ang layunin ng mga ito.

Paano makilala

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

Rekomendasyon

Lumikha ng [mas] dalubhasa index na may kondisyon na WHERE o magsama ng mga karagdagang field sa index.

Kung ang kundisyon ng filter ay "static" para sa iyong mga layunin - iyon ay ay hindi nagpapahiwatig ng pagpapalawak listahan ng mga halaga sa hinaharap - mas mahusay na gumamit ng WHERE index. Ang iba't ibang boolean/enum status ay akma sa kategoryang ito.

Kung ang kondisyon ng pagsasala maaaring magkaroon ng iba't ibang kahulugan, pagkatapos ay mas mahusay na palawakin ang index sa mga patlang na ito - tulad ng sa sitwasyon sa BitmapAt sa itaas.

Halimbawa:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

[tingnan sa explain.tensor.ru]

Pagwawasto:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[tingnan sa explain.tensor.ru]

Tulad ng nakikita mo, ang pag-filter ay ganap na nawala mula sa plano, at ang kahilingan ay naging 5 beses na mas mabilis.

#5: kalat-kalat na mesa

Kapag umusbong

Iba't ibang mga pagtatangka upang lumikha ng iyong sariling pila sa pagproseso ng gawain, kapag ang isang malaking bilang ng mga pag-update/pagtanggal ng mga tala sa talahanayan ay humantong sa isang sitwasyon ng isang malaking bilang ng mga "patay" na mga tala.

Paano makilala

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

Rekomendasyon

Isagawa nang manu-mano nang regular VACUUM [FULL] o makamit ang sapat na madalas na pagsasanay autovacuum sa pamamagitan ng pag-fine-tune ng mga parameter nito, kabilang ang para sa isang tiyak na talahanayan.

Sa karamihan ng mga kaso, ang mga naturang problema ay sanhi ng hindi magandang komposisyon ng query kapag tumatawag mula sa lohika ng negosyo tulad ng mga tinalakay sa PostgreSQL Antipatterns: pakikipaglaban sa sangkawan ng "patay".

Ngunit kailangan mong maunawaan na kahit na ang VACUUM FULL ay maaaring hindi palaging makakatulong. Para sa mga ganitong kaso, sulit na pamilyar ka sa algorithm mula sa artikulo DBA: kapag nabigo ang VACUUM, nililinis namin nang manu-mano ang mesa.

#6: Pagbasa mula sa "gitna" ng index

Kapag umusbong

Mukhang nagbasa kami ng kaunti, at na-index ang lahat, at hindi namin na-filter ang sinuman nang labis - ngunit nagbabasa pa rin kami ng mas maraming mga pahina kaysa sa gusto namin.

Paano makilala

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

Rekomendasyon

Tingnang mabuti ang istraktura ng index na ginamit at ang mga pangunahing field na tinukoy sa query - malamang bahagi ng index ay hindi tinukoy. Malamang na kailangan mong lumikha ng isang katulad na index, ngunit walang mga patlang ng prefix o matutong ulitin ang kanilang mga halaga.

Halimbawa:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;

[tingnan sa explain.tensor.ru]

Mukhang maayos ang lahat, kahit na ayon sa index, ngunit kahit papaano ay kahina-hinala - para sa bawat isa sa 20 talaang nabasa, kinailangan naming ibawas ang 4 na pahina ng data, 32KB bawat tala - hindi ba matapang iyon? At ang pangalan ng index tbl_fk_org_fk_cli_idx nakakapukaw ng pag-iisip.

Pagwawasto:

CREATE INDEX ON tbl(fk_cli);

[tingnan sa explain.tensor.ru]

Bigla - 10 beses na mas mabilis, at 4 na beses na mas mababa sa pagbabasa!

Ang iba pang mga halimbawa ng mga sitwasyon ng hindi epektibong paggamit ng mga index ay makikita sa artikulo DBA: paghahanap ng mga walang kwentang index.

#7: CTE × CTE

Kapag umusbong

Sa kahilingan nakapuntos ng "taba" CTE mula sa iba't ibang mga talahanayan, at pagkatapos ay nagpasya na gawin ito sa pagitan nila JOIN.

May kaugnayan ang kaso para sa mga bersyon sa ibaba ng v12 o mga kahilingan na may WITH MATERIALIZED.

Paano makilala

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

Rekomendasyon

Maingat na pag-aralan ang kahilingan - at Kailangan ba dito ang mga CTE?? Kung oo, kung gayon ilapat ang "diksyonaryo" sa hstore/json ayon sa modelong inilarawan sa PostgreSQL Antipatterns: pindutin natin ang mabigat na JOIN gamit ang isang diksyunaryo.

#8: swap sa disk (nakasulat ang temp)

Kapag umusbong

Ang isang beses na pagpoproseso (pag-uuri o pag-uuri) ng isang malaking bilang ng mga tala ay hindi akma sa memorya na inilaan para dito.

Paano makilala

-> *
   && temp written > 0

Rekomendasyon

Kung ang halaga ng memorya na ginamit ng operasyon ay hindi lubos na lumampas sa tinukoy na halaga ng parameter work_mem, ito ay nagkakahalaga ng pagwawasto nito. Pwede ka agad sa config para sa lahat, o kaya mo SET [LOCAL] para sa isang partikular na kahilingan/transaksyon.

Halimbawa:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[tingnan sa explain.tensor.ru]

Pagwawasto:

SET work_mem = '128MB'; -- перед выполнением запроса

[tingnan sa explain.tensor.ru]

Para sa mga malinaw na kadahilanan, kung memorya lamang ang ginagamit at hindi disk, kung gayon ang query ay isasagawa nang mas mabilis. Kasabay nito, ang bahagi ng pag-load mula sa HDD ay tinanggal din.

Ngunit kailangan mong maunawaan na hindi ka palaging makakapaglaan ng maraming at maraming memorya - hindi ito magiging sapat para sa lahat.

#9: walang kaugnayang istatistika

Kapag umusbong

Nagbuhos sila ng marami sa database nang sabay-sabay, ngunit walang oras upang itaboy ito ANALYZE.

Paano makilala

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

Rekomendasyon

Isagawa ito ANALYZE.

Ang sitwasyong ito ay inilarawan nang mas detalyado sa Mga PostgreSQL Antipattern: ang mga istatistika ay lahat.

#10: “may nangyaring mali”

Kapag umusbong

May paghihintay para sa isang lock na ipinataw ng isang nakikipagkumpitensyang kahilingan, o walang sapat na mapagkukunan ng CPU/hypervisor hardware.

Paano makilala

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

Rekomendasyon

Gumamit ng panlabas systemang pang-monitor server para sa pagharang o abnormal na pagkonsumo ng mapagkukunan. Napag-usapan na namin ang tungkol sa aming bersyon ng pag-aayos ng prosesong ito para sa daan-daang mga server dito и dito.

Pinagmulan: www.habr.com