ایا تاسو په یاد ولرئ چې دا ټول څنګه پیل شوي. هر څه د لومړي ځل لپاره وو او بیا بیا

د دې په اړه چې څنګه ما د PostgreSQL پوښتنې اصلاح کړې او له دې ټولو څخه څه راوتلي.
ته ولې اړ وې؟ هو، ځکه چې د تیرو 4 کلونو لپاره هرڅه په خاموشۍ سره کار کوي، په آرامۍ سره، لکه د ساعت ټکي.
د یوې نسخې په توګه.

د واقعي پیښو پر بنسټ.
ټول نومونه بدل شوي، تصادفي دي.

کله چې تاسو یوه ټاکلې پایله ترلاسه کړئ، دا تل په زړه پورې ده چې په یاد ولرئ چې د پیل لپاره محرک څه و، چیرته چې دا ټول پیل شوي.

نو، په پایله کې څه پیښ شوي په لنډه توګه په مقاله کې تشریح شوي "ترکیب د PostgreSQL فعالیت ښه کولو لپاره د یوې میتود په توګه".

دا به شاید په زړه پوري وي چې د تیرو پیښو سلسله بیا جوړه کړئ.
تاریخ د پیل دقیق نیټه خوندي کړې - 2018-09-10 18:02:48.
همدارنګه، په کیسه کې یوه غوښتنه شتون لري چې له هغې څخه دا ټول پیل شوي:
د ستونزې غوښتنهغوره کول
p. "PARAMETER_ID" د parameter_id په توګه،
pd."PD_NAME" AS pd_name،
pd."CUSTOMER_PARTNUMBER" د پیرودونکي_شریک شمیره،
w"LRM" AS LRM،
w. "LOTID" لکه لوټید،
w. "RTD_VALUE" د RTD_value په توګه،
w. "LOWER_SPEC_LIMIT" لکه د ټیټ_ځانګړي_حد،
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit،
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. "SPENT_DATE" AS spent_date,
استخراج (د "SPENT_DATE" څخه کال) AS کال،
استخراج (د "SPENT_DATE" څخه میاشت) د میاشتې په توګه،
s."REPORT_NAME" AS راپور_نوم،
p."STPM_NAME" AS stpm_name،
p. "CUSTOMERPARAM_NAME" د پیرودونکي پارام_نوم په توګه
د wdata w څخه
لګول
pmtr p،
spent_pd sp,
pd pd
WHERE s. "SPENT_ID" = w. "SPENT_ID"
او p."PARAMETER_ID" = w."PARAMETER_ID"
او "SPENT_ID" = sp. "SPENT_ID"
او pd."PD_ID" = sp."PD_ID"
او "SPENT_DATE" >= '2018-07-01' او s. "SPENT_DATE" <= '2018-09-30'
او s. "SPENT_DATE" = (MAX وټاکئ(s2. "SPENT_DATE")
له s2 مصرف شوي،
wdata w2
WHERE s2. "SPENT_ID" = w2. "SPENT_ID"
او w2. ​​"LRM" = w. "LRM");

د ستونزې تشریح د اټکل وړ معیاري دی - "هر څه خراب دي. راته ووایه چې ستونزه څه ده.»
ما سمدلاسه د درې نیم انچ ډرایو د وخت څخه یوه کیسه را په یاد کړه:

لیمر هیکر ته راځي.
- زما لپاره هیڅ کار نه کوي، ما ته ووایه چې ستونزه چیرته ده.
- په DNA کې ...

مګر البته، دا د فعالیت پیښو حل کولو لاره نه ده. "دوی ممکن موږ نه پوهیږي"(سره). موږ باید دا معلومه کړو.
ښه، راځئ چې کیندل. شاید یو څه به د پایلې په توګه راټول شي.

تحقیقات پیل شول

نو، هغه څه چې سمدلاسه د ناڅاپه سترګو سره لیدل کیدی شي، پرته له دې چې تشریح کولو ته لاره هواره کړي.
1) یوځای کیدل نه کارول کیږي. دا بد دی، په ځانګړې توګه که د اړیکو شمیر له یو څخه ډیر وي.
2) مګر هغه څه چې تر دې هم بد دي د اړونده فرعي پوښتنو سره، سربیره پردې، د راټولولو سره. دا ډیر بد دی.
البته دا بد دی. مګر دا یوازې په یوه اړخ کې دی. له بلې خوا، دا خورا ښه دی، ځکه چې ستونزه په روښانه توګه حل او غوښتنه لري چې ښه کیدی شي.
بختور (ج) ته مه ځئ.
د پوښتنې پلان دومره پیچلی ندی، مګر دا خورا اشاره ده:
د اجرا کولو پلان

