PostgreSQL Query Profiler: maitiro ekufananidza chirongwa uye mubvunzo

Vazhinji vari kutoshandisa explain.tensor.ru - yedu PostgreSQL hurongwa hwekuona sevhisi inogona kunge isingazive imwe yemasimba ayo makuru - kushandura yakaoma-kuverenga-chidimbu cheiyo server server...

... mumubvunzo wakagadzirwa zvine mutsindo une matinji ezvirongwa zvinowirirana node:

Muchinyorwa ichi chechikamu chechipiri chake taura paPGConf.Russia 2020 Ndichakuudza kuti takakwanisa sei kuita izvi.

Chinyorwa chechikamu chekutanga, chakatsaurirwa kune akajairwa mubvunzo kuita matambudziko uye mhinduro dzawo, anogona kuwanikwa muchinyorwa. "Mabikirwo emibvunzo yeSQL inorwara".

Kutanga, ngatitangei kupenda - uye isu hatichazopendera chirongwa, isu tatochipenda, isu tatova nacho chakanaka uye chinonzwisisika, asi chikumbiro.

Zvaiita kwatiri kuti ne "shiti" rakadaro risina kurongeka chikumbiro chakadhonzwa kubva mugogi chinotaridzika chakashata uye saka chinokanganisa.


Kunyanya apo vanogadzira "glue" mutumbi wechikumbiro mukodhi (izvi, zvechokwadi, antipattern, asi zvinoitika) mumutsara mumwe. Zvinotyisa!

Ngatidhirowei izvi neimwe nzira zvinoyevedza.


Uye kana tikakwanisa kudhirowa izvi zvakanaka, ndiko kuti, kuparadzanisa uye kudzorera pamwechete muviri wechikumbiro, saka isu tinokwanisa "kuisa" zano kune chimwe nechimwe chinhu chechikumbiro ichi - zvakaitika panguva inoenderana muchirongwa.

Mubvunzo syntax muti

Kuti uite izvi, chikumbiro chinofanira kutanga chapatsanurwa.


Nokuti tine musimboti weiyo system inomhanya paNodeJS, takabva tagadzira module yacho, unogona zviwane paGitHub. Muchokwadi, izvi zvinowedzerwa "zvisungo" kune vemukati vePostgreSQL parser pachayo. Kureva kuti, girama inongori mabhinari akaunganidzwa uye zvisungo zvinogadzirwa kwairi kubva kuNodeJS. Takatora mamodule evamwe vanhu sehwaro - hapana chakavanzika chikuru pano.

Isu tinodyisa muviri wechikumbiro sekuisa kune yedu basa - pane zvakabuda tinowana yakakamurwa syntax muti muchimiro chechinhu cheJSON.


Iye zvino isu tinogona kumhanya nemumuti uyu takananga kune yakatarisana uye tounganidza chikumbiro nemaindents, coloring, uye fomati yatinoda. Aiwa, izvi hazvigoneke, asi isu zvaiita sekuti izvi zvingava nyore.

Mepu mubvunzo uye kuronga node

Zvino ngationei kuti tingabatanidza sei hurongwa hwatakaongorora padanho rekutanga nemubvunzo watakaongorora mune yechipiri.

Ngatitorei muenzaniso wakapfava - isu tine mubvunzo unogadzira CTE uye unoverenga kubva mairi kaviri. Anogadzira chirongwa chakadaro.

CTE

Kana iwe ukanyatsozvitarisa, kusvika kune vhezheni 12 (kana kutanga kubva pairi nekiyi izwi MATERIALIZED) kuumbwa CTE imhedziso chipinganidzo chemurongi.


Izvi zvinoreva kuti kana tikaona chizvarwa cheCTE pane imwe nzvimbo mukukumbira uye node pane imwe nzvimbo muurongwa CTE, ipapo node idzi zvechokwadi "kurwa" kune mumwe nemumwe, tinogona kuzvibatanidza pakarepo.

Dambudziko neasterisk: CTEs inogona kugadzirwa.

Kune dzinenge dzisina kusimba, uye kunyange dzine mazita mamwe chete. Somuenzaniso, unogona mukati CTE A ita CTE X, uye pamwero wakafanana mukati CTE B zviite zvakare CTE X:

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

