"Đừng dùng OR, nó giết index" là một trong những lời khuyên được lặp lại nhiều nhất về tối ưu SQL — và nó không chính xác. OR không tự động vô hiệu hóa index; PostgreSQL có cơ chế BitmapOr gộp nhiều index scan lại. Sự thật tinh tế hơn và hữu ích hơn: OR chỉ nhanh bằng nhánh chậm nhất của nó. Bài này đo thật khi nào OR dùng được index, khi nào một nhánh duy nhất thiếu index kéo cả câu xuống vực, và vì sao index gộp cứu được AND nhưng bó tay với OR.

OR trên các cột có index vẫn dùng index

Bắt đầu bằng ca mà lời khuyên dân gian nói sai. Bảng nguoi_dung 3 triệu dòng, mỗi cột email và sodienthoai có index đơn cột riêng:

SELECT id FROM nguoi_dung
WHERE email = 'user12345@mail.com' OR sodienthoai = 'sdt3999999';

Kế hoạch là BitmapOr gộp hai Bitmap Index Scan — PostgreSQL tra từng nhánh bằng index của nó, hợp hai tập kết quả lại, chỉ chạm đúng vài dòng. Chạy trong 0,090 ms. OR ở đây không giết gì cả.

Ảnh chụp đoạn mã SQL nền tối minh hoạ điều kiện OR làm mất index khi nào và vì sao, huyền thoại OR luôn giết index sự thật tinh tế hơn OR trên hai cột mỗi cột có index riêng PostgreSQL vẫn dùng index SELECT id FROM nguoi_dung WHERE email bằng OR sodienthoai bằng kế hoạch BitmapOr gộp hai Bitmap Index Scan cực nhanh, cạm bẫy thật một nhánh OR không có index quét cả bảng SELECT id FROM nguoi_dung WHERE email bằng có index OR tuoi bằng 200 không index phải kiểm mọi dòng OR chỉ nhanh bằng nhánh chậm nhất một nhánh không index kéo cả câu thành Seq Scan toàn bảng dù email có thể tra tức thì, vì sao OR nghĩa là dòng thỏa bất kỳ nhánh nào AND thu hẹp dần index lọc trước phần còn lại ít OR mở rộng phải xét mọi dòng trừ khi từng nhánh tự loại được bằng index của riêng nó rồi BitmapOr gộp lại, cách sửa cho mọi nhánh OR một index dùng được 1 đánh index cho cột còn thiếu tuoi CREATE INDEX idx_nd_tuoi ON nguoi_dung tuoi BitmapOr hai nhánh 2 hoặc tách thành UNION mỗi nhánh một truy vấn dùng index SELECT id WHERE email bằng UNION SELECT id WHERE tuoi bằng 200 lưu ý index gộp a b phục vụ a AND b nhưng không phục vụ a OR b

Hình 1: OR không tự động giết index. Trên hai cột có index, PostgreSQL dùng BitmapOr. Cạm bẫy thật nằm ở nhánh thiếu index — và cách sửa là cho mọi nhánh một index dùng được.

Cạm bẫy thật: một nhánh thiếu index

Đây mới là chỗ OR cắn. Cùng bảng, nhưng giờ một nhánh so với cột tuoi không có index:

SELECT id FROM nguoi_dung
WHERE email = 'user12345@mail.com'   -- có index
   OR tuoi = 200;                     -- KHÔNG index

Ảnh chụp bảng kết quả đo thật nền tối điều kiện OR và index nguoi_dung 3 triệu dòng PostgreSQL 16 EXPLAIN ANALYZE BUFFERS shared_buffers 128MB, email bằng OR sodienthoai bằng có 2 index đơn cột BitmapOr hai Bitmap Index Scan 0,090 mili giây, email bằng OR tuoi bằng 200 chỉ email có index Parallel Seq Scan cả bảng 48 mili giây, email bằng OR tuoi bằng 200 thêm index tuoi BitmapOr hai Bitmap Index Scan 0,039 mili giây, trường hợp 2 Filter email OR tuoi 200 Rows Removed by Filter 1 triệu nhân 3 workers một nhánh tuoi không index buộc quét và lọc từng dòng của cả 3 triệu dù email tra được tức thì sau khi index cả tuoi 48 mili giây xuống 0,039 mili giây nhanh hơn khoảng 1200 lần, index gộp email sodienthoai phục vụ AND không phục vụ OR email AND sodienthoai Index Scan using idx_composite 0,033 mili giây email OR sodienthoai Seq Scan Rows Removed 999999 nhân 3 56 mili giây cùng một index gộp AND dùng nó hoàn hảo OR không đụng tới được vì OR cần mỗi nhánh tự loại dòng bằng index riêng mà sodienthoai không phải tiền tố của index gộp, cốt lõi OR không tự động giết index OR chỉ nhanh bằng nhánh chậm nhất mỗi nhánh cần index dùng được để BitmapOr gộp lại một nhánh thiếu index bằng Seq Scan toàn bảng index gộp a b phục vụ a AND b không phục vụ a OR b

Hình 2: email OR sodienthoai với hai index cho BitmapOr 0,090 ms; thêm nhánh tuoi không index biến cả câu thành Parallel Seq Scan toàn bảng (loại bỏ 1 triệu dòng mỗi worker), 48 ms. Index cả tuoi đưa về BitmapOr 0,039 ms — nhanh hơn ~1.200 lần.

