คุณจำได้ไหมว่ามันเริ่มต้นอย่างไร ทุกอย่างเป็นครั้งแรกและอีกครั้ง

เกี่ยวกับวิธีที่เราต้องเพิ่มประสิทธิภาพการสืบค้น PostgreSQL และผลลัพธ์ที่ได้ทั้งหมด
ทำไมคุณต้อง? ใช่ เพราะตลอด 4 ปีที่ผ่านมา ทุกอย่างดำเนินไปอย่างเงียบๆ สงบ เหมือนนาฬิกาเดิน
เป็น epigraph

อิงจากเหตุการณ์จริง
เปลี่ยนชื่อทั้งหมดแล้ว ความบังเอิญเป็นแบบสุ่ม

เมื่อคุณบรรลุผลสำเร็จ เป็นเรื่องน่าสนใจเสมอที่จะจดจำว่าอะไรเป็นแรงผลักดันในการเริ่มต้น และทุกอย่างเริ่มต้นจากที่ใด

ดังนั้นสิ่งที่เกิดขึ้นจึงอธิบายสั้น ๆ ไว้ในบทความ “การสังเคราะห์เป็นหนึ่งในวิธีการปรับปรุงประสิทธิภาพของ PostgreSQL'

มันอาจจะน่าสนใจที่จะสร้างห่วงโซ่ของเหตุการณ์ก่อนหน้านี้ขึ้นมาใหม่
ประวัติบันทึกวันที่เริ่มต้นที่แน่นอน - 2018-09-10 18:02:48.
นอกจากนี้ในเรื่องยังมีคำขอซึ่งทุกอย่างเริ่มต้นขึ้น:
คำขอปัญหาSELECT
p. “PARAMETER_ID” เป็น parameter_id,
pd"PD_NAME" เป็น pd_name,
pd "CUSTOMER_PARTNUMBER" ในฐานะลูกค้าหมายเลขชิ้นส่วน
w. "LRM" เป็น LRM
w. "LOTID" AS โลทิด,
w.“RTD_VALUE” เป็น RTD_value,
w. “LOWER_SPEC_LIMIT” เป็น lower_spec_limit,
w.“UPPER_SPEC_LIMIT” เป็น upper_spec_limit,
p."TYPE_CALCUL" เป็น type_calcul
s"SPENT_NAME" ตามการใช้จ่าย_ชื่อ
s.“SPENT_DATE” ตามการใช้จ่าย_วันที่,
สารสกัด(ปีจาก "SPENT_DATE") AS ปี
แยก (เดือนจาก "SPENT_DATE") เป็นเดือน
s"REPORT_NAME" เป็น report_name
p "STPM_NAME" เป็น stpm_name
หน้า “CUSTOMERPARAM_NAME” เป็นลูกค้า param_name
จาก wdata w
ใช้เวลา
พีเอ็มทีอาร์พี,
ใช้เวลา_pd sp,
พีดีพีดี
โดยที่ s.“SPENT_ID” = w.“SPENT_ID”
และ p"PARAMETER_ID" = w"PARAMETER_ID"
และ s.“SPENT_ID” = sp.“SPENT_ID”
และ pd"PD_ID" = sp"PD_ID"
และ s.“SPENT_DATE” >= '2018-07-01' และ s.“SPENT_DATE” <= '2018-09-30'
และ s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
จาก ใช้เวลา s2
ดับเบิลยูดาต้า W2
โดยที่ s2.“SPENT_ID” = w2.“SPENT_ID”
และ w2.“LRM” = w.“LRM”);

คำอธิบายของปัญหาเป็นมาตรฐานที่คาดเดาได้ - “ ทุกอย่างแย่ไปหมด บอกฉันมาว่าปัญหาคืออะไร”
ฉันจำเรื่องเล็ก ๆ น้อย ๆ ได้ทันทีจากไดรฟ์ขนาด 3 นิ้วครึ่ง:

ลาเมอร์มาหาแฮกเกอร์
-ไม่มีอะไรทำงานสำหรับฉัน บอกฉันว่าปัญหาอยู่ที่ไหน
-ในดีเอ็นเอ...

แต่แน่นอนว่านี่ไม่ใช่วิธีแก้ปัญหาด้านประสิทธิภาพ “พวกเขาอาจจะไม่เข้าใจเรา" (กับ). เราจำเป็นต้องคิดออก
มาขุดกันดีกว่า บางทีบางสิ่งบางอย่างจะสะสมตามมา

การสอบสวนเริ่มขึ้น

ดังนั้นสิ่งที่สามารถมองเห็นได้ด้วยตาเปล่าโดยไม่ต้องอาศัยคำอธิบายเลย
1) ไม่ได้ใช้ JOIN สิ่งนี้ไม่ดี โดยเฉพาะอย่างยิ่งหากจำนวนการเชื่อมต่อมากกว่าหนึ่งรายการ
2) แต่สิ่งที่แย่กว่านั้นคือแบบสอบถามย่อยที่สัมพันธ์กัน ยิ่งไปกว่านั้นยังมีการรวมกลุ่มอีกด้วย นี่มันแย่มาก
นี่มันแย่แน่นอน แต่นี่เป็นเพียงด้านเดียวเท่านั้น ในทางกลับกันถือว่าดีมากเพราะปัญหามีทางแก้ไขและคำขอที่สามารถปรับปรุงได้อย่างชัดเจน
อย่าไปหาหมอดู (C)
แผนการสืบค้นไม่ซับซ้อนมากนัก แต่ค่อนข้างบ่งชี้:
แผนปฏิบัติการ

สิ่งที่น่าสนใจและมีประโยชน์ที่สุดเช่นเคยอยู่ที่จุดเริ่มต้นและจุดสิ้นสุด
Nested Loop (ต้นทุน=935.84..479763226.18 แถว=3322 ความกว้าง=135) (เวลาจริง=31.536..8220420.295 แถว=8111656 ลูป=1)
เวลาการวางแผน: 3.807 มิลลิวินาที
เวลาดำเนินการ: 8222351.640 มิลลิวินาที
เวลาเสร็จสิ้นมากกว่า 2 ชั่วโมง

สมมติฐานเท็จที่ต้องใช้เวลา

สมมติฐานที่ 1 - เครื่องมือเพิ่มประสิทธิภาพทำผิดพลาดและสร้างแผนผิด

เพื่อให้เห็นภาพแผนการดำเนินการ เราจะใช้ไซต์ https://explain.depesz.com/. อย่างไรก็ตาม เว็บไซต์ไม่ได้แสดงสิ่งที่น่าสนใจหรือมีประโยชน์ใดๆ เมื่อมองแวบแรกและครั้งที่สองก็ไม่มีอะไรสามารถช่วยได้จริงๆ เป็นไปได้ไหมที่ Full Scan มีน้อย ไปข้างหน้า.

สมมติฐานที่ 2 - แรงกระแทกที่ฐานจากฝั่งออโต้สุญญากาศ คุณต้องถอดเบรกออก

แต่ภูตอัตโนมัติทำงานได้ดีไม่มีกระบวนการที่แขวนอยู่นาน ไม่มีภาระร้ายแรง เราจำเป็นต้องมองหาสิ่งอื่น

สมมติฐานที่ 3 - สถิติล้าสมัย ทุกอย่างจำเป็นต้องคำนวณใหม่

ไม่ใช่อย่างนั้นด้วยซ้ำ สถิติมีความทันสมัย ซึ่งการไม่มีปัญหาเรื่องเครื่องดูดฝุ่นอัตโนมัติจึงไม่น่าแปลกใจ

มาเริ่มเพิ่มประสิทธิภาพกันดีกว่า

ตารางหลัก 'wdata' ไม่เล็กแน่นอน เกือบ 3 ล้านบันทึก
และนี่คือตารางที่ Full Scan ตามมา

