Chiến lược IndexingIndexing Strategies

Thuộc bộ kiến thức PostgreSQL DBA Roadmap.

Tổng quan

Index là một cấu trúc dữ liệu riêng, được sắp xếp theo thứ tự, giúp PostgreSQL tìm ra các row mà không cần quét toàn bộ table. Nếu không có index, mọi truy vấn vào table đều là sequential scan: PostgreSQL đọc từng block, kiểm tra từng row với điều kiện truy vấn, rồi loại bỏ những row không khớp. Điều này không thành vấn đề với table vài nghìn row, nhưng sẽ là thảm họa với table hàng trăm triệu row.

Index giải quyết vấn đề này bằng cách đánh đổi thêm dung lượng lưu trữ và thêm chi phí ghi để đổi lấy tốc độ đọc nhanh hơn đáng kể. Mỗi lệnh INSERT, UPDATE, hoặc DELETE trên table có index đều phải cập nhật index đó — đôi khi là nhiều index cùng lúc — nên table càng nhiều index thì chi phí ghi càng cao. Đây chính là mâu thuẫn trung tâm của việc đánh index: index quá ít thì đọc chậm; index quá nhiều (hoặc index sai chỗ) thì việc ghi bị chậm lại trong khi ổ đĩa bị lấp đầy bởi những cấu trúc chẳng ai truy vấn tới. Chiến lược indexing tốt nghĩa là hiểu rõ PostgreSQL cung cấp những loại index nào, mỗi loại tăng tốc kiểu truy cập nào, và áp dụng chúng một cách có chủ đích dựa trên truy vấn thực tế mà table đó phục vụ — chứ không phải đánh index phòng thủ trên mọi cột.

Khác với nhiều hệ quản trị cơ sở dữ liệu quan hệ chỉ có B-tree, PostgreSQL cung cấp sẵn sáu loại index (access method) — B-tree, Hash, GIN, GiST, SP-GiST, và BRIN — mỗi loại được tối ưu cho một dạng dữ liệu và một kiểu truy vấn khác nhau. Chọn đúng loại index quan trọng không kém việc quyết định có nên đánh index hay không.

Kiến thức nền tảng

B-tree hoạt động như thế nào, về mặt khái niệm

Loại index mặc định và phổ biến nhất là B-tree (balanced tree — cây cân bằng). Về mặt khái niệm, nó tổ chức các giá trị được index thành một cây gồm các page: root page trỏ tới các intermediate page, các page này lại trỏ tới leaf page chứa giá trị index thực tế theo thứ tự sắp xếp, cùng với con trỏ (tuple ID) trỏ ngược về row tương ứng trong heap. Vì cây được giữ cân bằng và có chiều cao thấp — chiều cao tăng theo logarit so với số row — việc tìm một giá trị chỉ mất khoảng O(log n) lần đọc page bất kể table lớn cỡ nào. Một table tỷ row có thể chỉ cần 4-5 lần đọc page để tìm ra kết quả khớp, so với việc quét toàn bộ table.

Vì các entry được lưu theo thứ tự sắp xếp, B-tree không chỉ hỗ trợ so sánh bằng (=) mà còn hỗ trợ so sánh thứ tự (<, <=, >, >=), BETWEEN, IN, IS NULL, so khớp pattern ở đầu chuỗi (LIKE 'abc%' với locale C hoặc operator class phù hợp), và ORDER BY/MIN/MAX mà không cần bước sort riêng. Tính linh hoạt này là lý do B-tree là access method mặc định của CREATE INDEX và là lựa chọn đầu tiên hợp lý cho hầu hết các cột dạng scalar — primary key, foreign key, timestamp, mã trạng thái, bất cứ thứ gì bạn filter hoặc sort bằng toán tử so sánh thông thường.

-- Index B-tree cơ bản (access method mặc định, nên USING btree là ngầm định)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Hỗ trợ cả equality, range, và sorting trên cùng một cột
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id BETWEEN 100 AND 200;
SELECT * FROM orders ORDER BY customer_id;

Hash index

