Database index คืออะไร ทำไม query ถึงช้า ดูจาก EXPLAIN

ตอบสั้น ๆ ก่อนครับ index คือโครงสร้างข้อมูลที่ database เก็บแยกจากตัวตาราง เพื่อหาแถวที่ตรงเงื่อนไขได้โดยไม่ต้องอ่านทุกแถว ใน PostgreSQL ถ้าไม่มี index ที่ใช้ได้ query อย่าง WHERE id = 42 ต้องอ่านทั้งตารางทีละแถว แต่ถ้ามี index บนคอลัมน์ id ระบบอาจลงไปแค่ไม่กี่ชั้นใน search tree

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

SELECT * FROM orders WHERE id = 42;                             
                                                                
Seq Scan on orders    Filter: (id = 42)                         
                                                                
  [p1]  [p2]  [p3]  [p4]  [p5]  [p6]  [p7]  [p8]  [p9]  [p10]   
                                                                
heap pages read:  0 / 10                                        
                                                                
┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄
Index Scan using orders_pkey  Index Cond: (id = 42)             
                          [root] ●                              
             [1-40]       [41-80]       [81-120]                
                  [41-50]  [51-60]  [61-80]                     
                                                                
  [p1]  [p2]  [p3]  [p4]  [p5]  [p6]  [p7]  [p8]  [p9]  [p10]   
                                                                
index pages read: 0   heap pages read:  0 / 10                  
                                                                

Database index คืออะไร

index คือข้อมูลชุดที่สองที่เก็บค่าของคอลัมน์ (หรือ expression) ไว้แบบเรียงลำดับ พร้อมตำแหน่งของแถวในตาราง ใน PostgreSQL index ทุกตัวเป็น secondary index คือเก็บแยกจากพื้นที่หลักของตารางซึ่งเรียกว่า heap

สร้าง index ด้วยคำสั่งเดียว:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

ถ้าไม่ระบุชนิด CREATE INDEX สร้าง B-tree ซึ่งเหมาะกับงานส่วนใหญ่ PostgreSQL ยังมี Hash, GiST, SP-GiST, GIN และ BRIN ให้เลือกด้วย USING เมื่อสร้างแล้วไม่ต้องดูแลเอง ระบบอัปเดต index ทุกครั้งที่ตารางเปลี่ยน และจะใช้ index ก็ต่อเมื่อ planner คิดว่าเร็วกว่า sequential scan

PostgreSQL หาแถวอย่างไรเมื่อไม่มี index

PostgreSQL ใช้ Seq Scan คืออ่านทุก page ของตารางตามลำดับ แล้วเช็กเงื่อนไขกับทุกแถว ตัวอย่างจากเอกสาร EXPLAIN ของ PostgreSQL เอง (Using EXPLAIN):

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

 Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244)
   Filter: (unique1 < 7000)

บรรทัด Filter แปลว่า node นี้อ่านทุกแถวแล้วคัดเฉพาะแถวที่ผ่านเงื่อนไข เอกสารบอกไว้ว่า scan นี้ยังต้องอ่านครบ 10000 แถว cost จึงไม่ลดลง แต่เพิ่มขึ้นเล็กน้อยจากการเช็กเงื่อนไข

Seq Scan ไม่ได้ผิดเสมอไปครับ ถ้า query ต้องการแถวส่วนใหญ่ของตาราง การอ่านทุก page ตามลำดับมักถูกกว่า ปัญหาเกิดเมื่อ query ต้องการแถวเดียวจากตารางหลายล้านแถว แต่ plan ยังเป็น Seq Scan

B-tree index เก็บข้อมูลอย่างไร

B-tree ใน PostgreSQL เป็นโครงสร้างหลายชั้น ประกอบด้วย:

• leaf page ชั้นล่างสุด แต่ละ tuple ใน leaf ชี้ไปที่แถวในตาราง
• internal page ชั้นที่อยู่เหนือขึ้นไป แต่ละ tuple ชี้ลงไปที่ page ในชั้นถัดไป
• metapage หนึ่ง page อยู่ตำแหน่งคงที่ตอนต้นไฟล์ของ index

เอกสารระบุว่าโดยทั่วไปมากกว่า 99% ของ page ทั้งหมดเป็น leaf page เมื่อ leaf page เต็มจะเกิด page split และถ้า root page เต็มด้วย ระบบจะสร้าง root ใหม่เพิ่มอีกหนึ่งชั้น จำนวนชั้นจึงโตช้ากว่าจำนวนแถวมาก