เงื่อนไขแฮช: ((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.640 มิลลิวินาที (มากกว่า 2 ชั่วโมงเล็กน้อย)
กลายเป็น: 6 985 431.575 ms (เกือบ 2 ชั่วโมง)
โดยทั่วไปแล้วแอปเปิ้ลชนิดเดียวกันมุมมองด้านข้าง
มาจำคลาสสิกกันเถอะ:
“คุณมีแบบเดียวกันแต่ไม่มีปีกไหม? จะแสวงหา".

โดยหลักการแล้วอาจเรียกได้ว่าเป็นผลดีก็ไม่ดีแต่ก็ยอมรับได้ อย่างน้อยที่สุด ให้จัดทำรายงานขนาดใหญ่แก่ลูกค้าโดยอธิบายว่าได้ทำอะไรไปแล้วไปมากน้อยเพียงใด และเหตุใดสิ่งที่ทำจึงดี
แต่การตัดสินใจขั้นสุดท้ายยังอยู่อีกไกล ไกลมาก.

และตอนนี้สิ่งที่น่าสนใจที่สุด - เราปรับปรุงประสิทธิภาพต่อไป เราจะขัดเกลาคำขอ

ขั้นตอนที่หนึ่ง - ใช้ JOIN

คำขอที่เขียนใหม่ตอนนี้มีลักษณะเช่นนี้ (อย่างน้อยก็สวยกว่านี้):
แบบสอบถามโดยใช้ JOINSELECT
p. “PARAMETER_ID” เป็น parameter_id,
pd"PD_NAME" เป็น pd_name,
pd "CUSTOMER_PARTNUMBER" ในฐานะลูกค้าหมายเลขชิ้นส่วน
w. "LRM" เป็น LRM
w. "LOTID" AS โลทิด,
w.“RTD_VALUE” เป็น RTD_value,
w. “LOWER_SPEC_LIMIT” เป็น lower_spec_limit,
w.“UPPER_SPEC_LIMIT” เป็น upper_spec_limit,
p."TYPE_CALCUL" เป็น type_calcul
s"SPENT_NAME" ตามการใช้จ่าย_ชื่อ
s.“SPENT_DATE” ตามการใช้จ่าย_วันที่,
สารสกัด(ปีจาก "SPENT_DATE") AS ปี
แยก (เดือนจาก "SPENT_DATE") เป็นเดือน
s"REPORT_NAME" เป็น report_name
p "STPM_NAME" เป็น stpm_name
หน้า “CUSTOMERPARAM_NAME” เป็นลูกค้า param_name
จาก wdata ที่เข้าร่วมภายในใช้เวลาไปกับ w. “SPENT_ID” = s” “ SPENT_ID”
เข้าร่วมภายใน pmtr p ON p. “PARAMETER_ID” = w. “PARAMETER_ID”
เข้าร่วมภายใน ใช้เวลา_pd sp ON s. “SPENT_ID” = sp. “SPENT_ID”
เข้าร่วมภายใน pd pd ON pd.“PD_ID” = sp.“PD_ID”
WHERE
s.“SPENT_DATE” >= '2018-07-01' และ s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
จาก wdata w2 เข้าร่วมภายในใช้เวลา s2 กับ w2. “SPENT_ID” = s2. “SPENT_ID”
เข้าร่วมภายใน wdata ด้วย
บน w2.“LRM” = w.“LRM” );
เวลาการวางแผน: 2.486 มิลลิวินาที
เวลาดำเนินการ: 1223680.326 มิลลิวินาที

ดังนั้นผลลัพธ์แรก
มันเป็น: 6 มิลลิวินาที (เกือบ 985 ชั่วโมง)
กลายเป็น: 1 223 680.326 ms (มากกว่า 20 นาที)
ผลลัพธ์ที่ดี โดยหลักการแล้ว อีกครั้ง เราสามารถหยุดอยู่แค่นั้นได้ แต่มันไม่น่าสนใจจนคุณหยุดไม่ได้
สำหรับ

ขั้นตอนที่สอง - กำจัดแบบสอบถามย่อยที่สัมพันธ์กัน

ข้อความคำขอที่เปลี่ยนแปลง:
โดยไม่มีแบบสอบถามย่อยที่สัมพันธ์กันSELECT
p. “PARAMETER_ID” เป็น parameter_id,
pd"PD_NAME" เป็น pd_name,
pd "CUSTOMER_PARTNUMBER" ในฐานะลูกค้าหมายเลขชิ้นส่วน
w. "LRM" เป็น LRM
w. "LOTID" AS โลทิด,
w.“RTD_VALUE” เป็น RTD_value,
w. “LOWER_SPEC_LIMIT” เป็น lower_spec_limit,
w.“UPPER_SPEC_LIMIT” เป็น upper_spec_limit,
p."TYPE_CALCUL" เป็น type_calcul
s"SPENT_NAME" ตามการใช้จ่าย_ชื่อ
s.“SPENT_DATE” ตามการใช้จ่าย_วันที่,
สารสกัด(ปีจาก "SPENT_DATE") AS ปี
แยก (เดือนจาก "SPENT_DATE") เป็นเดือน
s"REPORT_NAME" เป็น report_name
p "STPM_NAME" เป็น stpm_name
หน้า “CUSTOMERPARAM_NAME” เป็นลูกค้า param_name
จาก wdata ที่เข้าร่วมภายในใช้จ่าย s. “SPENT_ID” = w. “SPENT_ID”
เข้าร่วมภายใน pmtr p ON p. “PARAMETER_ID” = w. “PARAMETER_ID”
เข้าร่วมภายใน ใช้เวลา_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 เข้าร่วมภายใน wdata w2 บน s2. “SPENT_ID” = w2. “SPENT_ID”
จัดกลุ่มตาม w2. “LRM”
) md บน w.“LRM” = md.“LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' และ s."SPENT_DATE" <= '2018-09-30';
เวลาการวางแผน: 2.291 มิลลิวินาที
เวลาดำเนินการ: 165021.870 มิลลิวินาที

