Specialized index

B-tree จัดการ equality, range, การ sort และ prefix match ได้ — ซึ่งครอบคลุม query ส่วนใหญ่ แต่ข้อมูลบางอย่างและรูปแบบ query บางอย่างต้องการโครงสร้างที่ต่างออกไป PostgreSQL มาพร้อมกับ index access method อื่น ๆ อีกหลายแบบ และ variant ของ index อีกสองสามแบบที่ใช้ได้กับ access method ใด ๆ บทเรียนนี้คือการพาทัวร์ว่าควรหยิบแต่ละแบบมาใช้เมื่อใด

GIN — สำหรับค่าที่บรรจุหลายรายการ

Generalized Inverted Index (GIN) สร้างขึ้นสำหรับ column ที่แต่ละ row บรรจุรายการที่ค้นหาได้ หลายรายการ: สมาชิกของอาร์เรย์, คีย์และค่าของเอกสาร jsonb หรือคำ (“lexemes”) ของเอกสาร full-text GIN จับคู่แต่ละรายการเข้ากับ row ที่บรรจุรายการนั้น ดังนั้น “หาทุก row ที่บรรจุรายการนี้” จึงรวดเร็ว

-- speed up containment queries on a jsonb column
CREATE INDEX books_meta_gin ON books USING gin (metadata);

-- now this uses the index
SELECT * FROM books WHERE metadata @> '{"genre": "fiction"}';

ใช้ GIN เมื่อคุณ query ภายใน ค่าที่เป็นองค์ประกอบ (composite values): การหา containment ของ jsonb ด้วย @>, ความเป็นสมาชิกของอาร์เรย์ หรือ full-text search ด้วย tsvector

GiST — สำหรับข้อมูลเชิงเรขาคณิต ช่วง และเพื่อนบ้านที่ใกล้ที่สุด

Generalized Search Tree (GiST) รองรับ query ที่ B-tree แสดงออกไม่ได้: รูปทรงสองรูปซ้อนทับกันไหม, จุดหนึ่งตกอยู่ในกล่องหรือไม่, row ใดอยู่ใกล้ตำแหน่งนี้ที่สุด, ช่วงสองช่วงตัดกันไหม GiST คือกระดูกสันหลังของชนิดข้อมูลเชิงเรขาคณิต, ชนิดข้อมูลแบบช่วง (range) และส่วนขยาย PostGIS

-- index a range column for overlap queries
CREATE INDEX rooms_booking_gist ON rooms USING gist (booked_during);

-- find bookings that overlap a given period
SELECT * FROM rooms WHERE booked_during && '[2026-06-01,2026-06-08)'::tsrange;

ใช้ GiST สำหรับข้อมูลเชิงพื้นที่ (spatial), การซ้อนทับของช่วงด้วย && และการเรียงลำดับตามเพื่อนบ้านที่ใกล้ที่สุด

BRIN — สำหรับ table ขนาดมหึมาที่เรียงลำดับโดยธรรมชาติ

Block Range Index (BRIN) มีขนาดเล็กจิ๋ว แทนที่จะทำ index ให้ทุก row BRIN เก็บแค่ค่าต่ำสุดและสูงสุดของแต่ละช่วงบล็อก (block range) ของ table วิธีนี้ทำงานได้สวยงามเมื่อค่าของ column เรียงตัวสอดคล้องกับลำดับการจัดเก็บทางกายภาพ — โดยทั่วไปคือ column timestamp บน log แบบ append-only หรือ table events ที่ row ใหม่จะมี timestamp มากกว่าเสมอ

-- a tiny index for a massive, time-ordered table
CREATE INDEX events_created_brin ON events USING brin (created_at);

SELECT * FROM events WHERE created_at >= '2026-06-01';

BRIN ใช้พื้นที่เพียงเศษเสี้ยวของ B-tree ข้อแลกเปลี่ยนคือ BRIN ช่วยได้ก็ต่อเมื่อข้อมูลเรียงทางกายภาพตาม column ที่ทำ index ถ้าค่ากระจายแบบสุ่ม ก็แทบไร้ประโยชน์

Hash — สำหรับ equality เท่านั้น

index แบบ Hash รองรับ operation เพียงอย่างเดียวเท่านั้น: equality (=) ทำ range หรือ sort ไม่ได้ การค้นหาแบบ equality ของ B-tree สมัยใหม่นั้นดีมากจน Hash แทบจะไม่ใช่ผู้ชนะที่ชัดเจน แต่กินพื้นที่น้อยกว่าเล็กน้อยสำหรับ column ขนาดใหญ่ที่ใช้ equality อย่างเดียว

