EXPLAIN และ query plan

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

planner เป็นผู้เลือกแผน

คุณเขียน ว่า คุณต้องการอะไร ส่วน query planner เป็นคนตัดสินใจ ว่าจะ เอามาอย่างไร สำหรับ query หนึ่ง ๆ อาจมีแผนที่เป็นไปได้หลายแบบ — สแกนทั้ง table, ใช้ index ตัวนี้, ใช้ตัวนั้น, รวมสองตัวเข้าด้วยกัน — และ planner จะประเมินต้นทุนของแต่ละแบบ แล้วเลือกแบบที่ถูกที่สุด

flowchart LR
  Q[Your SQL query] --> P[Query planner]
  P --> E1[Estimate: Seq Scan cost]
  P --> E2[Estimate: Index Scan cost]
  P --> E3[Estimate: Bitmap Scan cost]
  E1 --> C[Pick the cheapest plan]
  E2 --> C
  E3 --> C
  C --> X[Execute chosen plan]

planner เลือกแผนอย่างไร

ค่าประมาณต้นทุนเหล่านั้นขึ้นอยู่กับ statistics เกี่ยวกับข้อมูลของคุณ — table มีกี่ row, column หนึ่งมีค่าที่แตกต่างกันกี่ค่า และอื่น ๆ หาก statistics เก่าล้าสมัย planner ก็จะเดาผิดพลาด และนั่นคือหัวข้อของบทเรียนสุดท้ายในโมดูลนี้

EXPLAIN: แผนโดยประมาณ

EXPLAIN พิมพ์แผนที่ planner จะ ใช้ออกมา โดยไม่ต้องรัน query จริง คำสั่งนี้ทำงานทันทีและปลอดภัยพอที่จะรันกับอะไรก็ได้

EXPLAIN SELECT * FROM books WHERE author_id = 4;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using books_author_idx on books  (cost=0.42..12.61 rows=6 width=64)
   Index Cond: (author_id = 4)

อ่านตัวเลขในวงเล็บ:

cost=0.42..12.61 — สองตัวเลขในหน่วยต้นทุนแบบสมมติ ตัวแรกคือต้นทุนเริ่มต้นโดยประมาณ (งานก่อนที่ row แรกจะปรากฏ) ส่วนตัวที่สองคือต้นทุนรวมโดยประมาณ ค่ายิ่งต่ำยิ่งถูก planner จะนำค่าเหล่านี้มาเปรียบเทียบกันระหว่างแผนที่เป็นตัวเลือก
rows=6 — จำนวน row ที่ planner ประมาณ ว่าโหนดนี้จะคืนกลับมา
width=64 — ขนาดเฉลี่ยของ row โดยประมาณในหน่วยไบต์

บรรทัดบนสุดยังระบุชื่อกลยุทธ์ด้วย ในที่นี้คือ Index Scan ที่ใช้ index ที่มีชื่อ โดยมี Index Cond แสดงว่า index จัดการเงื่อนไขใด

EXPLAIN ANALYZE: สิ่งที่เกิดขึ้นจริง

EXPLAIN ANALYZE รัน query จริง ๆ และรายงานเวลาที่เกิดขึ้นจริงควบคู่ไปกับค่าประมาณ การเพิ่ม BUFFERS ยังรายงานด้วยว่าอ่านหน้าข้อมูล (data page) ไปกี่หน้า ซึ่งบอกคุณได้ว่างานมาจากแคชหรือจากดิสก์

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM books WHERE author_id = 4;

                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Index Scan using books_author_idx on books  (cost=0.42..12.61 rows=6 width=64) (actual time=0.018..0.024 rows=6 loops=1)
   Index Cond: (author_id = 4)
   Buffers: shared hit=4
 Planning Time: 0.092 ms
 Execution Time: 0.041 ms

บล็อก actual time=0.018..0.024 rows=6 loops=1 ใหม่นี้คือความจริง: เวลาเริ่มต้นและเวลารวมจริงในหน่วยมิลลิวินาที, จำนวน row จริง และจำนวนครั้งที่โหนดนี้ทำงาน จงเปรียบเทียบ rows ที่ประมาณกับ rows จริง: ช่องว่างที่กว้างคือสัญญาณคลาสสิกของ statistics ที่เก่าล้าสมัยหรือ planner ที่ถูกชี้นำผิดทาง Buffers: shared hit=4 หมายความว่ามีสี่หน้าที่ถูกเสิร์ฟมาจากแคชและไม่มีหน้าใดมาจากดิสก์

