Nên dùng một composite index nhiều cột hay nhiều index đơn cột? Database tự kết hợp index được không?

Kết hợp được, nhưng kém hơn một composite index đúng nghĩa.

PostgreSQL kết hợp nhiều index qua bitmap: quét từng index thành một bitmap các trang, rồi BitmapAnd (cho AND) hoặc BitmapOr (cho OR) trước khi đọc bảng. MySQL có cơ chế tương tự gọi là index merge.

Bitmap Heap Scan on events
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_events_tenant
        ->  Bitmap Index Scan on idx_events_status

Vì sao vẫn thua composite: với (tenant_id, status), database seek một lần tới đúng đoạn cần đọc. Với hai index rời, nó phải quét toàn bộ entry của tenant_id, quét toàn bộ entry của status — hai tập này có thể rất lớn — rồi mới giao nhau. Chi phí tỉ lệ với tổng kích thước hai tập, không phải với kích thước kết quả.

Nhưng nhiều index đơn cột vẫn hợp lý khi truy vấn thay đổi tổ hợp điều kiện khó lường (màn hình lọc động với 8 filter tuỳ chọn). Dựng đủ composite cho mọi tổ hợp là bất khả thi, để bitmap kết hợp là lựa chọn thực dụng.

Chọn thế nào: nhìn vào truy vấn thật có tần suất cao nhất. Vài truy vấn cố định chiếm phần lớn tải → dựng composite riêng cho chúng. Truy vấn ad-hoc, tần suất thấp → index đơn cột là đủ.

Lưu ý: một composite (a, b, c) đã phục vụ luôn WHERE a = ? và WHERE a = ? AND b = ?, nên không cần tạo thêm index riêng cho (a) — đó là loại index thừa hay gặp nhất.