Expression index

Bạn có một index trên cột email, và một truy vấn tìm email không phân biệt hoa thường: WHERE lower(email) = 'user@example.com'. Có vẻ index sẽ giúp — nó nằm đúng trên cột email mà. Nhưng đo ra nó không giúp: truy vấn quét tuần tự cả một triệu hàng. Lý do và cách chữa là chủ đề của bài này: expression index — index đánh trên kết quả một biểu thức chứ không phải trên cột trần. Và cả cách chữa cũng có một cái bẫy khiến index vừa tạo lại không được dùng.

Vì sao hàm bọc cột làm index vô dụng

Một index thường lưu đúng giá trị của cột. Index trên email chứa các chuỗi như 'User500002@Example.COM' — nguyên văn, giữ hoa thường. Khi bạn hỏi WHERE lower(email) = 'user@example.com', CSDL cần so sánh phiên bản đã hạ chữ thường của mỗi email với chuỗi tìm. Nhưng index không hề chứa phiên bản hạ chữ đó — nó chứa giá trị gốc. Để biết lower(email) bằng gì, CSDL phải lấy từng hàng ra, tự chạy lower(), rồi so — tức là quét cả bảng.

Đây là biến thể của bài học ở bài khi chỉ mục không giúp: bọc một cột trong hàm là làm index của cột đó vô dụng, vì index sắp xếp theo giá trị gốc, không theo giá trị-sau-hàm. Tôi đo trên bảng một triệu hàng email hoa thường lẫn lộn:

WHERE lower(email) = '...'  với index trên cột email:
  -> Parallel Seq Scan, đọc 7352 trang, lọc bỏ cả triệu hàng

Index email 39 MB nằm đó hoàn toàn vô dụng cho truy vấn này — nó vẫn tốn chỗ đĩa và vẫn phải cập nhật ở mỗi lần ghi, chỉ là không phục vụ được câu truy vấn ta cần. Cái CSDL cần không phải index trên email, mà index trên lower(email): một chỉ mục chứa sẵn dạng đã hạ chữ thường để so thẳng.

Đo: expression index cứu, từ 7352 trang xuống 4

Cách chữa là tạo index thẳng trên biểu thức:

CREATE INDEX idx_lower_email ON users (lower(email));

Index này không lưu giá trị email gốc, nó lưu kết quả lower(email) cho mỗi hàng — đã tính sẵn, sắp xếp sẵn. Mỗi lần bạn INSERT hay UPDATE một hàng, PostgreSQL chạy biểu thức đó một lần rồi cất kết quả vào index; chi phí tính lower() được trả lúc ghi thay vì lặp lại ở mỗi lần đọc. Vì lý do này, biểu thức trong một expression index bắt buộc phải tất định (immutable) — cùng đầu vào luôn cho cùng đầu ra; một hàm phụ thuộc thời gian hay locale hiện tại thì không dùng được, vì giá trị đã cất trong index có thể lệch với giá trị tính lại sau này. Giờ khi truy vấn hỏi WHERE lower(email) = 'user@example.com', CSDL tìm thẳng trong index đã chứa đúng dạng đó:

WHERE lower(email) = '...'  sau khi có expression index:
  -> Index Scan, đọc 4 trang, trả về 1 hàng

Từ 7352 trang xuống 4 trang — gần 1800 lần ít hơn, cho đúng cùng một truy vấn. Đây là công cụ cho mọi trường hợp bạn thường xuyên tra theo một dạng biến đổi của cột: tìm không phân biệt hoa thường (lower(email)), lọc theo ngày từ một timestamp (date(created_at)), tra theo một khóa trong JSON (data ->> 'ma_kh'), hay bất kỳ biểu thức tất định nào. Expression index tính trước biểu thức một lần khi ghi, để mọi lần đọc dùng lại. Nó cũng ghép được với ý tưởng ở bài partial index: bạn có thể tạo một index vừa trên biểu thức vừa có điều kiện WHERE, ví dụ CREATE INDEX ON users (lower(email)) WHERE active — chỉ đánh dạng đã chuẩn hóa, và chỉ cho những hàng đang hoạt động, gọn cả hai chiều.

Một lần tôi đo hớ: biểu thức phải khớp chính xác

