🥇PostgreSQL Query Profiler: unsaon pagpares sa plano ug pangutana

Daghan na ang naggamit explain.tensor.ru - ang among PostgreSQL plan visualization nga serbisyo mahimong wala makahibalo sa usa sa mga superpower niini - ang paghimo sa usa ka lisud basahon nga piraso sa log sa server...

... ngadto sa usa ka nindot nga gidesinyo nga pangutana nga adunay mga pahiwatig sa konteksto alang sa katugbang nga mga node sa plano:

Niini nga transcript sa ikaduhang bahin sa iyang report sa PGConf.Russia 2020 Isulti ko kanimo kung giunsa namon kini nahimo.

Ang transcript sa unang bahin, nga gipahinungod sa kasagaran nga mga problema sa performance sa pangutana ug ang ilang mga solusyon, makita sa artikulo "Mga resipe alang sa masakit nga mga pangutana sa SQL".

Una, magsugod kita sa pagkolor - ug dili na nato koloran ang plano, gikoloran na nato kini, aduna na kita'y nindot ug masabtan, apan usa ka hangyo.

Para sa amon, sa ingon nga wala ma-format nga "panid" ang hangyo nga gikuha gikan sa log tan-awon nga ngil-ad kaayo ug busa dili kombenyente.

Ilabi na kung ang mga developer "nagpapilit" sa lawas sa hangyo sa code (kini, siyempre, usa ka antipattern, apan kini mahitabo) sa usa ka linya. Makalilisang!

Atong idrowing kini nga mas nindot.

Ug kung mahimo naton kini nga matahum, kana mao, i-disassemble ug ibalik ang lawas sa hangyo, nan mahimo naton "ilakip" ang usa ka timaan sa matag butang sa kini nga hangyo - kung unsa ang nahitabo sa katugbang nga punto sa plano.

Pangutana nga syntax tree

Aron mahimo kini, ang hangyo kinahanglan una nga ma-parse.

Kay naa mi ang kinauyokan sa sistema midagan sa NodeJS, unya naghimo kami usa ka module alang niini, mahimo nimo pangitaa kini sa GitHub. Sa tinuud, kini gipalapdan nga "pagbugkos" sa mga internal sa postgreSQL parser mismo. Kana mao, ang gramatika yano nga binary nga gihugpong ug ang mga pagbugkos gihimo niini gikan sa NodeJS. Gikuha namo ang mga module sa ubang mga tawo isip basehan - walay dakong sekreto dinhi.

Gipakaon namo ang lawas sa hangyo isip input sa among function - sa output makakuha kami og parsed syntax tree sa porma sa JSON object.

Karon makadagan na kami sa kini nga kahoy sa atbang nga direksyon ug mag-assemble sa usa ka hangyo nga adunay mga indent, pagkolor, ug pag-format nga gusto namon. Dili, kini dili mapasibo, apan ingon kanamo nga kini mahimong kombenyente.

Mapping query ug plan nodes

Karon atong tan-awon kon unsaon nato paghiusa ang plano nga atong gisusi sa unang lakang ug ang pangutana nga atong gisusi sa ikaduha.

Atong kuhaon ang usa ka yano nga pananglitan - kita adunay usa ka pangutana nga nagmugna og usa ka CTE ug nagbasa gikan niini kaduha. Naghimo siya og ingon nga plano.

CTE

Kung tan-awon nimo kini pag-ayo, hangtod sa bersyon 12 (o magsugod gikan niini sa keyword MATERIALIZED) pagporma Ang CTE usa ka hingpit nga babag alang sa tigplano.

Kini nagpasabot nga kung makakita kita og CTE nga henerasyon sa usa ka dapit sa hangyo ug usa ka node sa usa ka dapit sa plano CTE, unya kini nga mga node siguradong "mag-away" sa usag usa, mahimo naton dayon nga i-combine sila.

Problema sa asterisk: Ang mga CTE mahimong magsalag.

Adunay mga dili maayo nga mga salag, ug bisan ang mga parehas nga ngalan. Pananglitan, mahimo nimo sa sulod CTE A paghimo CTE X, ug sa samang lebel sa sulod CTE B buhata kini pag-usab CTE X:

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