Kana uchienzanisa, unofanira kunzwisisa izvi. Kunzwisisa izvi "nemeso ako" - kunyange kuona chirongwa, kunyange kuona muviri wechikumbiro - zvakaoma zvikuru. Kana chizvarwa chako cheCTE chakaoma, chakagara, uye zvikumbiro zvakakura, saka hazvizivi zvachose.

UNION

Kana isu tiine keyword mumubvunzo UNION [ALL] (opareta yekubatanidza masampuli maviri), ipapo muchirongwa inoenderana chero node Append, kana zvimwe Recursive Union.


Icho chiri "kumusoro" kumusoro UNION - uyu ndiye muzukuru wekutanga we node yedu, iyo "pasi" - yechipiri. Kana kuburikidza UNION isu tine akati wandei mabhuraki "glued" kamwechete, ipapo Append-kuchave kuine node imwe chete, asi haizove nevaviri, asi vana vazhinji - mukurongeka kwavanoenda, zvichiteerana:

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

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

Dambudziko neasterisk: mukati recursive sampling chizvarwa (WITH RECURSIVE) inogonawo kuva yakawanda UNION. Asi chete bhuroka yekupedzisira mushure meyekupedzisira ndiyo inogara ichidzokorodza UNION. Zvose zviri pamusoro ndezvimwe, asi zvakasiyana UNION:

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

Iwe unofanirwawo kukwanisa "kunamatira" mienzaniso yakadaro. Mumuenzaniso uyu tinoona izvozvo UNION-paive nezvikamu zvitatu muchikumbiro chedu. Naizvozvo, imwe UNION соответствck Append-node, uye kune imwe - Recursive Union.

Verenga-nyora data

Zvose zvakarongwa, ikozvino tinoziva kuti chidimbu chipi chechikumbiro chinoenderana nechidimbu chechirongwa. Uye muzvidimbu izvi tinogona kuwana nyore uye zvakasikwa zvinhu izvo "zvinoverengwa".

Kubva pamubvunzo wekutarisa, hatizive kana iri tafura kana CTE, asi ivo vanosarudzwa neinode imwechete. RangeVar. Uye maererano ne "kuverenga", iyi zvakare yakaganhurwa seti yemanodhi:

• 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]

Isu tinoziva maumbirwo echirongwa nemubvunzo, tinoziva kuenderana kwezvivharo, tinoziva mazita ezvinhu - tinoita kuenzanisa-kune-imwe.


Zvakare basa "nenyeredzi". Isu tinotora chikumbiro, tochiita, isu hatina maaases - isu tinongoverenga kaviri kubva kuCTE imwechete.


Tinotarisa chirongwa - chii chinonetsa? Sei takanga tine zita rezita? Hatina kuodha. “Nhamba yenhamba” yakadaro anoiwanepi?

PostgreSQL inozviwedzera pachayo. Ungofanire kuzwisisa izvozvo zita rakadaro kwatiri, nokuda kwezvinangwa zvekuenzanisa nehurongwa, hazvina musoro, zvinongowedzerwa pano. Ngatisaita hanya naye.

Yechipiri basa "nenyeredzi": kana isu tiri kuverenga kubva patafura yakakamurwa, saka tichawana node Append kana Merge Append, iyo ichange iine nhamba huru ye "vana", uye imwe neimwe ichava neimwe nzira Scan'om kubva patafura-chikamu: Seq Scan, Bitmap Heap Scan kana Index Scan. Asi, chero zvakadaro, "vana" ava havazove mibvunzo yakaoma - iyi ndiyo nzira iyo node idzi dzinogona kusiyaniswa kubva Append at UNION.


Isu tinonzwisisawo mapfundo akadaro, tinoaunganidza "mumurwi mumwe" toti: "zvese zvaunoverenga kubva megatable zviri pano uye pasi pemuti".

"Yakareruka" data inogamuchira node

Values Scan zvinoenderana nehurongwa VALUES muchikumbiro.

