ดัชนี B-tree vs GIN vs GiST: คู่มือปฏิบัติสำหรับ PostgreSQL

สิ่งที่คุณกำลังเลือกจริง ๆ เมื่อเลือกดัชนี

ปัญหาดัชนีใน PostgreSQL ส่วนใหญ่เริ่มจากสถานการณ์เดียวกัน: มุมมองรายการรู้สึกเร็วเมื่อมี 1,000 แถว แต่ช้าลงเมื่อมี 1,000,000 แถว หรือกล่องค้นหาที่ทำงานได้ดีตอนทดสอบกลับกลายเป็นหน่วงวินาทีใน production เมื่อเกิดแบบนี้ มักจะอยากถามว่า “ดัชนีไหนดีที่สุด?” คำถามที่ดีกว่าคือ: “หน้าจอนี้สั่งให้ฐานข้อมูลทำอะไร?”

ตารางเดียวกันอาจต้องการดัชนีคนละแบบเพราะหน้าจอแต่ละอันอ่านข้อมูลต่างกัน หนึ่งมุมมองกรองโดย status และเรียงตาม created_at อีกมุมมองทำ full-text search อีกอันตรวจว่าฟิลด์ JSON มีคีย์หนึ่งหรือไม่ อีกอันค้นหาไอเท็มใกล้จุดบนแผนที่ นั่นคือรูปแบบการเข้าถึงต่างกัน ดังนั้นดัชนีแบบเดียวจะไม่ชนะทุกกรณี

นั่นคือสิ่งที่คุณกำลังเลือกเมื่อเลือกดัชนี: วิธีที่แอปเข้าถึงข้อมูล คุณทำการเทียบค่า ช่วง และการเรียงลำดับเป็นหลักหรือไม่? คุณค้นหาภายในเอกสารหรืออาร์เรย์หรือไม่? คุณถามว่า “อันไหนใกล้จุดนี้” หรือ “อันไหนทับซ้อนกับช่วงนี้”? คำตอบจะกำหนดว่า B-tree, GIN หรือ GiST เหมาะสม

B-tree, GIN, และ GiST อธิบายแบบง่าย ๆ

การเลือกดัชนีขึ้นกับสิ่งที่คิวรีทำมากกว่าชนิดคอลัมน์ PostgreSQL เลือกดัชนีตามตัวดำเนินการเช่น =, <, @>, หรือ @@ ไม่ใช่แค่เพราะคอลัมน์เป็น "text" หรือ "json" นี่คือเหตุผลที่ฟิลด์เดียวกันอาจต้องดัชนีต่างกันบนหน้าจอคนละอัน

B-tree: เร็วสำหรับการค้นหาแบบมีลำดับ

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

ตัวอย่างทั่วไปคือหน้าผู้ดูแลที่กรองโดย status และเรียงตาม created_at ดัชนี B-tree บน (status, created_at) ช่วยทั้งการกรองและการเรียง B-tree ยังเป็นเครื่องมือปกติสำหรับความเป็นเอกลักษณ์ (unique constraints)

GIN: เร็วเมื่อแต่ละแถวมีคีย์ที่ค้นหาได้หลายตัว

GIN ถูกออกแบบมาสำหรับคำถามแบบ “แถวนี้มีคำ/ค่า X ไหม?” ซึ่งแถวหนึ่งสามารถจับคู่กับคีย์ได้หลายค่า ตัวอย่างทั่วไปคือ full-text search (เอกสารมีคำหลายคำ) และการเป็นสมาชิกของ JSONB/array (JSON มีคีย์/ค่า)

คิดถึงเรคอร์ดลูกค้าที่มี preferences เป็น JSONB และหน้าจอกรองผู้ใช้ที่ preferences มี { "newsletter": true } นั่นเป็นการค้นหาแนว GIN

GiST: ยืดหยุ่นสำหรับช่วงเวลา ภูมิศาสตร์ และความคล้ายกัน