Kung itandi, kinahanglan nimong masabtan kini. Ang pagsabut niini "sa imong mga mata" - bisan ang pagtan-aw sa plano, bisan ang pagtan-aw sa lawas sa hangyo - lisud kaayo. Kung ang imong henerasyon sa CTE komplikado, nagsalag, ug ang mga hangyo dako, nan kini hingpit nga walay panimuot.

Union

Kung kami adunay usa ka keyword sa pangutana UNION [ALL] (operator sa pag-apil sa duha ka mga sample), unya sa plano kini katumbas sa bisan hain sa usa ka node Append, o uban Recursive Union.

Kanang naa sa "ibabaw" sa ibabaw UNION - kini ang una nga kaliwat sa among node, nga "sa ubos" - ang ikaduha. Kung pinaagi UNION kami adunay daghang mga bloke nga "gipilit" sa usa ka higayon, unya Append-adunay usa ra ka node, apan dili kini duha, apan daghang mga bata - sa han-ay nga ilang lakaw, matag usa:

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

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

Problema sa asterisk: sulod sa recursive sampling generation (WITH RECURSIVE) mahimo usab nga labaw pa sa usa UNION. Apan ang katapusan nga bloke lamang pagkahuman sa katapusan ang kanunay nga recursive UNION. Ang tanan sa ibabaw usa ra, apan lahi UNION:

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

Kinahanglan usab nimo nga "ipadayon" ang ingon nga mga pananglitan. Niini nga pananglitan atong makita kana UNION-adunay 3 ka bahin sa among hangyo. Subay niini, usa UNION соответствует Append-node, ug sa uban pa - Recursive Union.

Pagbasa-pagsulat sa datos

Ang tanan gipahimutang, karon nahibal-an na namon kung unsang piraso sa hangyo ang katumbas kung unsang piraso sa plano. Ug niini nga mga piraso dali ra ug natural natong makit-an ang mga butang nga "mabasa".

Gikan sa punto sa panan-aw sa pangutana, wala kami mahibal-an kung kini usa ka lamesa o usa ka CTE, apan sila gitudlo sa parehas nga node RangeVar. Ug sa termino sa "pagkabasa", kini usa usab ka limitado nga hugpong sa 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]

Nahibal-an namon ang istruktura sa plano ug ang pangutana, nahibal-an namon ang mga sulat sa mga bloke, nahibal-an namon ang mga ngalan sa mga butang - naghimo kami usa ka us aka pagtandi.

Pag-usab buluhaton "nga adunay asterisk". Gidawat namo ang hangyo, gipatuman kini, wala kami bisan unsang mga alyas - gibasa lang namo kini kaduha gikan sa samang CTE.

Atong tan-awon ang plano - unsa ang problema? Nganong naa miy alyas? Wala mi nag-order niini. Diin niya makuha ang ingon nga "numero nga numero"?

Gidugang kini sa PostgreSQL mismo. Kinahanglan ra nimo nga masabtan kana alyas nga lang alang kanamo, alang sa mga katuyoan sa pagtandi sa plano, kini wala’y kahulogan, kini gidugang lamang dinhi. Dili nato siya tagdon.

Ang ikaduha buluhaton "nga adunay asterisk": kung nagbasa kami gikan sa usa ka partitioned nga lamesa, nan makakuha kami usa ka node Append o Merge Append, nga naglangkob sa usa ka dako nga gidaghanon sa "mga bata", ug ang matag usa niini mahimong sa usa ka paagi Scan'om gikan sa table-section: Seq Scan, Bitmap Heap Scan o Index Scan. Apan, sa bisan unsa nga kaso, kini nga mga "mga bata" dili komplikado nga mga pangutana - kini kung giunsa kini nga mga node mailhan gikan sa Append sa UNION.

Nakasabut usab kami sa ingon nga mga knot, kolektaha kini "sa usa ka pundok" ug isulti: "ang tanan nga imong nabasa gikan sa megatable naa dinhi ug sa ubos sa punoan".

"Simple" nga datos nga nakadawat sa mga node

Values Scan katumbas sa plano VALUES sa hangyo.