Result chikumbiro pasina FROM aina ye SELECT 1. Kana kana iwe uine nemaune kutaura kwenhema mukati WHERE-block (ipapo hunhu hunooneka 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 "mepu" kune maSRF ezita rimwe chete.

Asi nemibvunzo inested zvese zvakanyanya kuomarara - zvinosuruvarisa, ivo havawanzo kushanduka kuita InitPlan/SubPlan. Dzimwe nguva vanoshanduka kuva ... Join kana ... Anti Join, kunyanya kana iwe uchinyora chimwe chinhu chakadai WHERE NOT EXISTS .... Uye pano hazvigoneki nguva dzose kuvabatanidza - mune zvinyorwa zvechirongwa hapana vashandi vanoenderana nemanodhi echirongwa.

Zvakare basa "nenyeredzi": zvimwe VALUES muchikumbiro. Muchiitiko ichi uye muchirongwa iwe uchawana node dzakawanda Values Scan.


"Chiverengo" zvirevo zvichabatsira kusiyanisa kubva kune mumwe - ivo vanowedzerwa chaizvo muhurongwa uhwo hunoenderana hunowanikwa. VALUES-blocks pamwe nekukumbira kubva kumusoro kusvika pasi.

Kugadziriswa kwedata

Zvinoita sekunge zvese zviri muchikumbiro chedu zvagadziriswa - zvese zvasara ndizvo Limit.


Asi pano zvose zviri nyore - node dzakadai Limit, Sort, Aggregate, WindowAgg, Unique "mepu" imwe-kune-imwe kune vanoenderana vanoshanda mukukumbira, kana varipo. Hapana "nyeredzi" kana matambudziko pano.

ONA

Matambudziko anomuka kana toda kubatanidza JOIN pakati pavo. Izvi hazviiti nguva dzose, asi zvinogoneka.


Kubva pane maonero emubvunzo wemubvunzo, isu tine node JoinExpr, iyo ine vana vaviri chaivo - kuruboshwe uye kurudyi. Izvi, saizvozvo, ndizvo zviri "pamusoro" JOIN yako uye zvakanyorwa "pazasi" pazviri muchikumbiro.

Uye kubva pakuona kwechirongwa, ava vazukuru vaviri vevamwe * Loop/* Join-node. Nested Loop, Hash Anti Join,... - chimwe chinhu chakadaro.

Ngatishandisei pfungwa dzakareruka: kana isu tine matafura A uye B ayo "akabatana" mumwe nemumwe muchirongwa, saka mukukumbira vanogona kuwanikwa chero A-JOIN-B, kana B-JOIN-A. Ngatiedzei kubatanidza nenzira iyi, ngatiedzei kubatanidza imwe nzira yakapoteredza, uye zvichingodaro kusvikira tapera mapeya akadaro.

Ngatitorei muti wedu we syntax, tora chirongwa chedu, tarisa kwavari ... kwete zvakafanana!


Ngatiidzore zvakare muchimiro chegirafu - o, inotoita sechinhu!


Ngationei kuti tine node dzine vana B uye C panguva imwe chete - hatina basa nehurongwa hupi. Ngativasanganise uye tishandure mufananidzo wenodhi pamusoro.


Ngatitarisei zvakare. Iye zvino tine nodes nevana A uye vaviri vaviri (B + C) - inoenderana navo zvakare.


Hukuru! Zvinoratidza kuti tiri vaviri ava JOIN kubva kuchikumbiro nemanodhi ehurongwa zvakabatanidzwa zvakabudirira.

Maiwe, dambudziko iri harigadziriswe nguva dzose.


Somuenzaniso, kana muchikumbiro A JOIN B JOIN C, uye muurongwa, kutanga kwezvose, "kunze" node A uye C. Asi hapana mushandisi akadaro muchikumbiro, isu hatina chatinosimbisa, hapana chatingabatanidza nacho. Ndizvo zvakafanana ne "comma" paunonyora A, B.

Asi, kazhinji, dzinenge node dzese dzinogona "kusunungurwa" uye unogona kuwana rudzi urwu rwekufembera kuruboshwe nenguva - chaizvo, senge muGoogle Chrome paunoongorora JavaScript kodhi. Iwe unogona kuona kuti mutsara wega wega uye chirevo chimwe nechimwe chakatora nguva yakareba sei "kuita."


Uye kuita kuti zvive nyore kwauri kushandisa zvese izvi, isu takagadzira kuchengetedza Archive, kwaunogona kuchengetedza uye wozowana zvirongwa zvako pamwe chete nezvikumbiro zvakabatana kana kugovera chinongedzo nemumwe munhu.

Kana iwe uchingoda kuunza mubvunzo usingaverengeki mune fomu yakakwana, shandisa yedu "normalizer".

Source: www.habr.com