เกิดอะไรขึ้น
เราสร้าง index ใหม่แล้วรัน EXPLAIN ANALYZE เทียบกับ query เดิม ผลรอบแรกของ query ที่ใช้ index ใหม่ดูช้าจนน่าตกใจ เกือบสรุปไปแล้วว่า index นี้ไม่ช่วย
พอรันซ้ำอีกรอบ เวลากลับลดลงมาก ทั้งที่ไม่ได้เปลี่ยนอะไรเลย
ทำไมถึงเป็นแบบนั้น
EXPLAIN ANALYZE ไม่ได้โกหก แต่มันวัดตามสภาพ cache ของรอบนั้น ถ้าข้อมูลหรือ index ที่ต้องใช้ยังไม่อยู่ใน shared buffers ของ PostgreSQL Postgres จะต้องขออ่านจากระบบปฏิบัติการ ซึ่งอาจต้องไปอ่านจาก disk จริง การรันรอบแรกจึงรวมต้นทุนการโหลดข้อมูลเข้ามาด้วย
รอบถัดไปหน้าเหล่านั้นอยู่ใน cache แล้ว เวลาจึงสั้นลงมาก ปัญหาคือถ้าเราเอาผลรอบแรก (cold) ของ query หนึ่งไปเทียบกับผลรอบหลัง (warm) ของอีก query หนึ่ง เราจะสรุปผิดได้ว่า index ช่วยหรือไม่ช่วย ทั้งที่ความต่างมาจาก cache ไม่ใช่แผนการทำงาน
ตอนนี้เราทำอย่างไร
เราเปลี่ยนมารันสองรอบเสมอ แล้วอ่านผลรอบที่สอง และใส่ BUFFERS ด้วย เพื่อดู shared hit ซึ่งคือหน้าที่อ่านจาก shared buffers และ read ซึ่งคือหน้าที่ต้องขอจากข้างนอก ถ้ารอบแรกมี read มากแต่รอบสองเป็น hit เกือบทั้งหมด แปลว่าความต่างของเวลามาจาก cache
ควรจำไว้ด้วยว่าค่า read อาจมาจาก page cache ของระบบปฏิบัติการ ไม่ได้แปลว่าไปถึง disk เสมอไป และถ้า production จริงมักพบข้อมูลที่ไม่ได้อยู่ใน cache ผลรอบ warm ก็อาจดูดีเกินจริง จึงควรดูทั้งสองกรณี
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 42 AND deleted_at IS NULL;
-- รันซ้ำ แล้วเทียบ "shared hit" กับ "read" ของแต่ละรอบเช็กลิสต์สั้น ๆ
- 1รัน EXPLAIN (ANALYZE, BUFFERS) รอบแรกแล้วจดผลไว้
- 2รันซ้ำอีกรอบแล้วดูรอบที่สอง
- 3เทียบ shared hit กับ read ในแต่ละรอบ
- 4เวลาเทียบก่อนและหลังสร้าง index ให้เทียบรอบ warm กับรอบ warm
จำไว้สั้น ๆ
รัน EXPLAIN ANALYZE สองรอบพร้อม BUFFERS และเทียบรอบ warm กับ warm เสมอ