PostgreSQL Query Profiler: วิธีจับคู่แผนและการสืบค้น

หลายๆท่านที่ใช้อยู่แล้ว exp.tensor.ru - บริการแสดงภาพแผน PostgreSQL ของเราอาจไม่ได้ตระหนักถึงหนึ่งในพลังพิเศษของมัน - เปลี่ยนบันทึกเซิร์ฟเวอร์ที่อ่านยาก...

... เป็นแบบสอบถามที่ออกแบบมาอย่างสวยงามพร้อมคำแนะนำตามบริบทสำหรับโหนดแผนที่เกี่ยวข้อง:

ในบันทึกของส่วนที่สองของเขานี้ รายงานที่ PGConf.Russia 2020 ฉันจะบอกคุณว่าเราทำเช่นนี้ได้อย่างไร

คุณสามารถดูบันทึกของส่วนแรกที่เกี่ยวข้องกับปัญหาประสิทธิภาพคิวรีทั่วไปและแนวทางแก้ไขได้ในบทความ "สูตรสำหรับการสืบค้น SQL ที่ไม่สบาย".

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

สำหรับเราดูเหมือนว่าด้วย "ชีต" ที่ไม่ได้ฟอร์แมตดังกล่าว คำขอที่ดึงออกมาจากบันทึกจึงดูน่าเกลียดมากและไม่สะดวก


โดยเฉพาะอย่างยิ่งเมื่อนักพัฒนา "ติด" เนื้อความของคำขอในโค้ด (แน่นอนว่านี่คือการต่อต้านรูปแบบ แต่มันเกิดขึ้น) ในบรรทัดเดียว น่ากลัว!

มาวาดสิ่งนี้ให้สวยงามกว่านี้กันดีกว่า


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

ต้นไม้ไวยากรณ์แบบสอบถาม

เมื่อต้องการทำเช่นนี้ คำขอต้องถูกแยกวิเคราะห์ก่อน


เพราะว่าเรามี แกนหลักของระบบทำงานบน NodeJSจากนั้นเราก็สร้างโมดูลสำหรับมัน คุณก็ทำได้ ค้นหาได้ใน GitHub. ที่จริงแล้ว สิ่งเหล่านี้ขยาย “การเชื่อมโยง” ไปยังภายในของตัวแยกวิเคราะห์ PostgreSQL เอง นั่นคือไวยากรณ์เป็นเพียงการคอมไพล์แบบไบนารีและการเชื่อมโยงจาก NodeJS เรายึดเอาโมดูลของผู้อื่นเป็นพื้นฐาน - ไม่มีความลับใหญ่ที่นี่

เราป้อนเนื้อความของคำขอเป็นอินพุตไปยังฟังก์ชันของเรา - ที่เอาต์พุตเราได้รับแผนผังไวยากรณ์ที่แยกวิเคราะห์ในรูปแบบของวัตถุ JSON


ตอนนี้เราสามารถวิ่งผ่านแผนผังนี้ในทิศทางตรงกันข้าม และประกอบคำขอด้วยการเยื้อง การระบายสี และการจัดรูปแบบที่เราต้องการ ไม่ สิ่งนี้ไม่สามารถปรับแต่งได้ แต่ดูเหมือนว่ามันจะสะดวกสำหรับเรา

การแมปแบบสอบถามและโหนดแผน

ตอนนี้เรามาดูกันว่าเราจะรวมแผนที่เราวิเคราะห์ในขั้นตอนแรกเข้ากับคำค้นหาที่เราวิเคราะห์ในขั้นตอนที่สองได้อย่างไร

ลองยกตัวอย่างง่ายๆ - เรามีแบบสอบถามที่สร้าง CTE และอ่านจากข้อความนั้นสองครั้ง เขาสร้างแผนดังกล่าว

CTE

หากพิจารณาให้ละเอียดจนถึงเวอร์ชัน 12 (หรือเริ่มจากคีย์เวิร์ด MATERIALIZED) รูปแบบ CTE เป็นอุปสรรคอย่างยิ่งสำหรับผู้วางแผน.


ซึ่งหมายความว่าหากเราเห็นการสร้าง CTE ที่ใดที่หนึ่งในคำขอและโหนดที่ใดที่หนึ่งในแผน CTEจากนั้นโหนดเหล่านี้จะ "ต่อสู้" กันอย่างแน่นอนเราสามารถรวมเข้าด้วยกันได้ทันที

มีปัญหากับเครื่องหมายดอกจัน: CTE สามารถซ้อนกันได้

มีอันที่ซ้อนกันได้แย่มากและแม้แต่อันที่มีชื่อเดียวกันด้วยซ้ำ ตัวอย่างเช่นคุณสามารถเข้าไปข้างในได้ 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 เพิ่มเข้าไปเอง คุณเพียงแค่ต้องเข้าใจว่า แค่นามแฝงแบบนั้น สำหรับเรา จุดประสงค์ในการเปรียบเทียบกับแผน มันไม่สมเหตุสมผลเลย แค่เพิ่มไว้ที่นี่ อย่าไปสนใจเขาเลย

สอง งาน "มีเครื่องหมายดอกจัน": หากเรากำลังอ่านจากตารางที่แบ่งพาร์ติชั่น เราจะได้โหนด Append หรือ Merge Appendซึ่งจะประกอบไปด้วย “ลูก” จำนวนมาก และแต่ละลูกก็จะเป็นอย่างไร Scan'om จากส่วนของตาราง: Seq Scan, Bitmap Heap Scan หรือ Index Scan. แต่ไม่ว่าในกรณีใด "ลูก" เหล่านี้จะไม่เป็นคำถามที่ซับซ้อน - นี่คือวิธีการแยกแยะโหนดเหล่านี้ Append ที่ UNION.


เราก็เข้าใจปมดังกล่าวเช่นกัน รวบรวมมัน "เป็นกองเดียว" แล้วพูดว่า: "ทุกสิ่งที่คุณอ่านจาก megable อยู่ที่นี่และลงมาจากต้นไม้".

โหนดรับข้อมูล "แบบง่าย"

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 ระหว่างพวกเขาเอง สิ่งนี้ไม่สามารถทำได้เสมอไป แต่ก็เป็นไปได้


จากมุมมองของตัวแยกวิเคราะห์แบบสอบถาม เรามีโหนด 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.

แต่ในกรณีส่วนใหญ่ โหนดเกือบทั้งหมดสามารถ "แก้" ได้ และคุณสามารถรับโปรไฟล์ประเภทนี้ทางด้านซ้ายได้ทันเวลา - เหมือนกับใน Google Chrome เมื่อคุณวิเคราะห์โค้ด JavaScript คุณสามารถดูได้ว่าแต่ละบรรทัดและแต่ละคำสั่งใช้เวลานานเท่าใดในการ "ดำเนินการ"


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

หากคุณเพียงต้องการนำข้อความค้นหาที่อ่านไม่ออกมาอยู่ในรูปแบบที่เพียงพอ ให้ใช้ “นอร์มัลไลเซอร์” ของเรา.

ที่มา: will.com