Nui ka poʻe e hoʻohana nei - ʻaʻole ʻike ʻia kā mākou lawelawe ʻike hoʻolālā hoʻolālā PostgreSQL i kekahi o kāna mau mana nui - ke hoʻohuli nei i kahi ʻāpana paʻakikī i ka heluhelu ʻana o ka log server...
... i loko o kahi nīnau i hoʻolālā nani ʻia me nā ʻōlelo hōʻikeʻike no nā node hoʻolālā e pili ana:
Ma keia transcript o ka mahele elua o kana E haʻi wau iā ʻoe pehea mākou i hana ai i kēia.
ʻO ka transcript o ka ʻāpana mua, i hoʻolaʻa ʻia i nā pilikia hana nīnau maʻamau a me kā lākou hoʻonā, hiki ke loaʻa i ka ʻatikala. .
ʻO ka mea mua, e hoʻomaka kākou e kala - a ʻaʻole mākou e kala hou i ka hoʻolālā, ua kala mākou iā ia, ua loaʻa iā mākou he nani a hoʻomaopopo ʻia, akā he noi.
Ua manaʻo mākou me kahi "pepa" i hoʻohālikelike ʻole ʻia ka noi i huki ʻia mai ka lāʻau e nānā ʻino loa a no laila ʻaʻole kūpono.
ʻOi loa i ka wā e "hoʻopili" ai nā mea hoʻomohala i ke kino o ka noi i ke code (ʻoiaʻiʻo, he antipattern, akā hiki ke hana) i hoʻokahi laina. Weliweli!
E kahakiʻi kākou i kēia me ka nani.
A inā hiki iā mākou ke kahakiʻi i kēia me ka nani, ʻo ia hoʻi, wehe a hoʻohui hou i ke kino o ka noi, a laila hiki iā mākou ke "hoʻopili" i kahi hōʻailona i kēlā me kēia mea o kēia noi - ka mea i hana ʻia ma kahi kikoʻī o ka hoʻolālā.
Ka lāʻau syntax nīnau
No ka hana ʻana i kēia, pono e hoʻopili mua ʻia ka noi.
No ka mea, ua loaʻa iā mākou , a laila hana mākou i module no ia, hiki iā ʻoe . ʻO ka ʻoiaʻiʻo, ua hoʻonui ʻia kēia mau "hoʻopaʻa" i nā internals o ka PostgreSQL parser ponoʻī. ʻO ia hoʻi, ua hui pū ʻia ka grammar a ua hana ʻia nā paʻa iā ia mai NodeJS. Lawe mākou i nā modula o nā poʻe ʻē aʻe i kumu - ʻaʻohe mea huna nui ma aneʻi.
Hānai mākou i ke kino o ka noi ma ke ʻano he hoʻokomo i kā mākou hana - ma ka hopena e loaʻa iā mākou kahi lāʻau syntax parsed ma ke ʻano o kahi mea JSON.
I kēia manawa hiki iā mākou ke holo i loko o kēia kumulāʻau ma ka ʻaoʻao ʻē aʻe a hōʻuluʻulu i kahi noi me nā indents, kala, a me ka hoʻopili ʻana a mākou e makemake ai. ʻAʻole, ʻaʻole hiki ke maʻalahi kēia, akā ua manaʻo mākou he mea maʻalahi kēia.
Ka nīnau palapala palapala a me ka hoʻolālā nodes
I kēia manawa e ʻike kākou pehea e hiki ai iā mākou ke hoʻohui i ka hoʻolālā a mākou i kālailai ai i ka ʻanuʻu mua a me ka nīnau a mākou i kālailai ai i ka lua.
E lawe i kahi laʻana maʻalahi - loaʻa iā mākou kahi nīnau e hoʻopuka i kahi CTE a heluhelu ʻelua mai ia mea. Hana ʻo ia i kahi hoʻolālā.
CTE
Inā ʻoe e nānā pono iā ia, a hiki i ka mana 12 (a i ʻole e hoʻomaka me ka huaʻōlelo MATERIALIZED) hoʻokumu .
ʻO ia ke ʻano inā ʻike mākou i kahi hanauna CTE ma kahi o ka noi a me kahi node ma kahi o ka hoʻolālā CTE, a laila "hakakā" kēia mau nodes me kekahi, hiki iā mākou ke hoʻohui koke iā lākou.
Pilikia me ka asterisk: Hiki ke hoʻopaʻa ʻia nā CTE.
Aia nā pūnana maikaʻi ʻole, a me nā inoa like. No ka laʻana, hiki iā ʻoe i loko CTE A make CTE X, a ma ka pae like o loko CTE B e hana hou CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...I ka hoʻohālikelike ʻana, pono ʻoe e hoʻomaopopo i kēia. ʻO ka hoʻomaopopo ʻana i kēia "me kou mau maka" - ʻo ka ʻike ʻana i ka hoʻolālā, ʻoiai ke ʻike ʻana i ke kino o ka noi - paʻakikī loa. Inā paʻakikī kāu hanauna CTE, pūnana, a nui nā noi, a laila ʻaʻole maopopo.
UNION
Inā loaʻa iā mākou kahi huaʻōlelo i ka nīnau UNION [ALL] (mea hoʻohana o ka hoʻohui ʻana i ʻelua laʻana), a laila ma ka hoʻolālā e pili ana i kahi node Append, a i ʻole kekahi Recursive Union.
ʻO ka mea i "luna" ma luna UNION - ʻo ia ka moʻopuna mua o kā mākou node, ʻo ia ka "lalo" - ka lua. Ina ma o UNION loaʻa iā mākou kekahi mau poloka "glued" i ka manawa hoʻokahi, a laila Append-e hoʻokahi wale nō node, akā ʻaʻole ʻelua, akā nui nā keiki - ma ke kauoha a lākou e hele ai:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Pilikia me ka asterisk: i loko o ka hana hoʻohālike hou ʻana (WITH RECURSIVE) hiki ke ʻoi aku ma mua o hoʻokahi UNION. Akā ʻo ka poloka hope loa ma hope o ka mea hope loa ka recursive mau UNION. ʻO nā mea a pau ma luna nei hoʻokahi, akā ʻokoʻa UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
Pono ʻoe e "hoʻopaʻa" i kēlā mau hiʻohiʻona. Ma kēia laʻana, ʻike mākou i kēlā UNION-he 3 mau ʻāpana i kā mākou noi. No laila, hoʻokahi UNION соответствует Append-node, a i kekahi - Recursive Union.
Heluhelu-kākau ʻikepili
Ua waiho ʻia nā mea a pau, i kēia manawa ua ʻike mākou i ka ʻāpana o ka noi e pili ana i kahi ʻāpana o ka hoʻolālā. A i loko o kēia mau ʻāpana hiki iā mākou ke maʻalahi a ʻike maoli i kēlā mau mea i "heluhelu ʻia".
Mai kahi nīnau nīnau, ʻaʻole mākou ʻike inā he papaʻaina a he CTE paha, akā ua koho ʻia lākou e ka node hoʻokahi. RangeVar. A ma ke ʻano o ka "readable", he ʻano palena liʻiliʻi kēia o nā nodes:
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]
ʻIke mākou i ke ʻano o ka hoʻolālā a me ka nīnau, ʻike mākou i ka pilina o nā poloka, ʻike mākou i nā inoa o nā mea - hana mākou i kahi hoʻohālikelike hoʻokahi.
hou hana "me ka asterisk". Lawe mākou i ka noi, hoʻokō, ʻaʻohe o mākou inoa inoa - heluhelu ʻelua mākou iā ia mai ka CTE like.
Nānā mākou i ka papahana - he aha ka pilikia? No ke aha i loaʻa iā mākou kahi inoa inoa? ʻAʻole mākou i kauoha. Ma hea ʻo ia e loaʻa ai kēlā "helu helu"?
Hoʻohui ʻo PostgreSQL iā ia iho. Pono ʻoe e hoʻomaopopo i kēlā he inoa inoa wale nō no mākou, no ka hoʻohālikelike ʻana me ka hoʻolālā, ʻaʻohe manaʻo, ua hoʻohui wale ʻia ma aneʻi. Mai hoʻolohe kākou iā ia.
ʻO ka lua hana "me ka asterisk": ina e heluhelu ana kakou mai ka papaaina i maheleia, alaila, e loaa ia kakou ka node Append ai ole ia, Merge Append, i loko o ka heluna nui o "keiki", a me kela mea keia mea e like me Scan'om mai ka papa-ʻāpana: Seq Scan, Bitmap Heap Scan ai ole ia, Index Scan. Akā, i kēlā me kēia hihia, ʻaʻole e lilo kēia mau "keiki" i mau nīnau paʻakikī - ʻo ia ke ʻano e ʻike ʻia ai kēia mau nodes Append i UNION.
Hoʻomaopopo pū mākou i kēlā mau puʻupuʻu, e hōʻiliʻili iā lākou "i ka puʻu hoʻokahi" a ʻōlelo: "ʻO nā mea a pau āu e heluhelu ai mai ka megatable aia ma lalo o ka lāʻau".
"Maʻalahi" loaʻa nā nodes
Values Scan pili i ka hoʻolālā VALUES ma ke noi.
Result he noi me ka ole FROM elike SELECT 1. A i ʻole inā loaʻa iā ʻoe kahi ʻōlelo wahaheʻe i loko WHERE-block (a laila ʻike ʻia ke ʻano One-Time Filter):
EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1Result (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 "palapala palapala" i nā SRF o ka inoa like.
Akā me nā nīnau nested ʻoi aku ka paʻakikī o nā mea āpau - akā naʻe, ʻaʻole lākou e huli mau i loko InitPlan/SubPlan. I kekahi manawa huli lākou i loko ... Join ai ole ia, ... Anti Join, ʻoi loa ke kākau ʻoe i kekahi mea like WHERE NOT EXISTS .... A ma aneʻi ʻaʻole hiki ke hoʻohui iā lākou - ma ka kikokikona o ka hoʻolālā ʻaʻohe mea hoʻohana e pili ana i nā nodes o ka hoʻolālā.
hou hana "me ka asterisk": kekahi VALUES ma ke noi. Ma kēia hihia a ma ka hoʻolālā e loaʻa iā ʻoe kekahi mau nodes Values Scan.
E kōkua nā suffixes "Numbered" e hoʻokaʻawale iā lākou mai kekahi i kekahi - ua hoʻohui pū ʻia lākou ma ke ʻano i loaʻa ai nā mea pili. VALUES- nā poloka ma ka noi mai luna a lalo.
Ka hoʻoponopono ʻikepili
Me he mea lā ua hoʻokaʻawale ʻia nā mea a pau o kā mākou noi - ʻo nā mea i koe Limit.
Akā ma ʻaneʻi ua maʻalahi nā mea āpau - nā nodes e like me Limit, Sort, Aggregate, WindowAgg, Unique "palapala palapala" hoʻokahi i kekahi i nā mea hoʻohana pili i ka noi, inā aia lākou. ʻAʻohe "hōkū" a pilikia paha ma ʻaneʻi.
hui
Hiki mai nā pilikia ke makemake mākou e hui JOIN mawaena o lakou iho. ʻAʻole hiki kēia i nā manawa a pau, akā hiki.
Mai ka manaʻo o ka parser nīnau, loaʻa iā mākou kahi node JoinExpr, ʻelua mau keiki - hema a ʻākau. ʻO kēia, no laila, ʻo ia ka mea "ma luna" o kāu JOIN a me ka mea i kākau ʻia "ma lalo" i loko o ka noi.
A mai ka manaʻo o ka papahana, ʻelua kēia mau mamo a kekahi * Loop/* Join-node. Nested Loop, Hash Anti Join,... - kekahi mea e like me ia.
E hoʻohana i ka loiloi maʻalahi: inā loaʻa iā mākou nā papa A a me B e "hui" kekahi i kekahi i ka hoʻolālā, a laila ma ka noi hiki ke loaʻa iā lākou. A-JOIN-B, aiʻole B-JOIN-A. E hoʻāʻo kākou e hoʻohui i kēia ʻano, e hoʻāʻo kākou e hoʻohui i ka ʻaoʻao ʻē aʻe, a pēlā aku a pau i ka pau ʻana o ia mau pālua.
E lawe i kā mākou kumu lāʻau syntax, e lawe i kā mākou hoʻolālā, e nānā iā lākou ... ʻaʻole like!
E kahaki hou kākou ma ke ʻano o nā kiʻi - ʻo, ua like ia me kekahi mea!
E hoʻomanaʻo mākou he mau nodes i loaʻa nā keiki B a me C i ka manawa like - ʻaʻole mākou e mālama i ke ʻano. E hui pū kākou a hoʻohuli i ke kiʻi o ka node.
E nana hou kakou. I kēia manawa, loaʻa iā mākou nā nodes me nā keiki A a me nā pālua (B + C) - kūpono pū me lākou.
Nui! ʻIke ʻia ʻo mākou kēia ʻelua JOIN mai ka noi me nā node hoʻolālā ua hoʻohui maikaʻi ʻia.
Auē, ʻaʻole hoʻoponopono mau kēia pilikia.
Eia kekahi laʻana, inā ma kahi noi A JOIN B JOIN C, a ma ka hoʻolālā, ʻo ka mea mua, ua hoʻopili ʻia nā "waho" nodes A a me C. Akā, ʻaʻohe mea hoʻohana like i ka noi, ʻaʻohe mea e hōʻike aku ai, ʻaʻohe mea e hoʻopili ai i kahi hint. Ua like ia me ka "koma" ke kākau ʻoe A, B.
Akā, i ka hapanui o nā hihia, aneane hiki ke "wehe" nā node a hiki iā ʻoe ke loaʻa i kēia ʻano profiling ma ka hema i ka manawa - ʻoiaʻiʻo, e like me Google Chrome ke nānā ʻoe i ka code JavaScript. Hiki iā ʻoe ke ʻike i ka lōʻihi o kēlā me kēia laina a me kēlā me kēia ʻōlelo e "hoʻokō."
A i mea e maʻalahi ai ka hoʻohana ʻana i kēia mau mea a pau, ua hana mākou i kahi waihona , kahi e hiki ai iā ʻoe ke mālama a loaʻa ma hope i kāu mau hoʻolālā me nā noi pili a kaʻana like paha i ka loulou me kekahi.
Inā pono ʻoe e lawe mai i kahi nīnau hiki ʻole ke heluhelu ʻia i kahi ʻano kūpono, e hoʻohana .
Source: www.habr.com