Hash index lưu giá trị hash của giá trị được index thay vì lưu giá trị thực, và chỉ hỗ trợ so sánh bằng (=) — không hỗ trợ range, không hỗ trợ sort. Trước đây hash index có tiếng xấu: trước PostgreSQL 10, hash index không được WAL-log, nghĩa là không an toàn khi crash và phải REINDEX lại sau khi crash hoặc trên standby server, đồng thời không được replicate. Từ PostgreSQL 10 trở đi, hash index đã được WAL-log đầy đủ, an toàn khi crash, và replicate bình thường, nên lo ngại lịch sử đó không còn đúng nữa.

Tuy vậy, hash index ngày nay hiếm khi là lựa chọn đúng đắn. B-tree xử lý truy vấn equality hiệu quả không kém gì hash index, mà còn hỗ trợ thêm range query, sorting, và composite index prefix — nên gần như không có kiểu truy vấn nào mà hash thắng còn B-tree thua, trong khi có rất nhiều trường hợp B-tree thắng còn hash thua (bất kỳ range query nào trong tương lai, bất kỳ ORDER BY nào, bất kỳ nhu cầu composite index nào). Về lý thuyết, hash index có thể nhỏ hơn và nhanh hơn một chút cho các truy vấn equality thuần túy trên cột có cardinality cao trong một số workload, nhưng điều này hiếm khi đáng để đánh đổi tính linh hoạt của B-tree. Trong thực tế, chỉ nên dùng hash index trong những trường hợp hẹp, đã được đo đạc cụ thể — còn lại, với đại đa số nhu cầu equality lookup, hãy dùng B-tree.

-- Hiếm khi cần trong thực tế — nêu ra để đầy đủ
CREATE INDEX idx_sessions_token_hash ON sessions USING hash (token);

SELECT * FROM sessions WHERE token = 'abc123...';  -- chỉ equality, không hỗ trợ range

Khái niệm chính

GIN — Generalized Inverted Index

GIN được thiết kế cho các cột lưu giá trị composite — giá trị chứa nhiều “key” thành phần mà bạn muốn tìm kiếm bên trong, như các phần tử của array, các key/value của JSONB document, hoặc các lexeme của tsvector. Về bản chất, GIN đảo ngược mối quan hệ: thay vì một entry index cho mỗi row, nó tạo một entry index cho mỗi giá trị thành phần, mỗi entry trỏ tới tất cả row chứa giá trị đó. Điều này khiến GIN cực kỳ nhanh cho các truy vấn kiểu “row này có chứa X không” — containment (@>), overlap (&&), full-text search (@@) — đổi lại kích thước index lớn hơn và ghi chậm hơn so với B-tree (GIN dùng “pending list” để gom nhóm insert và giảm write amplification, được flush bởi autovacuum hoặc theo gin_pending_list_limit).

Các use case GIN phổ biến nhất trong thực tế là full-text search trên cột tsvector và truy vấn containment trên JSONB:

-- Đánh index một cột JSONB để truy vấn containment
CREATE TABLE products (
    id serial PRIMARY KEY,
    name text NOT NULL,
    attributes jsonb NOT NULL
);

CREATE INDEX idx_products_attributes_gin ON products USING gin (attributes);

-- Index GIN tăng tốc toán tử @> (contains)
SELECT * FROM products
WHERE attributes @> '{"color": "red", "in_stock": true}';

-- Full-text search: đánh index một cột tsvector được generate
ALTER TABLE products ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', name)) STORED;

CREATE INDEX idx_products_search_gin ON products USING gin (search_vector);

SELECT * FROM products
WHERE search_vector @@ to_tsquery('english', 'wireless & mouse');

Riêng với JSONB, PostgreSQL cung cấp hai operator class cho GIN: jsonb_ops mặc định, index toàn bộ key lẫn value và hỗ trợ @>, ?, ?&, ?|; và jsonb_path_ops, chỉ index hash của giá trị dẫn xuất từ path, cho ra index nhỏ hơn và nhanh hơn riêng với @> nhưng không hỗ trợ nhóm toán tử ?. Hãy chọn jsonb_path_ops khi containment là toán tử JSONB duy nhất bạn cần:

CREATE INDEX idx_products_attributes_pathops
    ON products USING gin (attributes jsonb_path_ops);

GiST — Generalized Search Tree

