PostgreSQL Query Profiler: kung paano itugma ang plano at query

Marami na ang gumagamit explain.tensor.ru - ang aming PostgreSQL plan visualization service ay maaaring hindi alam ang isa sa mga superpower nito - ang paggawa ng mahirap basahin na piraso ng server log...

... sa isang query na may magandang disenyo na may mga pahiwatig sa konteksto para sa kaukulang mga node ng plano:

Sa transcript na ito ng ikalawang bahagi ng kanyang ulat sa PGConf.Russia 2020 Sasabihin ko sa iyo kung paano namin nagawa ito.

Ang transcript ng unang bahagi, na nakatuon sa mga karaniwang problema sa pagganap ng query at ang mga solusyon sa mga ito, ay makikita sa artikulo. "Mga recipe para sa may sakit na mga query sa SQL".

Una, simulan natin ang kulay - at hindi na natin kukulayan ang plano, nalagyan na natin ng kulay, mayroon na tayong maganda at naiintindihan, ngunit isang kahilingan.

Tila sa amin na may tulad na isang hindi naka-format na "sheet" ang kahilingan na nakuha mula sa log ay mukhang napakapangit at samakatuwid ay hindi maginhawa.


Lalo na kapag ang mga developer ay "idikit" ang katawan ng kahilingan sa code (ito ay, siyempre, isang antipattern, ngunit nangyayari ito) sa isang linya. Nakakakilabot!

Iguhit natin ito kahit papaano nang mas maganda.


At kung maaari nating iguhit ito nang maganda, iyon ay, i-disassemble at ibalik ang katawan ng kahilingan, pagkatapos ay maaari nating "ilakip" ang isang pahiwatig sa bawat bagay ng kahilingang ito - kung ano ang nangyari sa kaukulang punto sa plano.

Query syntax tree

Upang magawa ito, dapat munang ma-parse ang kahilingan.


Dahil mayroon kami ang core ng system ay tumatakbo sa NodeJS, tapos gumawa kami ng module para dito, kaya mo hanapin ito sa GitHub. Sa katunayan, ang mga ito ay pinalawak na "mga binding" sa mga panloob ng PostgreSQL parser mismo. Ibig sabihin, binary compiled lang ang grammar at ang mga binding ay ginawa dito mula sa NodeJS. Kinuha namin ang mga module ng ibang tao bilang batayan - walang malaking sikreto dito.

Pinapakain namin ang katawan ng kahilingan bilang input sa aming function - sa output nakakakuha kami ng parsed syntax tree sa anyo ng isang JSON object.


Ngayon ay maaari na tayong tumakbo sa punong ito sa kabaligtaran ng direksyon at mag-ipon ng isang kahilingan na may mga indent, pangkulay, at pag-format na gusto natin. Hindi, hindi ito napapasadya, ngunit tila sa amin ay magiging maginhawa ito.

Query sa pagmamapa at mga node ng plano

Ngayon tingnan natin kung paano natin pagsasama-samahin ang plano na sinuri natin sa unang hakbang at ang query na sinuri natin sa pangalawa.

Kumuha tayo ng isang simpleng halimbawa - mayroon tayong query na bumubuo ng CTE at nagbabasa mula dito nang dalawang beses. Siya ang gumagawa ng ganoong plano.

CTE

Kung titingnan mo itong mabuti, hanggang sa bersyon 12 (o simula dito sa keyword MATERIALIZED) pagbuo Ang CTE ay isang ganap na hadlang para sa tagaplano.


Nangangahulugan ito na kung makakita tayo ng henerasyon ng CTE sa isang lugar sa kahilingan at isang node sa isang lugar sa plano CTE, kung gayon ang mga node na ito ay tiyak na "mag-aaway" sa isa't isa, maaari nating agad na pagsamahin ang mga ito.

Problema sa isang asterisk: Maaaring ma-nest ang mga CTE.

May mga napakahina ang nested, at kahit na may parehong pangalan. Halimbawa, maaari ka sa loob CTE A gumawa CTE X, at sa parehong antas sa loob CTE B gawin mo ulit CTE X:

WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...

Kapag naghahambing, dapat mong maunawaan ito. Ang pag-unawa dito "sa iyong mga mata" - kahit na makita ang plano, kahit na makita ang katawan ng kahilingan - ay napakahirap. Kung ang iyong henerasyon ng CTE ay kumplikado, naka-nest, at ang mga kahilingan ay malaki, kung gayon ito ay ganap na walang malay.

