PostgreSQL විමසුම් පැතිකඩ: සැලැස්ම සහ විමසුමට ගැලපෙන ආකාරය

දැනටමත් භාවිතා කරන බොහෝ දෙනෙක් පැහැදිලි කරන්න.tensor.ru - අපගේ PostgreSQL සැලසුම් දෘශ්‍යකරණ සේවාව එහි එක් සුපිරි බලයක් ගැන නොදන්නවා විය හැක - කියවීමට අපහසු සේවාදායක ලොගයක් හැරවීම...

... අනුරූප සැලසුම් නෝඩ් සඳහා සන්දර්භාත්මක ඉඟි සහිත අලංකාර ලෙස නිර්මාණය කර ඇති විමසුමකට:

ඔහුගේ දෙවන කොටසේ මෙම පිටපතෙහි PGConf.Russia 2020 හි වාර්තාව අපි මෙය කළ ආකාරය මම ඔබට කියමි.

සාමාන්‍ය විමසුම් කාර්ය සාධන ගැටළු සහ ඒවායේ විසඳුම් සඳහා කැප වූ පළමු කොටසේ පිටපත ලිපියෙන් සොයාගත හැකිය "රෝගී SQL විමසුම් සඳහා වට්ටෝරු".

පළමුව, අපි වර්ණ ගැන්වීම ආරම්භ කරමු - සහ අපි තවදුරටත් සැලැස්ම වර්ණ ගන්වන්නේ නැත, අපි දැනටමත් එය වර්ණ ගන්වා ඇත, අපට දැනටමත් එය ලස්සන හා තේරුම් ගත හැකි නමුත් ඉල්ලීමක් ඇත.

එවැනි ආකෘතිකරණය නොකළ “පත්‍රයක්” සමඟ ලොගයෙන් ඇද ගන්නා ලද ඉල්ලීම ඉතා කැත සහ එබැවින් අපහසු බව අපට පෙනුණි.


විශේෂයෙන් සංවර්ධකයින් කේතයේ ඇති ඉල්ලීමේ ශරීරය “මැලියම්” කරන විට (මෙය ඇත්ත වශයෙන්ම ප්‍රති රටාවකි, නමුත් එය සිදු වේ) එක් පේළියකින්. බිහිසුණු!

අපි මේක කොහොම හරි ලස්සනට අඳිමු.


අපට මෙය අලංකාර ලෙස ඇඳීමට හැකි නම්, එනම්, ඉල්ලීමේ ශරීරය විසුරුවා හැර නැවත එකට තැබිය හැකි නම්, එවිට අපට මෙම ඉල්ලීමේ එක් එක් වස්තුවට ඉඟියක් “ඇමිණීමට” හැකිය - සැලැස්මේ අනුරූප ස්ථානයේ සිදු වූ දේ.

විමසුම් සින්ටැක්ස් ගස

මෙය සිදු කිරීම සඳහා, ඉල්ලීම පළමුව විග්‍රහ කළ යුතුය.


මොකද අපිට තියෙනවා පද්ධතියේ හරය NodeJS මත ධාවනය වේ, ඊට පස්සේ අපි ඒකට මොඩියුලයක් හැදුවා, ඔයාට පුළුවන් GitHub මත එය සොයා ගන්න. ඇත්ත වශයෙන්ම, මේවා PostgreSQL විග්‍රහයේම අභ්‍යන්තරයට “බන්ධන” දිගු කරයි. එනම් ව්‍යාකරණ සරලව ද්විමය සම්පාදනය කර එයට NodeJS වෙතින් බැඳීම් සිදු කරයි. අපි අන් අයගේ මොඩියුලයන් පදනමක් ලෙස ගත්තෙමු - මෙහි විශාල රහසක් නොමැත.

අපි අපගේ කාර්යයට ආදානය ලෙස ඉල්ලීමේ ශරීරය පෝෂණය කරමු - ප්‍රතිදානයේදී අපට JSON වස්තුවක ස්වරූපයෙන් විග්‍රහ කළ සින්ටැක්ස් ගසක් ලැබේ.