ترټولو زړه پورې او ګټور، د معمول په څیر، په پیل او پای کې دی.
نیست شوی لوپ (لګښت = 935.84..479763226.18 قطار = 3322 عرض = 135) (حقیقي وخت = 31.536..8220420.295 قطار = 8111656 لوپس = 1)
د پلان کولو وخت: 3.807 ms
د اجرا کولو وخت: 8222351.640 ms
د بشپړولو وخت د 2 ساعتونو څخه ډیر دی.

غلط فرضیه چې وخت نیسي

فرضیه 1 - اصلاح کوونکی غلطي کوي او غلط پلان جوړوي.

د پلي کولو پلان لیدو لپاره ، موږ به سایټ وکاروو https://explain.depesz.com/. په هرصورت، سایټ هیڅ په زړه پورې یا ګټور ندی ښودلی. په لومړي او دویم نظر کې، هیڅ شی شتون نلري چې واقعیا مرسته وکړي. ایا دا امکان لري چې بشپړ سکین لږترلږه وي. وړاندې ځه.

فرضیه 2-د اوټوواکوم اړخ څخه په بیس باندې تاثیر ، تاسو اړتیا لرئ د بریکونو څخه ځان خلاص کړئ.

مګر د آټوواکوم ډیمونز ښه چلند کوي ، هیڅ اوږده ځړول پروسې شتون نلري. هیڅ جدي بار نشته. موږ باید د بل څه په لټه کې شو.

فرضیه 3 - احصایې پخوانۍ دي، هرڅه باید بیا محاسبه شي

بیا بیا، دا نه. احصایې تازه دي. کوم چې د آټوواکوم سره د ستونزو نشتوالي ته په پام سره ، د حیرانتیا خبره نده.

راځئ چې اصلاح پیل کړو

اصلي جدول 'wdata' یقینا کوچنی ندی ، نږدې 3 ملیون ریکارډونه.
او دا دا جدول دی چې بشپړ سکین تعقیبوي.

د هش کنډ: ((w."SPENT_ID" = s."SPENT_ID") او ((SubPlan 1) = s."SPENT_DATE"))
-> سیق سکین په wdata w (لګښت = 0.00..574151.49 قطار = 26886249 عرض = 46) (حقیقي وخت = 0.005.. 8153.565 قطار = 26873950 لوپس = 1)
موږ معیاري کار کوو: "راځئ چې یو شاخص جوړ کړو او هرڅه به الوتنه وکړي."
په "SPENT_ID" ساحه کې یو شاخص جوړ کړ
په پایله کښې:
د شاخص په کارولو سره د پلي کولو پلان پوښتنه وکړئ

ښه، دا مرسته وکړه؟
وو: 8 222 351.640ms (له 2 ساعتونو څخه لږ څه)
دا شوه: 6 985 431.575 ms (نږدې 2 ساعته)
په عموم کې، ورته مڼې، د غاړې لید.
راځئ چې کلاسیک یاد کړو:
"ایا تاسو ورته لرئ، مګر پرته له وزرونو؟ لټوو".

په اصل کې، دا کیدای شي ښه پایله وبلل شي، ښه، ښه نه، مګر د منلو وړ. لږترلږه، پیرودونکي ته یو لوی راپور چمتو کړئ چې تشریح کوي چې څومره ترسره شوي او ولې څه ترسره شوي ښه وو.
خو بیا هم وروستۍ پرېکړه لا ډېره لرې ده. ډیر لرې.

او اوس ترټولو په زړه پوری شی - موږ اصلاح کولو ته دوام ورکوو، موږ به غوښتنه پالش کړو

لومړی ګام - یوځای کیدل وکاروئ