GiST là access method dạng cây cân bằng — không phải một thuật toán duy nhất mà là một framework cho phép nhiều kiểu dữ liệu khác nhau tự cắm vào khái niệm “gần nhau” hay “chồng lấn” của riêng chúng, đó là lý do nó hỗ trợ kiểu geometric, range type, full-text search (như một lựa chọn thay thế GIN, update nhanh hơn nhưng lookup chậm hơn), và tìm kiếm nearest-neighbor (ORDER BY <distance> LIMIT n) qua k-NN.

Ứng dụng vận hành quan trọng nhất của GiST đối với một DBA là exclusion constraint, dùng để ngăn các giá trị chồng lấn giữa các row — ví dụ đảm bảo hai lượt đặt phòng cho cùng một phòng không bao giờ có khoảng ngày chồng lấn nhau. Exclusion constraint được xây dựng trên GiST vì GiST hiểu sẵn khái niệm “A có chồng lấn B hay không” đối với range type và kiểu geometric. Xem ./03-data-types-and-schema-objects.md để biết thêm về range type và cách khai báo EXCLUDE USING gist.

-- Truy vấn geometric về vị trí lân cận / containment
CREATE INDEX idx_locations_point_gist ON locations USING gist (coordinates);

SELECT * FROM locations
ORDER BY coordinates <-> point(10.775, 106.700)
LIMIT 5;  -- tìm kiếm nearest-neighbor, được tăng tốc bởi index GiST