CREATE INDEX books_isbn_hash ON books USING hash (isbn);

SELECT * FROM books WHERE isbn = '978-0-00-000000-0';

จงหยิบ Hash มาใช้ก็ต่อเมื่อคุณวัดผลแล้วว่าชนะ B-tree จริง สำหรับงานที่ใช้ equality ล้วน ๆ มิเช่นนั้นให้ใช้ B-tree เป็นค่าเริ่มต้น

partial index — ทำ index เฉพาะ row ที่คุณ query

partial index ครอบคลุมเฉพาะ row ที่ตรงกับเงื่อนไข WHERE หาก query ของคุณมองเฉพาะชุดย่อยเล็ก ๆ อยู่เสมอ การทำ index ให้เฉพาะชุดย่อยนั้นจะทำให้ index เล็กลงและดูแลรักษาได้เร็วขึ้น

-- only books still in print get indexed
CREATE INDEX books_active_idx ON books (title) WHERE in_print = true;

index นี้เหมาะที่สุดเมื่อ query ส่วนใหญ่มี WHERE in_print = true; planner หยิบไปใช้ได้ และ row ที่ in_print เป็น false จะไม่เข้าไปอยู่ใน index เลย

expression index — ทำ index ให้ค่าที่คำนวณได้

หากคุณกรองด้วย ผลลัพธ์ ของ expression อยู่บ่อย ๆ จงทำ index ให้ expression นั้นโดยตรง index ธรรมดาบน email ไม่ช่วยการค้นหาแบบไม่สนตัวพิมพ์เล็กใหญ่ แต่ index บน lower(email) ช่วยได้

CREATE INDEX authors_lower_name_idx ON authors (lower(name));

-- now this is an index scan, not a seq scan
SELECT * FROM authors WHERE lower(name) = 'ada lovelace';

expression ใน query ต้องตรงกับ expression ที่ทำ index ไว้ทุกประการ planner ถึงจะหยิบ index นั้นมาใช้

covering index — ตอบ query จาก index เพียงอย่างเดียว

covering index ใช้ INCLUDE เพื่อเก็บ column เพิ่มเติมไว้ใน index ที่ไม่ได้เป็นส่วนหนึ่งของคีย์การค้นหา หากทุก column ที่ query ต้องการอยู่ใน index PostgreSQL ก็สามารถตอบจาก index ได้โดยไม่ต้องแตะ table เลย — เรียกว่า index-only scan

-- search on author_id, but also carry title and published along
CREATE INDEX books_author_cover_idx
  ON books (author_id) INCLUDE (title, published);

-- can be answered from the index alone
SELECT title, published FROM books WHERE author_id = 4;

column ใน INCLUDE ถูกเก็บไว้เฉพาะที่ใบ (leaves) ของ index เท่านั้น ไม่ได้ใช้ในการเรียงลำดับ จึงเพิ่มข้อมูลได้โดยไม่เปลี่ยนพฤติกรรมการค้นหา

การสร้าง index เหล่านี้จาก driver

คำสั่ง CREATE INDEX ก็เป็นเพียง SQL driver ตัวไหนก็ส่งคำสั่งนี้ออกไปด้วยวิธีเดียวกัน นี่คือตัวอย่าง GIN ใน driver ต่าง ๆ

CREATE INDEX books_meta_gin ON books USING gin (metadata);

await pool.query('CREATE INDEX books_meta_gin ON books USING gin (metadata)');

cur.execute("CREATE INDEX books_meta_gin ON books USING gin (metadata)")

_, err := conn.Exec(ctx, "CREATE INDEX books_meta_gin ON books USING gin (metadata)")

sqlx::query("CREATE INDEX books_meta_gin ON books USING gin (metadata)")
    .execute(&pool)
    .await?;

ใน pgAdmin: กล่องโต้ตอบ Create Index มี dropdown ของ Access Method ที่แสดงรายการ btree, hash, gin, gist, brin และอื่น ๆ และมีตัวเลือกสำหรับ column INCLUDE ดังนั้นคุณจึงสร้าง index เหล่านี้ได้โดยไม่ต้องเขียน SQL ด้วยมือ

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