GiST เป็นเฟรมเวิร์กทั่วไปที่ใช้กับชนิดข้อมูลที่ไม่เข้าพวกการเรียงเรียบ มันเหมาะกับ ranges (การทับซ้อน/การครอบคลุม), คิวรีเชิงเรขาคณิตและภูมิศาสตร์ (ใกล้, ภายใน) และการค้นหาความคล้ายบางรูปแบบ

เมื่อเปรียบเทียบ B-tree vs GIN vs GiST เริ่มด้วยการจดตัวดำเนินการที่หน้าจอที่มีทราฟิกมากที่สุดใช้ ดัชนีที่เหมาะสมจะชัดเจนขึ้นหลังจากนั้น

ตารางตัดสินใจสำหรับหน้าจอทั่วไป (ตัวกรอง, การค้นหา, JSON, geo)

แอปส่วนใหญ่ต้องการรูปแบบดัชนีไม่กี่แบบ เคล็ดลับคือตรงกับพฤติกรรมหน้าจอเข้ากับตัวดำเนินการที่คิวรีของคุณใช้

ดัชนีสองตัวสามารถอยู่ร่วมกันได้เมื่อ endpoints ต่างกัน ตัวอย่างเช่น รายการผู้ดูแลอาจต้องการ B-tree บน (tenant_id, created_at) เพื่อการเรียงที่เร็ว ในขณะที่หน้าค้นหาต้องการ GIN สำหรับ @@ เก็บทั้งสองเฉพาะเมื่อรูปแบบคิวรีทั้งคู่ใช้บ่อย

ถ้าคุณไม่แน่ใจ ให้ดูตัวดำเนินการก่อน ดัชนีช่วยเมื่อฐานข้อมูลสามารถใช้มันเพื่อข้ามส่วนใหญ่ของตารางได้

ตัวกรองและการเรียง: ที่ซึ่ง B-tree มักชนะ

สำหรับหน้าจอทั่วไป B-tree เป็นตัวเลือกที่น่าเบื่อแต่ได้ผล หากคิวรีของคุณเป็นแบบ “เลือกแถวที่คอลัมน์เท่ากับค่า แล้วอาจเรียง แล้วแสดงหน้าแรก” B-tree มักเป็นสิ่งแรกที่ควรลอง

การกรองแบบเท่ากันเป็นกรณีคลาสสิก คอลัมน์เช่น status, user_id, account_id, type, หรือ tenant_id ปรากฎอยู่บ่อยครั้งในแดชบอร์ดและแผงผู้ดูแล ดัชนี B-tree กระโดดตรงไปยังค่าที่ตรงกันได้

ตัวกรองช่วงเข้ากันได้ดีกับ B-tree เมื่อคุณกรองตามเวลา หรือช่วงตัวเลข โครงสร้างที่เรียงช่วยเช่น created_at >= ..., price BETWEEN ..., id > ... ถ้า UI ของคุณมี “7 วันที่ผ่านมา” หรือ “$50 ถึง $100” B-tree ทำงานได้ตามต้องการ

การเรียงและการแบ่งหน้าคือพื้นที่ที่ B-tree ช่วยคุณได้มาก หากลำดับของดัชนีตรงกับ ORDER BY PostgreSQL มักจะคืนแถวที่เรียงไว้แล้วแทนที่จะต้องเรียงชุดใหญ่ในหน่วยความจำ

-- หน้าจอทั่วไป: \"ตั๋วที่เปิดของฉัน เรียงใหม่สุดก่อน\"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

ดัชนีคอมโพสิตเป็นไปตามกฎง่าย ๆ: PostgreSQL ใช้ส่วนหัวของดัชนี (leading part) อย่างมีประสิทธิภาพเท่านั้น คิดแบบ "ซ้ายไปขวา" กับ (user_id, status, created_at) คิวรีที่กรอง user_id (และอาจจะ status) จะได้ประโยชน์ คิวรีที่กรองเพียง status มักจะไม่ได้