เพราะค่าใน B-tree เรียงลำดับ planner จึงใช้ B-tree กับ operator < <= = >= > รวมถึง BETWEEN, IN, IS NULL และ LIKE 'foo%' ที่ pattern ขึ้นต้นด้วยค่าคงที่ แต่ใช้กับ LIKE '%bar' ไม่ได้ และถ้า database ไม่ได้ใช้ C locale ต้องสร้าง index ด้วย operator class พิเศษจึงจะใช้กับ LIKE ได้

ทำไม PostgreSQL ไม่ใช้ index ที่สร้างไว้

เพราะ planner เลือก plan ที่ cost ต่ำที่สุดตามที่มันประเมิน และบางครั้ง Seq Scan ถูกกว่าจริง ๆ ครับ สาเหตุที่เจอบ่อยมีดังนี้

• query ต้องการแถวจำนวนมาก index scan ปกติต้องอ่านทั้ง index และ heap และแถวที่ index ชี้ไปอาจอยู่ที่ไหนก็ได้ใน heap จึงเป็นการอ่านแบบ random ค่า random_page_cost ซึ่งเป็น cost ของการอ่าน page แบบไม่เรียงลำดับ มีค่าเริ่มต้น 4.0 เทียบกับ seq_page_cost ที่ 1.0
• ตารางเล็ก ตารางที่มีแค่ page เดียว planner แทบจะเลือก sequential scan เสมอ ไม่ว่ามี index หรือไม่ เพราะยังไงก็ต้องอ่าน page นั้นอยู่ดี
• เงื่อนไขไม่ตรงกับ index เช่น index บน email แต่ query เขียน WHERE lower(email) = '...' กรณีนี้ต้องสร้าง index บน expression lower(email) แทน
• statistics เก่าหรือไม่มี planner ใช้ statistics จาก ANALYZE ประเมินจำนวนแถว เอกสารเขียนไว้ตรง ๆ ว่าการดูการใช้ index โดยยังไม่ได้รัน ANALYZE ไม่มีประโยชน์

อ่าน EXPLAIN อย่างไร

EXPLAIN แสดง plan เป็นต้นไม้ของ node โดยแต่ละบรรทัดคือหนึ่ง node และ node ที่ย่อหน้าลึกกว่าคือ input ของ node ด้านบน node ล่างสุดเป็น scan node ที่อ่านแถวจากตาราง เช่น sequential scan, index scan และ bitmap index scan

ตัวเลขใน cost

ใน (cost=0.00..445.00 rows=10000 width=244) ตัวเลขจากซ้ายไปขวาคือ cost ก่อนเริ่มส่งแถวแรก, cost รวมถ้า node รันจนจบ, จำนวนแถวที่คาดว่าจะส่งออก และความกว้างเฉลี่ยของแถวเป็นไบต์ (Using EXPLAIN)

cost ไม่ใช่มิลลิวินาที แต่เป็นหน่วยที่ planner กำหนดเอง ตัวอย่างในเอกสารคำนวณให้ดู ตาราง tenk1 มี 345 page และ 10000 แถว cost ของ Seq Scan คือ (345 × 1.0) + (10000 × 0.01) = 445 โดย 1.0 คือ seq_page_cost และ 0.01 คือ cpu_tuple_cost

Seq Scan, Index Scan และ Bitmap Heap Scan

เมื่อเงื่อนไขแคบลงเป็น unique1 < 100 plan ในเอกสารเปลี่ยนเป็นสองชั้น:

 Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

node ลูกอ่าน index เพื่อหาตำแหน่งของแถว ส่วน node แม่เรียงตำแหน่งเหล่านั้นตามลำดับทางกายภาพก่อนไปอ่าน heap เพื่อลดต้นทุนของการอ่านแยกทีละแถว ส่วนเงื่อนไข unique1 = 42 ที่ได้แถวเดียว planner เลือก Index Scan ธรรมดา ซึ่งเอกสารบอกว่าเจอบ่อยที่สุดกับ query ที่ดึงแถวเดียว

Index Cond กับ Filter

สองบรรทัดนี้คือสิ่งแรกที่ผมดูใน plan ครับ Index Cond คือเงื่อนไขที่ใช้ค้นใน index ส่วน Filter คือเงื่อนไขที่เช็กกับแถวหลังจากอ่านมาแล้ว ในตัวอย่างของเอกสาร query WHERE unique1 < 100 AND stringu1 = 'xxx' ใช้ index ได้แค่ส่วน unique1 ส่วน stringu1 = 'xxx' กลายเป็น filter เพราะ index มีแค่คอลัมน์ unique1 จำนวนแถวที่คาดจะลดลง แต่ cost ไม่ลด เพราะยังต้องอ่านแถวชุดเดิม

EXPLAIN ANALYZE บอกอะไรเพิ่ม

