เข้าใจ B-tree Index ใน PostgreSQL ก่อนรีบเพิ่ม Index ให้ Query เร็วขึ้น

เวลาที่ PostgreSQL ทำงานช้า หลายคนมักรีบสรุปว่าควรเพิ่ม index ทันที แต่ในความเป็นจริง การเพิ่ม index แบบไม่วิเคราะห์ก่อน อาจไม่ได้ช่วยให้ query เร็วขึ้นเสมอไป และบางครั้งยังทำให้ระบบโดยรวมช้าลงกว่าเดิมด้วย

หัวใจสำคัญคือการเข้าใจว่า B-tree index ซึ่งเป็น index แบบ default ของ PostgreSQL ทำงานอย่างไร และเหมาะกับ query แบบไหน หากเข้าใจตรงนี้ เราจะเลือกสร้าง index ที่ช่วยจริง แทนที่จะเพิ่มภาระให้ database โดยไม่จำเป็น

B-tree คืออะไร และช่วยให้ค้นหาเร็วขึ้นอย่างไร

B-tree เป็นโครงสร้าง index มาตรฐานที่ PostgreSQL ใช้เป็นค่าเริ่มต้น แนวคิดง่าย ๆ คือมันทำหน้าที่คล้าย สารบัญที่เรียงลำดับข้อมูลไว้ล่วงหน้า ทำให้ระบบไม่จำเป็นต้องอ่านทุกแถวในตารางเมื่อมีการค้นหา

เช่น ถ้าเรามีการ query ด้วยคอลัมน์อย่าง user_id, email หรือ created_at PostgreSQL สามารถใช้ B-tree เพื่อกระโดดไปยังช่วงข้อมูลที่มีแนวโน้มจะตรงกับเงื่อนไขได้ทันที แทนการไล่อ่านทั้ง table แบบทีละแถว

ตัวอย่าง query ที่พบได้บ่อยในงาน backend คือ:

SELECT * FROM orders WHERE user_id = 42;

ถ้า orders มีข้อมูลหลายล้านแถว และยังไม่มี index ที่ user_id PostgreSQL อาจเลือกใช้ Seq Scan หรือการอ่านทั้ง table ตั้งแต่ต้นจนจบ ซึ่งมีต้นทุนสูงมากเมื่อข้อมูลขยายใหญ่ขึ้น

ในกรณีนี้ การสร้าง index แบบตรงไปตรงมาจะช่วยได้มาก:

CREATE INDEX idx_orders_user_id ON orders(user_id);

B-tree เหมาะกับเงื่อนไขประเภทต่อไปนี้เป็นพิเศษ:

• =
• <, >
• BETWEEN
• IN
• การค้นหาแบบช่วงข้อมูล เช่น created_at >= yesterday

Index ไม่ได้ช่วยแค่ WHERE แต่ช่วย ORDER BY ได้ด้วย

หลายคนมองว่า index มีไว้ช่วยเฉพาะการ filter ข้อมูลใน WHERE แต่จริง ๆ แล้ว index ยังช่วยลดภาระของการ ORDER BY ได้ด้วย หากลำดับใน index ตรงกับลำดับที่ query ต้องการ

ตัวอย่างเช่น การดึง feed ล่าสุดของผู้ใช้:

SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

ถ้าเราออกแบบ index แบบนี้:

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

PostgreSQL จะสามารถค้นหาข้อมูลของ user_id ที่ต้องการก่อน จากนั้นอ่านข้อมูลต่อในลำดับ created_at DESC ได้เลย ทำให้ลดหรือเลี่ยงการ sort ขนาดใหญ่หลัง filter ซึ่งมักส่งผลต่อความเร็วอย่างชัดเจน โดยเฉพาะ query ที่มี LIMIT

Composite Index: มีหลายคอลัมน์ไม่ได้แปลว่าใช้ได้ทุกกรณี

