Hammasi qanday boshlanganini eslaysizmi. Hamma narsa birinchi marta va yana bo'ldi

PostgreSQL so'rovini qanday optimallashtirishimiz kerakligi va undan nima chiqqanligi haqida.
Nega kerak edi? Ha, chunki oldingi 4 yil davomida hamma narsa jim, xotirjam, soat tiqillagandek ishladi.
Epigraf sifatida.

Haqiqiy voqealarga asoslangan.
Barcha ismlar o'zgartirildi, tasodiflar tasodifiy.

Muayyan natijaga erishganingizda, boshlanishiga nima turtki bo'lganini, hammasi qaerdan boshlanganini eslash har doim qiziq.

Natijada nima sodir bo'lganligi maqolada qisqacha tasvirlangan "Sintez PostgreSQL ish faoliyatini yaxshilash usullaridan biri sifatida".

Ehtimol, avvalgi voqealar zanjirini qayta tiklash qiziqarli bo'ladi.
Tarix aniq boshlanish sanasini saqladi - 2018-09-10 18:02:48.
Shuningdek, hikoyada hammasi boshlangan so'rov bor:
Muammoli so'rovSELECT
p.“PARAMETER_ID” parametr_id sifatida,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS mijoz_partiya raqami,
w. "LRM" AS LRM,
w. "LOTID" lotid kabi,
w.“RTD_VALUE” RTD_qiymati sifatida,
w.“LOWER_SPEC_LIMIT” past_spec_chegara AS,
w.“UPPER_SPEC_LIMIT” yuqori_spec_chegara sifatida,
p."TYPE_CALCUL" AS type_hisoblash,
s."SPENT_NAME" sarflangan_nom sifatida,
s.“SPENT_DATE” sarflangan_data,
ko'chirma("SPENT_DATE"dan yil) AS Yil,
ko'chirma ("SPENT_DATE" dan boshlab oy) oy sifatida,
s."REPORT_NAME" AS hisobot_nomi,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
W ma'lumotlaridan w,
sarflangan s,
pmtr p,
sarflangan_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
VA p."PARAMETER_ID" = w."PARAMETER_ID"
VA s.“SPENT_ID” = sp.“SPENT_ID”
VA pd."PD_ID" = sp."PD_ID"
VA s.“SPENT_DATE” >= '2018-07-01' VA s.“SPENT_DATE” <= '2018-09-30'
va s.“SPENT_DATE” = (MAXS ni tanlang(s2.“SPENT_DATE”)
s2 sarflanganidan,
wdata w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
VA w2.“LRM” = w.“LRM”);

Muammoning tavsifi taxminiy standartdir - “Hammasi yomon. Muammo nimada ekanligini ayting."
Men darhol 3 yarim dyuymli disklar davridagi anekdotni esladim:

Lamer xakerning oldiga keladi.
-Menga hech narsa ishlamayapti, muammo qayerdaligini ayt.
-DNKda...

Lekin, albatta, bu ishlash hodisalarini hal qilishning yo'li emas. "Ular bizni tushunmasligi mumkin"(bilan). Biz buni aniqlab olishimiz kerak.
Mayli, qazib olaylik. Ehtimol, natijada biror narsa to'planadi.

Tergov boshlandi

Shunday qilib, oddiy ko'z bilan darhol ko'rish mumkin bo'lgan narsalarni, hatto TUSHLASHga ham murojaat qilmasdan.
1) JOINlar ishlatilmaydi. Bu yomon, ayniqsa ulanishlar soni bir nechta bo'lsa.
2) Ammo bundan ham yomoni, o'zaro bog'liq pastki so'rovlar, bundan tashqari, yig'ish bilan. Bu juda yomon.
Bu yomon albatta. Ammo bu faqat bir tomondan. Boshqa tomondan, bu juda yaxshi, chunki muammo aniq yechim va yaxshilanishi mumkin bo'lgan so'rovga ega.
Folbinga bormang (C).
So'rov rejasi unchalik murakkab emas, lekin u juda ko'p ma'noga ega:
Amalga oshirish rejasi

Eng qiziqarli va foydali, odatdagidek, boshida va oxirida.
Ichki tsikl (narxi=935.84..479763226.18 qator=3322 kenglik=135) (haqiqiy vaqt=31.536..8220420.295 qator=8111656 ko‘chadan=1)
Rejalashtirish vaqti: 3.807 ms
Bajarish vaqti: 8222351.640 ms
Yakunlash vaqti 2 soatdan ortiq.

Vaqtni talab qiladigan noto'g'ri farazlar

Gipoteza 1 - Optimizator xato qiladi va noto'g'ri reja tuzadi.

Ijro rejasini tasavvur qilish uchun biz saytdan foydalanamiz https://explain.depesz.com/. Biroq, sayt qiziqarli va foydali narsalarni ko'rsatmadi. Birinchi va ikkinchi qarashda, albatta, yordam beradigan hech narsa yo'q. To'liq skanerlash minimal bo'lishi mumkinmi? Davom etishga ruxsat.