EXPLAIN ANALYZE รัน query จริง แล้วแสดงจำนวนแถวและเวลาจริงของแต่ละ node คู่กับค่าที่ประเมินไว้ สิ่งที่เอกสารแนะนำให้ดูก่อนคือจำนวนแถวที่ประเมินใกล้กับของจริงหรือไม่ ถ้าประเมินไว้ 10 แถวแต่จริงได้ 50000 แถว planner ก็เลือก plan จากข้อมูลที่ผิด

รายละเอียดที่ควรรู้:

• actual time เป็นมิลลิวินาที ส่วน cost เป็นหน่วยของ planner สองค่านี้จึงไม่ควรนำมาเทียบกันตรง ๆ
• loops ถ้า node ถูกรันหลายรอบ เช่นด้านในของ nested loop เวลาและจำนวนแถวที่แสดงเป็นค่าเฉลี่ยต่อรอบ ต้องคูณด้วย loops เอง
• Rows Removed by Filter บอกว่า filter ทิ้งไปกี่แถว ในตัวอย่างของเอกสาร Seq Scan ที่ ten < 7 ส่งออก 7000 แถวและทิ้ง 3000 แถว ถ้าเลขนี้สูงมากเทียบกับแถวที่ได้ ส่วนใหญ่แปลว่า index ไม่ครอบเงื่อนไขนั้น
• Heap Fetches ใน Index Only Scan คือจำนวนครั้งที่ยังต้องไปเปิด heap เพื่อเช็กว่าแถวมองเห็นได้หรือไม่ index-only scan ได้ประโยชน์ก็ต่อเมื่อ heap page ส่วนใหญ่ถูกตั้งค่า all-visible ใน visibility map แล้ว

ข้อควรระวังสำคัญคือ EXPLAIN ANALYZE กับ UPDATE หรือ DELETE จะแก้ข้อมูลจริง ถ้าแค่อยากดู plan ให้ครอบด้วย transaction แล้ว rollback:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE id = 42;
ROLLBACK;

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

index หลายคอลัมน์ควรเรียงคอลัมน์อย่างไร

ให้คอลัมน์ที่กรองด้วย = อยู่ซ้ายสุด แล้วตามด้วยคอลัมน์ที่ใช้เป็นช่วง (range) PostgreSQL เขียนกฎของ B-tree หลายคอลัมน์ไว้ว่า เงื่อนไข = บนคอลัมน์ซ้ายสุด บวกกับเงื่อนไขแบบไม่เท่ากันบนคอลัมน์แรกที่ไม่มี = คือส่วนที่จำกัดช่วงของ index ที่ต้อง scan เงื่อนไขบนคอลัมน์ทางขวาถัดไปจะถูกเช็กใน index ซึ่งช่วยลดการเปิด heap แต่ไม่จำเป็นต้องลดส่วนของ index ที่ต้องอ่าน

ตัวอย่างเช่น query ที่ใช้บ่อยคือ "คำสั่งซื้อของลูกค้าคนหนึ่งในช่วงเวลาหนึ่ง":

SELECT id, created_at FROM orders
WHERE customer_id = 1001 AND created_at >= '2026-01-01';

CREATE INDEX orders_cust_created_idx ON orders (customer_id, created_at);

ลำดับ (customer_id, created_at) ทำให้ index กระโดดไปที่ลูกค้า 1001 แล้วอ่านต่อเนื่องเฉพาะช่วงวันที่ ถ้าสลับเป็น (created_at, customer_id) index จะต้องอ่านทุกแถวตั้งแต่ 1 มกราคมเป็นต้นไปของลูกค้าทุกคน แล้วค่อยเช็ก customer_id ทีละ entry

PostgreSQL 18 เพิ่ม skip scan ให้ B-tree หลายคอลัมน์ใช้ได้แม้ query ไม่มี = บนคอลัมน์นำหน้า แต่เอกสารบอกว่าโดยทั่วไป planner จะเลือกวิธีนี้เมื่อคอลัมน์นำหน้ามีค่าที่ต่างกันน้อยมาก ถ้ามีค่าต่างกันเยอะ planner ส่วนใหญ่จะเลือก sequential scan แทน การเรียงคอลัมน์ให้ตรงกับ query ยังสำคัญอยู่ครับ index หนึ่งตัวมีได้สูงสุด 32 คอลัมน์ แต่ไม่ได้แปลว่าควรใส่เยอะ

index มีราคาอะไรบ้าง

index ทำให้อ่านเร็วขึ้น แต่ทำให้การเขียนช้าลง และใช้พื้นที่เพิ่มครับ

