ทำไม index จึงสำคัญ

คุณสามารถเขียน SQL ที่ถูกต้องสมบูรณ์แบบได้ แต่ก็ยังช้าอยู่ดี คำสั่ง query คืน row ที่ถูกต้องกลับมา แต่ใช้เวลาหลายวินาทีแทนที่จะเป็นมิลลิวินาที เพราะ PostgreSQL ต้องทำงานมากเกินกว่าที่จำเป็น โดยส่วนใหญ่แล้วทางแก้คือ index — โครงสร้างข้อมูลที่แยกต่างหากและถูกเรียงลำดับไว้ ที่ช่วยให้ server กระโดดตรงไปยัง row ที่คุณต้องการได้ทันที แทนที่จะต้องตรวจสอบทั้ง table

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

Sequential Scan

ลองนึกถึง table books ที่มีสิบล้าน row และ query นี้:

SELECT * FROM books WHERE title = 'The Glass Orchard';

เมื่อไม่มี index บน title PostgreSQL ก็ไม่มีทางรู้ว่า row นั้นอยู่ที่ไหน จึงต้องอ่าน table ตั้งแต่ row แรกไปจนถึง row สุดท้าย โดยตรวจสอบ title ของทุก ๆ row นี่คือ Sequential Scan (มักเรียกย่อ ๆ ว่า seq scan) วิธีนี้เรียบง่ายและเชื่อถือได้ แต่ต้นทุนโตขึ้นตามขนาดของ table คือถ้า row เพิ่มเป็นสองเท่า เวลาก็เพิ่มขึ้นราว ๆ สองเท่า

seq scan ไม่ได้แย่เสมอไป หากคุณกำลังอ่านข้อมูลส่วนใหญ่ของ table อยู่แล้ว การสแกนทื่อ ๆ ไปเรื่อย ๆ ก็เป็นวิธีที่เร็วที่สุด ปัญหาคือการเอาวิธีนี้ไปใช้งมเข็มในกองฟาง

index เปลี่ยนอะไรไป

index บน title คือโครงสร้างที่ถูกเรียงลำดับไว้ล่วงหน้า ซึ่งจับคู่แต่ละ title เข้ากับตำแหน่งของ row บนดิสก์ แทนที่จะอ่านสิบล้าน row PostgreSQL จะเดินผ่าน index — เพียงไม่กี่ขั้นตอน — ลงไปยังรายการของ 'The Glass Orchard' แล้วดึงเฉพาะ row นั้นออกมา นี่คือ Index Scan

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

flowchart TB
  subgraph NoIndex[No index on title]
    direction TB
    Q1[Query: title equals a value] --> S1[Read row 1]
    S1 --> S2[Read row 2]
    S2 --> S3[... read every row ...]
    S3 --> S4[Read row N]
    S4 --> R1[Matching rows]
  end
  subgraph WithIndex[Index on title]
    direction TB
    Q2[Query: title equals a value] --> I1[Look up value in sorted index]
    I1 --> I2[Jump to matching row location]
    I2 --> R2[Matching rows]
  end

Sequential Scan เทียบกับ Index Scan

เส้นทางด้านซ้ายแตะทุก row และขยายตามขนาดของ table เส้นทางด้านขวาแตะรายการใน index เพียงจำนวนเล็กน้อยไม่ว่า table จะใหญ่แค่ไหนก็ตาม นั่นคือเหตุผลทั้งหมดที่ทำให้ index มีอยู่

การสาธิตอย่างรวดเร็ว

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

psql

-- before: no index exists yet
EXPLAIN SELECT * FROM books WHERE title = 'The Glass Orchard';

-- create an index, then ask again
CREATE INDEX ON books (title);
EXPLAIN SELECT * FROM books WHERE title = 'The Glass Orchard';

แผนแรกรายงานเป็น Seq Scan ส่วนแผนที่สองรายงานเป็น Index Scan ผลลัพธ์มีหน้าตาแบบนี้:

                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on books  (cost=0.00..18584.00 rows=1 width=64)
   Filter: (title = 'The Glass Orchard'::text)

และหลังจากเพิ่ม index:

                              QUERY PLAN
---------------------------------------------------------------------
 Index Scan using books_title_idx on books  (cost=0.42..8.44 rows=1 width=64)
   Index Cond: (title = 'The Glass Orchard'::text)

สังเกตว่าต้นทุน (cost) โดยประมาณลดลงจากหลักพันเหลือเพียงหลักหน่วย ค่าประมาณต้นทุนนั่นแหละคือสิ่งที่ planner ใช้ในการตัดสินใจ

ใน pgAdmin: เปิด Query Tool พิมพ์ SELECT แล้วกดปุ่ม Explain (หรือใช้เมนู Explain) pgAdmin จะวาดแผนออกมาเป็นแผนภาพให้คุณเห็นโหนดของการสแกนได้ในพริบตา

โมดูลนี้ครอบคลุมอะไรบ้าง

B-tree และพื้นฐานของ index — CREATE INDEX, B-tree ที่เป็นค่าเริ่มต้น, query แบบไหนที่ index ช่วยให้เร็วขึ้น, composite index และ unique index
EXPLAIN และ query plan — การอ่านผลลัพธ์ของ planner, การแยกแยะชนิดของ scan และเหตุที่บางครั้ง index ถูกเพิกเฉยอย่างจงใจ
Specialized index — GIN, GiST, BRIN และ Hash รวมถึง partial, expression และ covering index สำหรับงานเฉพาะทาง
VACUUM และ statistics — PostgreSQL คืนพื้นที่จาก row ที่ถูกลบอย่างไร และรักษาความแม่นยำของค่าประมาณของ planner ไว้อย่างไร

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

index ช่วยการอ่านแต่เพิ่มภาระให้การเขียน เพราะทุก ๆ INSERT, UPDATE และ DELETE ต้องคอยปรับปรุง index ให้เป็นปัจจุบันด้วย จงสร้าง index อย่างมีเจตนา ไม่ใช่ทำไปโดยอัตโนมัติ
seq scan บน table ขนาดเล็กนั้นไม่มีปัญหาเลย และมักจะเร็วกว่าการค้นหาผ่าน index ด้วยซ้ำ index จะคุ้มค่ากับ table ขนาดใหญ่และ query ที่เลือกเฉพาะเจาะจง
planner ไม่ใช่คุณ เป็นผู้ตัดสินใจว่าจะใช้ index หรือไม่ หน้าที่ของคุณคือจัดเตรียม index ที่มีประโยชน์และ statistics ที่ถูกต้อง ส่วนที่เหลือเป็นเรื่องของ planner

🐘 แนวคิดหลัก

หากไม่มี index ที่เป็นประโยชน์ PostgreSQL จะอ่านทุก row เพื่อตอบ query — นั่นคือ Sequential Scan ที่ต้นทุนเพิ่มขึ้นตามขนาดของ table ส่วน index ที่ถูกเลือกมาอย่างดีจะเปลี่ยนการค้นหาแบบเชิงเส้นนั้นให้กลายเป็นการค้นหาที่รวดเร็วและแทบจะคงที่ ทุกสิ่งทุกอย่างที่เหลือในโมดูลนี้ล้วนต่อยอดมาจากการแลกเปลี่ยน (trade-off) เพียงข้อเดียวนี้