Gipoteza 2-Avtovakuum tomondan bazaga ta'sir qilish, siz tormozlardan xalos bo'lishingiz kerak.

Ammo avtovakuum demonlari o'zini yaxshi tutadi, uzoq davom etadigan jarayonlar yo'q. Jiddiy yuk yo'q. Biz boshqa narsani izlashimiz kerak.

Gipoteza 3 - Statistik ma'lumotlar eskirgan, hamma narsani qayta hisoblash kerak

Yana, bu emas. Statistik ma'lumotlar dolzarb. Avtovakuum bilan bog'liq muammolar yo'qligini hisobga olsak, bu ajablanarli emas.

Keling, optimallashtirishni boshlaylik

Asosiy jadval "wdata" albatta kichik emas, deyarli 3 million yozuv.
Va aynan shu jadval to'liq skanerdan keyin keladi.

Xash Cond: ((w."SPENT_ID" = s. "SPENT_ID") VA ((1-kichik reja) = s. "SPENT_DATE"))
-> Seq Scan wdata w (narxi=0.00..574151.49 satr=26886249 eni=46) (haqiqiy vaqt=0.005..8153.565 satr=26873950 halqa=1)
Biz standart ishni qilamiz: "Kelinglar, indeks tuzamiz va hamma narsa uchib ketadi."
“SPENT_ID” maydonida indeks yaratildi
Natijada:
Indeks yordamida so'rovni bajarish rejasi

Xo'sh, yordam berdimi?
edi: 8 222 351.640 ms (2 soatdan bir oz ko'proq)
Bo'ldi: 6 985 431.575 ms (deyarli 2 soat)
Umuman olganda, bir xil olma, yon ko'rinish.
Keling, klassikalarni eslaylik:
“Sizda ham xuddi shunday, ammo qanotsizmi? izlaydi".

Aslida, buni yaxshi natija deb atash mumkin, yaxshi emas, lekin maqbuldir. Hech bo'lmaganda, mijozga qancha ish qilinganligi va nima uchun yaxshi bo'lganligi haqida katta hisobot taqdim eting.
Biroq, yakuniy qaror hali ham uzoqda. Juda uzoq.

Va endi eng qiziq narsa - biz optimallashtirishni davom ettiramiz, so'rovni jilolaymiz

Birinchi qadam - JOIN-dan foydalaning

Qayta yozilgan so'rov endi shunday ko'rinadi (hech bo'lmaganda yanada chiroyli):
JOIN yordamida so'rovSELECT
p.“PARAMETER_ID” parametr_id sifatida,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS mijoz_partiya raqami,
w. "LRM" AS LRM,
w. "LOTID" lotid kabi,
w.“RTD_VALUE” RTD_qiymati sifatida,
w.“LOWER_SPEC_LIMIT” past_spec_chegara AS,
w.“UPPER_SPEC_LIMIT” yuqori_spec_chegara sifatida,
p."TYPE_CALCUL" AS type_hisoblash,
s."SPENT_NAME" sarflangan_nom sifatida,
s.“SPENT_DATE” sarflangan_data,
ko'chirma("SPENT_DATE"dan yil) AS Yil,
ko'chirma ("SPENT_DATE" dan boshlab oy) oy sifatida,
s."REPORT_NAME" AS hisobot_nomi,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN sarflangan s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN sarflangan_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
WHERE
s.“SPENT_DATE” >= '2018-07-01' VA s.“SPENT_DATE” <= '2018-09-30'VA
s.“SPENT_DATE” = (MAKS MAXS (2.“SPENT_DATE”) ni tanlang)
FROM wdata w2 INNER JOIN sarflangan s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
Rejalashtirish vaqti: 2.486 ms
Bajarish vaqti: 1223680.326 ms

Shunday qilib, birinchi natija.
edi: 6 985 431.575 ms (deyarli 2 soat).
Bo'ldi: 1 223 680.326 ms (20 daqiqadan ko'proq).
Yaxshi natija. Printsipial jihatdan yana, biz u erda to'xtashimiz mumkin edi. Lekin bu juda qiziq emas, siz to'xtata olmaysiz.
UCHUN

Ikkinchi qadam - o'zaro bog'liq pastki so'rovdan xalos bo'ling

O'zgartirilgan so'rov matni:
O'zaro bog'liq pastki so'rovlarsizSELECT
p.“PARAMETER_ID” parametr_id sifatida,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS mijoz_partiya raqami,
w. "LRM" AS LRM,
w. "LOTID" lotid kabi,
w.“RTD_VALUE” RTD_qiymati sifatida,
w.“LOWER_SPEC_LIMIT” past_spec_chegara AS,
w.“UPPER_SPEC_LIMIT” yuqori_spec_chegara sifatida,
p."TYPE_CALCUL" AS type_hisoblash,
s."SPENT_NAME" sarflangan_nom sifatida,
s.“SPENT_DATE” sarflangan_data,
ko'chirma("SPENT_DATE"dan yil) AS Yil,
ko'chirma ("SPENT_DATE" dan boshlab oy) oy sifatida,
s."REPORT_NAME" AS hisobot_nomi,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN sarflangan s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN sarflangan_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
ICHKI QO‘SHILMA (Tanlash w2.“LRM”, MAX(s2.“SPENT_DATE”))
FROM sarflangan s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
w2.“LRM” BOʻYICHA GURUHLASH
) md on w.“LRM” = md.“LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' VA s."SPENT_DATE" <= '2018-09-30';
Rejalashtirish vaqti: 2.291 ms
Bajarish vaqti: 165021.870 ms

