PostgreSQL Index ที่ใช้ไม่ได้ผล และวิธีดูด้วย EXPLAIN

การสร้าง index เพิ่มไม่ได้แปลว่า query จะเร็วขึ้นเสมอ หลายครั้ง index ที่สร้างไว้ไม่เคยถูกใช้เลย เพราะเงื่อนไขใน query ทำให้ planner เลือกใช้ไม่ได้ วิธีเดียวที่จะรู้คือดู plan จริง

เริ่มจาก EXPLAIN ANALYZE เสมอ

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM posts
WHERE status = 'published' AND published_at > now() - interval '30 days'
ORDER BY published_at DESC
LIMIT 20;

สิ่งที่ต้องดูคือ Seq Scan บนตารางใหญ่ ค่า rows ที่ประมาณไว้เทียบกับ actual rows และเวลาที่ใช้จริงในแต่ละ node ถ้าตัวเลขประมาณกับของจริงต่างกันหลายสิบเท่า แปลว่าสถิติของตารางเก่า ให้รัน ANALYZE ก่อนแล้วดูใหม่

กรณีที่ index ถูกมองข้าม

ใส่ฟังก์ชันครอบคอลัมน์ — WHERE lower(email) = $1 จะไม่ใช้ index บน email ต้องสร้าง expression index บน lower(email) แทน
ชนิดข้อมูลไม่ตรงกัน — เทียบ bigint กับค่าที่ส่งมาเป็น text ทำให้ต้อง cast ทั้งคอลัมน์
ผลลัพธ์กว้างเกินไป — ถ้า query ดึงข้อมูลเกินราวหนึ่งในสามของตาราง การอ่านทั้งตารางเรียงตามลำดับมักถูกกว่าการวิ่ง index แล้วสุ่มอ่าน
LIKE '%คำค้น%' — B-tree ช่วยไม่ได้เมื่อ wildcard อยู่หน้าสุด ต้องใช้ trigram index

ลำดับคอลัมน์ใน composite index สำคัญ

CREATE INDEX idx_posts_status_published
  ON posts (status, published_at DESC);

หลักคร่าวๆ คือให้คอลัมน์ที่ใช้เทียบแบบเท่ากับอยู่หน้า แล้วตามด้วยคอลัมน์ที่ใช้เทียบแบบช่วงหรือใช้เรียงลำดับ ถ้าสลับเป็น (published_at, status) index นี้จะช่วยเฉพาะการกรองด้วยช่วงเวลา แต่ยังต้องกรอง status ทีละแถวอยู่ดี

Partial index สำหรับข้อมูลที่ query จริงแค่ส่วนเดียว

ถ้า 95% ของ query สนใจเฉพาะบทความที่เผยแพร่แล้ว ไม่จำเป็นต้องทำ index ครอบทั้งตาราง

CREATE INDEX idx_posts_published
  ON posts (published_at DESC)
  WHERE status = 'published';

index เล็กลงแปลว่าอยู่ใน memory ได้มากขึ้น และการเขียนข้อมูลก็เสียค่าใช้จ่ายน้อยลงด้วย

ตรวจว่า index ไหนไม่เคยถูกใช้

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

index ที่ไม่เคยถูกสแกนเลยคือค่าใช้จ่ายล้วนๆ ทั้งพื้นที่และเวลาเขียน ลบทิ้งได้ แต่ควรดูให้แน่ใจก่อนว่าไม่ได้เป็น index ที่รองรับ constraint หรือเพิ่งสร้างมาไม่นาน