มันเป็น: 1 223 680.326 ms (มากกว่า 20 นาที)
กลายเป็น: 165 021.870 ms (เพียง 2 นาทีกว่าๆ)
นี่ก็ค่อนข้างดีอยู่แล้ว
อย่างไรก็ตาม ดังที่อังกฤษกล่าวว่า "แต่ก็มีแต่เสมอ" ผลลัพธ์ที่ดีเกินไปควรกระตุ้นความสงสัยโดยอัตโนมัติ มีบางอย่างผิดปกติที่นี่

สมมติฐานเกี่ยวกับการแก้ไขแบบสอบถามเพื่อกำจัดแบบสอบถามย่อยที่สัมพันธ์กันนั้นถูกต้อง แต่คุณต้องปรับแต่งเล็กน้อยเพื่อให้ได้ผลลัพธ์สุดท้ายที่ถูกต้อง
เป็นผลให้ผลลัพธ์ระดับกลางแรก:
แก้ไขแบบสอบถามโดยไม่มีแบบสอบถามย่อยที่สัมพันธ์กันSELECT
p. “PARAMETER_ID” เป็น parameter_id,
pd"PD_NAME" เป็น pd_name,
pd "CUSTOMER_PARTNUMBER" ในฐานะลูกค้าหมายเลขชิ้นส่วน
w. "LRM" เป็น LRM
w. "LOTID" AS โลทิด,
w.“RTD_VALUE” เป็น RTD_value,
w. “LOWER_SPEC_LIMIT” เป็น lower_spec_limit,
w.“UPPER_SPEC_LIMIT” เป็น upper_spec_limit,
p."TYPE_CALCUL" เป็น type_calcul
s"SPENT_NAME" ตามการใช้จ่าย_ชื่อ
s.“SPENT_DATE” ตามการใช้จ่าย_วันที่,
สารสกัด(ปีจาก s. “SPENT_DATE”) AS ปี
แยก (เดือนจาก s. “SPENT_DATE”) เป็นเดือน
s"REPORT_NAME" เป็น report_name
p "STPM_NAME" เป็น stpm_name
หน้า “CUSTOMERPARAM_NAME” เป็นลูกค้า param_name
จาก wdata ที่เข้าร่วมภายในใช้จ่าย s. “SPENT_ID” = w. “SPENT_ID”
เข้าร่วมภายใน pmtr p ON p. “PARAMETER_ID” = w. “PARAMETER_ID”
เข้าร่วมภายใน ใช้เวลา_pd sp ON s. “SPENT_ID” = sp. “SPENT_ID”
เข้าร่วมภายใน pd pd ON pd.“PD_ID” = sp.“PD_ID”
เข้าร่วมภายใน ( SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”) AS “SPENT_DATE”
จากการใช้จ่าย s2 เข้าร่วมภายใน wdata w2 บน s2. “SPENT_ID” = w2. “SPENT_ID”
จัดกลุ่มตาม w2. “LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” และ md.“LRM” = w.“LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' และ s."SPENT_DATE" <= '2018-09-30';
เวลาการวางแผน: 3.192 มิลลิวินาที
เวลาดำเนินการ: 208014.134 มิลลิวินาที