edi: 1 223 680.326 ms (20 daqiqadan ko'proq).
Bo'ldi: 165 021.870 ms (2 daqiqadan ko'proq).
Bu allaqachon juda yaxshi.
Biroq, inglizlar aytganidek "Lekin, har doim bir lekin bor" Juda yaxshi natija avtomatik ravishda shubha uyg'otishi kerak. Bu yerda nimadir noto'g'ri.

Korrelyatsiya qilingan pastki so'rovdan xalos bo'lish uchun so'rovni tuzatish haqidagi gipoteza to'g'ri. Ammo yakuniy natija to'g'ri bo'lishi uchun uni biroz sozlashingiz kerak.
Natijada, birinchi oraliq natija:
O'zaro bog'liq pastki so'rovsiz tahrirlangan so'rovSELECT
p.“PARAMETER_ID” parametr_id sifatida,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS mijoz_partiya raqami,
w. "LRM" AS LRM,
w. "LOTID" lotid kabi,
w.“RTD_VALUE” RTD_qiymati sifatida,
w.“LOWER_SPEC_LIMIT” past_spec_chegara AS,
w.“UPPER_SPEC_LIMIT” yuqori_spec_chegara sifatida,
p."TYPE_CALCUL" AS type_hisoblash,
s."SPENT_NAME" sarflangan_nom sifatida,
s.“SPENT_DATE” sarflangan_data,
ko‘chirma (“SPENT_DATE” dan boshlab) Yil AS,
ko‘chirma (“SPENT_DATE” dan boshlab oy), oy sifatida,
s."REPORT_NAME" AS hisobot_nomi,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN sarflangan s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN sarflangan_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
ICHKI QO‘SHILMA ( 2. “LRM”, MAX(s2. “SPENT_DATE”) “SPENT_DATE” SIFATIDA TANLASH
FROM sarflangan s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
w2.“LRM” BOʻYICHA GURUHLASH
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” VA md.“LRM” = w.“LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' VA s."SPENT_DATE" <= '2018-09-30';
Rejalashtirish vaqti: 3.192 ms
Bajarish vaqti: 208014.134 ms

Shunday qilib, biz birinchi qabul qilinadigan natijaga erishamiz, bu mijozga ko'rsatish uchun uyat emas:
Boshlangan: 8 222 351.640 ms (2 soatdan ortiq)
Biz erisha oldik: 1 223 680.326 ms (20 daqiqadan bir oz ko'proq).
Natija (oraliq): 208 014.134 ms (3 daqiqadan ko'proq).

Ajoyib natija.

Xulosa

Biz u erda to'xtashimiz mumkin edi.
LEKIN...
Ishtaha ovqatlanish bilan birga keladi. Yurgan kishi yo'lni o'zlashtiradi. Har qanday natija oraliq hisoblanadi. To'xtadi va o'ldi. Va boshqalar.
Keling, optimallashtirishni davom ettiramiz.
Ajoyib fikr. Ayniqsa, mijoz bunga qarshi emasligini hisobga olsak. Va hatto buning uchun qattiq.

Shunday qilib, ma'lumotlar bazasini qayta loyihalash vaqti keldi. So'rovlar strukturasini endi optimallashtirish mumkin emas (garchi keyinchalik ma'lum bo'lishicha, hamma narsa haqiqatan ham muvaffaqiyatsiz bo'lishini ta'minlash imkoniyati mavjud). Ammo ma'lumotlar bazasi dizaynini optimallashtirish va rivojlantirishni boshlash allaqachon juda istiqbolli g'oya. Va eng muhimi, qiziqarli. Yana yoshligingizni eslang. Men darhol DBA bo'lmadim, men dasturchi sifatida o'sdim (BASIC, assembler, C, double-plus C, Oracle, plsql). Qiziqarli mavzu, albatta, alohida xotira uchun ;-).
Biroq, keling, chalg'itmaylik.

Va shunday qilib,

Yoki bo'linish bizga yordam beradimi?
Spoyler - "Ha, bu yordam berdi, shu jumladan ishlashni optimallashtirishda."

Ammo bu butunlay boshqacha hikoya ...

Davomi bor…

Manba: www.habr.com