จุดที่ผู้เริ่มต้นพลาดบ่อยมากคือการออกแบบ composite index หรือ index ที่มีหลายคอลัมน์ เพราะคิดว่าถ้ามีครบทุก field ที่ query ใช้ ก็ต้องช่วยแน่นอน

ตัวอย่างเช่น:

(user_id, status, created_at)

index นี้ไม่ได้หมายความว่าจะทำงานดีเท่ากันกับทุก query ที่มีทั้ง 3 คอลัมน์นี้อยู่ในระบบ หลักสำคัญคือ ลำดับคอลัมน์มีผลมาก และมักอธิบายง่าย ๆ ได้ด้วยกฎ “ซ้ายสุดมาก่อน”

• ถ้า query มี WHERE user_id = ? PostgreSQL มักใช้ index นี้ได้ดี
• ถ้ามี WHERE user_id = ? AND status = ? ก็ยิ่งเหมาะ
• แต่ถ้ามีแค่ WHERE status = ? index นี้อาจช่วยได้น้อย หรือแทบไม่ช่วยเลย เพราะ status ไม่ใช่คอลัมน์ตัวแรก

ดังนั้น การเรียงลำดับคอลัมน์ใน composite index ต้องอิงจากรูปแบบ query ที่ใช้จริง ไม่ใช่อิงจากความรู้สึกว่าคอลัมน์ไหน “น่าจะสำคัญ”

หลักคิดเรื่อง equality และ range ในการจัดลำดับคอลัมน์

อีกหลักหนึ่งที่ใช้ได้ดีมากในงานจริงคือ คอลัมน์ที่ใช้เทียบเท่ากับค่าแน่นอน มักควรมาก่อนคอลัมน์ที่ใช้ค้นหาเป็นช่วง

ตัวอย่างเช่น ถ้า query ของเรามีลักษณะนี้:

WHERE user_id = ?
AND created_at BETWEEN ? AND ?

index ที่มักเหมาะคือ:

(user_id, created_at)

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

ทำไมบางครั้งสร้าง Index แล้ว PostgreSQL ก็ยังไม่ใช้

การมี index ไม่ได้การันตีว่า PostgreSQL จะเลือกใช้เสมอ เพราะ query planner จะประเมินต้นทุนของหลายแผนก่อนตัดสินใจ

1) ข้อมูลไม่ selective พอ

ถ้าคอลัมน์มีค่าซ้ำกันเยอะมาก index อาจไม่คุ้มที่จะใช้ เช่นคอลัมน์ status ที่มีแค่ไม่กี่ค่าอย่าง pending, paid, failed และสมมุติว่า paid กินสัดส่วนถึง 80% ของทั้งตาราง

ในสถานการณ์แบบนี้ PostgreSQL อาจมองว่าอ่าน table ตรง ๆ คุ้มกว่า เพราะถึงใช้ index ก็ยังต้องกลับไปอ่านข้อมูลจำนวนมากจาก heap อยู่ดี

2) รูปแบบ query ไม่ตรงกับ index

ตัวอย่างคลาสสิกคือสร้าง index ไว้ที่ email แต่ query เขียนแบบนี้:

LOWER(email) = 'a@b.com'

index ปกติบน email อาจช่วยไม่ได้ เพราะ query ไม่ได้ใช้ค่าดิบของคอลัมน์โดยตรง แต่ใช้ผลลัพธ์จาก expression แทน กรณีนี้อาจต้องพิจารณา expression index ให้ตรงกับรูปแบบ query

3) ตารางยังเล็กเกินไป

ถ้า table มีข้อมูลแค่ไม่กี่พันแถว การทำ Seq Scan อาจเร็วกว่า index scan เพราะต้นทุนในการเปิดและเดินผ่าน index อาจไม่คุ้ม

นี่จึงเป็นเหตุผลว่าทำไมบางครั้งบนเครื่อง development PostgreSQL อาจไม่ใช้ index แต่เมื่อขึ้น production ที่ข้อมูลเยอะกว่า กลับมีการใช้ index อย่างชัดเจน