Unyon

Kung mayroon tayong keyword sa query UNION [ALL] (operator ng pagsali sa dalawang sample), pagkatapos ay sa plano ito ay tumutugma sa alinman sa isang node Append, o ilan Recursive Union.


Iyon ay "sa itaas" sa itaas UNION - ito ang unang inapo ng aming node, na "sa ibaba" - ang pangalawa. Kung sa pamamagitan ng UNION mayroon kaming ilang mga bloke na "nakadikit" nang sabay-sabay, pagkatapos Append-magkakaroon pa rin ng isang node, ngunit hindi ito magkakaroon ng dalawa, ngunit maraming mga bata - sa pagkakasunud-sunod na kanilang pupuntahan, ayon sa pagkakabanggit:

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

Append
  -> ... #1
  -> ... #2
  -> ... #3

Problema sa isang asterisk: sa loob ng recursive sampling generation (WITH RECURSIVE) ay maaari ding higit sa isa UNION. Ngunit ang pinakahuling bloke lamang pagkatapos ng huli ang palaging recursive UNION. Lahat ng nasa itaas ay iisa, ngunit magkaiba UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

Kailangan mo ring "iwasan" ang mga ganitong halimbawa. Sa halimbawang ito makikita natin iyan UNION-mayroong 3 segment sa aming kahilingan. Alinsunod dito, isa UNION tumutugma sa Append-node, at sa iba pa - Recursive Union.

Read-write data

Ang lahat ay inilatag, ngayon alam namin kung aling piraso ng kahilingan ang tumutugma sa kung aling bahagi ng plano. At sa mga pirasong ito madali at natural nating mahahanap ang mga bagay na “nababasa”.

Mula sa punto ng view ng query, hindi namin alam kung ito ay isang talahanayan o isang CTE, ngunit ang mga ito ay itinalaga ng parehong node RangeVar. At sa mga tuntunin ng "kakayahang mabasa", ito ay isang medyo limitadong hanay ng mga node:

• Seq Scan on [tbl]
• Bitmap Heap Scan on [tbl]
• Index [Only] Scan [Backward] using [idx] on [tbl]
• CTE Scan on [cte]
• Insert/Update/Delete on [tbl]

Alam namin ang istraktura ng plano at ang query, alam namin ang mga sulat ng mga bloke, alam namin ang mga pangalan ng mga bagay - gumawa kami ng isa-sa-isang paghahambing.


muli gawain "na may asterisk". Kinukuha namin ang kahilingan, isinasagawa ito, wala kaming anumang mga alias - binasa lang namin ito nang dalawang beses mula sa parehong CTE.


Tinitingnan namin ang plano - ano ang problema? Bakit tayo nagkaroon ng alyas? Hindi kami nag-order. Saan siya kumukuha ng ganoong "number number"?

Idinagdag ito mismo ng PostgreSQL. Kailangan mo lang intindihin yun parang alias lang para sa amin, para sa mga layunin ng paghahambing sa plano, ito ay walang anumang kahulugan, ito ay idinagdag lamang dito. Wag na natin siyang pansinin.

Ang pangalawang gawain "na may asterisk": kung kami ay nagbabasa mula sa isang partitioned table, pagkatapos ay makakakuha kami ng isang node Append o Merge Append, na bubuuin ng malaking bilang ng "mga bata", at bawat isa ay magiging kahit papaano Scan'om mula sa table-section: Seq Scan, Bitmap Heap Scan o Index Scan. Ngunit, sa anumang kaso, ang "mga bata" na ito ay hindi magiging kumplikadong mga tanong - ito ay kung paano ang mga node na ito ay maaaring makilala mula sa Append sa UNION.


Naiintindihan din namin ang gayong mga buhol, kolektahin ang mga ito "sa isang tumpok" at sabihin: "lahat ng nabasa mo mula sa megatable ay nandito at pababa ng puno".

"Simple" na data sa pagtanggap ng mga node

Values Scan tumutugma sa plano VALUES sa kahilingan.