بیا لیکل شوې غوښتنه اوس داسې ښکاري (ښه لږترلږه ډیر ښکلی):
د JOIN په کارولو سره پوښتنه وکړئغوره کول
p. "PARAMETER_ID" د parameter_id په توګه،
pd."PD_NAME" AS pd_name،
pd."CUSTOMER_PARTNUMBER" د پیرودونکي_شریک شمیره،
w"LRM" AS LRM،
w. "LOTID" لکه لوټید،
w. "RTD_VALUE" د RTD_value په توګه،
w. "LOWER_SPEC_LIMIT" لکه د ټیټ_ځانګړي_حد،
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit،
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. "SPENT_DATE" AS spent_date,
استخراج (د "SPENT_DATE" څخه کال) AS کال،
استخراج (د "SPENT_DATE" څخه میاشت) د میاشتې په توګه،
s."REPORT_NAME" AS راپور_نوم،
p."STPM_NAME" AS stpm_name،
p. "CUSTOMERPARAM_NAME" د پیرودونکي پارام_نوم په توګه
د "SPENT_ID"=s." "SPENT_ID" سره د داخلي ګډون په اړه د wdata څخه
د ماسپښین دننه ګډون کول p on p. "PARAMETER_ID" = w. "PARAMETER_ID"
داخلي ګډون spent_pd sp ON s. "SPENT_ID" = sp. "SPENT_ID"
داخلي ګډون pd pd ON pd. "PD_ID" = sp. "PD_ID"
کله چې
s. “SPENT_DATE” >= '2018-07-01' او s. "SPENT_DATE" <= '2018-09-30'AND
s. "SPENT_DATE" = (MAX وټاکئ(s2. "SPENT_DATE")
د w2 د داخلی ګډون له wdata څخه s2 په w2 مصرف شوی. "SPENT_ID"=s2. "SPENT_ID"
داخلي ګډون wdata w
ON w2. "LRM" = w. "LRM" );
د پلان کولو وخت: 2.486 ms
د اجرا کولو وخت: 1223680.326 ms

نو، لومړۍ پایله.
وو: 6 ms (نږدې 985 ساعته).
دا شوه: 1 223 680.326 ms (یوازې له 20 دقیقو څخه ډیر).
ښه پایله. په اصل کې، بیا، موږ کولی شو هلته ودریږو. مګر دا ډیره زړه پورې ده، تاسو نشئ کولی ودروئ.
لپاره

دوهم ګام - د اړونده فرعي پوښتنو څخه ځان خلاص کړئ

د غوښتنې متن بدل شوی:
د اړونده فرعي پوښتنو پرتهغوره کول
p. "PARAMETER_ID" د parameter_id په توګه،
pd."PD_NAME" AS pd_name،
pd."CUSTOMER_PARTNUMBER" د پیرودونکي_شریک شمیره،
w"LRM" AS LRM،
w. "LOTID" لکه لوټید،
w. "RTD_VALUE" د RTD_value په توګه،
w. "LOWER_SPEC_LIMIT" لکه د ټیټ_ځانګړي_حد،
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit،
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. "SPENT_DATE" AS spent_date,
استخراج (د "SPENT_DATE" څخه کال) AS کال،
استخراج (د "SPENT_DATE" څخه میاشت) د میاشتې په توګه،
s."REPORT_NAME" AS راپور_نوم،
p."STPM_NAME" AS stpm_name،
p. "CUSTOMERPARAM_NAME" د پیرودونکي پارام_نوم په توګه
د داخلی ګډون په اړه د wdata څخه. "SPENT_ID" = w. "SPENT_ID"
د ماسپښین دننه ګډون کول p on p. "PARAMETER_ID" = w. "PARAMETER_ID"
داخلي ګډون spent_pd sp ON s. "SPENT_ID" = sp. "SPENT_ID"
داخلي ګډون pd pd ON pd. "PD_ID" = sp. "PD_ID"
داخلي ګډون (w2 غوره کړئ. "LRM"، MAX(s2. "SPENT_DATE")
له مصرف شوي s2 INNER سره یوځای شئ wdata w2 ON s2. "SPENT_ID" = w2. "SPENT_ID"
د W2 لخوا ګروپ. "LRM"
) md on w. "LRM" = md. "LRM"
کله چې
s."SPENT_DATE" >= '2018-07-01' او s."SPENT_DATE" <= '2018-09-30';
د پلان کولو وخت: 2.291 ms
د اجرا کولو وخت: 165021.870 ms

وو: 1 223 680.326 ms (یوازې له 20 دقیقو څخه ډیر).
دا شوه: 165 021.870 ms (یوازې په 2 دقیقو کې).
دا لا دمخه خورا ښه دی.
په هرصورت، لکه څنګه چې برتانویان وايي "مګر، تل یو مګر شتون لري" یوه پایله چې خورا ښه وي باید په اوتومات ډول شک رامینځته کړي. دلته څه ګډوډی شته.

