Vì sao có index mà query vẫn quét cả bảng?

Bảng vehicles có hàng triệu xe, và cột plate đã được tạo index. Khi tìm đúng biển số 47P-600.55, PostgreSQL chỉ cần đọc 4 page trong bài thực hành.

Nhưng nếu người dùng nhập 47p-600.55, ứng dụng muốn tìm mà không phân biệt chữ hoa, chữ thường. Lập trình viên sửa câu SQL bằng cách thêm lower() vào cột plate. Kết quả: PostgreSQL đọc tới 192.308 page của bảng, dù index vẫn tồn tại.

Vì sao chỉ thêm lower() mà index không còn giúp được nữa?

Hai câu SQL trông giống nhau, nhưng hỏi hai thứ khác nhau

Câu đầu tìm trực tiếp giá trị đã lưu trong plate:

SELECT * FROM vehicles
WHERE plate = '47P-600.55';

Index trên plate lưu các giá trị biển số theo thứ tự. PostgreSQL có thể dùng thứ tự đó để tìm đúng biển số, thay vì xem từng dòng trong bảng.

Câu thứ hai đổi thành:

SELECT * FROM vehicles
WHERE lower(plate) = '47p-600.55';

lower() là hàm chuyển chữ hoa thành chữ thường. Với một dòng có plate = '47P-600.55', hàm trả về '47p-600.55'.

Đây chính là điều người ta thường gọi là bọc cột trong hàm: thay vì so sánh trực tiếp plate, câu SQL lấy giá trị của cột, biến đổi nó, rồi mới so sánh.

• plate = '47P-600.55' — so sánh giá trị gốc.
• lower(plate) = '47p-600.55' — so sánh giá trị sau khi đổi thành chữ thường.
• replace(plate, '-', '') = '47P600.55' — so sánh giá trị sau khi bỏ dấu gạch ngang.

Cùng index trên plate, ba điều kiện WHERE

vehicles_plate_idx giữ plate, đã sắp xếp

1. WHERE plate = '47P-600.55'

   ✓ dùng được index

   kế hoạch

   Index Scan

   page đọc

   4

2. WHERE lower(plate) = '47p-600.55'

   ✗ không dùng được

   kế hoạch

   Seq Scan

   page đọc

   192.308

3. WHERE plate::varchar(20) = '47P-600.55'

   ✗ không dùng được

   kế hoạch

   Seq Scan

   page đọc

   192.308

Vì sao index trên plate không trả lời được lower(plate)?

Hãy tưởng tượng index như một danh sách biển số đã được sắp xếp. Nó chứa giá trị gốc của cột:

30G-888.99
47P-600.55
51A-123.45
...

Khi nhận điều kiện plate = '47P-600.55', PostgreSQL có thể dựa vào thứ tự của danh sách để đi thẳng tới vị trí cần tìm trong cây B-tree.

Nhưng lower(plate) = '47p-600.55' lại yêu cầu tìm kết quả của hàm lower(), không phải giá trị plate ban đầu. Index hiện có không chứa danh sách các kết quả lower(plate) đã được sắp xếp.

PostgreSQL không thể mặc nhiên coi thứ tự của plate cũng là thứ tự của lower(plate). Nếu không có index phù hợp khác, nó phải kiểm tra các dòng và tính lower(plate) để biết dòng nào khớp. Trong bài thực hành, PostgreSQL chọn quét tuần tự toàn bảng (Sequential Scan).

Index trên một cột không tự động trở thành index cho mọi biểu thức tạo ra từ cột đó.

Cách sửa 1: Đừng biến đổi cột nếu không cần

Nếu hệ thống luôn lưu biển số ở dạng chữ hoa, ta có thể đổi giá trị người dùng nhập thành chữ hoa, thay vì đổi toàn bộ cột plate thành chữ thường:

SELECT * FROM vehicles
WHERE plate = upper('47p-600.55');

upper('47p-600.55') cho kết quả '47P-600.55'. Điều kiện cuối cùng vẫn là so sánh trực tiếp với plate, nên index thông thường vẫn có thể được sử dụng.

Cách này chỉ đúng nếu dữ liệu trong cột được chuẩn hoá nhất quán. Nếu bảng vừa có 47P-600.55, vừa có 47p-600.55, đổi riêng đầu vào thành chữ hoa không giải quyết được hết.

Cách sửa 2: Tạo index trên chính lower(plate)

Nếu cần tìm không phân biệt hoa thường trên dữ liệu hiện có, ta có thể tạo một index mới:

CREATE INDEX vehicles_plate_lower_idx
ON vehicles (lower(plate));

Đây là index trên biểu thức (expression index). Khác với index cũ, index mới lưu kết quả lower(plate) cho các dòng và sắp xếp những kết quả ấy.

Ví dụ, dòng lưu '47P-600.55' sẽ có giá trị '47p-600.55' trong index mới. Khi chạy:

SELECT * FROM vehicles
WHERE lower(plate) = '47p-600.55';

PostgreSQL giờ có thể tìm ngay trong index trên lower(plate), thay vì tính lower() cho từng dòng lúc tìm kiếm. Trong bài thực hành, lượng page cần đọc giảm từ 192.308 xuống còn khoảng 4.

Điểm khác nhau không phải là PostgreSQL bỗng hiểu hàm lower(). Ta đã tạo sẵn một danh sách được sắp xếp theo đúng giá trị mà câu WHERE cần tìm.

Ép kiểu cột cũng có thể gây ra vấn đề tương tự

Giả sử id là số nguyên và có index. Câu này tìm theo đúng kiểu dữ liệu của cột:

SELECT * FROM vehicles WHERE id = 12345;

Nhưng nếu ứng dụng đưa id về chuỗi ngay trong câu SQL:

SELECT * FROM vehicles WHERE id::text = '12345';

Câu WHERE giờ so sánh kết quả chuyển id thành văn bản. Index B-tree trên số nguyên id không phải index trên biểu thức id::text, nên PostgreSQL thường không thể dùng nó để tìm kiếm theo điều kiện này.

Cách đúng là truyền hoặc ép giá trị cần tìm về kiểu số, thay vì đổi cả cột sang kiểu chữ.

Không phải mọi phép ép kiểu đều làm mất khả năng dùng index. Một số phép ép kiểu không thay đổi giá trị có thể được PostgreSQL loại bỏ khi lập kế hoạch truy vấn. Vì vậy không nên kết luận chỉ dựa vào việc có :: trong SQL; hãy kiểm tra bằng EXPLAIN (ANALYZE, BUFFERS).

Nếu muốn bỏ cả dấu gạch ngang và dấu chấm?

Người dùng gõ 47P60055, nhưng bảng lưu 47P-600.55. Ta có thể bỏ hai dấu trước khi so sánh:

SELECT * FROM vehicles
WHERE replace(replace(plate, '-', ''), '.', '') = '47P60055';

Index thông thường trên plate không chứa giá trị đã bỏ dấu. Nếu kiểu tìm kiếm này diễn ra thường xuyên, tạo index cho chính biểu thức đó:

CREATE INDEX vehicles_plate_norm_idx
ON vehicles ((replace(replace(plate, '-', ''), '.', '')));

Index trên biểu thức chỉ khớp đúng biểu thức đó

CREATE INDEX ON vehicles ((replace(replace(plate, '-', ''), '.', '')));

plate 47P-600.55 → index giữ 47P60055

1. Chưa có index biểu thức

   WHERE replace(replace(plate, '-', ''), '.', '') = '47P60055'

   ✗ không dùng index tính biểu thức cho từng dòng

   kế hoạch

   Seq Scan

   page đọc

   192.308

2. Có index, viết đúng biểu thức

   WHERE replace(replace(plate, '-', ''), '.', '') = '47P60055'

   ✓ dùng index biểu thức giống hệt định nghĩa index

   kế hoạch

   Index Scan

   page đọc

   4

3. Có index, đổi thứ tự hai replace

   WHERE replace(replace(plate, '.', ''), '-', '') = '47P60055'

   ✗ không dùng index kết quả như nhau, nhưng là biểu thức khác

   kế hoạch

   Seq Scan

   page đọc

   192.308

Có một chi tiết cần nhớ: biểu thức dùng trong WHERE phải khớp với biểu thức index theo cách PostgreSQL nhận diện. Đổi thứ tự hai hàm replace() vẫn cho cùng kết quả trong ví dụ này, nhưng PostgreSQL không có nghĩa vụ chứng minh hai biểu thức tương đương để dùng chung index.

Vậy có nên tạo index cho mọi hàm?

Không. Mỗi index mới chiếm thêm dung lượng và phải được duy trì khi dữ liệu thay đổi. Trong bài thực hành này, index trên lower(plate) chiếm khoảng 150 MB; đổi lại, những lần tìm kiếm theo lower(plate) không cần tính lại biểu thức cho từng dòng.

Nên ưu tiên chuẩn hoá dữ liệu ngay khi ghi nếu ứng dụng có thể làm như vậy. Khi bắt buộc phải tìm theo một biểu thức thường xuyên, expression index là cách phù hợp. Và ngay cả khi có index phù hợp, PostgreSQL vẫn có thể chọn quét bảng nếu ước lượng cách đó rẻ hơn.

Cột không bị biến đổi cũng chưa bảo đảm rằng index sẽ được dùng. Chẳng hạn, plate LIKE '47P-600%' không gọi hàm nào, nhưng với một số collation, B-tree thông thường vẫn không hỗ trợ kiểu tìm kiếm đó như mong đợi.

Cột để nguyên, sao LIKE vẫn quét cả bảng?