การแยกแยะชนิดของ scan

กลยุทธ์ของ scan สามแบบปรากฏอยู่ตลอดเวลา แค่จำสามแบบนี้ให้ได้ก็ชนะไปครึ่งทางแล้ว

Seq Scan — อ่านทั้ง table ตั้งแต่บนลงล่าง คาดว่าจะเกิดขึ้นเมื่อไม่มี index ที่เป็นประโยชน์อยู่ หรือเมื่อ query คืน row เป็นสัดส่วนใหญ่ของ table อยู่แล้ว
Index Scan — เดินผ่าน index แล้วจึงดึงแต่ละ row ที่ตรงเงื่อนไขจาก table ดีที่สุดเมื่อ query นั้น เลือกเฉพาะเจาะจง คือคืนข้อมูลเพียงส่วนน้อยของ table
Bitmap Heap Scan (จับคู่กับ Bitmap Index Scan) — สร้าง bitmap ของตำแหน่ง row ที่ตรงเงื่อนไขจาก index, เรียงลำดับ bitmap นั้น แล้วค่อยอ่าน table ตามลำดับทางกายภาพ PostgreSQL เลือกทางสายกลางนี้เมื่อ query ตรงกับ row มากกว่าที่ index scan จัดการได้ดี แต่น้อยกว่า seq scan แบบเต็ม

แผนแบบ bitmap มีหน้าตาแบบนี้:

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on books  (cost=12.10..520.30 rows=900 width=64)
   Recheck Cond: (published > 2000)
   ->  Bitmap Index Scan on books_published_idx  (cost=0.00..11.88 rows=900 width=0)
         Index Cond: (published > 2000)

บรรทัด -> ที่ย่อหน้าเข้าไปคือโหนดลูกที่ป้อนข้อมูลให้โหนดแม่ แผนมีโครงสร้างเป็นต้นไม้ (tree) ให้อ่านจากโหนดที่ย่อหน้ามากที่สุดออกมาด้านนอก เพราะโหนดด้านในทำงานก่อนแล้วส่ง row ขึ้นไปให้ชั้นถัดไป

ทำไมบางครั้ง planner จึงเพิกเฉยต่อ index

เรื่องน่าประหลาดใจที่พบบ่อย: คุณสร้าง index แต่ EXPLAIN ก็ยังแสดง Seq Scan อยู่ดี ปกติแล้วนี่คือ planner ที่ ถูกต้อง ไม่ใช่ผิด:

query ไม่ได้เลือกเฉพาะเจาะจง หากเงื่อนไขตรงกับครึ่งหนึ่งของ table การกระโดดไปกลับระหว่าง index กับ table จะช้ากว่าการอ่าน table ทื่อ ๆ ไปเรื่อย ๆ seq scan จึงชนะ
table เล็กมาก บน table ที่มีเพียงไม่กี่ร้อย row seq scan แทบจะไม่มีต้นทุน และ planner ก็จะข้ามภาระของ index ไป
statistics เก่าล้าสมัย หาก planner คิดว่าเงื่อนไขหนึ่งตรงกับ row มากกว่าความเป็นจริงไปมาก planner อาจเลี่ยงการใช้ index การรัน ANALYZE บน table จะแก้ไขค่าประมาณให้ถูกต้อง
เงื่อนไขไม่สามารถใช้ index ได้ ทั้ง LIKE แบบ leading-wildcard, ฟังก์ชันที่ครอบ column ไว้ หรือชนิดข้อมูลที่ไม่ตรงกัน ล้วนทำให้ index ที่มีอยู่ใช้งานไม่ได้

ใน pgAdmin: ปุ่ม Explain รัน EXPLAIN และเมนู Explain มีตัวเลือก Analyze สำหรับ EXPLAIN ANALYZE pgAdmin จะวาดผลลัพธ์ออกมาเป็นต้นไม้แบบกราฟิก โดยแต่ละโหนดจะแสดงชนิดของ scan และต้นทุน และลูกศรที่หนากว่าหมายถึง row ที่มากกว่า — เป็นวิธีที่รวดเร็วในการมองหาโหนดที่แพง