• ทุกการเขียนต้องอัปเดต index ด้วย ระบบต้องทำให้ index ตรงกับตารางตลอด ซึ่งเพิ่ม overhead ให้ INSERT, UPDATE และ DELETE และ index ยังอาจทำให้ใช้ HOT (heap-only tuple) ไม่ได้ ในกรณีที่ UPDATE ใช้ HOT ไม่ได้ การเปลี่ยนค่าแค่คอลัมน์เดียวต้องสร้าง index tuple ใหม่ในทุก index ของตาราง รวมถึง index ที่ไม่ได้ครอบคอลัมน์นั้นเลย
• การสร้าง index บนตารางใหญ่ล็อกการเขียน CREATE INDEX ปกติให้ SELECT ทำงานต่อได้ แต่ INSERT, UPDATE, DELETE จะรอจนสร้างเสร็จ ถ้าเป็นระบบที่ใช้งานจริงให้ใช้ CREATE INDEX CONCURRENTLY ซึ่งไม่ล็อกการเขียน แต่ต้อง scan ตารางสองรอบและใช้เวลานานกว่ามาก รันใน transaction block ไม่ได้ และถ้าล้มกลางทางจะทิ้ง index ที่สถานะ INVALID ไว้ ซึ่งไม่ถูกใช้ตอน query แต่ยังมี overhead ตอนเขียน
• index ที่ไม่มีใครใช้คือต้นทุนเปล่า เอกสารแนะนำให้ลบ index ที่แทบไม่ถูกใช้ ดูได้จาก view pg_stat_user_indexes ซึ่งมีคอลัมน์ idx_scan (จำนวน index scan) และ last_idx_scan (Cumulative Statistics System)

SELECT relname, indexrelname, idx_scan, last_idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

ก่อนลบ index ที่ idx_scan ต่ำ ผมจะเช็กก่อนว่า index นั้นไม่ได้ใช้บังคับ unique constraint อยู่ และ statistics ไม่ได้เพิ่งถูก reset

ไล่หาสาเหตุ query ช้าทีละขั้นได้อย่างไร

ลำดับที่ผมใช้เมื่อเจอ query ช้า เรียงจากสิ่งที่เช็กง่ายไปหายากครับ

1. รัน ANALYZE บนตารางนั้นก่อน ให้ planner มี statistics ที่ใหม่ (Examining Index Usage)
2. รัน EXPLAIN ANALYZE บนข้อมูลจริงหรือขนาดใกล้เคียง เอกสารแนะนำให้ใช้ข้อมูลจริงในการทดลอง เพราะข้อมูลทดสอบบอกได้แค่ว่าข้อมูลทดสอบต้องการ index อะไร
3. หา node ที่ใช้เวลามากที่สุด อย่าลืมคูณ loops
4. เทียบ rows ที่ประเมินกับ actual rows ถ้าห่างกันมาก ให้แก้เรื่อง statistics ก่อนเพิ่ม index
5. ดู Filter และ Rows Removed by Filter ถ้าทิ้งแถวเยอะ เงื่อนไขนั้นอาจต้องการ index หรือต้องเพิ่มคอลัมน์ใน index เดิม
6. ถ้าสงสัยว่าทำไม planner ไม่ใช้ index ลองปิด enable_seqscan ใน session ทดสอบ ถ้า planner ยังไม่ใช้ index แปลว่าเงื่อนไขอาจไม่ตรงกับ index ถ้าใช้แล้ว ให้จับเวลาเทียบทั้งสองแบบ เพราะบางครั้ง planner เลือกถูกแล้ว
7. สร้าง index ด้วย CONCURRENTLY บนระบบจริง แล้วรัน EXPLAIN ANALYZE ซ้ำเพื่อยืนยันว่า plan เปลี่ยนจริง

index ที่ดีคือ index ที่ตรงกับ query ที่รันจริงบ่อย ๆ ไม่ใช่ index บนทุกคอลัมน์ครับ

แหล่งอ้างอิง

• PostgreSQL 18: 11.1 Introduction (Indexes)
• PostgreSQL 18: 11.2 Index Types
• PostgreSQL 18: 11.3 Multicolumn Indexes
• PostgreSQL 18: 11.7 Indexes on Expressions
• PostgreSQL 18: 11.9 Index-Only Scans and Covering Indexes
• PostgreSQL 18: 11.12 Examining Index Usage
• PostgreSQL 18: 14.1 Using EXPLAIN
• PostgreSQL 18: 19.7 Query Planning
• PostgreSQL 18: 27.2 The Cumulative Statistics System
• PostgreSQL 18: 65.1 B-Tree Indexes
• PostgreSQL 18: CREATE INDEX
• PostgreSQL 18 Released (25 September 2025)