Ép kiểu ngầm định làm mất index trong PostgreSQL: hàm ẩn quanh cột

Bài trước ta thấy bọc một hàm quanh cột (lower(email), date(tao_luc)) làm index vô dụng. Bài này là phiên bản vô hình của đúng bẫy đó: ép kiểu ngầm định. Khi bạn so một cột với một giá trị sai kiểu, PostgreSQL âm thầm chèn một phép ép kiểu — và nếu nó chọn ép vế cột, kết quả là một hàm ẩn (cột)::kiểu bọc quanh cột, giết index y hệt. Điều nguy hiểm: bạn không viết hàm nào cả, nên nhìn truy vấn không thấy gì sai. Bài này đo thật và chỉ cách đọc EXPLAIN để bắt nó.

Bẫy vô hình: PG ép vế nào?

Bảng sp 3 triệu dòng, cột so_lon kiểu int8 (bigint) có index. So sánh tưởng chừng vô hại:

SELECT id FROM sp WHERE so_lon = 1234567.0;   -- 1234567.0 là NUMERIC, không phải int8
SELECT id FROM sp WHERE so_lon = 1234567;     -- 1234567 là int8, khớp cột

Chỉ khác dấu .0, nhưng hệ quả khác một trời một vực. Vì 1234567.0 là kiểu numeric còn cột là int8, hai kiểu không có toán tử so sánh trực tiếp, nên PostgreSQL phải ép một vế. Nó chọn ép cột lên numeric (kiểu "rộng" hơn) — biến điều kiện thành (so_lon)::numeric = 1234567.0. Cột giờ bị bọc trong một phép cast, và index trên so_lon (lưu giá trị int8) không còn khớp.

Hình 1: Ép kiểu ngầm là "hàm ẩn" quanh cột. Hướng ép quyết định: PG ép vế hằng thì index sống, ép vế cột thì index chết. Dấu hiệu nhận biết nằm trong EXPLAIN.

Đo thật: hướng ép quyết định tất cả

Cùng bảng, năm cách viết, đọc kỹ cột "PG ép kiểu thành" trong EXPLAIN:

Hình 2: so_lon = 1234567.0 ép cột thành (so_lon)::numeric → Seq Scan 55,5 ms; so_lon = 1234567 giữ cột trần → Index Scan 0,040 ms. Ép chính vế hằng (1234567.0::int8) cũng cứu về 0,025 ms. Cột text ma_vch::int8 cũng seq scan 42,2 ms.

Quy luật hiện ra rõ ràng:

• PG ép vế cột ((so_lon)::numeric, (ma_vch)::bigint) → cột bị bọc → Parallel Seq Scan toàn bảng, 42–55 ms.
• PG ép vế hằng ('1234567'::bigint) hoặc kiểu khớp sẵn → cột trần → Index Scan, 0,01–0,04 ms. Nhanh hơn ~1.400 lần.

Cách đọc EXPLAIN để bắt lỗi này: nhìn dòng điều kiện. Nếu thấy Index Cond: (cột = 'x'::kiểu) — tốt, hằng bị ép, index dùng được. Nếu thấy Filter: ((cột)::kiểu = x) — cột bị bọc cast, index đã chết. Chữ (cột)::kiểu trong Filter là cờ đỏ.

Một chút may: đôi khi PG báo lỗi thay vì âm thầm

Không phải mọi lệch kiểu đều âm thầm. Với một số cặp kiểu không có ép ngầm định nào, PostgreSQL từ chối thẳng:

SELECT id FROM sp WHERE ma_vch = 1234567;
-- ERROR: operator does not exist: text = integer
-- HINT: You might need to add explicit type casts.

So một cột text với số nguyên trực tiếp không có ép ngầm nên PG báo lỗi — dễ phát hiện hơn nhiều so với ca int8 gặp numeric (im lặng chạy chậm). Trớ trêu, lỗi rõ ràng lại an toàn hơn cái "chạy được mà chậm". Ca nguy hiểm nhất là những cặp kiểu có ép ngầm (các kiểu số với nhau), vì chúng không báo gì cả.