දැන් අපට මෙම ගස හරහා ප්‍රතිවිරුද්ධ දිශාවට දිව ගොස් අපට අවශ්‍ය ඉන්ඩෙන්ට්, වර්ණ ගැන්වීම සහ හැඩතල ගැන්වීම සමඟ ඉල්ලීමක් එක්රැස් කළ හැකිය. නැත, මෙය අභිරුචිකරණය කළ නොහැකි නමුත් මෙය පහසු වනු ඇති බව අපට පෙනී ගියේය.

විමසුම සහ සැලසුම් නෝඩ් සිතියම්ගත කිරීම

දැන් අපි බලමු අපි පළමු පියවරේදී විශ්ලේෂණය කළ සැලැස්ම සහ දෙවන පියවරේදී විශ්ලේෂණය කළ විමසුම ඒකාබද්ධ කරන්නේ කෙසේද කියා.

අපි සරල උදාහරණයක් ගනිමු - CTE එකක් ජනනය කර එයින් දෙවරක් කියවන විමසුමක් අප සතුව ඇත. ඔහු එවැනි සැලැස්මක් ජනනය කරයි.

CTE

ඔබ එය ප්‍රවේශමෙන් බැලුවහොත්, 12 වන අනුවාදය දක්වා (හෝ එහි මූල පදයෙන් ආරම්භ වේ MATERIALIZED) පිහිටුවීම CTE යනු සැලසුම්කරුට නිරපේක්ෂ බාධකයකි.


මෙයින් අදහස් කරන්නේ අපි ඉල්ලීමේ කොතැනක හෝ CTE පරම්පරාවක් සහ සැලැස්මේ කොතැනක හෝ නෝඩයක් දුටුවහොත් බවයි CTE, එවිට මෙම නෝඩ් අනිවාර්යයෙන්ම එකිනෙකා සමඟ "සටන්", අපි වහාම ඒවා ඒකාබද්ධ කළ හැකිය.

තරු ලකුණක් සමඟ ගැටළුවක්: CTEs කූඩු කළ හැක.

ඉතා දුර්වල ලෙස කැදලි ඇති අතර, එම නමම ඇති ඒවා පවා ඇත. උදාහරණයක් ලෙස, ඔබට ඇතුළත හැක CTE A සෑදීමට CTE X, සහ ඇතුළත එකම මට්ටමේ CTE B එය නැවත කරන්න CTE X:

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

සංසන්දනය කිරීමේදී, ඔබ මෙය තේරුම් ගත යුතුය. මෙය “ඔබේ ඇස්වලින්” තේරුම් ගැනීම - සැලැස්ම දැකීම පවා, ඉල්ලීමේ ශරීරය දැකීම පවා - ඉතා අපහසුය. ඔබේ CTE පරම්පරාව සංකීර්ණ, කැදලි, සහ ඉල්ලීම් විශාල නම්, එය සම්පූර්ණයෙන්ම සිහිසුන් ය.

යුනියන්

විමසුමේ අපට මූල පදයක් තිබේ නම් UNION [ALL] (සාම්පල දෙකක් සම්බන්ධ කිරීමේ ක්‍රියාකරු), පසුව සැලැස්මේ එය නෝඩයකට අනුරූප වේ Append, හෝ සමහරක් Recursive Union.


ඉහත "ඉහළ" ඇති දේ UNION - මෙය අපගේ නෝඩයේ පළමු පරම්පරාවයි, එය “පහළ” - දෙවැන්න. හරහා නම් UNION අපට එකවර "ඇලවූ" කුට්ටි කිහිපයක් තිබේ, එවිට Append- තවමත් ඇත්තේ එක් නෝඩයක් පමණි, නමුත් එයට දෙදෙනෙකු නොව බොහෝ දරුවන් සිටිනු ඇත - ඔවුන් යන පිළිවෙලට පිළිවෙලින්:

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

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

