Partial index là gì và khi nào nên dùng?

Trang xe mới đăng ở Đà Nẵng đã có một index để tìm xe theo tỉnh và sắp xếp theo ngày đăng:

CREATE INDEX vehicles_province_listed_idx
ON vehicles (province, listed_at DESC);

Index này chứa cả xe đang bán lẫn xe đã bán. Nhưng trang xe đang bán chỉ hiển thị những xe chưa bán.

Nếu chỉ phục vụ trang này, liệu chúng ta có cần đưa xe đã bán vào index không?

Partial index là gì?

Partial index là index chỉ chứa một phần số dòng trong bảng.

Để tạo partial index, chúng ta thêm điều kiện WHERE vào câu lệnh tạo index. PostgreSQL chỉ đưa những dòng thoả điều kiện đó vào index.

Vì không phải lưu tất cả các dòng, partial index thường nhỏ hơn và tốn ít dung lượng lưu trữ hơn index đầy đủ trên cùng các cột.

Ví dụ, chúng ta chỉ tạo index cho xe chưa bán:

CREATE INDEX vehicles_unsold_idx
ON vehicles (province, listed_at DESC)
WHERE sold_at IS NULL;

Ở đây, sold_at IS NULL nghĩa là xe chưa bán. Những xe đã bán không được đưa vào index này.

Index đầy đủ lấy tất cả xe, partial index chỉ lấy xe chưa bán

Bảng vehicles có 5.000.000 xe, gồm cả xe đã bán và chưa bán. Index đầy đủ chứa tất cả 5.000.000 xe, chiếm 180 MB. Partial index với điều kiện WHERE sold_at IS NULL chỉ chứa 775.435 xe chưa bán, chiếm 28 MB.

Bảng vehicles · 5.000.000 xe

đã bán chưa bán

1. ↓lấy tất cả xe

   Index đầy đủ

   5.000.000 xe · 180 MB

2. ↓WHERE sold_at IS NULL

   Partial index

   775.435 xe · 28 MB

Với bộ dữ liệu thực hành, index cho toàn bộ xe chiếm 180 MB, còn index chỉ cho xe chưa bán chiếm 28 MB.

Tìm xe đang bán

Trang xe đang bán cần lấy 20 xe mới đăng ở Đà Nẵng:

SELECT id, plate, price_vnd, listed_at
FROM vehicles
WHERE province = 'Đà Nẵng'
  AND sold_at IS NULL
ORDER BY listed_at DESC
LIMIT 20;

Câu truy vấn chỉ cần xe chưa bán. Đây cũng chính là nhóm xe có trong vehicles_unsold_idx, nên PostgreSQL có thể dùng index này.

Index được sắp xếp theo tỉnh, sau đó theo ngày đăng từ mới đến cũ. PostgreSQL có thể tìm đến Đà Nẵng rồi đọc các xe mới nhất trước, không cần thêm bước sắp xếp.

Trong bộ dữ liệu thực hành, EXPLAIN cho thấy PostgreSQL chọn vehicles_unsold_idx và không có bước Sort.

Nhưng nếu câu truy vấn cần cả xe đã bán thì sao?

Khi câu truy vấn cần cả xe đã bán

Giả sử chúng ta bỏ điều kiện sold_at IS NULL:

SELECT id, plate, price_vnd, listed_at
FROM vehicles
WHERE province = 'Đà Nẵng'
ORDER BY listed_at DESC
LIMIT 20;

Lần này, kết quả cần bao gồm cả xe đã bán và chưa bán.

Trong khi đó, vehicles_unsold_idx không có xe đã bán. Nếu chỉ đọc index này, PostgreSQL có thể bỏ sót một xe mới đăng nhưng đã bán ngay sau đó.

Vì vậy, PostgreSQL không thể chỉ dùng partial index này để tìm đủ kết quả. Nó cần một cách khác, chẳng hạn dùng index đầy đủ nếu planner chọn index đó.

Muốn dùng partial index, PostgreSQL phải nhận biết được rằng những dòng cần tìm đều có trong index.

Với index WHERE sold_at IS NULL, cách rõ ràng nhất là đưa điều kiện sold_at IS NULL vào câu truy vấn. Dù vậy, planner vẫn có thể chọn cách khác nếu ước lượng cách đó có cost thấp hơn.

Khi nào nên dùng partial index?

Partial index hữu ích khi ứng dụng thường xuyên tìm một nhóm nhỏ trong bảng, chẳng hạn:

• Xe chưa bán trong bảng chứa cả xe đã bán.
• Đơn hàng đang xử lý trong bảng chứa cả đơn hàng đã hoàn thành.

Khi nhóm cần tìm nhỏ, index có thể tiết kiệm đáng kể dung lượng. Tuy nhiên, index vẫn cần được cập nhật khi dữ liệu thay đổi: xe mới chưa bán cần được thêm vào index, còn xe chuyển sang trạng thái đã bán sẽ không còn thuộc index này.

Mỗi index thêm vào khiến việc ghi dữ liệu tốn thêm gì?