Kế hoạch tụt xuống Parallel Seq Scan toàn bảng, Filter: (email=... OR tuoi=200), Rows Removed by Filter: 1.000.000 mỗi worker (3 worker × 1 triệu = cả 3 triệu dòng) — 48 ms. Điều đau nhất: nhánh email vốn tra được trong micro-giây, nhưng vì OR tuoi=200 không thể loại dòng bằng index, PostgreSQL buộc phải đọc mọi dòng để kiểm cả hai điều kiện. Một nhánh hỏng kéo sập cả câu.

Lý do nằm ở ngữ nghĩa của OR: một dòng lọt kết quả nếu nó thỏa bất kỳ nhánh nào. AND thu hẹp dần (index lọc trước, phần còn lại ít), còn OR mở rộng — không có cách nào bỏ qua một dòng trừ khi từng nhánh tự loại được nó bằng index riêng, để rồi BitmapOr hợp các tập lại. Thiếu một index, cả cơ chế BitmapOr sụp đổ về quét tuần tự.

Cách sửa: index cho mọi nhánh, hoặc tách UNION

Sửa trực tiếp là cho nhánh còn thiếu một index:

CREATE INDEX idx_nd_tuoi ON nguoi_dung(tuoi);

Sau đó, cùng truy vấn email=... OR tuoi=200 cho BitmapOr hai nhánh — 0,039 ms, nhanh hơn ~1.200 lần so với 48 ms trước đó. Nguyên tắc: mỗi nhánh của một OR cần một index dùng được, thì BitmapOr mới gộp được.

Khi không thể (hoặc không muốn) đánh index cho mọi nhánh, tách bằng UNION cũng phục hồi khả năng dùng index cho các nhánh có index:

SELECT id FROM nguoi_dung WHERE email = '...'
UNION
SELECT id FROM nguoi_dung WHERE tuoi = 200;

Mỗi SELECT là một truy vấn độc lập, planner tối ưu riêng và dùng index của từng nhánh. (UNION thêm bước khử trùng như bài trước đã đo; nếu chắc hai nhánh rời nhau thì UNION ALL rẻ hơn.)

Index gộp phục vụ AND, không phục vụ OR

Một hiểu lầm phổ biến: nghĩ rằng một index gộp (email, sodienthoai) sẽ giúp cả AND lẫn OR trên hai cột đó. Đo thật cho thấy nó chỉ giúp AND:

  • email=... AND sodienthoai=...: Index Scan using idx_composite — 0,033 ms. Index gộp phục vụ hoàn hảo.
  • email=... OR sodienthoai=...: Seq Scan, loại bỏ 999.999 dòng mỗi worker — 56 ms. Index gộp không đụng tới được.

Vì sao? Index gộp (email, sodienthoai) sắp theo email trước, sodienthoai sau. Với AND, cả hai điều kiện cùng thu hẹp một vùng liên tục của index. Với OR, nhánh sodienthoai cần tìm độc lập — mà sodienthoai không phải tiền tố của index gộp nên không tra riêng được. OR cần hai index (hoặc một index cho mỗi nhánh), không phải một index gộp.

Đánh đổi cần cân nhắc

OR không phải kẻ thù — nhánh thiếu index mới là. Đừng máy móc viết lại mọi OR thành UNION. Nếu mọi nhánh đã có index, BitmapOr nhanh và gọn hơn UNION (không cần bước khử trùng). Chỉ can thiệp khi EXPLAIN cho thấy Seq Scan vì một nhánh không index.

Thêm index có cái giá ghi. Đánh index cho tuoi để cứu một truy vấn OR nghĩa là mọi INSERT/UPDATE cột đó giờ phải cập nhật thêm một index. Nếu truy vấn OR hiếm mà ghi nhiều, UNION (không cần index mới) hoặc chấp nhận seq scan có thể là lựa chọn tổng thể tốt hơn. Đo cả hai phía.

IN (...) là OR được tối ưu tốt. col IN (1,2,3) tương đương col=1 OR col=2 OR col=3 nhưng trên cùng một cột, nên PostgreSQL dùng một index scan với nhiều điểm tra — không dính bẫy này. Bẫy chỉ xảy ra khi OR trải trên các cột khác nhau.

Ba ý mang về

  1. OR không tự động giết index: đo thật, OR trên hai cột đều có index cho BitmapOr gộp hai index scan (0,090 ms) — lời khuyên "tránh mọi OR" là sai; OR chỉ nhanh bằng nhánh chậm nhất của nó.
  2. Một nhánh thiếu index kéo cả câu thành Seq Scan toàn bảng: đo thật, thêm OR tuoi=200 (không index) biến truy vấn tức thì thành quét 3 triệu dòng (48 ms); index cả nhánh đó đưa về BitmapOr 0,039 ms — nhanh hơn ~1.200 lần.
  3. Index gộp (a,b) phục vụ a AND b chứ không phục vụ a OR b: đo thật, cùng index gộp cho AND chạy 0,033 ms nhưng OR phải seq scan 56 ms — OR cần một index cho mỗi nhánh, hoặc tách bằng UNION.

Phần sau ta xét một cách khác vô tình vô hiệu hóa index, tinh vi hơn cả OR: Phần sau đo vì sao bọc một hàm quanh cột trong WHERE (như lower(email) hay date(created_at)) khiến index trên cột đó thành vô dụng, và index biểu thức cứu thế nào.