Partial index เป็นการอัพเกรดที่ดีเมื่อหน้าจอของคุณสนใจเฉพาะชิ้นส่วนของข้อมูล ชิ้นส่วนที่พบบ่อยคือ “เฉพาะแถวที่ active” “ยังไม่ได้ลบแบบ soft-deleted” หรือ “กิจกรรมล่าสุด” มันทำให้ดัชนีเล็กลงและเร็วขึ้น

คอลัมน์ความหลากหลายสูงและต้นทุนของดัชนีเพิ่ม

คอลัมน์ความหลากหลายสูงมีค่าหลายค่ามาก เช่น user_id, order_id, email หรือ created_at ลงไปถึงวินาที ดัชนีมักจะโดดเด่นที่นี่เพราะตัวกรองสามารถจำกัดผลลัพธ์ให้เหลือชิ้นเล็ก ๆ ของตารางได้เร็ว

คอลัมน์ความหลากหลายน้อยตรงกันข้าม: booleans และ enum เล็ก ๆ เช่น is_active, status IN ('open','closed'), หรือ plan IN ('free','pro') ดัชนีบนพวกนี้มักทำให้ผิดหวังเพราะแต่ละค่าจับคู่กับส่วนใหญ่ของแถว PostgreSQL อาจเลือก sequential scan เป็นการตัดสินใจถูกต้อง

ต้นทุนละเอียดอีกอย่างคือการดึงแถว แม้ว่าดัชนีจะหา ID ที่ตรงได้เร็ว แต่ฐานข้อมูลยังอาจต้องไปอ่านตารางเพื่อเอาคอลัมน์ที่เหลือ หากคิวรีของคุณต้องการเพียงไม่กี่ฟิลด์ ดัชนีแบบ covering (รวมคอลัมน์ที่ต้องการไว้ในดัชนี) อาจช่วย แต่ก็ทำให้ดัชนีใหญ่ขึ้นและแพงขึ้นในการดูแล

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

คำแนะนำเชิงปฏิบัติ:

• เริ่มจาก 1-2 ดัชนีหลักต่อเทเบิลที่มีงานหนัก โดยอิงจากตัวกรองและการเรียงจริง
• ให้ความสำคัญกับคอลัมน์ความหลากหลายสูงที่ใช้ใน WHERE และ ORDER BY
• ระวังการทำดัชนีบน boolean และ enum เล็ก ๆ เว้นแต่จะรวมกับคอลัมน์ที่คัดกรองได้ดีอื่น ๆ
• เพิ่มดัชนีใหม่เมื่อคุณสามารถระบุคิวรีเฉพาะที่มันจะเร่งความเร็วได้

ตัวอย่าง: รายการตั๋วซัพพอร์ตที่กรองด้วย assignee_id (ความหลากหลายสูง) ได้ประโยชน์จากดัชนี ขณะที่ is_archived = false เพียงอย่างเดียวมักไม่คุ้ม

หน้าการค้นหา: full-text, prefix, และ "contains"

กล่องค้นหาดูเรียบง่าย แต่ผู้ใช้คาดหวังมาก: หลายคำ รูปแบบคำต่าง ๆ และการจัดลำดับความเกี่ยวข้อง ใน PostgreSQL นั่นมักเป็น full-text search: เก็บ tsvector (ข้อความเตรียมแล้ว) และคิวรีด้วย tsquery (สิ่งที่ผู้ใช้พิมพ์ แปลงเป็นเทอร์ม)

สำหรับ full-text search, GIN เป็นค่าดีฟอลต์ที่พบได้บ่อยเพราะมันเร็วในการตอบว่า "เอกสารนี้มีคำเหล่านี้ไหม" ข้อแลกเปลี่ยนคือค่าเขียนหนักขึ้น: การแทรกและอัพเดตแถวมักมีต้นทุนสูงกว่า

