Điều kiện sargable

Bạn sẽ học được

• Sargable là gì và vì sao nó quyết định index có được dùng.
• Vì sao WHERE date_trunc('month', order_date) = ... không dùng được index.
• Viết lại điều kiện thời gian thành khoảng nửa mở.
• Các dạng bọc hàm khác: EXTRACT, to_char, CAST, phép tính trên cột.
• Khi nào bọc hàm là chấp nhận được, và cách xử lý bằng index biểu thức.

Khái niệm cốt lõi

Sargable (viết tắt của Search ARGument-able) là điều kiện mà index có thể dùng để tìm thẳng dòng cần thiết. Dạng sargable điển hình:

cot = hang_so
cot > hang_so
cot BETWEEN a AND b
cot LIKE 'tien_to%'

Điều kiện không sargable là khi cột bị bọc trong hàm hoặc phép tính. Khi đó PostgreSQL buộc phải tính biểu thức cho từng dòng rồi mới so sánh — index trên cột trở nên vô dụng vì giá trị trong index là giá trị gốc, không phải giá trị sau khi biến đổi.

Xét hai câu trên orders có index order_date:

-- Sargable: so sánh trực tiếp trên cột
EXPLAIN SELECT * FROM orders
WHERE order_date >= DATE '2025-03-01'
  AND order_date <  DATE '2025-04-01';

Seq Scan on orders  (cost=0.00..1.24 rows=1 width=44)
  Filter: ((order_date >= '2025-03-01'::date) AND (order_date < '2025-04-01'::date))

-- Không sargable: cột bị bọc trong hàm
EXPLAIN SELECT * FROM orders
WHERE date_trunc('month', order_date) = DATE '2025-03-01';

Seq Scan on orders  (cost=0.00..1.28 rows=1 width=44)
  Filter: (date_trunc('month'::text, (order_date)::timestamp with time zone) = '2025-03-01'::date)

Bảng orders chỉ có 16 dòng nên cả hai đều Seq Scan — chưa đủ để thấy khác biệt. Nhưng hãy đọc dòng Filter: câu thứ nhất giữ order_date trần, câu thứ hai bọc nó trong date_trunc. Chỉ dạng thứ nhất mới cho phép PostgreSQL dùng index khi bảng lớn.

Vì sao? Index B-tree lưu order_date đã sắp xếp. Muốn tìm mọi ngày trong tháng 3, PostgreSQL chỉ cần hai lần dò: đến 2025-03-01 và đến trước 2025-04-01. Nhưng với date_trunc('month', order_date), nó phải tính hàm cho từng dòng rồi so kết quả — không có cách nào nhảy thẳng trong index.

Viết lại thành khoảng

Quy tắc: đưa phép biến đổi sang phía hằng số, giữ cột trần.

Dạng thứ ba xuất hiện trong kế hoạch thật:

Filter: (to_char((order_date)::timestamp with time zone, 'YYYY-MM'::text) = '2025-03'::text)

Mỗi dòng phải chuyển ngày thành chuỗi rồi so chuỗi. Trên bảng 10 triệu dòng, đó là 10 triệu lần gọi to_char.

Khoảng nửa mở >= đầu AND < đầu_kỳ_sau an toàn hơn BETWEEN vì không phụ thuộc vào việc cột là date hay timestamp. Nếu order_date là timestamp, BETWEEN '2025-03-01' AND '2025-03-31' sẽ bỏ sót mọi thời điểm trong ngày 31 sau 00:00:00.

LIKE và các dạng khác

LIKE chỉ sargable khi mẫu bắt đầu bằng ký tự cụ thể:

Với tìm kiếm chuỗi con, công cụ đúng là trigram index (CREATE EXTENSION pg_trgm; CREATE INDEX ... USING gin (name gin_trgm_ops)), không phải B-tree.

Phép tính trên cột cũng phá sargability:

-- Không dùng được index trên order_date
WHERE order_date + INTERVAL '7 days' > DATE '2025-03-10';

-- Tương đương, dùng được index
WHERE order_date > DATE '2025-03-10' - INTERVAL '7 days';

Nguyên tắc: nếu cột xuất hiện một mình ở một vế và mọi phép biến đổi nằm ở vế hằng số, điều kiện sargable.

Ngoại lệ có kiểm soát

Có ba trường hợp bọc hàm vẫn chấp nhận được:

Một, bảng nhỏ. Với 16 dòng, quét toàn bảng rẻ hơn mọi thứ khác. Đừng hy sinh tính rõ ràng của date_trunc chỉ để tối ưu một bảng vài chục dòng.

Hai, index biểu thức. Nếu điều kiện bọc hàm xuất hiện ở truy vấn nóng, tạo index đúng biểu thức đó:

CREATE INDEX idx_orders_month ON orders (date_trunc('month', order_date));

PostgreSQL sẽ khớp biểu thức trong WHERE với index. Cách này hiệu quả nhưng phải giữ biểu thức giống hệt — thêm một phép CAST là index không khớp.

Ba, cột sinh sẵn. Với trường hợp phổ biến, thêm cột đã tính:

ALTER TABLE orders ADD COLUMN order_month date
  GENERATED ALWAYS AS (date_trunc('month', order_date)::date) STORED;

Cột sinh sẵn lưu giá trị thật, đánh index bình thường, và truy vấn viết như cột thường.

Thực hành

1. Chạy hai câu EXPLAIN ở trên và so sánh dòng Filter.
2. Viết lại WHERE EXTRACT(YEAR FROM order_date) = 2025 thành dạng khoảng và kiểm tra Filter không còn extract.
3. Viết lại WHERE order_date + 7 > DATE '2025-03-10' và giải thích vì sao hai câu tương đương.
4. Với WHERE name LIKE '%SQL%', đề xuất hai cách tăng tốc: trigram index và tìm kiếm toàn văn.
5. Tạo index biểu thức trên date_trunc('month', order_date) rồi chạy lại câu không sargable. Tìm Index Scan trong kế hoạch.

Lỗi thường gặp

Lỗi cuối nguy hiểm nhất: tối ưu hiệu năng không được phép đổi kết quả. Luôn chạy cả hai phiên bản trên cùng dữ liệu và so sánh số dòng trước khi thay vào production.

Tóm tắt

• Sargable là điều kiện cho phép index tìm thẳng dòng cần thiết.
• Bọc cột trong hàm hoặc phép tính làm index vô dụng.
• Viết lại thời gian thành khoảng nửa mở >= đầu AND < kỳ_sau.
• LIKE 'tien_to%' dùng được B-tree; %giua% cần trigram.
• Khi buộc phải bọc hàm: index biểu thức hoặc cột sinh sẵn.