-- Đánh index range type làm nền cho exclusion constraint
CREATE TABLE room_bookings (
    room_id int NOT NULL,
    during tsrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

SP-GiST — Space-Partitioned GiST

SP-GiST hỗ trợ các cây tìm kiếm được phân vùng nhưng không nhất thiết cân bằng, phù hợp với dữ liệu có tính phân cụm tự nhiên, không đồng đều — có thể hình dung như trie, quad-tree, hoặc k-d tree. Nó phù hợp với các kiểu dữ liệu mà giá trị tự nhiên chia thành các phân vùng tách biệt thay vì một khoảng liên tục: prefix của địa chỉ IP/khối CIDR, prefix số điện thoại, điểm 2D phù hợp với quad-tree, hoặc chuỗi text có prefix chung. SP-GiST ít được dùng hơn nhiều so với B-tree, GIN, hay GiST — đa số DBA sẽ đi rất lâu mà không cần dùng tới — nhưng đây là công cụ đúng đắn khi dữ liệu của bạn có cấu trúc phân vùng tự nhiên, không cân bằng kiểu này.

CREATE INDEX idx_devices_ip_spgist ON devices USING spgist (ip_address inet_ops);

SELECT * FROM devices WHERE ip_address <<= '10.0.0.0/8';

BRIN — Block Range Index

BRIN tiếp cận theo hướng hoàn toàn khác so với mọi loại index còn lại: thay vì lưu một entry cho mỗi row (hoặc mỗi giá trị), nó lưu thông tin tóm tắt — thường chỉ là giá trị min và max — cho mỗi dải block vật lý của table (kích thước dải có thể cấu hình qua pages_per_range, mặc định là 128). Vì nó tóm tắt theo dải block thay vì theo từng row, index BRIN rất nhỏ: thường chỉ vài trăm kilobyte đến vài megabyte cho một table mà B-tree tương ứng cần tới nhiều gigabyte. Chi phí bảo trì cũng nhỏ tương ứng, vì cập nhật một entry BRIN chỉ cần chạm vào phần tóm tắt của một dải thay vì phải cân bằng lại cả cây.

Điểm cần lưu ý — và rất quan trọng — là BRIN chỉ có ích khi cột được index có tương quan vật lý với thứ tự row trên đĩa của table. Trường hợp điển hình là table append-only, được sắp theo thời gian: nếu row được insert theo thứ tự created_at và không bao giờ bị sắp xếp lại, thì mỗi dải block sẽ tự nhiên chứa một khoảng timestamp hẹp, nên “min/max theo dải” là một bộ lọc rất hiệu quả (PostgreSQL có thể bỏ qua hoàn toàn những dải không thể chứa row khớp). Nếu cột được index không có tương quan gì với thứ tự vật lý (ví dụ customer_id nằm rải rác ngẫu nhiên trong table theo thứ tự insert), thì min/max theo dải của BRIN gần như bao phủ toàn bộ khoảng giá trị trong mọi block, nên không thể loại trừ dải nào cả, và hiệu quả gần như suy biến thành quét toàn bộ table — lúc đó BRIN không mang lại lợi ích gì và nên dùng B-tree thay thế. Kiểm tra mức độ tương quan bằng SELECT correlation FROM pg_stats WHERE tablename = '...' AND attname = '...' — giá trị gần 1 hoặc -1 cho thấy tương quan vật lý mạnh, gần 0 cho thấy BRIN sẽ không giúp ích gì.

-- Ứng viên lý tưởng cho BRIN: table sự kiện append-only, sắp theo thời gian
CREATE TABLE events (
    id bigserial PRIMARY KEY,
    created_at timestamptz NOT NULL,
    payload jsonb
);

CREATE INDEX idx_events_created_at_brin ON events USING brin (created_at);

-- Truy vấn range trên created_at hưởng lợi rất lớn, với kích thước chỉ bằng một phần nhỏ của B-tree
SELECT * FROM events
WHERE created_at >= '2026-07-01' AND created_at < '2026-07-08';

Với một table sự kiện append-only dung lượng 500 GB, B-tree trên created_at có thể chiếm hàng chục gigabyte; index BRIN tương đương có thể chỉ chiếm vài megabyte, trong khi vẫn giúp planner bỏ qua phần lớn block cho một truy vấn khoảng ngày hẹp.

Composite (multi-column) index và leftmost-prefix matching

Composite index trải trên nhiều cột, và thứ tự cột không chỉ mang tính hình thức — nó quyết định index có thể phục vụ dạng truy vấn nào. Index trên (a, b) được lưu theo thứ tự sắp xếp theo a trước, rồi theo b trong mỗi giá trị a. Điều đó có nghĩa là nó phục vụ hiệu quả các truy vấn filter trên a riêng lẻ, hoặc trên a AND b cùng lúc, vì trong cả hai trường hợp index đều có thể nhảy thẳng tới (các) giá trị a liên quan rồi, nếu cần, thu hẹp thêm bằng b. Nó không phục vụ hiệu quả truy vấn chỉ filter trên b riêng lẻ, vì không biết a thì index không có cách nào thu hẹp về phần cây đáng quan tâm — kiểu truy cập đó sẽ suy biến thành quét toàn bộ index.

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

-- Dùng index hiệu quả: filter trên cột dẫn đầu
SELECT * FROM orders WHERE customer_id = 42;

-- Dùng index hiệu quả: filter trên cả hai cột, vẫn giữ nguyên leftmost prefix
SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';

-- KHÔNG dùng index này hiệu quả: bỏ qua cột dẫn đầu
SELECT * FROM orders WHERE status = 'shipped';

Đây gọi là leftmost-prefix matching: index trên (a, b, c) có thể phục vụ truy vấn trên (a), (a, b), hoặc (a, b, c), nhưng không phục vụ được truy vấn chỉ trên (b), (c), hoặc (b, c). Khi thiết kế composite index, hãy đặt cột có tần suất filter đơn lẻ cao nhất (hoặc độ chọn lọc cao nhất) lên đầu, và sắp xếp các cột tiếp theo sao cho khớp với các dạng truy vấn multi-column phổ biến nhất của bạn. Nếu cả WHERE customer_id = ? lẫn WHERE status = ? đều cần nhanh một cách độc lập, thông thường bạn cần hai index riêng biệt thay vì một composite index cố gắng phục vụ cả hai.

Partial index

Partial index chỉ bao gồm các row khớp với mệnh đề WHERE được chỉ định khi tạo index, thay vì mọi row trong table. Điều này rất có giá trị khi truy vấn liên tục nhắm tới một tập con row đã biết trước, thường là nhỏ — user đang hoạt động trong một cơ sở user phần lớn không hoạt động, đơn hàng đang chờ xử lý trong một lịch sử đơn hàng phần lớn đã hoàn tất, hoặc ticket chưa lưu trữ trong một table không bao giờ xóa dữ liệu cũ. Index kết quả nhỏ hơn (quét nhanh hơn, bảo trì rẻ hơn, cần ít RAM hơn để cache), và chi phí ghi cũng giảm vì các row nằm ngoài predicate hoàn toàn không chạm vào index.

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- Planner sẽ dùng index hẹp, rẻ này cho các truy vấn có predicate khớp
-- (hoặc có thể chứng minh là tương thích)
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

Partial index cũng là cách chuẩn để áp đặt ràng buộc unique trên một tập con row — ví dụ chỉ cho phép một row đang hoạt động cho mỗi key, trong khi các row lịch sử/không hoạt động được phép lặp lại:

CREATE UNIQUE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;

Expression index (functional index)

Expression index lưu kết quả của một biểu thức thay vì giá trị cột thô, giúp PostgreSQL dùng được index cho các truy vấn filter trên giá trị đã tính toán hoặc biến đổi. Ví dụ kinh điển là tra cứu email không phân biệt hoa thường:

CREATE INDEX idx_users_email_lower ON users (lower(email));

-- Hưởng lợi từ index vì biểu thức khớp chính xác
SELECT * FROM users WHERE lower(email) = 'jane@example.com';

-- KHÔNG hưởng lợi — biểu thức khác, planner sẽ không khớp truy vấn này với index trên
SELECT * FROM users WHERE email = 'jane@example.com';

Quy tắc then chốt là truy vấn phải dùng đúng biểu thức mà index đã được tạo dựa trên (trừ một số trường hợp planner có thể tự suy ra tương đương về immutability và ép kiểu) thì planner mới cân nhắc dùng index. Đây cũng là lý do hàm dùng trong expression index phải là IMMUTABLE — PostgreSQL cần đảm bảo cùng một input luôn cho ra cùng một output, vì giá trị đã tính toán được lưu vật lý trong index và không được tính lại khi đọc.

Covering index với INCLUDE

Thông thường, ngay cả khi index có thể xác định vị trí các row khớp qua cột key của nó, PostgreSQL vẫn phải ghé qua heap (nơi lưu trữ row thực tế của table) để lấy bất kỳ cột nào truy vấn cần mà không nằm trong index key — trừ khi visibility map xác nhận page hoàn toàn “all-visible”, lúc đó index-only scan có thể bỏ qua hoàn toàn việc ghé heap cho các cột key. Mệnh đề INCLUDE, được thêm từ PostgreSQL 11, cho phép bạn gắn thêm các cột “payload” vào một index B-tree mà không thuộc search key (nên không ảnh hưởng đến thứ tự sắp xếp hay các toán tử mà index hỗ trợ) nhưng vẫn được lưu trong index và do đó có sẵn cho index-only scan.

CREATE INDEX idx_orders_customer_covering
    ON orders (customer_id)
    INCLUDE (status, total_amount);

-- Nếu visibility map được cập nhật đầy đủ, truy vấn này có thể được thỏa mãn
-- hoàn toàn từ index — không cần ghé heap
SELECT customer_id, status, total_amount
FROM orders
WHERE customer_id = 42;

Cột INCLUDE hữu ích khi danh sách SELECT của một truy vấn cần thêm một vài cột ngoài các cột filter, và bạn muốn có index-only scan thực sự mà không làm phình to search key (điều này cũng sẽ thay đổi những toán tử/truy vấn mà index hỗ trợ và có thể thêm ràng buộc unique không mong muốn trong một unique index). Lưu ý rằng cột INCLUDE vẫn chiếm không gian trong index và vẫn cần cập nhật khi ghi vào các cột đó, nên đây vẫn là một đánh đổi về kích thước/chi phí ghi như bất kỳ quyết định index nào khác, không phải một lợi ích miễn phí.

Best Practices

Đánh index có chủ đích, không phải phòng thủ

Best practice quan trọng nhất là đánh index dựa trên cách table thực sự được truy vấn, chứ không phải dựa trên linh cảm rằng “cột này có thể sẽ được tìm kiếm một ngày nào đó”. Mỗi index đều thêm chi phí ghi và dung lượng lưu trữ; một table có mười index mà chỉ năm truy vấn thực sự dùng tới đang phải trả một chi phí thực, liên tục mà không nhận lại lợi ích gì. Hãy dùng EXPLAIN (ANALYZE, BUFFERS) trên các truy vấn tiêu biểu để xem planner thực sự đang làm gì và liệu sequential scan có thực sự là vấn đề hay không trước khi thêm index để khắc phục — xem ./10-query-planning-and-performance-tuning.md để biết cách đọc query plan và ra quyết định về index dựa trên bằng chứng thay vì phỏng đoán. pg_stat_user_indexes và pg_stat_user_tables cũng hữu ích để tìm những index không bao giờ được scan (idx_scan = 0 sau một khoảng thời gian tiêu biểu) và là ứng viên để loại bỏ.

Khớp loại index với kiểu truy vấn

Mặc định dùng B-tree; nó bao phủ đại đa số nhu cầu equality/range/sort trên dữ liệu scalar. Chuyển sang GIN khi một cột chứa giá trị composite mà bạn cần tìm kiếm bên trong — array, JSONB, full-text. Chuyển sang GiST cho dữ liệu geometric, overlap range, k-NN, hoặc exclusion constraint. Chỉ cân nhắc BRIN khi một table cực lớn có một cột tương quan vật lý tự nhiên với thứ tự insert — xác nhận bằng pg_stats.correlation trước khi quyết định, vì BRIN có thể gây hại thực sự (chỉ có mặt xấu, không có lợi ích) trên dữ liệu không tương quan. Tránh hash index trừ khi bạn có lý do cụ thể, đã được đo đạc, cho thấy B-tree không đáp ứng được — điều này rất hiếm.

Thiết kế composite index dựa trên dạng truy vấn thực tế

Hãy xem xét các mệnh đề WHERE mà ứng dụng của bạn thực sự phát ra, và sắp xếp thứ tự cột trong composite index sao cho cột có độ chọn lọc cao nhất, được filter riêng lẻ thường xuyên nhất đứng đầu, tuân theo leftmost-prefix matching. Đừng xây dựng một composite index rộng bao phủ nhiều cột một cách phỏng đoán “phòng khi cần” — nó sẽ lớn, chậm bảo trì, và vẫn chỉ phục vụ được các truy vấn khớp prefix.

Dùng partial index và expression index để thu nhỏ trường hợp phổ biến

Nếu phần lớn truy vấn filter về một tập con row đã biết, ổn định (status = 'active', deleted_at IS NULL), partial index trên tập con đó thường tốt hơn index đầy đủ — nhỏ hơn, nhanh hơn, bảo trì rẻ hơn. Nếu truy vấn liên tục bọc một cột trong một hàm (lower(), date_trunc(), trích xuất JSON), một expression index nhắm đúng biểu thức đó sẽ được sử dụng; một index thông thường trên cột thô thì không.

Chú ý write amplification trên table nhiều index

Mỗi index bổ sung trên một table đều thêm việc cho mỗi INSERT/UPDATE/DELETE, và UPDATE đặc biệt có thể tốn kém nếu nó chạm vào cột được index và ngăn cản Heap-Only Tuple (HOT) update — xem ./17-schema-design-patterns-and-antipatterns.md để biết các pattern ở tầng schema ảnh hưởng đến chi phí update, khả năng HOT, và bloat. Trên table nhiều ghi, hãy định kỳ rà soát index để tìm sự trùng lặp (ví dụ một index đơn cột trở nên dư thừa vì đã có composite index cùng cột dẫn đầu) và loại bỏ những index không xứng đáng với chi phí duy trì.

Tạo index mà không chặn ghi

Trên một table production đang hoạt động, hãy dùng CREATE INDEX CONCURRENTLY để tránh giữ một lock chặn ghi trong suốt quá trình build index. Cách này mất nhiều thời gian hơn và không thể chạy trong một transaction block, nhưng đây là cách chuẩn để thêm hoặc build lại index trên table không thể chấp nhận downtime.

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

Bảng so sánh

Loại index	Phù hợp nhất cho	Use case điển hình
B-tree	Equality, so sánh range, sorting trên giá trị dạng scalar	Primary/foreign key, timestamp, mã trạng thái, WHERE/ORDER BY thông thường
Hash	Truy vấn equality thuần túy	Hiếm khi là lựa chọn đúng — B-tree bao phủ equality và hơn thế nữa; chỉ dùng khi có lý do cụ thể, đã đo đạc
GIN	Dữ liệu composite/multi-valued, “row này có chứa X không”	Full-text search (tsvector), JSONB containment (@>), array containment
GiST	Dữ liệu geometric, overlap/containment, nearest-neighbor, exclusion constraint	Truy vấn geospatial, exclusion constraint dựa trên range-overlap, tìm kiếm k-NN
SP-GiST	Dữ liệu phân vùng tự nhiên, không đồng đều	Prefix IP/CIDR, prefix số điện thoại, dữ liệu không gian kiểu quad-tree
BRIN	Table cực lớn có cột tương quan vật lý với thứ tự row	Table sự kiện/log append-only, sắp theo thời gian, được filter theo khoảng timestamp

Tài liệu tham khảo

• PostgreSQL Documentation — Chapter 11: Indexes
• PostgreSQL Documentation — Index Types
• PostgreSQL Documentation — Partial Indexes
• PostgreSQL Documentation — Indexes on Expressions
• PostgreSQL Documentation — Index-Only Scans and Covering Indexes
• PostgreSQL Documentation — GIN Indexes
• PostgreSQL Documentation — BRIN Indexes
• PostgreSQL Documentation — CREATE INDEX

-- Basic B-tree index (the default access method, so USING btree is implicit)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Supports equality, range, and sorting on the same column
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id BETWEEN 100 AND 200;
SELECT * FROM orders ORDER BY customer_id;

-- Rarely needed in practice — shown for completeness
CREATE INDEX idx_sessions_token_hash ON sessions USING hash (token);

SELECT * FROM sessions WHERE token = 'abc123...';  -- equality only, no range support

-- Indexing a JSONB column for containment queries
CREATE TABLE products (
    id serial PRIMARY KEY,
    name text NOT NULL,
    attributes jsonb NOT NULL
);

CREATE INDEX idx_products_attributes_gin ON products USING gin (attributes);

-- The GIN index accelerates the @> (contains) operator
SELECT * FROM products
WHERE attributes @> '{"color": "red", "in_stock": true}';

-- Full-text search: index a generated tsvector column
ALTER TABLE products ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', name)) STORED;

CREATE INDEX idx_products_search_gin ON products USING gin (search_vector);

SELECT * FROM products
WHERE search_vector @@ to_tsquery('english', 'wireless & mouse');

CREATE INDEX idx_products_attributes_pathops
    ON products USING gin (attributes jsonb_path_ops);

-- Geometric proximity / containment queries
CREATE INDEX idx_locations_point_gist ON locations USING gist (coordinates);

SELECT * FROM locations
ORDER BY coordinates <-> point(10.775, 106.700)
LIMIT 5;  -- nearest-neighbor search, accelerated by the GiST index

-- Range type indexing backing an exclusion constraint
CREATE TABLE room_bookings (
    room_id int NOT NULL,
    during tsrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

CREATE INDEX idx_devices_ip_spgist ON devices USING spgist (ip_address inet_ops);

SELECT * FROM devices WHERE ip_address <<= '10.0.0.0/8';

-- Ideal BRIN candidate: an append-only, time-ordered events table
CREATE TABLE events (
    id bigserial PRIMARY KEY,
    created_at timestamptz NOT NULL,
    payload jsonb
);

CREATE INDEX idx_events_created_at_brin ON events USING brin (created_at);

-- Range queries on created_at benefit hugely, at a fraction of a B-tree's size
SELECT * FROM events
WHERE created_at >= '2026-07-01' AND created_at < '2026-07-08';

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

-- Uses the index efficiently: filters on the leading column
SELECT * FROM orders WHERE customer_id = 42;

-- Uses the index efficiently: filters on both columns, leftmost prefix intact
SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';

-- Does NOT use this index efficiently: skips the leading column
SELECT * FROM orders WHERE status = 'shipped';

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- The planner will use this narrow, cheap index for queries with a matching
-- (or provably compatible) predicate
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

CREATE UNIQUE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;

CREATE INDEX idx_users_email_lower ON users (lower(email));

-- Benefits from the index because the expression matches exactly
SELECT * FROM users WHERE lower(email) = 'jane@example.com';

-- Does NOT benefit — different expression, planner won't match it to the index above
SELECT * FROM users WHERE email = 'jane@example.com';

CREATE INDEX idx_orders_customer_covering
    ON orders (customer_id)
    INCLUDE (status, total_amount);

-- If the visibility map is up to date, this can be satisfied entirely from
-- the index — no heap fetch needed
SELECT customer_id, status, total_amount
FROM orders
WHERE customer_id = 42;

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);