CREATE INDEX name ON table (column); tạo một chỉ mục (index) giúp PostgreSQL tìm các hàng khớp mà không phải đọc cả bảng. Loại mặc định là B-tree, phục vụ =, <, >, BETWEEN, IN, IS NULL và ORDER BY. Chọn loại khác bằng USING: gin, gist, spgist, brin hoặc hash.
Dòng đầu tiên của kế hoạch là Index Scan using users_email_idx on users: PostgreSQL tra giá trị trong index và chỉ đọc hàng khớp. Bỏ dòng CREATE INDEX rồi chạy lại để thấy Seq Scan on users, đọc cả 10.000 hàng.
Với một bảng chỉ vài chục hàng, bộ lập kế hoạch thường chọn quét tuần tự kể cả khi có index: đọc vài trang thì rẻ hơn. EXPLAIN ANALYZE giải thích cách đọc các kế hoạch này.
Chọn loại index
| Loại | Dùng cho |
|---|---|
| B-tree (mặc định) | so sánh bằng, khoảng, sắp xếp, LIKE 'prefix%' (với collation C hoặc text_pattern_ops) |
| GIN | giá trị gồm nhiều phần: jsonb (@>, ?), mảng (@>, &&), tsvector cho tìm kiếm toàn văn |
| GiST | hình học, kiểu khoảng (&&, chồng lấn), tìm kiếm toàn văn, láng giềng gần nhất ORDER BY ... <-> |
| SP-GiST | dữ liệu chia được thành các vùng không chồng lấn: điểm, địa chỉ IP, tiền tố văn bản |
| BRIN | bảng rất lớn có giá trị theo thứ tự vật lý, như created_at chỉ ghi thêm |
| Hash | chỉ so sánh bằng; B-tree làm được việc đó và hơn thế, nên hiếm khi được chọn |
Một index GIN trên cột jsonb giúp các truy vấn kiểm tra chứa dùng được index:
Kế hoạch cho thấy một Bitmap Index Scan trên index GIN, đưa kết quả cho một Bitmap Heap Scan đọc các hàng khớp. Trang JSONB trình bày các toán tử của nó.
Index trên nhiều cột
Một index B-tree nhiều cột sắp xếp theo cột đầu tiên, rồi theo cột thứ hai trong mỗi giá trị của cột đầu. Nó phục vụ các truy vấn lọc theo cột đứng đầu, hoặc theo cột đứng đầu và các cột tiếp theo. Một bộ lọc chỉ trên cột thứ hai được lợi rất ít, nên hãy đặt cột bạn luôn lọc bằng = lên đầu.
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);
-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;
INCLUDE (PostgreSQL 11+) lưu thêm các cột vào index mà không sắp xếp theo chúng: CREATE INDEX ON orders (customer_id) INCLUDE (total);. Một truy vấn chỉ đọc những cột đó có thể được trả lời từ riêng index, gọi là Index Only Scan.
Index unique và partial index: áp quy tắc cho một số hàng
CREATE UNIQUE INDEX từ chối các giá trị trùng lặp. Một partial index thêm WHERE và chỉ đánh index những hàng khớp với nó. Nó nhỏ hơn, và có thể làm cho quy tắc duy nhất chỉ áp dụng cho một phần các hàng:
Cả hai hàng đều được chấp nhận vì chỉ một trong hai chưa bị xóa. Một hàng thứ ba chưa xóa với cùng email sẽ báo lỗi duplicate key value violates unique constraint "accounts_email_live". Một truy vấn chỉ dùng partial index khi WHERE của nó suy ra được điều kiện của index, ở đây là WHERE NOT deleted.
Index trên biểu thức
Một index có thể lưu kết quả của một biểu thức. Truy vấn phải lặp lại đúng biểu thức đó để dùng được nó, và một index trên email không giúp được WHERE lower(email) = ...:
Kế hoạch dùng users_email_lower_idx vì WHERE lặp lại đúng lower(email). Biểu thức phải là immutable, nên now() hoặc một hàm phụ thuộc vào thiết lập sẽ bị từ chối.
Liệt kê, tạo và xóa index trên cơ sở dữ liệu đang chạy
pg_indexes liệt kê mọi index cùng câu lệnh có thể tạo lại nó:
Khóa chính và ràng buộc UNIQUE đã tự tạo index của chúng, users_pkey và users_email_key. Trong psql, \d users hiển thị cùng danh sách đó bên dưới các cột của bảng.
Trên một bảng đang nhận ghi, hãy tạo và xóa index với CONCURRENTLY để không chặn việc ghi. Các dạng này không chạy được bên trong khối transaction. Một lần tạo concurrent thất bại để lại một index INVALID, bạn phải xóa nó rồi tạo lại:
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx; -- rebuild a bloated index
Câu hỏi thường gặp
PostgreSQL có tự tạo index không?
Chỉ cho PRIMARY KEY, UNIQUE và ràng buộc exclusion. Cột khóa ngoại không có index, nên hãy tự thêm khi bạn join trên nó hoặc xóa hàng khỏi bảng được tham chiếu.
Vì sao PostgreSQL không dùng index đã tạo?
Những lý do hay gặp: bảng nhỏ nên đọc hết cả bảng lại rẻ hơn; truy vấn trả về một phần lớn số hàng; WHERE bọc cột trong một hàm hoặc phép ép kiểu mà index không có; hoặc thống kê đã cũ (hãy chạy ANALYZE). EXPLAIN cho thấy kế hoạch nào được chọn.
Bao nhiêu index là quá nhiều?
Không có con số cố định. Mỗi index làm nhanh một số lần đọc nhưng làm chậm mọi INSERT và hầu hết UPDATE trên bảng, và chiếm dung lượng đĩa. pg_stat_user_indexes.idx_scan cho biết mỗi index được dùng bao nhiêu lần, nên bạn có thể xóa những index cứ nằm ở 0.
CREATE INDEX có lock bảng không?
CREATE INDEX thông thường chặn INSERT, UPDATE và DELETE trên bảng cho tới khi xong, dù việc đọc vẫn tiếp tục. CREATE INDEX CONCURRENTLY cho phép ghi trong lúc tạo, mất nhiều thời gian hơn và không chạy được bên trong khối transaction.