Indexes, statistics và query planner
Index chỉ hữu ích khi access method, column order và predicate phù hợp dữ liệu. Planner chọn plan bằng estimates; tối ưu bắt đầu từ chênh lệch estimated/actual rows, không từ việc ép dùng index.
Access methods
| Index | Phù hợp | Caveat |
|---|---|---|
| B-tree | Equality, range, ordering, uniqueness và leftmost prefix. | Mặc định cho phần lớn workloads; random heap fetch có thể đắt. |
| Hash | Equality chuyên biệt. | Không giữ order, không unique/range; phải benchmark. PostgreSQL hiện đại có WAL và crash safety. |
| GIN | Multi-valued JSONB, arrays và full-text search. | Write/build cost và pending-list behavior. |
| GiST/SP-GiST | Spatial, ranges và specialized search/operator classes. | Semantics phụ thuộc operator class. |
| BRIN | Table rất lớn có physical correlation theo block ranges. | Rất nhỏ nhưng lossy; dữ liệu random làm hiệu quả thấp. |
Không kết luận Hash nhanh hơn B-tree chỉ từ O(1) lý thuyết. So bằng EXPLAIN (ANALYZE, BUFFERS) với dữ liệu, cache state và concurrency đại diện.
Composite, covering, partial và expression indexes
Thứ tự columns dựa trên predicates và sort; equality-leading columns rồi range/order thường hiệu quả. INCLUDE thêm payload cho index-only scan nhưng làm index lớn hơn. Partial index chỉ chứa subset, và query predicate phải imply index predicate. Expression index chỉ được dùng khi query expression tương ứng.
Planner và cost model
Planner sinh candidate paths và ước lượng cost từ row counts, distinct values, histograms, most-common values, correlation cùng cost parameters. Nó chọn sequential, index hoặc bitmap scan; nested-loop, hash hoặc merge join; aggregate và sort. Cost là đơn vị tương đối, không phải milliseconds.
Cardinality estimates
Estimated rows lệch actual rows là tín hiệu quan trọng vì sai số lan sang join order, algorithm và memory. Correlated columns cần extended statistics; expression hoặc skew cần statistics target phù hợp. Prepared statements có custom/generic plan trade-off và parameter-sensitive workloads có thể bị generic plan kém.
Đọc EXPLAIN đúng cách
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) thực thi query. Đọc actual time, rows và loops; total rows của nested node cần xét loops. Quan sát buffer hits/reads, rows removed, heap fetches, sort method và disk spill. Với DML, chạy trong transaction rollback hoặc staging an toàn.
Index-only scan và visibility
Index chứa đủ columns chưa chắc tránh heap access: heap page phải all-visible trong Visibility Map. VACUUM ảnh hưởng khả năng index-only scan. Nếu query phải random-fetch nhiều heap pages, sequential hoặc bitmap scan có thể rẻ hơn index scan.
Write amplification và index lifecycle
Mỗi index làm INSERT, UPDATE và DELETE thêm WAL, I/O, vacuum work và storage. Theo dõi index usage, size, bloat và write cost, nhưng đừng xóa index phục vụ uniqueness, foreign-key maintenance hoặc rare critical query chỉ vì counter thấp.