Result ay isang kahilingan na wala FROM tulad ng SELECT 1. O kapag mayroon kang sinasadyang maling pagpapahayag WHERE-block (pagkatapos ay lilitaw ang katangian One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1

Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false

Function Scan "mapa" sa mga SRF na may parehong pangalan.

Ngunit sa mga nested query ang lahat ay mas kumplikado - sa kasamaang-palad, hindi sila palaging nagiging InitPlan/SubPlan. Minsan sila ay nagiging ... Join o ... Anti Join, lalo na kapag nagsusulat ka ng isang bagay tulad ng WHERE NOT EXISTS .... At dito hindi laging posible na pagsamahin ang mga ito - sa teksto ng plano ay walang mga operator na naaayon sa mga node ng plano.

muli gawain "na may asterisk": ilan VALUES sa kahilingan. Sa kasong ito at sa plano makakakuha ka ng ilang mga node Values Scan.


Ang mga "numbered" na suffix ay makakatulong upang makilala ang mga ito mula sa isa't isa - ang mga ito ay idinagdag nang eksakto sa pagkakasunud-sunod kung saan matatagpuan ang mga katumbas. VALUES-block kasama ang kahilingan mula sa itaas hanggang sa ibaba.

Pagproseso ng data

Mukhang naayos na ang lahat sa aming kahilingan - ang natitira na lang Limit.


Ngunit narito ang lahat ay simple - tulad ng mga node Limit, Sort, Aggregate, WindowAgg, Unique "mapa" isa-sa-isa sa mga kaukulang operator sa kahilingan, kung naroon sila. Walang mga "bituin" o kahirapan dito.

SUMALI

Ang mga paghihirap ay lumalabas kapag nais nating pagsamahin JOIN sa pagitan nila. Ito ay hindi palaging posible, ngunit ito ay posible.


Mula sa pananaw ng query parser, mayroon kaming node JoinExpr, na may eksaktong dalawang anak - kaliwa at kanan. Ito, ayon dito, ay kung ano ang "sa itaas" ng iyong SUMALI at kung ano ang nakasulat sa "ibaba" nito sa kahilingan.

At mula sa punto ng view ng plano, ito ay dalawang inapo ng ilan * Loop/* Join-node. Nested Loop, Hash Anti Join,... - may ganyan.

Gumamit tayo ng simpleng lohika: kung mayroon tayong mga talahanayan A at B na "magsasama" sa isa't isa sa plano, kung gayon sa kahilingan maaari silang matatagpuan A-JOIN-BO B-JOIN-A. Subukan nating pagsamahin ang ganitong paraan, subukan nating pagsamahin ang iba pang paraan sa paligid, at iba pa hanggang sa maubusan tayo ng gayong mga pares.

Kunin natin ang ating syntax tree, kunin ang ating plano, tingnan ang mga ito... hindi katulad!


I-redraw natin ito sa anyo ng mga graph - naku, mukhang bagay na!


Tandaan natin na mayroon kaming mga node na magkasabay na may mga anak na B at C - wala kaming pakialam sa kung anong pagkakasunud-sunod. Pagsamahin natin ang mga ito at ibalik ang larawan ng node.


Tingnan natin muli. Ngayon mayroon kaming mga node na may mga bata A at mga pares (B + C) - tugma din sa kanila.


Malaki! Kami pala itong dalawa JOIN mula sa kahilingan sa mga node ng plano ay matagumpay na pinagsama.

Sa kasamaang palad, ang problemang ito ay hindi palaging nalutas.


Halimbawa, kung sa isang kahilingan A JOIN B JOIN C, at sa plano, una sa lahat, ang "panlabas" na mga node na A at C ay konektado. Ngunit walang ganoong operator sa kahilingan, wala kaming dapat i-highlight, walang mailakip na pahiwatig. Ganun din sa "comma" kapag nagsusulat ka A, B.

Ngunit, sa karamihan ng mga kaso, halos lahat ng mga node ay maaaring "matali" at maaari kang makakuha ng ganitong uri ng pag-profile sa kaliwa sa oras - literal, tulad ng sa Google Chrome kapag sinuri mo ang JavaScript code. Makikita mo kung gaano katagal ang bawat linya at bawat pahayag na "isagawa."


At para gawing mas maginhawa para sa iyo na gamitin ang lahat ng ito, gumawa kami ng storage archive, kung saan maaari mong i-save at mahanap sa ibang pagkakataon ang iyong mga plano kasama ng mga nauugnay na kahilingan o ibahagi ang link sa isang tao.

Kung kailangan mo lang magdala ng hindi nababasang query sa isang sapat na anyo, gamitin ang aming "normalizer".

Pinagmulan: www.habr.com