Result usa ka hangyo nga wala FROM ingon SELECT 1. O kung ikaw adunay usa ka tinuyo nga sayup nga ekspresyon sa WHERE-block (dayon makita ang attribute 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 sa parehas nga ngalan.

Apan sa mga nested nga pangutana ang tanan mas komplikado - sa kasubo, dili kini kanunay nga nahimo InitPlan/SubPlan. Usahay sila mahimong ... Join o ... Anti Join, labi na kung nagsulat ka usa ka butang sama sa WHERE NOT EXISTS .... Ug dinhi dili kanunay posible nga mahiusa sila - sa teksto sa plano wala’y mga operator nga katumbas sa mga node sa plano.

Pag-usab buluhaton "nga adunay asterisk": pipila VALUES sa hangyo. Sa kini nga kaso ug sa plano makakuha ka daghang mga node Values Scan.

Ang "numero" nga mga suffix makatabang sa pag-ila kanila gikan sa usag usa - kini gidugang nga eksakto sa pagkasunod-sunod kung diin nakit-an ang mga katugbang. VALUES-mga bloke sa hangyo gikan sa taas hangtod sa ubos.

Pagproseso sa datos

Morag nahan-ay na ang tanan sa among hangyo - ang nahabilin mao na Limit.

Apan dinhi ang tanan yano - ang mga node sama sa Limit, Sort, Aggregate, WindowAgg, Unique "mapa" sa usa-sa-usa ngadto sa katugbang nga mga operator sa hangyo, kon sila didto. Walay "mga bituon" o mga kalisdanan dinhi.

DAGHAN

Ang mga kalisud motungha kung gusto naton maghiusa JOIN tali sa ilang kaugalingon. Dili kini kanunay nga posible, apan posible.

Gikan sa punto sa panglantaw sa parser sa pangutana, kita adunay usa ka node JoinExpr, nga adunay eksaktong duha ka anak - wala ug tuo. Kini, sumala niana, mao ang "ibabaw" sa imong JOIN ug kung unsa ang nahisulat "sa ubos" niini sa hangyo.

Ug gikan sa punto sa panglantaw sa plano, kini mao ang duha ka mga kaliwat sa pipila * Loop/* Join- node. Nested Loop, Hash Anti Join,... - ingon niana.

Gamiton nato ang yano nga lohika: kung kita adunay mga lamesa A ug B nga "nag-uban" sa usag usa sa plano, nan sa hangyo mahimo silang makit-an A-JOIN-B, o B-JOIN-A. Atong sulayan ang paghiusa niini nga paagi, atong sulayan ang pagkombinar sa laing paagi sa palibot, ug uban pa hangtud nga mahurot nato ang maong mga pares.

Atong kuhaon ang atong syntax tree, kuhaa ang atong plano, tan-awa sila... dili parehas!

Atong idrowing pag-usab kini sa porma sa mga graph - oh, murag naa na!

Atong timan-an nga kita adunay mga node nga dungan nga adunay mga anak nga B ug C - dili nato igsapayan kung unsang pagkasunod-sunod. Atong i-combine sila ug ibalik ang hulagway sa node.

Atong tan-awon pag-usab. Karon kami adunay mga node nga adunay mga bata A ug mga pares (B + C) - katugma usab kanila.

Nindot! Mugawas nga kami kining duha JOIN gikan sa hangyo uban sa mga node sa plano malampuson nga gihiusa.

Alaut, kini nga problema dili kanunay masulbad.

Pananglitan, kon sa usa ka hangyo A JOIN B JOIN C, ug sa plano, una sa tanan, ang "gawas" nga mga node A ug C nakonektar. Parehas ra sa "comma" kung magsulat ka A, B.

Apan, sa kadaghanan nga mga kaso, hapit tanan nga mga node mahimong "gihigot" ug mahimo nimong makuha kini nga matang sa profiling sa wala sa oras - sa literal, sama sa Google Chrome kung imong analisahon ang JavaScript code. Imong makita kung unsa ka dugay ang matag linya ug ang matag pahayag nga gikuha sa "pagpatuman."

Ug aron mas sayon alang kanimo ang paggamit niining tanan, naghimo kami og storage archive, diin mahimo nimong i-save ug makit-an sa ulahi ang imong mga plano kauban ang kauban nga mga hangyo o ipaambit ang link sa usa ka tawo.

Kung kinahanglan nimo nga magdala usa ka dili mabasa nga pangutana sa usa ka igo nga porma, gamita atong "normalizer".

Source: www.habr.com

PostgreSQL Query Profiler: unsaon pagpares sa plano ug pangutana