GiST ก็ใช้ได้สำหรับ full-text ในบางกรณี มันมักจะเล็กกว่าและอัพเดตถูกกว่า แต่โดยทั่วไปอ่านช้ากว่า GIN หากข้อมูลของคุณเปลี่ยนบ่อย (เช่น เทเบิลลักษณะ event) สมดุลระหว่างอ่าน-เขียนนี้มีผล

การค้นหาแบบ prefix ไม่ใช่ full-text

Prefix search หมายถึง "เริ่มด้วย" เช่น ค้นหาลูกค้าจาก prefix ของอีเมล นั่นไม่ใช่สิ่งที่ full-text ทำได้ดี สำหรับรูปแบบ prefix B-tree สามารถช่วยได้ (มักด้วย operator class ที่เหมาะสม) เพราะมันสอดคล้องกับการเรียงลำดับสตริง

สำหรับการค้นหาแบบ "contains" เช่น ILIKE '%error%' B-tree ปกติช่วยไม่ได้ นี่แหละที่ trigram indexing หรือแนวทางการค้นหาอื่นมีความเกี่ยวข้อง

เมื่อผู้ใช้ต้องการตัวกรองร่วมกับการค้นหาข้อความ

หน้าจอจริงมักรวมการค้นหากับตัวกรอง: status, assignee, ช่วงวันที่, tenant เป็นต้น การตั้งค่าที่ปฏิบัติได้จริงคือ:

• ดัชนี GIN (หรือบางครั้ง GiST) สำหรับคอลัมน์ tsvector
• ดัชนี B-tree สำหรับตัวกรองที่คัดกรองได้ดี (เช่น account_id, status, created_at)
• กฎเข้มงวดว่า "เก็บให้เรียบง่าย" เพราะดัชนีมากเกินไปทำให้การเขียนช้าลง

ตัวอย่าง: หน้าตั๋วซัพพอร์ตที่ค้นหา "refund delayed" และกรอง status = 'open' และ account_id ด้าน full-text ให้รายการที่เกี่ยวข้อง ขณะที่ B-tree ช่วยให้ PostgreSQL จำกัดไปยังบัญชีและสถานะที่ถูกต้องเร็วขึ้น

ฟิลด์ JSONB: เลือกระหว่าง GIN และ B-tree แบบเจาะจง

JSONB ยืดหยุ่นดี แต่สามารถกลายเป็นคิวรีช้าถ้าคุณใช้มันเหมือนคอลัมน์ธรรมดา การตัดสินใจหลักง่าย: คุณค้นหา "ที่ไหนก็ได้ใน JSON นี้" หรือคุณกรองที่เส้นทาง (path) ไม่กี่ที่ซ้ำ ๆ ?

สำหรับการ containment เช่น metadata @> '{"plan":"pro"}' GIN มักเป็นตัวเลือกแรก เพราะมันถูกสร้างมาสำหรับ "เอกสารนี้มีรูปแบบนี้ไหม?" และรองรับการตรวจสอบการมีคีย์เช่น ?, ?|, และ ?&

ถ้าแอปของคุณมักกรองโดยคีย์ JSON หนึ่งหรือสองคีย์ การทำดัชนีแบบ expression ด้วย B-tree มักจะเร็วและเล็กกว่า มันยังช่วยเมื่อคุณต้องการการเรียงหรือการเปรียบเทียบตัวเลขจากค่าที่สกัดออกมา

-- Broad support for containment and key checks
CREATE INDEX ON customers USING GIN (metadata);

-- Targeted filters and sorting on one JSON path
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

กฎคร่าว ๆ:

• ใช้ GIN เมื่อลูกค้าค้นหาหลายคีย์ แท็ก หรือตำแหน่งซ้อนของข้อมูล
• ใช้ B-tree expression เมื่อตัวกรองหรือการเรียงพึ่งพา path ไม่กี่อันบ่อย ๆ
• ทำดัชนีเฉพาะสิ่งที่ปรากฎบนหน้าจอจริง อย่าใส่ทุกอย่าง
• ถ้าประสิทธิภาพขึ้นอยู่กับคีย์ JSON สองสามตัวที่ใช้เสมอ ให้พิจารณาย้ายพวกมันไปเป็นคอลัมน์จริง

