PostgreSQL มี index ชนิดใดบ้าง?

Question

Accepted Answer

นอกเหนือจาก **B-tree** ที่เป็นค่าเริ่มต้น PostgreSQL มี index ชนิดเฉพาะทางหลายแบบ — **GIN**, **GiST**, **BRIN**, **Hash** — แต่ละแบบเหมาะกับข้อมูลและรูปแบบ query ที่แตกต่างกัน บวกกับ **partial** และ **expression** indexes เพื่อความยืดหยุ่น การเลือกชนิด index ที่ถูกต้องสามารถปรับปรุง performance อย่างมากสำหรับ query เฉพาะทาง

## B-tree — ค่าเริ่มต้น (พบบ่อยที่สุด)

```sql
CREATE INDEX idx_users_email ON users(email);   -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
```

B-tree เป็นค่าเริ่มต้นและจัดการกรณีส่วนใหญ่ได้ — equality, ranges และการ sort บนคอลัมน์ทั่วไป

## GIN — สำหรับข้อมูลแบบ composite/multi-value

```sql
-- GIN (Generalized Inverted Index) — for values CONTAINING multiple items
CREATE INDEX idx_attrs ON products USING GIN (attributes);   -- JSONB
CREATE INDEX idx_tags  ON posts USING GIN (tags);            -- arrays
CREATE INDEX idx_search ON docs USING GIN (to_tsvector('english', body));  -- full-text
-- great for: JSONB containment, array membership, full-text search
```

**GIN** indexes จำเป็นสำหรับ **JSONB, arrays และ full-text search** — query ที่ตรวจ containment หรือ membership

## GiST, BRIN, Hash

```sql
-- GiST — geometric, range, nearest-neighbor (e.g. PostGIS, range types)
CREATE INDEX idx_geo ON places USING GiST (location);

-- BRIN (Block Range INdex) — tiny index for LARGE tables with naturally-ordered data
CREATE INDEX idx_time ON logs USING BRIN (created_at);   -- great for huge time-series
-- tiny size, efficient for large append-only/ordered data

-- Hash — equality only (=); rarely needed (B-tree handles equality too)
CREATE INDEX idx_hash ON t USING HASH (col);
```

## partial และ expression indexes (มีประโยชน์มาก)

```sql
-- PARTIAL index — index only SOME rows (smaller, targeted)
CREATE INDEX idx_active ON users(email) WHERE active = true;   -- only active users

-- EXPRESSION index — index a computed value
CREATE INDEX idx_lower_email ON users(LOWER(email));   -- for case-insensitive lookups
SELECT * FROM users WHERE LOWER(email) = 'ann@x.com';  -- uses the index
```

**partial** indexes (index เฉพาะแถวที่เกี่ยวข้อง — เล็กกว่า เร็วกว่า) และ **expression** indexes (index ค่าที่คำนวณได้ — ช่วยให้ query แบบ function-based ใช้ index ได้) เป็นเครื่องมือที่ทรงพลังและยืดหยุ่น

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

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

ในขณะที่ **B-tree** (ค่าเริ่มต้น) ครอบคลุมกรณีส่วนใหญ่ (equality, ranges, การ sort) แต่ชนิดเฉพาะทางปลดล็อก performance สำหรับข้อมูลเฉพาะทาง: **GIN** จำเป็นสำหรับ **JSONB, arrays และ full-text search** (query containment/membership ทำให้ความสามารถ JSONB และ array ที่ทรงพลังของ Postgres มี performance); **GiST** จัดการ query แบบ geometric, range และ nearest-neighbor (PostGIS ข้อมูลภูมิศาสตร์, range types); และ **BRIN** ให้ index ขนาดเล็กที่มีประสิทธิภาพสำหรับ **ตารางขนาดใหญ่ที่เรียงลำดับตามธรรมชาติ** (เช่นข้อมูล time-series/append-only ซึ่งเป็นตัวเลือกที่มีค่าสำหรับตารางขนาดใหญ่ที่ B-tree เต็มจะมีต้นทุนสูง)

การรู้ว่าชนิด index ใดเหมาะกับสถานการณ์ใด (GIN สำหรับ JSONB/arrays/full-text, GiST สำหรับ geometric/range, BRIN สำหรับข้อมูลที่เรียงลำดับขนาดใหญ่) เป็นกุญแจสู่การปรับความสามารถขั้นสูงของ Postgres ให้เหมาะสม

สิ่งที่มีคุณค่าเท่ากันคือ **partial indexes** (index เฉพาะแถวที่เกี่ยวข้อง — เล็กกว่าและมีประสิทธิภาพมากกว่าเมื่อคุณ query เฉพาะส่วนย่อย เช่น active users) และ **expression indexes** (index ค่าที่คำนวณได้ — ช่วยให้ lookup แบบ function-based เร็ว เช่นการค้นหา email แบบไม่สนตัวพิมพ์ ซึ่ง index ธรรมดารองรับไม่ได้)

เนื่องจากการเลือก index ส่งผลต่อ performance ของ query อย่างมีนัยสำคัญ และเนื่องจาก index ชนิดเฉพาะทางของ Postgres (โดยเฉพาะ GIN สำหรับ JSONB/arrays/full-text) เป็นสิ่งที่ทำให้ชนิดข้อมูลขั้นสูงของมันมี performance การเข้าใจชนิด index และเมื่อใดควรใช้แต่ละแบบ — รวมถึง partial และ expression indexes — จึงเป็นความรู้ที่มีคุณค่าและเกี่ยวข้องบ่อยสำหรับการปรับฐานข้อมูล PostgreSQL ให้เหมาะสม ซึ่งแยกแยะนักพัฒนาที่ทำให้ Postgres เร็วได้สำหรับรูปแบบ query ที่หลากหลาย และเป็นแง่มุมสำคัญของการใช้ประโยชน์จากความสามารถด้าน performance เต็มที่ของฐานข้อมูล