තරු ලකුණක් සමඟ ගැටළුවක්: ඇතුළත පුනරාවර්තන නියැදි උත්පාදනය (WITH RECURSIVE) එකකට වඩා වැඩි විය හැක UNION. නමුත් සෑම විටම පුනරාවර්තී වන්නේ අවසාන කොටසෙන් පසුව ඇති අවසාන කොටස පමණි UNION. ඉහත සියල්ල එකකි, නමුත් වෙනස් ය UNION:

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

එවැනි උදාහරණ "ඉවත් කිරීමට" ඔබට හැකි විය යුතුය. මෙම උදාහරණයේදී අපට එය පෙනේ UNION-අපගේ ඉල්ලීමෙහි කොටස් 3 ක් තිබුණි. ඒ අනුව එක් UNION අනුරූප වේ Append-node, සහ අනෙකට - Recursive Union.

දත්ත කියවීම-ලිවීම

සෑම දෙයක්ම සකස් කර ඇත, දැන් අපි දන්නවා කුමන ඉල්ලීමේ කොටස සැලැස්මේ කුමන කොටසට අනුරූප වේ. තවද මෙම කොටස් තුළ අපට පහසුවෙන් සහ ස්වභාවිකව "කියවිය හැකි" එම වස්තූන් සොයාගත හැකිය.

විමසුම් දෘෂ්ටි කෝණයකින්, එය මේසයක් හෝ CTE එකක් දැයි අපි නොදනිමු, නමුත් ඒවා එකම නෝඩයකින් නම් කර ඇත. RangeVar. “කියවීමේ හැකියාව” අනුව, මෙය තරමක් සීමිත නෝඩ් කට්ටලයකි:

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

සැලැස්මේ ව්‍යුහය සහ විමසුම අපි දනිමු, බ්ලොක් වල ලිපි හුවමාරුව අපි දනිමු, වස්තූන්ගේ නම් අපි දනිමු - අපි එකින් එක සැසඳීමක් කරන්නෙමු.


නැවතත් කාර්යය "තරු ලකුණක් සහිතව". අපි ඉල්ලීම භාර ගනිමු, එය ක්‍රියාත්මක කරන්න, අපට අන්වර්ථ කිසිවක් නැත - අපි එය එකම CTE වෙතින් දෙවරක් කියවා ඇත්තෙමු.


අපි සැලැස්ම දෙස බලමු - ගැටලුව කුමක්ද? අපට අන්වර්ථ නාමයක් තිබුණේ ඇයි? අපි එය ඇණවුම් කළේ නැහැ. ඔහු එවැනි "අංක අංකයක්" ලබා ගන්නේ කොහෙන්ද?

PostgreSQL එයම එකතු කරයි. ඔබ තේරුම් ගත යුත්තේ එය පමණි එවැනි අන්වර්ථයක් පමණි අප සඳහා, සැලැස්ම සමඟ සැසඳීමේ අරමුණු සඳහා, එය කිසිදු තේරුමක් නැත, එය සරලව මෙහි එකතු කර ඇත. අපි ඔහු කෙරෙහි අවධානය යොමු නොකරමු.

දෙවැන්න කාර්යය "තරු ලකුණක් සහිතව": අපි කොටස් කරන ලද වගුවකින් කියවන්නේ නම්, එවිට අපට node එකක් ලැබෙනු ඇත Append හෝ Merge Append, "ළමයින්" විශාල සංඛ්යාවක් සමන්විත වනු ඇත, සහ එක් එක් කෙසේ හෝ වනු ඇත Scan'ඕම් වගු කොටසෙන්: Seq Scan, Bitmap Heap Scan හෝ Index Scan. එහෙත්, ඕනෑම අවස්ථාවක, මෙම "දරුවන්" සංකීර්ණ විමසීම් නොවනු ඇත - මෙම නෝඩ් වෙන්කර හඳුනාගත හැක්කේ එලෙස ය Append දී UNION.