ตัวอย่าง: หน้าตั๋วซัพพอร์ตอาจกรองโดย metadata->>'priority' และเรียงตาม created_at ให้ทำดัชนี path ของ priority และคอลัมน์ created_at ปกติ ข้าม GIN กว้าง ๆ เว้นแต่ผู้ใช้ยังค้นหาแท็กหรือแอตทริบิวต์ซ้อนหลายอย่าง

Geo และ range queries: ที่ GiST เหมาะที่สุด

หน้าจอที่เกี่ยวกับภูมิศาสตร์และช่วงเวลาคือที่ GiST มักเป็นตัวเลือกชัดเจน GiST ถูกออกแบบมาสำหรับ "อันนี้ทับซ้อน อันนี้ครอบคลุม หรืออันนี้ใกล้อันนั้นไหม" มากกว่าการเทียบค่า

ข้อมูลเชิงภูมิศาสตร์มักเป็นจุด (ตำแหน่งร้าน), เส้นทาง, หรือรูปพหุ (delivery zone) หน้าจอทั่วไปเช่น “ร้านใกล้ฉัน”, “งานภายใน 10 กม.”, “แสดงไอเท็มในกล่องแผนที่นี้” หรือ “ที่อยู่นี้อยู่ในพื้นที่ให้บริการของเราหรือไม่?” ดัชนี GiST (มักผ่าน PostGIS geometry/geography) เร่งตัวดำเนินการเชิงพื้นที่เหล่านี้ให้ฐานข้อมูลข้ามแถวส่วนใหญ่แทนที่จะตรวจทุกรูปร่าง

range ก็เช่นกัน PostgreSQL มี range types เช่น daterange และ int4range คำถามทั่วไปคือการทับซ้อน: “การจองนี้ชนกับการจองเดิมไหม?” หรือ “แสดงการสมัครที่ active ในสัปดาห์นี้” GiST รองรับตัวดำเนินการทับซ้อนและการครอบคลุมอย่างมีประสิทธิภาพ

B-tree ยังมีบทบาทในหน้าจอแบบ geo หลายหน้า หลายหน้าจะกรองด้วย tenant, status หรือเวลา ก่อนแล้วจึงใช้เงื่อนไขเชิงพื้นที่ ตัวอย่าง: “เฉพาะการส่งของบริษัทฉัน ใน 7 วันที่ผ่านมา ใกล้ที่สุดก่อน” GiST ดูแลส่วนเชิงพื้นที่ แต่ B-tree ช่วยตัวกรองที่คัดกรองได้และการเรียง

วิธีเลือกดัชนีทีละขั้นตอน

การเลือกดัชนีขึ้นกับตัวดำเนินการ ไม่ใช่ชื่อคอลัมน์ เดียวกันคอลัมน์อาจต้องดัชนีต่างกันขึ้นกับว่าคุณใช้ =, >, LIKE 'prefix%', full-text, containment ใน JSON, หรือระยะทาง

อ่านคิวรีเหมือนเช็กลิสต์: WHERE ตัดสินว่าแถวไหนเข้าเงื่อนไข, JOIN กำหนดการเชื่อมต่อ, ORDER BY กำหนดลำดับผลลัพธ์, และ LIMIT กำหนดจำนวนแถวที่คุณต้องการจริง ๆ ดัชนีที่ดีที่สุดมักเป็นอันช่วยให้คุณหา 20 แถวแรกได้เร็ว

กระบวนการง่าย ๆ ที่ใช้ได้กับหน้าจอส่วนใหญ่:

1. จดตัวดำเนินการที่หน้าจอใช้จริง ๆ (เช่น: status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. เริ่มด้วยดัชนีที่ตรงกับตัวกรองที่คัดกรองได้มากที่สุดหรือการเรียงเริ่มต้น ถ้าหน้าเรียง created_at DESC ให้เริ่มจากตรงนั้น
3. เพิ่ม composite index เมื่อคุณเห็นตัวกรองเดียวกันรวมกันบ่อย ๆ ใส่คอลัมน์เท่าก่อน ตามด้วยคอลัมน์ช่วง แล้วคีย์การเรียง
4. ใช้ partial index เมื่อคุณมักกรองเฉพาะส่วน (เช่น status = 'open') ใช้ expression index เมื่อคุณคิวรีค่าที่คำนวณ (เช่น lower(email) สำหรับการค้นหาไม่สนใจ case)
5. ตรวจสอบด้วย EXPLAIN ANALYZE เก็บไว้เมื่อลดเวลาและจำนวนแถวที่อ่านอย่างมาก

ตัวอย่างเป็นรูปธรรม: แดชบอร์ดซัพพอร์ตกรองตั๋วตาม status และเรียงตามล่าสุด B-tree บน (status, created_at DESC) เป็นตัวเลือกแรกที่ดี หากหน้าจอเดียวกันยังกรอง meta @> '{"vip": true}' นั่นเป็นตัวดำเนินการต่างกันและมักต้องดัชนี JSON แยกต่างหาก

ความผิดพลาดทั่วไปที่เสียเวลา (และทำให้การเขียนช้าลง)

วิธีที่ทำให้ผิดหวังคือเลือกชนิดดัชนีที่ "ถูก" แต่สำหรับตัวดำเนินการที่ผิด PostgreSQL จะใช้ดัชนีได้เมื่อคิวรีตรงกับสิ่งที่ดัชนีถูกสร้างมา หากแอปของคุณใช้ ILIKE '%term%' ดัชนี B-tree ปกติบนคอลัมน์ข้อความจะไม่ถูกใช้ และคุณยังต้องสแกนตาราง

กับดักอีกอย่างคือการสร้างดัชนี multi-column ขนาดใหญ่ "เผื่อไว้" มันดูปลอดภัยแต่แพงในการบำรุงรักษาและมักไม่ตรงกับรูปแบบคิวรีจริง หากคอลัมน์ซ้ายสุดไม่ถูกใช้ ตัวที่เหลือของดัชนีอาจไม่ช่วย

การทำดัชนีบนคอลัมน์ความหลากหลายน้อยก็ง่ายเกินไป ดัชนี B-tree บน boolean หรือ status ที่มีค่าไม่กี่ค่ามักจะไร้ประโยชน์ เว้นแต่จะทำเป็น partial index

JSONB มีข้อควรระวังของมันเอง GIN กว้าง ๆ ดีสำหรับตัวกรองยืดหยุ่น แต่การตรวจ path บางอันอาจเร็วกว่าด้วย expression index ถ้าหน้าจอของคุณกรอง payload->>'customer_id' เสมอ การทำดัชนี expression บนค่านั้นมักเล็กและเร็วกว่า indexing ทั้งเอกสาร

สุดท้าย ดัชนีเพิ่มภาระการเขียน ในเทเบิลที่อัพเดตบ่อย (เช่น tickets หรือ orders) การแทรกและอัพเดตแต่ละครั้งต้องอัพเดตดัชนีทั้งหมด

ก่อนเพิ่มดัชนี หยุดแล้วตรวจ:

• ดัชนีตรงกับตัวดำเนินการที่คิวรีใช้หรือไม่?
• คุณสามารถแทนที่ดัชนีหลายคอลัมน์ด้วยหนึ่งหรือสองดัชนีที่เน้นจุดได้ไหม?
• ควรเป็น partial index เพื่อหลีกเลี่ยงสัญญาณจากความหลากหลายน้อยไหม?
• สำหรับ JSONB, expression index จะพอดีกับหน้าจอนี้หรือเปล่า?
• เทเบิลเขียนหนักพอที่ต้นทุนดัชนีจะมากกว่าประโยชน์การอ่านหรือไม่?

ตรวจสอบด่วนก่อนจะเพิ่ม (หรือล็อกไว้) ดัชนี

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

เริ่มจากสามหน้าจอแรก (หรือ endpoints) ของคุณและจดรูปแบบคิวรีจริง: ตัวกรอง การเรียง และสิ่งที่ผู้ใช้พิมพ์ บ่อยครั้ง "ปัญหาดัชนี" ที่แท้จริงคือ "ปัญหาคิวรีไม่ชัดเจน" โดยเฉพาะเมื่อคนถกเถียงเรื่อง B-tree vs GIN vs GiST โดยไม่ระบุตัวดำเนินการ

เช็คลิสต์ง่าย ๆ:

• เลือก 3 หน้าจอจริงและจด WHERE และ ORDER BY ที่แน่นอน (รวมทิศทางและการจัดการ NULL)
• ยืนยันชนิดตัวดำเนินการ: equality (=), range (>, BETWEEN), prefix, contains, overlap, หรือ distance
• เลือกดัชนีหนึ่งตัวต่อรูปแบบหน้าจอที่ใช้บ่อย ทดสอบ แล้วเก็บเฉพาะที่ลดเวลา/การอ่านได้อย่างวัดผลได้
• ถ้าเทเบิลเขียนเยอะ ให้เข้มงวด: ดัชนีเพิ่มต้นทุนการเขียนและเพิ่มแรงกด vacuum
• ตรวจสอบใหม่หลังการเปลี่ยนฟีเจอร์ การเพิ่มตัวกรอง การเปลี่ยนค่า default sort หรือการเปลี่ยนจาก "starts with" เป็น "contains" อาจทำให้ดัชนีเก่าหมดประโยชน์

ตัวอย่าง: แดชบอร์ดเพิ่ม default sort ใหม่ last_activity DESC ถ้าคุณมีดัชนีแค่ status ตัวกรองอาจยังเร็ว แต่การเรียงทำให้ต้องทำงานเพิ่ม

ตัวอย่าง: แมปหน้าจอจริงกับดัชนีที่ถูกต้อง

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

วิธีปฏิบัติที่เป็นรูปธรรมคือเริ่มจาก UI:

• จดทุกตัวควบคุมที่จำกัดผลลัพธ์ (ตัวกรอง)
• ระบุการเรียงลำดับเริ่มต้น
• ระบุพฤติกรรมการค้นหาอย่างชัดเจน (full-text vs starts-with vs contains)
• ชี้แจงฟิลด์พิเศษ (JSONB, geo, ranges)

ขั้นตอนต่อไป: ทำให้การทำดัชนีเป็นส่วนหนึ่งของกระบวนการพัฒนา

ดัชนีดี ๆ ตามหน้าจอของคุณ: ตัวกรองที่ผู้ใช้คลิก การเรียงที่คาดหวัง และกล่องค้นหาที่ใช้จริง ทำให้การทำดัชนีเป็นนิสัยระหว่างการพัฒนาและคุณจะหลีกเลี่ยงปัญหาประสิทธิภาพส่วนใหญ่ในภายหลัง

ทำให้ทำซ้ำได้: ระบุ 1-3 คิวรีที่หน้าจอรัน เพิ่มดัชนีที่เล็กที่สุดที่ตรงกับพวกมัน ทดสอบกับข้อมูลสมจริง แล้วลบสิ่งที่ไม่คุ้มค่า

ถ้าคุณกำลังสร้างเครื่องมือภายในหรือพอร์ทัลลูกค้า วางแผนความต้องการดัชนีตั้งแต่ต้นเพราะแอปเหล่านี้มักเติบโตด้วยการเพิ่มตัวกรองและหน้ารายการ หากคุณพัฒนาโดยใช้ AppMaster (appmaster.io) ควรถือการกำหนดตัวกรองและการเรียงของแต่ละหน้าจอเป็นสัญญาคิวรีที่ชัดเจน แล้วเพิ่มเฉพาะดัชนีที่สอดคล้องกับคลิกจริงเหล่านั้น