Trang danh sách đơn hàng load mất 8 giây. Slow query log: một câu SELECT trên bảng orders 15 triệu row, filter theo user_id và status. EXPLAIN ANALYZE — Seq Scan on orders, actual time=4521ms. Sequential scan trên 15 triệu row chỉ để tìm 12 đơn hàng của một user. Thêm composite index (user_id, status) — query giảm từ 4.5 giây xuống 3 millisecond. Không sửa code, không đổi kiến trúc, chỉ thêm index đúng chỗ.
Index là công cụ tối ưu query mạnh nhất trong tay developer, nhưng cũng là thứ hay bị dùng sai nhất. Nhiều team hoặc không có index nào ngoài primary key, hoặc tạo index cho mọi cột rồi thắc mắc tại sao write chậm. Bài này đi qua cách index hoạt động, cách đọc EXPLAIN ANALYZE để hiểu Postgres đang làm gì, và khi nào index giúp ích — khi nào gây hại.
Vì sao query chậm dần khi data lớn
Khi bảng có 1,000 row, hầu hết query chạy nhanh dù không có index — database đọc toàn bộ bảng (sequential scan) trong vài millisecond vì data nằm gọn trong bộ nhớ. Nhưng khi bảng lớn lên 1 triệu, 10 triệu, 100 triệu row, sequential scan trở thành thảm hoạ.
Sequential scan đọc mọi row trong bảng, kiểm tra điều kiện WHERE cho từng row, rồi trả về những row thoả mãn. Với bảng 10 triệu row, dù chỉ cần 5 row, Postgres vẫn phải đọc toàn bộ 10 triệu row để tìm 5 row đó. Thời gian tỷ lệ thuận với kích thước bảng — O(n). Data tăng gấp đôi, query chậm gấp đôi.
-- Query tìm đơn hàng của user cụ thể
SELECT * FROM orders WHERE user_id = 42 AND status = 'completed';
-- Không có index: Seq Scan, đọc 15 triệu row → 4.5 giây
-- Có index: Index Scan, đọc 12 row → 3ms
Index giải quyết bằng cách tạo cấu trúc dữ liệu phụ cho phép tìm row nhanh hơn đọc tuần tự. Thay vì duyệt từ đầu đến cuối, index cho phép Postgres “nhảy” thẳng đến vị trí cần tìm — giống như mục lục cuối sách giúp bạn tìm trang nhanh hơn đọc từ trang 1.
B-tree index — cấu trúc mặc định
Khi bạn chạy CREATE INDEX trong Postgres mà không chỉ định loại, Postgres tạo B-tree index. Đây là loại index phổ biến nhất, phù hợp cho hầu hết use case.
B-tree (balanced tree) là cây cân bằng — mỗi node chứa nhiều key đã sắp xếp, trỏ đến node con hoặc đến vị trí row trên disk. Khi tìm kiếm, Postgres bắt đầu từ root, so sánh giá trị cần tìm với key trong node, đi xuống nhánh phù hợp, lặp lại cho đến khi tìm được leaf node chứa con trỏ đến row thật.
Với bảng 10 triệu row, B-tree có chiều sâu khoảng 3-4 level. Tìm một giá trị cụ thể chỉ cần đọc 3-4 page từ disk — so với hàng nghìn page khi sequential scan. Đây là O(log n) — data tăng gấp đôi, số bước tìm kiếm chỉ tăng thêm 1.
-- Tạo B-tree index (mặc định)
CREATE INDEX idx_orders_user_id ON orders (user_id);
-- Postgres dùng index cho: =, <, >, <=, >=, BETWEEN, IN, IS NULL
-- Postgres KHÔNG dùng B-tree cho: LIKE '%abc' (wildcard đầu), != (selectivity thấp)
B-tree hoạt động tốt cho equality (=) và range query (<, >, BETWEEN) vì data đã sắp xếp. WHERE user_id = 42 tìm trực tiếp vị trí. WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31' tìm điểm bắt đầu rồi quét liên tiếp đến điểm kết thúc — rất hiệu quả vì leaf node liên kết với nhau theo thứ tự.
Nhưng WHERE name LIKE '%john%' thì B-tree không giúp được — pattern bắt đầu bằng wildcard không tận dụng được thứ tự sắp xếp. WHERE name LIKE 'john%' thì B-tree dùng được vì prefix match tương đương range scan.
Single column index và khi nào nó được dùng
Tạo index trên một cột là bước đầu tiên và đơn giản nhất. Nhưng có index không có nghĩa Postgres sẽ dùng — planner quyết định dựa trên nhiều yếu tố.
CREATE INDEX idx_orders_status ON orders (status);
-- Query 1: dùng index (nếu status có selectivity cao)
SELECT * FROM orders WHERE status = 'refunded';
-- Query 2: KHÔNG dùng index (status = 'completed' chiếm 80% bảng)
SELECT * FROM orders WHERE status = 'completed';
Selectivity là yếu tố quyết định. Selectivity đo tỷ lệ row thoả mãn điều kiện so với tổng row. status = 'refunded' trả về 0.1% bảng — selectivity cao, index rất hiệu quả vì chỉ cần đọc rất ít row. status = 'completed' trả về 80% bảng — selectivity thấp, dùng index phải nhảy giữa hàng triệu vị trí random trên disk, chậm hơn sequential scan đọc liên tục.
Quy tắc ngón tay cái: nếu query trả về hơn 10-15% bảng, Postgres thường chọn sequential scan thay vì index scan. Con số chính xác phụ thuộc nhiều yếu tố (random IO cost, bảng có nằm trong memory không), nhưng 10-15% là ước lượng hợp lý.
Postgres biết selectivity nhờ statistics — chạy ANALYZE (tự động hoặc thủ công) để Postgres sample data và tính phân phối giá trị. Statistics cũ hoặc sai dẫn đến planner chọn plan tệ. Sau khi load data lớn hoặc thay đổi phân phối dữ liệu đáng kể, chạy ANALYZE thủ công để cập nhật.
-- Xem statistics của cột
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
Composite index: thứ tự cột quyết định tất cả
Đây là phần mà mình thấy nhiều developer mắc lỗi nhất. Composite index (multi-column index) index nhiều cột cùng lúc, nhưng thứ tự cột trong index ảnh hưởng trực tiếp đến query nào dùng được index.
-- Composite index: user_id trước, status sau
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
B-tree sắp xếp data theo thứ tự cột từ trái sang phải. Index (user_id, status) sắp xếp theo user_id trước, trong cùng user_id thì sắp theo status. Giống như danh bạ điện thoại sắp theo họ trước, trong cùng họ sắp theo tên.
Leftmost prefix rule: index (user_id, status) có thể phục vụ query filter theo user_id hoặc (user_id AND status), nhưng không thể phục vụ query chỉ filter theo status. Lý do: data trong index sắp theo user_id trước — nếu bạn chỉ tìm status = 'refunded', Postgres phải scan toàn bộ index vì các row cùng status nằm rải rác ở mọi user_id.
-- Dùng được index (user_id, status):
SELECT * FROM orders WHERE user_id = 42; -- ✓ dùng prefix user_id
SELECT * FROM orders WHERE user_id = 42 AND status = 'completed'; -- ✓ dùng cả hai cột
SELECT * FROM orders WHERE user_id = 42 AND status > 'a'; -- ✓ range trên cột thứ hai
-- KHÔNG dùng được index (user_id, status):
SELECT * FROM orders WHERE status = 'refunded'; -- ✗ thiếu cột đầu tiên
Vậy nên khi thiết kế composite index, cột filter equality (dùng =) nên đứng trước, cột range (dùng <, >, BETWEEN) hoặc cột selectivity thấp hơn đứng sau. Với query WHERE user_id = 42 AND created_at BETWEEN ... AND ..., index tối ưu là (user_id, created_at) — equality trên user_id thu hẹp phạm vi, rồi range trên created_at quét liên tiếp trong phạm vi đó.
Mình từng thấy team tạo index (created_at, user_id) cho query trên — Postgres dùng index nhưng không hiệu quả bằng: range scan trên created_at trả về hàng nghìn row trải trên nhiều user, rồi phải lọc thêm user_id. Đảo thứ tự sang (user_id, created_at) giảm rows scanned từ 50,000 xuống 200.
Covering index — index-only scan
Khi Postgres dùng index để tìm row, nó thường phải quay lại bảng chính (heap) để đọc các cột không nằm trong index — bước này gọi là “heap fetch”. Nếu bảng lớn và data nằm rải rác trên disk, heap fetch tốn IO đáng kể.
Covering index giải quyết bằng cách đưa thêm cột vào index — không phải để tìm kiếm mà để Postgres đọc luôn từ index mà không cần quay về heap. Postgres 11+ hỗ trợ INCLUDE clause cho mục đích này.
-- Covering index: tìm theo user_id, "bao phủ" thêm cột total, created_at
CREATE INDEX idx_orders_user_covering
ON orders (user_id)
INCLUDE (total, created_at);
-- Query này có thể chạy index-only scan:
SELECT user_id, total, created_at
FROM orders
WHERE user_id = 42;
Khi tất cả cột mà query cần đều nằm trong index (search key + included columns), Postgres thực hiện index-only scan — nhanh hơn đáng kể vì không cần đọc heap. Trong EXPLAIN ANALYZE bạn sẽ thấy Index Only Scan thay vì Index Scan.
Có một điều kiện quan trọng cho index-only scan hoạt động hiệu quả: visibility map phải up-to-date. Postgres dùng MVCC — mỗi row có multiple versions. Index không biết version nào visible cho transaction hiện tại, nên phải kiểm tra heap. Visibility map đánh dấu page nào “tất cả row đều visible cho mọi transaction” — chỉ khi page có flag này thì index-only scan mới skip heap fetch. VACUUM cập nhật visibility map — bảng ít được vacuum thì index-only scan phải fetch heap nhiều, mất lợi thế.
-- Kiểm tra tỷ lệ heap fetch trong index-only scan
EXPLAIN (ANALYZE, BUFFERS) SELECT user_id, total FROM orders WHERE user_id = 42;
-- Heap Fetches: 0 ← lý tưởng, visibility map tốt
-- Heap Fetches: 847 ← cần VACUUM
Đừng lạm dụng covering index — mỗi cột thêm vào tăng kích thước index, tốn storage và chậm write. Chỉ include cột mà query thực sự cần và query đó đủ quan trọng (chạy thường xuyên hoặc nằm trên critical path).
Partial index — nhỏ hơn, nhanh hơn
Partial index chỉ index một phần bảng — những row thoả mãn điều kiện WHERE trong CREATE INDEX. Index nhỏ hơn, nhanh hơn cả khi build lẫn khi scan, tốn ít storage hơn.
-- Chỉ index đơn hàng chưa xử lý (5% bảng)
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';
-- Query thoả mãn điều kiện partial index:
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01';
-- → Postgres dùng idx_orders_pending, scan rất ít row
-- Query KHÔNG match:
SELECT * FROM orders WHERE status = 'completed' AND created_at > '2026-01-01';
-- → Partial index không áp dụng, cần index khác
Partial index phù hợp khi bạn có query thường xuyên filter trên một subset nhỏ của bảng. Ví dụ kinh điển: bảng jobs với cột status, 95% job đã completed, chỉ 5% đang pending hoặc running. Full index trên status bao gồm cả triệu row completed mà bạn gần như không bao giờ query. Partial index chỉ trên pending/running nhỏ hơn 20 lần.
Mình dùng partial index cho unique constraint có điều kiện — ví dụ mỗi user chỉ có một đơn hàng draft tại một thời điểm:
CREATE UNIQUE INDEX idx_one_draft_per_user
ON orders (user_id)
WHERE status = 'draft';
-- User 42 không thể có 2 đơn hàng draft cùng lúc
Đọc EXPLAIN ANALYZE — không đoán mò
EXPLAIN là công cụ quan trọng nhất để hiểu Postgres đang làm gì với query. Không đoán — chạy EXPLAIN ANALYZE và đọc output.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'completed';
Output trả về query plan — cây các node, mỗi node là một thao tác Postgres thực hiện. Mình sẽ đi qua từng loại scan phổ biến.
Seq Scan — sequential scan
Seq Scan on orders (cost=0.00..458732.00 rows=12 width=128) (actual time=2145.32..4521.87 rows=12 loops=1)
Filter: ((user_id = 42) AND (status = 'completed'))
Rows Removed by Filter: 14999988
Postgres đọc toàn bộ bảng, kiểm tra filter cho từng row. Rows Removed by Filter: 14999988 — đọc gần 15 triệu row chỉ để tìm 12. Đây là dấu hiệu rõ ràng cần index.
Index Scan
Index Scan using idx_orders_user_status on orders (cost=0.43..48.76 rows=12 width=128) (actual time=0.05..0.12 rows=12 loops=1)
Index Cond: ((user_id = 42) AND (status = 'completed'))
Postgres dùng index tìm vị trí row, rồi quay về heap đọc data. actual time=0.05..0.12 — nhanh hơn Seq Scan hàng nghìn lần. Cost giảm từ 458,732 xuống 48.
Index Only Scan
Index Only Scan using idx_orders_covering on orders (cost=0.43..36.21 rows=12 width=24) (actual time=0.03..0.08 rows=12 loops=1)
Index Cond: (user_id = 42)
Heap Fetches: 0
Postgres đọc mọi thứ từ index, không quay về heap. Heap Fetches: 0 — lý tưởng. Nhanh hơn Index Scan vì bỏ qua bước heap fetch.
Bitmap Index Scan + Bitmap Heap Scan
Bitmap Heap Scan on orders (cost=124.56..8745.32 rows=5000 width=128) (actual time=12.34..45.67 rows=4987 loops=1)
Recheck Cond: (user_id = 42)
-> Bitmap Index Scan on idx_orders_user_id (cost=0.00..123.31 rows=5000 width=0) (actual time=8.12..8.12 rows=5000 loops=1)
Index Cond: (user_id = 42)
Bitmap scan là phương án trung gian khi index scan trả về quá nhiều row (random IO đắt) nhưng sequential scan đọc quá nhiều row thừa. Postgres xây bitmap từ index — đánh dấu page nào chứa row cần thiết — rồi đọc các page đó theo thứ tự vật lý trên disk, giảm random IO. Bitmap scan xuất hiện khi selectivity trung bình — không đủ ít để index scan, không đủ nhiều để seq scan.
Hiểu cost, rows, actual time, loops
Mỗi node trong EXPLAIN có hai phần: estimate (trong ngoặc tròn đầu tiên) và actual (ngoặc tròn thứ hai, chỉ có khi dùng ANALYZE).
cost=0.43..48.76 — chi phí ước lượng. Số đầu là startup cost (trước khi trả row đầu tiên), số sau là total cost. Đơn vị là “cost unit” nội bộ của Postgres, không phải millisecond — dùng để so sánh giữa các plan, không phải đo thời gian tuyệt đối.
rows=12 — số row Postgres ước lượng sẽ trả về, dựa trên statistics. Nếu con số này sai lệch nhiều so với actual rows, statistics cần cập nhật (ANALYZE).
actual time=0.05..0.12 — thời gian thực tế tính bằng millisecond. Startup time 0.05ms, total time 0.12ms. Đây là con số bạn quan tâm nhất khi tối ưu.
loops=1 — node này chạy bao nhiêu lần. Trong nested loop join, inner node có thể chạy hàng nghìn lần — actual time là thời gian mỗi lần, nhân với loops mới ra tổng thời gian thật. Đây là bẫy phổ biến khi đọc EXPLAIN: thấy actual time=0.05 tưởng nhanh, nhưng loops=50000 → tổng thời gian 2500ms.
-- Tip: thêm BUFFERS để thấy IO thật
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42;
-- Buffers: shared hit=48 read=3
-- hit=48: 48 page đọc từ cache
-- read=3: 3 page đọc từ disk (chậm hơn nhiều)
Khi Postgres chọn KHÔNG dùng index
Có index không có nghĩa Postgres sẽ dùng. Planner tính cost cho mỗi plan khả thi và chọn plan có cost thấp nhất. Có vài trường hợp Postgres bỏ qua index mà nhiều dev bất ngờ.
Bảng nhỏ. Bảng dưới vài nghìn row — sequential scan nhanh hơn index scan vì data nằm gọn trong vài page, đọc tuần tự nhanh hơn nhảy qua index rồi quay về heap.
Selectivity thấp. Query trả về hơn 10-15% bảng — sequential scan rẻ hơn vì đọc tuần tự ít random IO, trong khi index scan phải nhảy random giữa nhiều page.
Type mismatch. Index trên user_id (integer) nhưng query dùng WHERE user_id = '42' (string). Postgres phải cast, có thể không dùng được index. Mình từng debug 30 phút vì ORM truyền tham số sai type — thêm explicit cast fix ngay.
-- Type mismatch: index trên integer, query dùng string
SELECT * FROM orders WHERE user_id = '42'; -- có thể không dùng index
SELECT * FROM orders WHERE user_id = 42; -- dùng index
-- Function trên cột: index trên email, query dùng LOWER()
SELECT * FROM users WHERE LOWER(email) = '[email protected]'; -- không dùng index trên email
SELECT * FROM users WHERE email = '[email protected]'; -- dùng index
Function trên indexed column. WHERE LOWER(email) = '...' không dùng được index trên email vì B-tree index lưu giá trị gốc, không lưu LOWER(email). Cần expression index (phần sau).
Implicit cast. WHERE varchar_col = 42 — Postgres cast varchar_col sang integer để so sánh, biến mỗi row thành function call, index vô dụng. Luôn so sánh cùng type.
Expression index — index trên biểu thức
Khi query luôn dùng function trên cột — LOWER(), DATE(), JSON extract — tạo expression index để Postgres index kết quả của biểu thức.
-- Expression index cho case-insensitive search
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
-- Query dùng được index:
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Expression index cho JSONB field
CREATE INDEX idx_orders_metadata_type
ON orders ((metadata->>'type'));
-- Query dùng được index:
SELECT * FROM orders WHERE metadata->>'type' = 'subscription';
Expression index tính giá trị biểu thức lúc insert/update rồi lưu vào index. Khi query, Postgres so khớp biểu thức trong query với biểu thức trong index definition — phải khớp chính xác. LOWER(email) trong index nhưng query dùng UPPER(email) thì không match.
Mình hay dùng expression index cho hai trường hợp: search case-insensitive (LOWER(email)) và trích xuất JSON field (metadata->>'type'). Cả hai đều phổ biến trong ứng dụng thực tế và cải thiện performance đáng kể khi bảng lớn.
GIN và GiST — index cho dữ liệu phi truyền thống
B-tree hoạt động tốt cho scalar value (số, text, date) nhưng không phù hợp cho dữ liệu phức tạp: full-text search, JSONB chứa nhiều key, array, geometric data.
GIN (Generalized Inverted Index)
GIN index phân rã giá trị phức tạp thành nhiều key, mỗi key trỏ đến danh sách row chứa key đó — giống inverted index trong search engine.
-- GIN cho JSONB (hỗ trợ operator @>, ?, ?|, ?&)
CREATE INDEX idx_orders_metadata_gin ON orders USING GIN (metadata);
SELECT * FROM orders WHERE metadata @> '{"priority": "high"}';
-- GIN scan, rất nhanh dù JSONB chứa nhiều key khác nhau
-- GIN cho full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', body));
SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'postgresql & index');
GIN index tốn nhiều thời gian build hơn B-tree và tốn storage hơn, nhưng search cực nhanh. Write chậm hơn vì mỗi row insert phải cập nhật nhiều entry trong inverted index. Postgres có fastupdate option (mặc định ON) buffer write rồi batch update GIN — giảm write overhead nhưng tăng nhẹ search latency cho pending entries.
GiST (Generalized Search Tree)
GiST phù hợp cho dữ liệu có quan hệ spatial hoặc cần nearest-neighbor search: geometric point, range type, full-text search (alternative cho GIN). GiST lossy hơn GIN — có thể trả về false positive cần recheck, nhưng build nhanh hơn và support operator nhiều hơn (nearest-neighbor <-> chỉ GiST hỗ trợ).
-- GiST cho range query
CREATE INDEX idx_events_timerange ON events USING GIST (tsrange(start_at, end_at));
SELECT * FROM events
WHERE tsrange(start_at, end_at) && tsrange('2026-05-01', '2026-05-31');
Quy tắc ngón tay cái: dùng GIN cho full-text search và JSONB containment, dùng GiST khi cần nearest-neighbor hoặc overlap query trên range/geometry.
Index bloat và REINDEX
Mình từng gặp bảng có index 2 GB cho 500 MB data — index to gấp 4 lần data. Nguyên nhân: Postgres MVCC giữ dead tuple trong index cho đến khi VACUUM dọn. Nếu bảng có nhiều UPDATE/DELETE mà VACUUM không chạy kịp, index phình lên — gọi là index bloat.
Index bloat làm scan chậm hơn vì phải đọc nhiều page hơn cần thiết. Detect bằng extension pgstattuple:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('idx_orders_user_id');
-- leaf_fragmentation, avg_leaf_density — density thấp = bloat cao
Hoặc so sánh kích thước index thực tế với kích thước lý thuyết. Nếu index thực tế lớn hơn 30-50% so với estimate, có bloat đáng kể.
Fix bằng REINDEX:
-- REINDEX lock bảng — cẩn thận trên production
REINDEX INDEX idx_orders_user_id;
-- REINDEX CONCURRENTLY (Postgres 12+) — không lock, an toàn cho production
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
REINDEX CONCURRENTLY build index mới song song với index cũ, swap khi xong, xoá index cũ — tương tự CREATE INDEX CONCURRENTLY. Không lock table nhưng tốn thêm disk space tạm thời (có hai bản index cùng lúc) và CPU cho build.
Phòng ngừa bloat tốt hơn chữa: tune autovacuum aggressive hơn cho bảng có write nhiều. autovacuum_vacuum_scale_factor mặc định 0.2 (20% dead tuple mới trigger vacuum) — với bảng lớn 100 triệu row, 20% = 20 triệu dead tuple trước khi vacuum chạy, quá chậm. Giảm xuống 0.01-0.05 cho bảng hot.
Khi index gây hại
Index không miễn phí — mỗi index có chi phí, và có lúc chi phí vượt quá lợi ích.
Write overhead. Mỗi INSERT/UPDATE/DELETE phải cập nhật tất cả index trên bảng đó. Bảng có 10 index thì mỗi INSERT cập nhật 10 index — chậm gấp nhiều lần so với bảng có 2 index. Mình từng audit bảng events có 15 index (nhiều index do nhiều đợt tối ưu mà không ai dọn) — drop 8 index không ai dùng, write throughput tăng 3 lần.
-- Xem index nào KHÔNG được dùng
SELECT
schemaname, tablename, indexname,
idx_scan, -- số lần index được scan
idx_tup_read, -- số row đọc qua index
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 -- chưa bao giờ được dùng!
ORDER BY pg_relation_size(indexrelid) DESC;
Query trên cho bạn danh sách index chưa bao giờ được scan kể từ lần reset statistics gần nhất. Index không dùng = chi phí write không có lợi ích đọc. Cần cẩn thận: reset statistics (pg_stat_reset()) có thể xoá data, và một số index chỉ dùng cho query hiếm nhưng quan trọng (report cuối tháng). Kiểm tra kỹ trước khi drop.
Storage cost. Index tốn disk space — đôi khi tổng kích thước index lớn hơn data. Mỗi index cũng cần WAL khi write, tăng replication traffic. Trong cloud, storage và IOPS đều tốn tiền.
Vacuum overhead. VACUUM phải dọn dead tuple trong tất cả index. Bảng nhiều index thì vacuum chậm hơn, giữ lock lâu hơn. Đây là lý do nên giữ số index hợp lý — đủ cho query quan trọng, không hơn.
Anti-pattern phổ biến
Index mọi cột. Tạo index riêng cho từng cột trong bảng vì “chắc sẽ cần”. Write chậm, vacuum chậm, storage phình to, mà hầu hết index không ai dùng. Chỉ index cột mà query thực tế filter hoặc join — đọc slow query log, tìm query chậm, tạo index cho query đó.
Thiếu composite index. Có index riêng trên user_id và index riêng trên status, nhưng query filter cả hai: WHERE user_id = 42 AND status = 'completed'. Postgres có thể dùng Bitmap AND để kết hợp hai index, nhưng composite index (user_id, status) nhanh hơn đáng kể vì đọc một index thay vì hai rồi merge.
Sai thứ tự cột composite. Index (status, user_id) cho query WHERE user_id = 42 — không dùng được vì thiếu cột đầu tiên status. Thứ tự cột phải match pattern query: equality trước, range sau, cột selectivity cao trước.
Duplicate index. Index (user_id) và index (user_id, status) — index thứ hai đã bao gồm chức năng của index thứ nhất (leftmost prefix). Index (user_id) riêng là thừa, tốn write overhead vô ích. Trừ khi index thứ nhất có thuộc tính đặc biệt (UNIQUE constraint, partial condition khác).
-- Tìm index có thể duplicate
SELECT
a.indexname AS index_1,
b.indexname AS index_2,
a.indexdef
FROM pg_indexes a
JOIN pg_indexes b ON a.tablename = b.tablename
AND a.indexname != b.indexname
AND b.indexdef LIKE a.indexdef || '%'
WHERE a.schemaname = 'public';
Thực hành: tối ưu query chậm từng bước
Mình chia sẻ quy trình mà team áp dụng khi gặp query chậm trên production — không phải lý thuyết mà là các bước cụ thể.
Bước 1: Xác định query chậm. Bật pg_stat_statements extension (nên bật mặc định trên mọi Postgres production). Query xem câu nào tốn nhiều thời gian nhất:
SELECT
query,
calls,
mean_exec_time,
total_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Ưu tiên query có total_exec_time cao nhất — đó là query ảnh hưởng lớn nhất đến hệ thống, dù mỗi lần chạy có thể không chậm nhưng gọi quá nhiều lần.
Bước 2: Chạy EXPLAIN ANALYZE. Với query đã xác định, chạy EXPLAIN (ANALYZE, BUFFERS) trên staging hoặc replica — không chạy ANALYZE trên production nếu query có side effect (INSERT, UPDATE, DELETE).
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
AND o.created_at > '2026-05-01'
ORDER BY o.created_at DESC
LIMIT 20;
Bước 3: Đọc plan, tìm bottleneck. Tìm node có actual time cao nhất, hoặc node có rows estimate sai lệch nhiều so với actual. Seq Scan trên bảng lớn, Nested Loop với loops cao, Sort trên nhiều row — đều là ứng viên tối ưu.
Bước 4: Thêm hoặc sửa index. Dựa trên WHERE clause và JOIN condition, tạo composite index phù hợp. Dùng CREATE INDEX CONCURRENTLY trên production để không lock bảng.
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at DESC);
Bước 5: Verify. Chạy lại EXPLAIN ANALYZE — confirm plan đã đổi sang Index Scan, actual time giảm. So sánh trước/sau. Nếu plan không đổi, kiểm tra ANALYZE đã chạy chưa (statistics cũ khiến planner không biết index mới tốt hơn).
Bước 6: Monitor. Sau khi deploy index, theo dõi pg_stat_statements vài ngày — mean exec time có giảm không, write latency có tăng đáng kể không. Index tốt giảm read time mà không tăng write time quá nhiều.
Tóm tắt
B-tree index biến O(n) sequential scan thành O(log n) lookup — sự khác biệt giữa 4 giây và 3 millisecond trên bảng 15 triệu row. Composite index cần đúng thứ tự cột — equality trước, range sau, tuân theo leftmost prefix rule. Covering index với INCLUDE cho phép index-only scan, nhanh hơn nữa vì không cần quay về heap.
Partial index giảm kích thước index khi chỉ cần index subset data. Expression index cho phép index kết quả hàm (LOWER(), JSONB extract). GIN/GiST cho full-text search và JSONB containment — mỗi loại có trade-off riêng giữa build time, search speed, và write overhead.
EXPLAIN ANALYZE là công cụ bắt buộc — không đoán mò query chạy thế nào, đọc plan để hiểu Postgres thực sự làm gì. Chú ý loops nhân với actual time, chú ý Seq Scan trên bảng lớn, chú ý estimate rows sai lệch nhiều so với actual.
Index không miễn phí: mỗi index tốn write overhead, storage, vacuum time. Index không ai dùng là nợ — query pg_stat_user_indexes để tìm và drop. Tối ưu index là quy trình lặp: tìm query chậm → đọc EXPLAIN → thêm index → verify → monitor. Không phải “tạo index rồi quên” mà là liên tục đo lường và điều chỉnh theo workload thực tế.