අපි ද එවැනි ගැට තේරුම් ගෙන, ඒවා “එක ගොඩකට” එකතු කර මෙසේ කියමු.ඔබ මෙගාටබල් වලින් කියවන සියල්ල මෙහි සහ ගස යට ඇත".

"සරල" දත්ත ලබා ගන්නා නෝඩ්

Values Scan සැලැස්මට අනුරූප වේ VALUES ඉල්ලීම තුළ.

Result නැති ඉල්ලීමකි FROM වර්ග කිරීම SELECT 1. නැතහොත් ඔබට හිතාමතාම අසත්‍ය ප්‍රකාශනයක් ඇති විට WHERE-block (එවිට ගුණාංගය දිස්වේ 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 "සිතියම" එකම නමේ SRF වෙත.

නමුත් කැදලි විමසුම් සමඟ සෑම දෙයක්ම වඩාත් සංකීර්ණ වේ - අවාසනාවකට මෙන්, ඒවා සෑම විටම හැරෙන්නේ නැත InitPlan/SubPlan. සමහර විට ඒවා හැරෙනවා ... Join හෝ ... Anti Join, විශේෂයෙන් ඔබ එවැනි දෙයක් ලියන විට WHERE NOT EXISTS .... තවද මෙහි ඒවා ඒකාබද්ධ කිරීම සැමවිටම කළ නොහැක - සැලැස්මේ පෙළෙහි සැලැස්මේ නෝඩ් වලට අනුරූප ක්‍රියාකරුවන් නොමැත.

නැවතත් කාර්යය "තරු ලකුණක් සහිතව": ඇතැම් VALUES ඉල්ලීම තුළ. මෙම අවස්ථාවේදී සහ සැලැස්ම තුළ ඔබට නෝඩ් කිහිපයක් ලැබෙනු ඇත Values Scan.


“සංඛ්‍යාගත” උපසර්ග ඒවා එකිනෙක වෙන්කර හඳුනා ගැනීමට උපකාරී වනු ඇත - ඒවා හරියටම අනුරූප වන අනුපිළිවෙලට එකතු කරනු ලැබේ. VALUES-ඉහළ සිට පහළට ඉල්ලීම දිගේ අවහිර කරයි.

දත්ත සැකසීම

අපගේ ඉල්ලීමෙහි ඇති සියල්ල නිරාකරණය කර ඇති බව පෙනේ - ඉතිරිව ඇත්තේ එය පමණි Limit.


නමුත් මෙහි සෑම දෙයක්ම සරලයි - එවැනි නෝඩ් Limit, Sort, Aggregate, WindowAgg, Unique "සිතියම" එකින් එක ඉල්ලීමෙහි අනුරූප ක්‍රියාකරුවන් සිටී නම්, ඔවුන්ට. මෙහි "තරු" හෝ දුෂ්කරතා නොමැත.

JOIN

අපට ඒකාබද්ධ වීමට අවශ්‍ය වූ විට දුෂ්කරතා ඇති වේ JOIN තමන් අතර. මෙය සැමවිටම කළ නොහැකි නමුත් එය කළ හැකි ය.


විමසුම් විග්‍රහ කරන්නාගේ දෘෂ්ටි කෝණයෙන්, අපට නෝඩයක් ඇත JoinExpr, හරියටම දරුවන් දෙදෙනෙකු සිටින - වම සහ දකුණ. මෙය, ඒ අනුව, ඔබගේ සම්බන්ධ වීමට "ඉහළ" ඇති දේ සහ ඉල්ලීමෙහි "පහළ" ලියා ඇති දේ වේ.

සැලැස්මේ දෘෂ්ටි කෝණයෙන්, මේ සමහරුන්ගෙන් පැවත එන්නන් දෙදෙනෙක් * Loop/* Join-නෝඩය. Nested Loop, Hash Anti Join,... - ඒ වගේ දෙයක්.

අපි සරල තර්කනය භාවිතා කරමු: සැලැස්ම තුළ එකිනෙකාට "එකතු" වන A සහ ​​B වගු අප සතුව තිබේ නම්, ඉල්ලීමෙහි ඒවා ස්ථානගත කළ හැකිය. A-JOIN-B, හෝ B-JOIN-A. අපි මේ ආකාරයෙන් ඒකාබද්ධ කිරීමට උත්සාහ කරමු, අපි වෙනත් ආකාරයකින් ඒකාබද්ධ කිරීමට උත්සාහ කරමු, සහ එවැනි යුගල අවසන් වන තුරු.

අපි අපේ සින්ටැක්ස් ගස ගනිමු, අපගේ සැලැස්ම ගනිමු, ඒවා දෙස බලමු ... සමාන නොවේ!


අපි එය ප්‍රස්ථාර ආකාරයෙන් නැවත අඳිමු - ඔහ්, එය දැනටමත් යමක් මෙන් පෙනේ!


අපට එකවර B සහ C දරුවන් සිටින නෝඩ් ඇති බව අපි සටහන් කරමු - අපි කුමන අනුපිළිවෙලකට කමක් නැත. අපි ඒවා ඒකාබද්ධ කර නෝඩයේ පින්තූරය පෙරළමු.


අපි නැවත බලමු. දැන් අපට දරුවන් A සහ ​​යුගල (B + C) සමඟ නෝඩ් ඇත - ඔවුන් සමඟ ද අනුකූල වේ.


මහා! අපි මේ දෙන්නා බව පෙනී යනවා JOIN සැලැස්ම නෝඩ් සමඟ ඉල්ලීමෙන් සාර්ථකව ඒකාබද්ධ විය.

අහෝ, මෙම ගැටළුව සැමවිටම විසඳන්නේ නැත.


උදාහරණයක් ලෙස, ඉල්ලීමක නම් A JOIN B JOIN C, සහ සැලැස්ම තුළ, පළමුවෙන්ම, "පිටත" නෝඩ් A සහ ​​C සම්බන්ධ විය.නමුත් ඉල්ලීමෙහි එවැනි ක්රියාකරුවෙකු නොමැත, අපට ඉස්මතු කිරීමට කිසිවක් නැත, ඉඟියක් ඇමිණිය යුතු කිසිවක් නැත. ඔබ ලියන විට "කොමාව" ද එසේමය A, B.

නමුත්, බොහෝ අවස්ථාවන්හීදී, සියලුම නෝඩ් පාහේ “බැඳ” කළ හැකි අතර ඔබට නියමිත වේලාවට වම් පසින් මෙවැනි පැතිකඩක් ලබා ගත හැකිය - වචනාර්ථයෙන්, ඔබ ජාවාස්ක්‍රිප්ට් කේතය විශ්ලේෂණය කරන විට ගූගල් ක්‍රෝම් හි මෙන්. එක් එක් පේළිය සහ එක් එක් ප්‍රකාශය "ක්‍රියාත්මක කිරීමට" කොපමණ කාලයක් ගතවේද යන්න ඔබට දැක ගත හැක.


ඔබට මේ සියල්ල භාවිතා කිරීම වඩාත් පහසු කිරීම සඳහා, අපි ගබඩා කර ඇත සංරක්ෂිතය, ඔබට ආශ්‍රිත ඉල්ලීම් සමඟ ඔබේ සැලසුම් සුරැකීමට සහ පසුව සොයා ගැනීමට හෝ සබැඳිය යමෙකු සමඟ බෙදා ගැනීමට හැකි තැන.

ඔබට කියවිය නොහැකි විමසුමක් ප්‍රමාණවත් පෝරමයකට ගෙන ඒමට අවශ්‍ය නම්, භාවිතා කරන්න අපගේ "සාමාන්යකරණය".

මූලාශ්රය: www.habr.com