ดังนั้นสิ่งที่เราได้คือผลลัพธ์แรกที่ยอมรับได้ ซึ่งไม่ใช่เรื่องน่าละอายที่จะแสดงให้ลูกค้าเห็น:
เริ่มต้นด้วย: 8 222 351.640 ms (มากกว่า 2 ชั่วโมง)
เราจัดการเพื่อให้บรรลุเป้าหมาย: 1 ms (มากกว่า 223 นาทีเล็กน้อย)
ผลลัพธ์ (ชั่วคราว): 208 014.134 ms (เพียง 3 นาทีกว่าๆ)

ผลลัพธ์ที่ยอดเยี่ยม

ทั้งหมด

เราอาจหยุดอยู่แค่นั้น
แต่…
ความอยากอาหารมาพร้อมกับการกิน ผู้ที่เดินจะเชี่ยวชาญถนน ผลลัพธ์ใด ๆ อยู่ในระดับกลาง หยุดแล้วเสียชีวิต ฯลฯ
มาเพิ่มประสิทธิภาพกันต่อไป
ความคิดที่ดี. โดยเฉพาะอย่างยิ่งเมื่อพิจารณาว่าลูกค้าไม่ได้สนใจด้วยซ้ำ และยิ่งแข็งแกร่งสำหรับมัน

ถึงเวลาสำหรับการออกแบบฐานข้อมูลใหม่แล้ว โครงสร้างการสืบค้นนั้นไม่สามารถปรับให้เหมาะสมได้อีกต่อไป (แม้ว่าจะปรากฏในภายหลัง แต่ก็มีตัวเลือกเพื่อให้แน่ใจว่าทุกอย่างล้มเหลวจริง ๆ ) แต่การเริ่มเพิ่มประสิทธิภาพและพัฒนาการออกแบบฐานข้อมูลถือเป็นแนวคิดที่น่าหวังมาก และที่สำคัญที่สุดคือน่าสนใจ ระลึกถึงวัยเยาว์ของคุณอีกครั้ง ฉันไม่ได้เป็น DBA ในทันที แต่ฉันโตมาในฐานะโปรแกรมเมอร์ (BASIC, แอสเซมเบลอร์, C, double-plus C, Oracle, plsql) แน่นอนว่าเป็นหัวข้อที่น่าสนใจสำหรับบันทึกความทรงจำแยกต่างหาก ;-)
อย่างไรก็ตามอย่าวอกแวก

ดังนั้น

หรือบางทีการแบ่งพาร์ติชั่นจะช่วยเราได้?
สปอยเลอร์ - “ใช่ มันช่วยได้ รวมถึงการเพิ่มประสิทธิภาพด้วย”

แต่นั่นเป็นเรื่องราวที่แตกต่างอย่างสิ้นเชิง...

ยังมีต่อ…

ที่มา: will.com