Sau khi thấy expression index hoạt động đẹp với lower(email), tôi tưởng mình đã "làm cho việc tra email không phân biệt hoa thường trở nên nhanh". Nhưng câu chữ đó ẩn một cái bẫy. Một đồng nghiệp giả định có thể viết truy vấn chuẩn hóa bằng upper() thay vì lower() — cùng ý định "so sánh không phân biệt hoa thường". Tôi thử:

WHERE upper(email) = 'USER500002@EXAMPLE.COM'
  -> Parallel Seq Scan lại, 7352 trang

Seq scan trở lại, dù tôi đã có một expression index cho email. Lý do: index của tôi đánh trên lower(email), còn truy vấn hỏi upper(email). Planner so khớp biểu thức theo cú pháp, không theo ý nghĩa: nó biết index chứa lower(email), thấy truy vấn cần upper(email), và kết luận hai biểu thức này khác nhau — nên không dùng index. Với PostgreSQL, lower và upper là hai hàm khác nhau tạo ra hai giá trị khác nhau; nó không "hiểu" rằng cả hai đều dùng để chuẩn hóa hoa thường.

Cái sai của tôi là nghĩ expression index phục vụ ý định (tra không phân biệt hoa thường), trong khi nó chỉ phục vụ đúng biểu thức mình đã khai. Bài học: một expression index chỉ giúp những truy vấn dùng y hệt biểu thức đó. Nếu code của bạn chuẩn hóa hoa thường bằng lower() ở chỗ này và upper() ở chỗ kia, chỉ một nửa số truy vấn được index phục vụ, nửa kia âm thầm quét cả bảng. Muốn expression index có tác dụng, phải nhất quán một biểu thức trên toàn bộ code — chọn lower, rồi dùng lower ở mọi nơi.

Vì sao điều này quan trọng khi lập trình

Hệ quả đầu tiên: khi truy vấn của bạn bọc cột trong một hàm, hãy đánh index trên chính hàm đó. WHERE lower(email)=, WHERE date(created_at)=, WHERE (gia * so_luong) > — mỗi cái cần một expression index tương ứng, index trên cột trần sẽ không được dùng. Kiểm bằng EXPLAIN rằng kế hoạch thật là Index Scan chứ không phải Seq Scan — đừng chỉ tin rằng "đã có index thì phải nhanh". Một cách khác, tùy trường hợp, là đổi truy vấn để không bọc cột: nếu bạn kiểm soát dữ liệu, lưu email luôn ở dạng chữ thường ngay khi ghi thì WHERE email = 'user@x.com' dùng được index cột trần, khỏi cần expression index. Chọn cách nào tùy bạn sửa được ứng dụng hay chỉ sửa được CSDL.

Hệ quả thứ hai: chuẩn hóa cách viết biểu thức trong toàn ứng dụng. Vì planner khớp biểu thức theo cú pháp, một expression index chỉ đáng giá khi mọi truy vấn liên quan viết biểu thức giống hệt. Con số mang theo: expression index đánh trên kết quả một biểu thức (lower(email), date(...), a+b), cứu những truy vấn bọc cột trong hàm mà index cột trần không giúp — WHERE lower(email)= từ Seq Scan 7352 trang xuống Index Scan 4 trang (~1800 lần); nhưng biểu thức truy vấn phải khớp CHÍNH XÁC biểu thức index, nên WHERE upper(email)= lại seq scan dù đã có index lower(email). Index không hiểu ý định của bạn; nó chỉ khớp đúng biểu thức bạn viết.

Thử ba mươi giây

Trong psql, tạo CREATE TABLE u (id int, email text); INSERT INTO u SELECT g, 'User'||g||'@X.com' FROM generate_series(1,200000) g; CREATE INDEX ON u(email);. Chạy EXPLAIN SELECT * FROM u WHERE lower(email)='user5@x.com'; — bạn sẽ thấy Seq Scan, index trên email không được dùng. Giờ thêm CREATE INDEX ON u (lower(email)); và chạy lại EXPLAIN cùng truy vấn — nó chuyển thành Index Scan. Rồi đổi truy vấn thành WHERE upper(email)=... và EXPLAIN lần nữa: Seq Scan trở lại, vì biểu thức không khớp index. Ba lần EXPLAIN đó, trong nửa phút, cho bạn thấy expression index sống theo đúng biểu thức nó khai — không hơn, không kém.