He tokomaha kua whakamahi kē — kāore pea tā mātou ratonga whakaaturanga mahere PostgreSQL e mōhio ki tētahi o ōna mana nui: te huri i tētahi wāhanga uaua ki te pānui o te rangitaki tūmau…
…ki roto i tētahi uiui kua whakatakotoranga ataahuatia me ngā tohutohu horopaki mō ngā pūnga mahere e tika ana:
I roto i tēnei tuhinga o te wāhanga tuarua o tana Māku e kōrero atu ki a koe me pēhea mātou i taea ai tēnei.
Kei roto i te tuhinga he tuhinga mō ngā raruraru mahi patai me ā rātou otinga, e pā ana ki te wāhanga tuatahi. .
Tuatahi, me mahi te tae - ā, kāore mātou e tae i te mahere, kua tae kē mātou, kua ātaahua, kua mārama kē, engari ko te tono.
I kite mātou ko te patai, i tangohia mai i te rangitaki pēnei, me te kore e hōputuhia, he tino kino te āhua, nō reira he uaua.
Ina koa ka "piri" ngā kaiwhakawhanake i te tinana tono (he tauira-kore tēnei, engari ka tupu) ki roto i te rārangi waehere kotahi. He whakamataku!
Kia ataahua ake te tuhi i tēnei.
Ā, ki te taea e tātou te tuhi i tēnei mea kia ātaahua, arā, te wetewete me te whakakotahi anō i te tinana o te tono, kātahi ka taea e tātou te "tāpiri" i tētahi tohu ki ia mea o tēnei tono - te mea i tupu i te wāhi e rite ana i roto i te mahere.
Rākau wetereo patai
Hei mahi i tēnei, me tātari tuatahi te tono.
Nā te mea kei a tātou , kātahi ka hangaia e mātou he kōwae mōna, ka taea e koe Ina hoki, he "herenga" whānui ēnei ki ngā āhuatanga o roto o te kaitātari a PostgreSQL. Arā, ko te wetereo he mea whakahiato noa i roto i te reo rua, ā, he mea here ki roto e NodeJS. I whakamahia e mātou ētahi atu kōwae hei pūtake—kāore he mea ngaro nui i konei.
Ka whāngaihia e mātou te tinana tono hei tāuru ki tā mātou mahi - i te putanga ka whiwhi mātou i tētahi rākau wetereo kua wetewetehia i te āhua o tētahi mea JSON.
Inaianei ka taea e tātou te whakawhiti whakamuri i tēnei rākau, ka hanga i tētahi patai me te kōhao, te tae, me te hōputu e hiahiatia ana e tātou. Kāo, kāore tēnei e taea te whakarite, engari i whakaaro mātou he mea watea.
Te mahere i ngā pona patai ki ngā pona whakamahere
Nā, kia titiro tātou me pēhea te whakakotahi i te mahere i kōrerohia i te taahiraa tuatahi me te patai i kōrerohia i te taahiraa tuarua.
Me tango he tauira māmā: he patai tā tātou e hanga ana i tētahi CTE, ā, ka pānuihia kia rua ngā wā mai i taua patai. Ka whakaputahia te mahere e whai ake nei.
CTE
Ki te tirohia pai, kei te putanga 12 (tērā rānei ka tīmata ki te kupumatua MATERIALIZED) hanganga .
A, nō reira, ki te kite tātou i tētahi wāhi o te patai te whakaputanga o tētahi CTE me tētahi wāhi o te mahere he pūnga CTE, kātahi ka mārama kei te "pakanga" ēnei pona tetahi ki tetahi, ka taea e tātou te whakakotahi tonu i a rātou.
He raruraru me te whetūKa taea te whakapūpū i ngā CTE.
He tino kino te hanganga o ētahi, tae atu ki ētahi he rite te ingoa. Hei tauira, ka taea e koe te whakauru ki roto CTE A hanga CTE X, ā, i te taumata kotahi i roto CTE B meatia anō CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...I te wā e mahere ana koe, me mārama koe ki tēnei. He tino uaua te mārama ki tēnei mea ā-kanohi—ahakoa te titiro ki te mahere, ahakoa te titiro ki te tinana o te patai. Mena he uaua, he kōpikopiko, ā, he nui ō patai, kāti kāore i te mōhiotia.
UNION
Mena he kupumatua kei roto i tā mātou patai UNION [ALL] (te kaiwhakahaere o te hono i ngā tauira e rua), kātahi i roto i te mahere ka rite ki tētahi pūnga Append, ētahi rānei Recursive Union.
Ko te mea kei runga ake nei UNION — koinei te uri tuatahi o tō tātou pūnga, kei "raro" — te tuarua. Ki te mea mā roto i UNION He maha ngā poraka kua whakapirihia i te wā kotahi, kātahi Append- kotahi tonu te pūnga, engari ehara i te mea e rua āna tamariki, engari he maha - kia rite ki te raupapa o tā rātou haere:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
He raruraru me te whetū: i roto i te whakaputanga o tētahi tauira tāruarua (WITH RECURSIVE) ka taea hoki te neke atu i te kotahi UNION. Engari ko te poraka whakamutunga anake i muri i te poraka whakamutunga he mea auau tonu. UNIONKotahi ngā mea katoa o runga ake nei, engari he rerekē UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
Me taea hoki e koe te "wete" i aua tauira. I roto i tēnei tauira, ka kite tātou i tērā UNION- e toru ngā wāhanga i roto i tā mātou tono. Nō reira, kotahi UNION соответствует Append-hono, ā, ki tētahi atu - Recursive Union.
Te pānui me te tuhi raraunga
Āe, kua wehewehea e tātou. Nā, kua mōhio tātou ko tēhea wāhanga patai e rite ana ki tēhea wāhanga mahere. Ā, i roto i ēnei wāhanga, ka taea e tātou te kimi ngāwari me te kore e uaua i ngā mea e "pānuihia ana".
Mai i te tirohanga patai, kāore mātou i te mōhio mēnā he ripanga, he CTE rānei, engari e tohuhia ana ēnei e te pūnga kotahi. RangeVarĀ, mō te "pānuitanga," he iti noa iho hoki tēnei huinga o ngā pūnga:
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]
E mōhio ana mātou ki te hanganga o te mahere me te uiui, e mōhio ana mātou ki te ōritetanga o ngā poraka, e mōhio ana mātou ki ngā ingoa o ngā mea - ka whakarite mārama mātou.
Anō raruraru me te whetūKa tangohia e mātou te patai, ka whakahaerehia, kāore he ingoa kē atu ā mātou—ka rua noa ngā wā ka pānuihia e mātou mai i te CTE kotahi.
Kei te tirohia e mātou te mahere—he aha te raruraru? He aha i riro mai ai i a mātou tēnei ingoa kē? Kāore mātou i tono. Nō hea mai tēnei ingoa "tau"?
Ka tāpirihia e PostgreSQL anō. Me mārama noa koe ki tēnā koinei tonu te ingoa kē Hei whakataurite mā mātou ki te mahere, kāore he tikanga; he mea tāpiri noa iho ki konei. Kāore mātou e aro atu.
Ko te tuarua raruraru me te whetū: ki te mea kei te pānui tātou mai i tētahi ripanga kua wehea, ka whiwhi tātou i tētahi pūnga Append ranei Merge Append, ka titoa mai i te tini o ngā "tamariki", ā, he momo tamariki ia o aua tamariki Scanmai i te ripanga wāhanga: Seq Scan, Bitmap Heap Scan ranei Index ScanEngari, ahakoa pēhea, ehara ēnei "tamariki" i te uiui uaua - koinei te huarahi e taea ai te wehewehe i ēnei pūnga mai i Append i UNION.
E mārama ana hoki mātou ki ēnei pūnga, ka kohia e mātou "ki te puranga kotahi" ka mea: "Kei konei ngā mea katoa e pānuihia ana e koe i te megatable, ā, kei raro iho i te rākau.".
Ngā pūnga whiwhi raraunga "māmā"
Values Scan e rite ana ki te mahere VALUES i roto i te tono.
Result - he tono tēnei kāore he FROM rite SELECT 1. Mēnā rānei he whakaaturanga teka tōu i roto i WHERE-block (kātahi ka puta te huanga 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 "kua maheretia" ki ngā SRF o taua ingoa anō.
Engari ki ngā uiui kōpiko he uaua ake ngā mea - heoi, kāore i te huri i ngā wā katoa ki InitPlan/SubPlanI ētahi wā ka huri rātou hei ... Join ranei ... Anti Join, ina koa ka tuhia e koe tētahi mea pēnei WHERE NOT EXISTS ...Kāore hoki e taea te whakakotahi i aua mea i ngā wā katoa—kāore i roto i te tuhinga mahere ngā kaiwhakahaere e pā ana ki ngā pūnga mahere.
Anō raruraru me te whetūētahi VALUES i roto i te patai. I tēnei wā, ka whiwhi koe i ētahi pūnga i roto i te mahere. Values Scan.
Mā ngā tāpiritanga "Tau" ka wehewehea rātou tetahi i tetahi - ka tāpirihia kia rite ki te raupapa i kitea ai ngā mea e rite ana. VALUES-ngā poraka i te raupapa o te tono mai i runga ki raro.
Te tukatuka raraunga
Me te mea kua kapi katoa i a mātou ngā mea katoa i roto i tā mātou tono - kotahi anake e toe ana Limit.
Engari he māmā noa iho ngā mea katoa i konei - pērā i ngā pona Limit, Sort, Aggregate, WindowAgg, Unique Ka maheretia takitahi-ki-te-kotahi ki ngā kaiwhakahaere e rite ana i roto i te patai, mēnā he mea kei roto. Kāore he whetūriki, he uauatanga rānei i konei.
Hono
Ka puta ngā uauatanga ina hiahia tātou ki te whakakotahi JOIN i waenganui i a rātou anō. Kāore e taea i ngā wā katoa tēnei, engari he mea ka taea.
Mai i te tirohanga a te kaitātari patai, he pūnga tā tātou JoinExpr, e rua tonu ngā uri—maui me matau. Ko ēnei, ia, ko te mea kei "runga ake" i tō JOIN me te mea kei "raro" i roto i te patai.
Ā, mai i te tirohanga a te mahere, he uri ēnei nō tētahi * Loop/* Join-pūnga Nested Loop, Hash Anti Join,… — tētahi mea pērā.
Me whakamahi tātou i tētahi arorau māmā: mēnā he ripanga A me B kei roto i te mahere e "honohono" ana, kāti i roto i te patai ka kitea pea rāua A-JOIN-B, ranei B-JOIN-AMe ngana tātou ki te whakakotahi i a rātou pēnei, me ngana tātou ki te whakakotahi i a rātou i tētahi atu huarahi, me pērā tonu kia pau rawa aua takirua.
Me tango tā tātou rākau wetereo, me tango tā tātou mahere, tirohia rāua... kāore e ōrite te āhua!
Me tuhi anō hei kauwhata - āe, kua tīmata kē te āhua!
Kia mōhio tātou kei a tātou ngā pona e mau ana ngā tamariki B me C i te wā kotahi—kāore he aha te raupapa. Me whakakotahi rāua, ka huri i te ahua o te pona.
Kia titiro anō tātou. Inaianei kei a tātou ngā pona me ngā tamariki A me ngā takirua (B + C) – me taurite hoki.
Pai rawa atu! Ko tāua tēnei tokorua JOIN mai i te patai me ngā pūnga mahere i honoa angitu.
Engari, kāore tēnei raruraru e whakatauhia i ngā wā katoa.
Hei tauira, mēnā i roto i te tono A JOIN B JOIN C, ā, i roto i te mahere, ko ngā pūnga "tino" A me C i honoa tuatahitia. Engari kāore he kaiwhakahaere pērā i roto i te patai, nō reira kāore he mea hei whakamārama, kāore he mea hei tāpiri i te tohu. Ko te mea anō hoki mō te piko ina tuhia e koe A, B.
Engari i te nuinga o te wā, ka taea te "wetewete" i te nuinga o ngā pona, ā, ka whiwhi i tētahi tirohanga whakarārangi e hangai ana ki te wā pēnei i te taha maui—pērā i roto i a Google Chrome ina tātarihia e koe te waehere JavaScript. Ka taea e koe te kite i te roa o te wā e pau ana ki te whakahaere i ia rārangi me ia tauākī.
Ā, kia māmā ake ai te whakamahi i ēnei mea katoa māu, kua hangaia e mātou he wāhi rokiroki , te wāhi ka taea e koe te tiaki, ā, muri iho ka kitea ō mahere me ngā pātai e pā ana, ka taea rānei te tohatoha hononga ki tētahi atu.
Ki te hiahia noa koe kia ātaahua ake te pānui i tētahi patai kāore e taea te pānui, whakamahia .
Source: will.com
