Ntau tus uas twb siv lawm - peb qhov kev pabcuam PostgreSQL pom qhov kev pabcuam yuav tsis paub txog ib qho ntawm nws lub zog loj - tig ib qho nyuaj-rau-nyeem ntawv ntawm lub server log ...
... mus rau hauv cov lus nug tsim zoo nkauj nrog cov ntsiab lus qhia txog kev sib raug zoo ntawm cov phiaj xwm:
Nyob rau hauv cov ntaub ntawv no ntawm qhov thib ob ntawm nws Kuv mam li qhia koj seb peb tswj tau li cas los ua qhov no.
Cov ntawv sau tseg ntawm thawj ntu, mob siab rau cov lus nug txog cov teeb meem kev ua tau zoo thiab lawv cov kev daws teeb meem, tuaj yeem pom hauv kab lus .
Ua ntej, cia peb pib xim - thiab peb yuav tsis xim cov phiaj xwm ntxiv lawm, peb twb tau xim nws, peb twb muaj nws zoo nkauj thiab nkag siab, tab sis ib qho kev thov.
Nws zoo nkaus li rau peb tias nrog xws li "daim ntawv" unformatted qhov kev thov rub tawm ntawm lub cav zoo li tsis zoo thiab yog li tsis yooj yim.
Tshwj xeeb tshaj yog thaum cov neeg tsim khoom "kos" lub cev ntawm qhov kev thov hauv cov cai (qhov no yog, ntawm chav kawm, ib qho kev tiv thaiv, tab sis nws tshwm sim) hauv ib kab. Txaus ntshai!
Cia peb kos qhov no zoo nkauj dua.
Thiab yog tias peb tuaj yeem kos qhov no zoo nkauj, uas yog, disassemble thiab muab rov qab ua ke lub cev ntawm qhov kev thov, tom qab ntawd peb tuaj yeem "txuas" cov lus qhia rau txhua qhov khoom ntawm qhov kev thov no - dab tsi tshwm sim ntawm qhov sib thooj ntawm txoj kev npaj.
Query syntax tsob ntoo
Txhawm rau ua qhov no, qhov kev thov yuav tsum ua ntej parsed.
Vim peb muaj , ces peb ua ib tug module rau nws, koj ua tau . Qhov tseeb, cov no tau txuas ntxiv "kev khi" rau sab hauv ntawm PostgreSQL parser nws tus kheej. Ntawd yog, cov qauv sau ntawv yog yooj yim binary compiled thiab khi tau ua rau nws los ntawm NodeJS. Peb coj lwm tus neeg cov qauv ua lub hauv paus - tsis muaj qhov zais cia loj ntawm no.
Peb pub lub cev ntawm qhov kev thov raws li kev nkag mus rau peb txoj haujlwm - ntawm qhov tso zis peb tau txais cov ntoo parsed syntax hauv daim ntawv ntawm JSON khoom.
Tam sim no peb tuaj yeem khiav los ntawm tsob ntoo no nyob rau sab nraud thiab sib sau ua ib qho kev thov nrog cov indents, coloring, thiab formatting uas peb xav tau. Tsis yog, qhov no tsis yog kho tau, tab sis nws zoo li peb tias qhov no yuav yooj yim.
Mapping query thiab npaj nodes
Tam sim no cia saib yuav ua li cas peb tuaj yeem muab cov phiaj xwm uas peb tau txheeb xyuas hauv thawj kauj ruam thiab cov lus nug uas peb tau txheeb xyuas hauv qhov thib ob.
Cia peb ua piv txwv yooj yim - peb muaj cov lus nug uas tsim CTE thiab nyeem los ntawm nws ob zaug. Nws tsim cov phiaj xwm zoo li no.
CTE
Yog tias koj saib kom zoo, mus txog version 12 (los yog pib ntawm nws nrog lo lus tseem ceeb MATERIALIZED) tsim .
Qhov no txhais tau hais tias yog peb pom CTE tiam ib qhov twg hauv qhov kev thov thiab ib qho ntawm qhov chaw hauv txoj kev npaj CTE, ces cov nodes mas nws yeej "sib ntaus" nrog ib leeg, peb tuaj yeem ua ke tam sim ntawd.
Teeb meem nrog lub hnub qub: CTEs tuaj yeem ua zes.
Muaj cov tsis zoo nested sawv daws yuav, thiab txawm ib tug ntawm tib lub npe. Piv txwv li, koj tuaj yeem sab hauv CTE A kom ua CTE X, thiab nyob rau tib theem sab hauv CTE B ua dua CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...Thaum sib piv, koj yuav tsum nkag siab qhov no. Nkag siab qhov no "nrog koj ob lub qhov muag" - txawm pom txoj kev npaj, txawm tias pom lub cev ntawm qhov kev thov - yog qhov nyuaj heev. Yog tias koj CTE tiam neeg nyuaj, zes, thiab cov kev thov loj, ces nws tsis nco qab kiag li.
UNION
Yog tias peb muaj lo lus tseem ceeb hauv cov lus nug UNION [ALL] (tus neeg teb xov tooj ntawm kev koom nrog ob qho qauv), tom qab ntawd hauv txoj kev npaj nws sib raug rau ob lub node Append, los yog ib co Recursive Union.
Qhov uas yog "sab saum toj" saum toj no UNION - qhov no yog thawj xeeb leej xeeb ntxwv ntawm peb cov node, uas yog "hauv qab" - thib ob. Yog dhau UNION peb muaj ob peb blocks "glued" ib zaug, ces Append- tseem yuav muaj ib leeg xwb, tab sis nws yuav tsis muaj ob, tab sis ntau tus me nyuam - raws li lawv mus, raws li:
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
Teeb meem nrog lub hnub qub: nyob rau hauv recursive sampling tiam (WITH RECURSIVE) kuj tuaj yeem ua tau ntau tshaj ib UNION. Tab sis tsuas yog qhov kawg block tom qab qhov kawg ib txwm rov ua dua UNION. Txhua yam saum toj no yog ib qho, tab sis txawv UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, ΡΡΡ ΠΊΠΎΠ½ΡΠ°Π΅ΡΡΡ Π³Π΅Π½Π΅ΡΠ°ΡΠΈΡ ΡΡΠ°ΡΡΠΎΠ²ΠΎΠ³ΠΎ ΡΠΎΡΡΠΎΡΠ½ΠΈΡ ΡΠ΅ΠΊΡΡΡΠΈΠΈ
UNION ALL
(...) -- #3, ΡΠΎΠ»ΡΠΊΠΎ ΡΡΠΎΡ Π±Π»ΠΎΠΊ ΡΠ΅ΠΊΡΡΡΠΈΠ²Π½ΡΠΉ ΠΈ ΠΌΠΎΠΆΠ΅Ρ ΡΠΎΠ΄Π΅ΡΠΆΠ°ΡΡ ΠΎΠ±ΡΠ°ΡΠ΅Π½ΠΈΠ΅ ΠΊ T
)
...
Koj kuj yuav tsum muaj peev xwm "tso tawm" cov piv txwv zoo li no. Hauv qhov piv txwv no peb pom tias UNION-muaj 3 ntu hauv peb qhov kev thov. Raws li, ib UNION ΡΠΎΠΎΡΠ²Π΅ΡΡΡΠ²ΡΠ΅Ρ Append-node, thiab rau lwm yam - Recursive Union.
Nyeem-sau cov ntaub ntawv
Txhua yam yog nteg tawm, tam sim no peb paub tias daim ntawv thov twg cuam tshuam rau daim phiaj xwm twg. Thiab hauv cov ntawv no peb tuaj yeem yooj yim thiab ib txwm pom cov khoom uas "nyeem tau".
Los ntawm cov lus nug ntawm qhov pom, peb tsis paub seb nws puas yog lub rooj lossis CTE, tab sis lawv raug xaiv los ntawm tib lub node RangeVar. Thiab nyob rau hauv cov nqe lus ntawm "readability", qhov no kuj yog ib qho kev txwv ntawm cov 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]
Peb paub cov qauv ntawm cov phiaj xwm thiab cov lus nug, peb paub cov ntawv xov xwm ntawm cov blocks, peb paub cov npe ntawm cov khoom - peb ua ib qho kev sib piv.
Ib zaug ntxiv ua haujlwm "nrog lub hnub qub". Peb muab qhov kev thov, ua nws, peb tsis muaj npe - peb tsuas yog nyeem ob zaug los ntawm tib CTE.
Peb saib ntawm txoj kev npaj - qhov teeb meem yog dab tsi? Vim li cas peb thiaj muaj npe? Peb tsis tau xaj nws. Nws puas tau txais "tus lej xov tooj" zoo li no?
PostgreSQL ntxiv nws tus kheej. Koj tsuas yog yuav tsum nkag siab qhov ntawd tsuas yog ib lub npe xwb rau peb, rau lub hom phiaj ntawm kev sib piv nrog rau txoj kev npaj, nws tsis muaj kev nkag siab, nws tsuas yog ntxiv ntawm no. Cia peb tsis txhob mloog nws.
Qhov thib ob ua haujlwm "nrog lub hnub qub": Yog tias peb nyeem los ntawm lub rooj sib faib, ces peb yuav tau txais cov node Append los yog Merge Append, uas yuav muaj ntau tus "cov menyuam yaus", thiab txhua tus yuav ua li cas Scan'om los ntawm lub rooj-section: Seq Scan, Bitmap Heap Scan los yog Index Scan. Tab sis, txawm li cas los xij, cov "cov menyuam yaus" no yuav tsis yog cov lus nug nyuaj - qhov no yog li cas cov nodes tuaj yeem txawv ntawm Append ntawm UNION.
Peb kuj nkag siab txog cov pob caus ntawd, sau lawv βhauv ib pawgβ thiab hais tias: βtxhua yam koj nyeem los ntawm megatable yog ntawm no thiab nqes tsob ntoo".
"Yooj yim" cov ntaub ntawv txais cov nodes
Values Scan sib raug rau hauv txoj kev npaj VALUES hauv kev thov.
Result yog kev thov tsis muaj FROM nyiam SELECT 1. Los yog thaum koj txhob txwm tshaj tawm tsis tseeb nyob rau hauv WHERE-block (ces tus cwj pwm tshwm 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 "daim ntawv qhia" rau SRFs ntawm tib lub npe.
Tab sis nrog cov lus nug nested txhua yam yog qhov nyuaj dua - hmoov tsis, lawv tsis tas yuav tig mus rau hauv InitPlan/SubPlan. Qee zaum lawv tig mus rau hauv ... Join los yog ... Anti Join, tshwj xeeb tshaj yog thaum koj sau ib yam dab tsi zoo li WHERE NOT EXISTS .... Thiab ntawm no nws tsis yog ib txwm ua tau los ua ke lawv - nyob rau hauv cov ntawv nyeem ntawm txoj kev npaj tsis muaj cov neeg ua haujlwm sib raug rau cov nodes ntawm txoj kev npaj.
Ib zaug ntxiv ua haujlwm "nrog lub hnub qub": ib co VALUES hauv kev thov. Nyob rau hauv cov ntaub ntawv no thiab nyob rau hauv txoj kev npaj koj yuav tau txais ob peb nodes Values Scan.
"Numbered" suffixes yuav pab kom paub qhov txawv ntawm ib leeg - lawv raug ntxiv raws nraim nyob rau hauv qhov kev txiav txim nyob rau hauv uas coj sawv daws yuav pom. VALUES-blocks raws qhov kev thov los ntawm sab saum toj mus rau hauv qab.
Cov ntaub ntawv ua
Nws zoo li txhua yam hauv peb qhov kev thov tau raug txheeb xyuas - txhua yam uas tseem tshuav yog Limit.
Tab sis ntawm no txhua yam yog yooj yim - xws li nodes li Limit, Sort, Aggregate, WindowAgg, Unique "daim ntawv qhia" ib-rau-ib rau cov neeg ua haujlwm sib txuas hauv qhov kev thov, yog tias lawv nyob ntawd. Tsis muaj "hnub qub" lossis teeb meem ntawm no.
SIB THAM
Teeb meem tshwm sim thaum peb xav ua ke JOIN nruab nrab ntawm lawv tus kheej. Qhov no tsis yog ib txwm ua tau, tab sis nws yog ua tau.
Los ntawm cov lus nug parser lub ntsiab lus ntawm kev pom, peb muaj qhov node JoinExpr, uas muaj raws nraim ob tug me nyuam - sab laug thiab sab xis. Qhov no, raws li, yog qhov "saum toj no" koj JOIN thiab sau dab tsi "hauv qab" nws hauv kev thov.
Thiab los ntawm qhov pom ntawm txoj kev npaj, cov no yog ob tug xeeb leej xeeb ntxwv ntawm qee leej * Loop/* Join- node. Nested Loop, Hash Anti Join, ... - ib yam li ntawd.
Cia peb siv cov laj thawj yooj yim: yog tias peb muaj cov rooj A thiab B uas "sib koom" ib leeg hauv txoj kev npaj, tom qab ntawd hauv kev thov lawv tuaj yeem nyob ib leeg. A-JOIN-B, lossis B-JOIN-A. Wb sim ua ke li no, cia wb sim ua ke ua lwm yam, thiab mus txog thaum peb khiav tawm ntawm cov khub zoo li no.
Wb coj peb cov ntoo syntax, coj peb txoj kev npaj, saib lawv... tsis zoo li!
Cia peb rov kos nws hauv daim duab - huag, nws twb zoo li ib yam dab tsi!
Cia peb nco ntsoov tias peb muaj cov nodes uas ib txhij muaj me nyuam B thiab C - peb tsis quav ntsej txog qhov kev txiav txim. Cia peb muab lawv thiab tig daim duab ntawm node dhau.
Wb rov los saib dua. Tam sim no peb muaj cov nodes nrog cov me nyuam A thiab khub (B + C) - sib xws nrog lawv ib yam nkaus.
Zoo heev! Nws hloov tawm tias peb yog ob tus no JOIN los ntawm kev thov nrog cov phiaj xwm nodes tau ua tiav ua ke.
Alas, qhov teeb meem no tsis yog ib txwm daws tau.
Piv txwv li, yog nyob rau hauv ib qho kev thov A JOIN B JOIN C, thiab nyob rau hauv txoj kev npaj, ua ntej ntawm tag nrho cov, "sab nrauv" nodes A thiab C tau txuas nrog, tab sis tsis muaj tus neeg teb xov tooj hauv qhov kev thov, peb tsis muaj dab tsi los qhia, tsis muaj dab tsi los txuas lus rau. Nws zoo ib yam nrog "comma" thaum koj sau A, B.
Tab sis, feem ntau, yuav luag tag nrho cov nodes tuaj yeem "kho" thiab koj tuaj yeem tau txais hom profile ntawm sab laug hauv lub sijhawm - lus, zoo li hauv Google Chrome thaum koj txheeb xyuas JavaScript code. Koj tuaj yeem pom ntev npaum li cas txhua kab thiab txhua nqe lus tau coj mus rau "ua tiav."
Thiab kom nws yooj yim dua rau koj siv tag nrho cov no, peb tau ua qhov chaw cia , qhov twg koj tuaj yeem txuag tau thiab tom qab ntawd pom koj cov phiaj xwm nrog rau kev thov cuam tshuam lossis qhia qhov txuas nrog lwm tus.
Yog tias koj tsuas yog xav nqa cov lus nug uas tsis tuaj yeem nyeem rau hauv daim ntawv txaus, siv .
Tau qhov twg los: www.hab.com