د اړوندې فرعي پوښتنې څخه د خلاصون لپاره د پوښتنې سمولو په اړه فرضیه سمه ده. مګر تاسو اړتیا لرئ دا یو څه ټیک کړئ ترڅو وروستۍ پایله سمه وي.
د پایلې په توګه، لومړنۍ منځنۍ پایلې:
د اړونده فرعي پوښتنو پرته ایډیټ شوې پوښتنهغوره کول
p. "PARAMETER_ID" د parameter_id په توګه،
pd."PD_NAME" AS pd_name،
pd."CUSTOMER_PARTNUMBER" د پیرودونکي_شریک شمیره،
w"LRM" AS LRM،
w. "LOTID" لکه لوټید،
w. "RTD_VALUE" د RTD_value په توګه،
w. "LOWER_SPEC_LIMIT" لکه د ټیټ_ځانګړي_حد،
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit،
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. "SPENT_DATE" AS spent_date,
استخراج (کال له s. "SPENT_DATE" څخه) لکه کال،
استخراج ( میاشت له s. "SPENT_DATE" څخه) د میاشتې په توګه،
s."REPORT_NAME" AS راپور_نوم،
p."STPM_NAME" AS stpm_name،
p. "CUSTOMERPARAM_NAME" د پیرودونکي پارام_نوم په توګه
د داخلی ګډون په اړه د wdata څخه. "SPENT_ID" = w. "SPENT_ID"
د ماسپښین دننه ګډون کول p on p. "PARAMETER_ID" = w. "PARAMETER_ID"
داخلي ګډون spent_pd sp ON s. "SPENT_ID" = sp. "SPENT_ID"
داخلي ګډون pd pd ON pd. "PD_ID" = sp. "PD_ID"
داخلي ګډون ( w2 انتخاب کړئ. "LRM"، MAX(s2. "SPENT_DATE") لکه "SPENT_DATE"
له مصرف شوي s2 INNER سره یوځای شئ wdata w2 ON s2. "SPENT_ID" = w2. "SPENT_ID"
د W2 لخوا ګروپ. "LRM"
) md ON md. "SPENT_DATE" = s. "SPENT_DATE" او md. "LRM" = w. "LRM"
کله چې
s."SPENT_DATE" >= '2018-07-01' او s."SPENT_DATE" <= '2018-09-30';
د پلان کولو وخت: 3.192 ms
د اجرا کولو وخت: 208014.134 ms

نو، هغه څه چې موږ یې پای ته رسوو لومړی د منلو وړ پایله ده، کوم چې پیرودونکي ته د ښودلو لپاره شرم نه دی:
سره پیل شوی: 8 222 351.640 ms (له 2 ساعتونو څخه ډیر)
موږ وکولی شو ترلاسه کړو: 1 ms (له 223 دقیقو څخه لږ څه ډیر).
پایله (لنډمهاله): 208 014.134 ms (یوازې په 3 دقیقو کې).

عالي پایله.

نتیجه

موږ کولی شو هلته ودریږو.
خو…
اشتها د خوړلو سره راځي. هغه څوک چې روان وي هغه به په سړک کې مهارت ولري. هره پایله منځمهاله ده. ودرېد او مړ شو. وغيره.
راځئ چې اصلاح ته دوام ورکړو.
ډیر ښه نظر. په ځانګړې توګه په پام کې نیولو سره چې پیرودونکي حتی پام نه کوي. او حتی د دې لپاره په کلکه.

نو، دا د ډیټابیس بیا ډیزاین لپاره وخت دی. د پوښتنې جوړښت پخپله نور نشي اصلاح کیدی (که څه هم، لکه څنګه چې وروسته معلومه شوه، یو اختیار شتون لري ترڅو ډاډ ترلاسه کړي چې هرڅه په حقیقت کې ناکامیږي). مګر د ډیټابیس ډیزاین اصلاح او پراختیا پیل کول لا دمخه خورا امید لرونکی نظر دی. او تر ټولو مهمه په زړه پورې. یو ځل بیا خپل ځواني په یاد کړه. زه سمدلاسه د DBA نه شوم، زه د پروګرامر په توګه لوی شوی یم (BASIC، جمع کونکی، C، ډبل پلس C، اوریکل، plsql). په زړه پورې موضوع، البته، د جلا یادښت لپاره؛-).
په هرصورت، راځئ چې انحراف ونه کړو.

او همداسې،

یا شاید ویشل به موږ سره مرسته وکړي؟
سپوئلر - "هو، دا مرسته وکړه، په شمول د فعالیت اصلاح کولو کې."

مګر دا یو بشپړ مختلف کیسه ده ...

نور بیا…

سرچینه: www.habr.com