จงจับคู่ access method ให้เข้ากับรูปแบบของ query: GIN สำหรับ “บรรจุรายการ”, GiST สำหรับ “ซ้อนทับหรืออยู่ใกล้”, BRIN สำหรับ “ขนาดมหึมาและเรียงลำดับทางกายภาพ”, Hash สำหรับ “equality เท่านั้น” และ B-tree สำหรับทุกอย่างที่เหลือ
BRIN มีประสิทธิภาพก็ต่อเมื่อลำดับ row ทางกายภาพเป็นไปตาม column ที่ทำ index ถ้าข้อมูลไม่เรียงลำดับ BRIN ก็ไม่ช่วยอะไร
partial และ expression index เป็น variant ที่คุณนำมาใช้ทับ access method ใด ๆ ได้ ไม่ใช่ชนิดที่แยกต่างหาก ผสมกันได้เลย เช่น partial GIN index ก็ใช้ได้สมบูรณ์
สำหรับ covering index จงระบุ column ค้นหาตามปกติ และวาง column ที่เพียงแค่คืนค่ากลับไว้ใน INCLUDE เพื่อไม่ให้ไปทำให้ส่วนที่ใช้ค้นหาของ index บวม (bloat)

🐘 เฉพาะทาง ไม่ใช่แปลกประหลาด

index ส่วนใหญ่ของคุณจะเป็น B-tree ธรรมดา แต่เมื่อคุณเก็บ jsonb, ค้นหาอาร์เรย์หรือ full text, ทำงานกับเรขาคณิตหรือช่วง หรือมี table time-series พันล้าน row access method เฉพาะทางจะเปลี่ยน query ที่เป็นไปไม่ได้ให้กลายเป็นเรื่องง่าย แค่รู้ว่ามีตัวเลือกพวกนี้อยู่ก็ได้ประโยชน์ไปครึ่งทางแล้ว หยิบมาใช้เมื่อ B-tree ตอบคำถามนั้นไม่ได้

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
index เฉพาะทาง (GIN, GiST, BRIN, Hash)	เล็กกว่าและเร็วกว่า B-tree มากสำหรับรูปแบบ query ที่ออกแบบมารองรับโดยเฉพาะ	ใช้ได้ดีเฉพาะรูปแบบ query ที่ตรงกันเท่านั้น เลือกผิดแล้วแทบไม่ช่วยอะไรเลย
B-tree ทั่วไป	รองรับ equality, range, sort และ prefix match ได้พร้อมกัน เป็นตัวเลือกปลอดภัยเมื่อไม่แน่ใจ	ใหญ่กว่าและช้ากว่า index เฉพาะทางสำหรับงานที่ index เฉพาะทางถนัด เช่น containment ของ `jsonb`

ข้อผิดพลาดที่พบบ่อย

ใช้ B-tree กับ column jsonb — B-tree ไม่ช่วย query แบบ containment (@>) ได้ดี จงใช้ GIN สำหรับ jsonb, อาร์เรย์ หรือ full-text search
ใช้ BRIN บน table ที่ลำดับ row ทางกายภาพไม่สอดคล้องกับ column ที่ทำ index — BRIN พึ่งพาว่าค่าของ column เรียงตัวตามลำดับการจัดเก็บจริง หากข้อมูลกระจายแบบสุ่ม BRIN แทบไร้ประโยชน์และควรใช้ B-tree แทน
เลือก Hash index โดยไม่ตรวจสอบข้อจำกัด — Hash รองรับแค่ equality (=) เท่านั้น ทำ range query หรือ ORDER BY ไม่ได้เลย จงมั่นใจว่า query ของคุณใช้ equality ล้วน ๆ ก่อนเลือก Hash

💡 ตัวอย่างจากของจริง

บริษัทที่รัน event/log table ขนาดใหญ่ — table แบบ append-only ที่ row ใหม่มี timestamp มากกว่าเสมอ (เช่น log หรือ event stream) ใช้ BRIN index บน created_at เพราะขนาด index เล็กกว่า B-tree มาก แต่ยังช่วย range query ตามเวลาได้ดี เป็นรูปแบบที่พบทั่วไปในบริษัท analytics ที่รัน PostgreSQL ในสเกลใหญ่

แพลตฟอร์มที่มี full-text search และ column jsonb เยอะ — ระบบที่เก็บ metadata แบบยืดหยุ่นใน jsonb (เช่น product catalog หรือ document store) พึ่งพา GIN index เพื่อให้ query แบบ containment และ full-text search เร็วพอสำหรับ production