Index ไม่ฟรี: ยิ่งเยอะยิ่งเพิ่มภาระการเขียนข้อมูล

แม้ index จะช่วยเร่งการอ่าน แต่ก็มีต้นทุนที่ต้องจ่ายเสมอ ทุกครั้งที่มี:

• INSERT
• UPDATE
• DELETE

PostgreSQL ต้องอัปเดต index ที่เกี่ยวข้องตามไปด้วย ยิ่ง table มี index มาก ต้นทุนของการเขียนข้อมูลก็ยิ่งสูง

ผลกระทบที่มักเกิดขึ้นคือ:

• write latency สูงขึ้น
• ใช้ storage มากขึ้น
• งานดูแลระบบอย่าง vacuum และ maintenance หนักขึ้น

ในระบบ production ปัญหา over-indexing เกิดขึ้นได้จริงมาก บางทีมเห็น query ช้าไม่กี่ตัวแล้วสร้าง index จำนวนมากเผื่อไว้ทุกทาง สุดท้าย query อ่านบางชุดอาจเร็วขึ้น แต่ API ที่ต้องเขียนข้อมูลบ่อย เช่น checkout หรือ event tracking กลับช้าลงกว่าเดิม

วิธีคิดที่ถูกต้อง: อย่าเดา ให้ดูแผนการทำงานจริง

แนวทางที่ดีที่สุดในการปรับปรุง performance คือ อย่าคาดเดา แต่ให้ใช้เครื่องมือของ PostgreSQL ตรวจสอบแผนจริงด้วย:

EXPLAIN (ANALYZE, BUFFERS)

คำสั่งนี้ช่วยให้เราเห็นว่า:

• PostgreSQL อ่านข้อมูลไปกี่แถว
• ใช้ scan แบบไหน
• เสียเวลาที่จุดใด
• มีการ sort หนักหรือไม่
• มีการอ่านจาก disk มากแค่ไหน

ตัวอย่างสิ่งที่ควรสังเกต:

• ถ้าเห็น Sort ขนาดใหญ่หลังการ filter อาจต้องคิดว่า index จะช่วย ORDER BY ได้ไหม
• ถ้าเห็น Rows Removed by Filter จำนวนมาก อาจแปลว่า index ยังไม่ตรงกับเงื่อนไข WHERE
• ถ้าเห็น Bitmap Heap Scan ก็ไม่จำเป็นต้องตกใจ เพราะสำหรับ query บางประเภท นี่อาจเป็นแผนที่เหมาะสมอยู่แล้ว

แนวทางออกแบบ Index แบบใช้งานจริง

หากต้องเริ่มปรับปรุงระบบจริง ควรใช้ลำดับความคิดดังนี้:

1. เริ่มจาก query ที่ช้าที่สุด และถูกเรียกบ่อยที่สุด
2. ออกแบบ index ให้ตรงกับ WHERE ก่อน
3. จากนั้นค่อยพิจารณา ORDER BY และ LIMIT
4. ถ้าเป็น composite index ให้คิดจากซ้ายไปขวา
5. อย่าสร้าง index เผื่อทุกกรณีที่ “อาจจะเกิดขึ้น”

เป้าหมายไม่ใช่การมี index เยอะที่สุด แต่คือการมี index เท่าที่จำเป็นและตอบโจทย์รูปแบบการใช้งานจริงที่สุด

สรุป

B-tree ไม่ใช่ปุ่มเร่งความเร็วแบบวิเศษที่กดแล้วทุก query จะดีขึ้นทันที แต่มันคือเครื่องมือจัดลำดับข้อมูลให้เหมาะกับวิธีค้นหาของเรา

ถ้า query สอดคล้องกับลำดับใน index PostgreSQL จะทำงานเร็วขึ้นอย่างเห็นผล แต่ถ้าออกแบบ index ไม่ตรงกับพฤติกรรมการใช้งาน ก็อาจได้เพียงภาระเพิ่มทั้งด้านการเขียนข้อมูล พื้นที่จัดเก็บ และงานดูแลระบบ

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