🥇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نوډ، او نورو ته - 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 دا پخپله اضافه کوي. تاسو یوازې د دې پوهیدلو ته اړتیا لرئ یوازې داسې یو عرف زموږ لپاره، د پلان سره د پرتله کولو موخو لپاره، دا هیڅ معنی نلري، دا په ساده ډول دلته اضافه شوی. راځئ چې هغه ته پام ونه کړو.

دوهم دنده "د ستوري سره": که موږ د ویشل شوي میز څخه لوستل کوو، نو موږ به یو نوډ ترلاسه کړو Append او یا Merge Append، کوم چې به د لوی شمیر "ماشومانو" څخه جوړ وي، او هر یو به یو څه وي Scanد جدول له برخې څخه: Seq Scan, Bitmap Heap Scan او یا Index Scan. مګر، په هر حالت کې، دا "ماشومان" به پیچلې پوښتنې نه وي - دا څنګه دا نوډونه توپیر کیدی شي. Append په UNION.

موږ دا ډول غوټۍ هم پوهیږو، دوی "په یوه ټوټه کې" راټولوو او وایو: "هرڅه چې تاسو له میګاټیبل څخه لوستلی دلته او د ونې لاندې دي".

"ساده" د معلوماتو ترلاسه کولو نوډونه

Values Scan په پلان کې مطابقت لري VALUES په غوښتنه کې.

Result پرته غوښتنه ده FROM د ډول ډول SELECT 1. یا کله چې تاسو په قصدي ډول غلط بیان لرئ WHERE- بلاک (بیا صفت څرګندیږي 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 د ورته نوم SRFs ته "نقشه"

مګر د ځړول شوي پوښتنو سره هرڅه خورا پیچلي دي - له بده مرغه ، دوی تل نه بدلیږي 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

د PostgreSQL پوښتنې پروفایلر: څنګه د پلان او پوښتنې سره سمون خوري