Cách sửa: cho hai vế khớp kiểu

Nguyên tắc chung là đừng để PostgreSQL phải ép vế cột. Ba cách:

-- 1) viết hằng đúng kiểu cột: số nguyên không có .0, chuỗi trong nháy đơn
WHERE so_lon = 1234567          WHERE ma_vch = '0001234567'
-- 2) ép chính vế hằng (không đụng cột)
WHERE so_lon = 1234567.0::int8
-- 3) tầng ứng dụng/ORM: khai đúng kiểu tham số

Cách thứ ba đáng lưu tâm nhất trong thực tế, vì bẫy này thường đến từ driver và ORM. Một tham số truyền dưới dạng numeric, bigint, hay chuỗi không khớp kiểu cột sẽ khiến PostgreSQL ép vế cột mà bạn không hề viết dòng cast nào. Nếu một truy vấn nhanh trong psql nhưng chậm khi chạy qua ứng dụng, hãy nghi ngay kiểu tham số — bật auto_explain hoặc xem pg_stat_statements để thấy điều kiện thật mà driver gửi lên.

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

PostgreSQL 16 khá thông minh với các kiểu số nguyên. So int4 với int8 (như id4 = 1234567::int8) vẫn dùng index nhờ họ toán tử liên kiểu (cross-type operators) trong integer_ops — PG không cần ép cột. Bẫy chỉ xảy ra khi hai kiểu không có toán tử liên kiểu trực tiếp và PG buộc chọn ép cột (như int8 với numeric, hay text với bigint). Đừng hoảng với mọi lệch kiểu; kiểm bằng EXPLAIN.

Không giải quyết bằng cách đánh index biểu thức (cột)::kiểu. Về lý thuyết bạn có thể tạo index trên (so_lon)::numeric để cứu truy vấn lỗi, nhưng đó là chữa triệu chứng: bạn đang nuôi một index thừa chỉ vì kiểu không khớp. Sửa gốc — cho kiểu hai vế trùng — rẻ hơn và đúng hơn.

Chọn kiểu cột hợp lý ngay từ thiết kế. Nhiều ca bắt nguồn từ việc lưu mã số (mã sản phẩm, số điện thoại) trong cột text rồi lại so với số nguyên ở nơi khác, hoặc ngược lại. Thống nhất kiểu ngay từ lược đồ tránh được cả lớp lỗi này.

Ba ý mang về

1. Ép kiểu ngầm định là "hàm ẩn" quanh cột: so cột với sai kiểu khiến PostgreSQL chèn cast, và nếu nó ép vế cột ((so_lon)::numeric) thì index chết y như bọc hàm — đo thật so_lon = 1234567.0 chạy Seq Scan 55 ms so với 0,04 ms của so_lon = 1234567.
2. Hướng ép quyết định tất cả — đọc EXPLAIN để biết: Index Cond: (cột = 'x'::kiểu) là hằng bị ép (index sống), còn Filter: ((cột)::kiểu = x) là cột bị ép (index chết); chữ (cột)::kiểu trong Filter là cờ đỏ.
3. Sửa bằng cách cho hai vế khớp kiểu: viết hằng đúng kiểu cột, ép chính vế hằng, hoặc khai đúng kiểu tham số ở tầng ORM — bẫy này thường đến từ driver, nên truy vấn nhanh trong psql mà chậm qua ứng dụng thường là dấu hiệu.

Phần sau ta xét một dạng tìm kiếm phổ biến với cạm bẫy index riêng: Phần sau đo LIKE và ILIKE — vì sao LIKE 'abc%' dùng được index còn LIKE '%abc%' thì không, và text_pattern_ops cùng trigram cứu thế nào.