เคล็ดลับและข้อควรระวัง

EXPLAIN ANALYZE รัน query จริง สำหรับ SELECT นั้นไม่เป็นอันตราย แต่ถ้าเป็น UPDATE หรือ DELETE ข้อมูลจริงจะเปลี่ยนตาม — ให้ห่อไว้ใน transaction ที่คุณ ROLLBACK หากคุณจำเป็นต้องตรวจสอบการเขียน
แผนจะมีความหมายก็ต่อเมื่ออยู่บนข้อมูลที่สมจริงเท่านั้น query ที่ทำ seq scan บน table ทดสอบ 100 row อาจทำ index scan บน table เดียวกันที่มีล้าน row จงทดสอบกับปริมาณข้อมูลที่เป็นตัวแทนจริง
จงเปรียบเทียบ rows ที่ประมาณกับ rows จริงเสมอ ความไม่ตรงกันที่มากชี้ไปยัง statistics ที่ต้องการการรีเฟรช
จงอ่านต้นไม้ของแผนจากในออกนอก: โหนดที่ย่อหน้ามากที่สุดทำงานก่อนแล้วป้อนข้อมูลให้โหนดแม่

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
`EXPLAIN`	ทำงานทันที ปลอดภัยกับทุก query เพราะไม่ได้รันจริง	ได้แค่ค่าประมาณจาก statistics ไม่ใช่ตัวเลขจริง
`EXPLAIN ANALYZE`	เห็นเวลาจริงและจำนวน row จริง เทียบกับค่าประมาณได้ตรง ๆ	รัน query จริง ถ้าเป็น `UPDATE`/`DELETE` จะเปลี่ยนข้อมูลจริงด้วย
`EXPLAIN (ANALYZE, BUFFERS)`	เห็นด้วยว่าข้อมูลมาจากแคชหรือดิสก์ ช่วยวินิจฉัยปัญหา I/O	อ่านผลลัพธ์ซับซ้อนขึ้นอีกนิด ต้องเข้าใจความหมายของ `Buffers`

ข้อผิดพลาดที่พบบ่อย

ปรับแต่ง query จากการเดา แทนที่จะรัน EXPLAIN ก่อน — การเดาว่า query ช้าเพราะอะไรมักผิด จงรัน EXPLAIN เพื่อดูว่า planner เลือกกลยุทธ์ไหนจริง ๆ ก่อนแก้ไขอะไร
รัน EXPLAIN ANALYZE กับ UPDATE/DELETE โดยไม่ห่อใน transaction ที่ ROLLBACK ได้ — EXPLAIN ANALYZE รัน query จริง ถ้าเป็นคำสั่งเขียนข้อมูล ข้อมูลจริงก็จะเปลี่ยนไปด้วย จงห่อไว้ใน transaction แล้ว ROLLBACK เมื่อดูผลเสร็จ
มองข้ามช่องว่างใหญ่ระหว่าง rows ที่ประมาณกับ rows จริง — ช่องว่างที่กว้างคือสัญญาณว่า statistics เก่าล้าสมัย ซึ่งนำไปสู่แผนที่ไม่ดี จงรัน ANALYZE เพื่อรีเฟรชค่าประมาณเหล่านั้น

💡 ตัวอย่างจากของจริง

pganalyze — เครื่องมืออย่าง pganalyze ถูกสร้างมาเพื่อดึง EXPLAIN plan ออกมาอัตโนมัติและแจ้งเตือนเมื่อ query ใดมี plan ที่แย่ลง (regression) ช่วยให้ทีมจับปัญหาได้ก่อนที่ผู้ใช้จะรู้สึก

GitLab — ทีมด้านประสิทธิภาพของ GitLab กำหนดให้ merge request ที่แก้ query ซึ่งถูกเรียกบ่อย ต้องแนบผลลัพธ์ EXPLAIN (ANALYZE, BUFFERS) มาด้วยเสมอ เพื่อให้ reviewer เห็นผลกระทบจริงก่อน merge