Tối ưu 3 — Viết lại truy vấn & sargable predicates

Có một sự thật khó chịu: bạn có thể đã tạo đúng index, đã ANALYZE bảng, đã cho planner mọi thông tin nó cần — mà truy vấn vẫn chậm. Lý do thường không nằm ở cấu hình database, mà ở cách bạn viết câu SQL. Cùng một kết quả logic có thể được diễn đạt bằng nhiều cách, và planner của PostgreSQL chỉ dùng được index khi câu truy vấn được viết theo dạng nó "nhìn ra được".

Bài này tập trung vào viết lại truy vấn (query rewriting) — biến một câu SQL "đúng nhưng chậm" thành "đúng và nhanh" mà không đổi kết quả. Đây là kỹ năng bổ trợ cho hai bài trước: EXPLAIN cho bạn đọc kế hoạch thực thi, Chỉ mục cho bạn tạo đúng index. Bài này dạy bạn viết câu truy vấn sao cho hai thứ kia phát huy tác dụng.

Trọng tâm là khái niệm sargable và một loạt mẫu viết lại thực dụng trên PostgreSQL.

Sargable predicate — điều kiện mà index dùng được

"Sargable" là viết tắt của Search ARGument able — một điều kiện WHERE mà database có thể dùng index để thu hẹp phạm vi tìm kiếm. Ngược lại, một predicate non-sargable buộc database phải tính toán trên từng dòng trước khi so sánh, và như vậy index trở nên vô dụng — kết quả là sequential scan trên toàn bảng.

Quy tắc gốc rất đơn giản: cột indexed phải đứng "trần" ở một vế của phép so sánh. Ngay khi bạn bọc một hàm hay phép tính lên cột, B-Tree index (vốn sắp theo giá trị nguyên gốc của cột) không còn khớp được nữa.

Bẫy 1: hàm bọc lên cột

Ví dụ kinh điển: tìm giao dịch trong một ngày cụ thể.

-- ✗ Viết xấu: date() bọc lên cột created_at
SELECT id, account_id, amount
FROM transactions
WHERE date(created_at) = '2024-03-15';

Cột created_at là timestamp. Hàm date(created_at) phải chạy trên mọi dòng để cắt phần giờ, rồi mới so sánh. Nếu bạn có index trên created_at, nó nằm im — vì index lưu giá trị timestamp đầy đủ, không lưu date(created_at).

Cách sửa: chuyển thành điều kiện khoảng (range) để cột đứng trần.

▶ Chạy được trong SQL Builder

-- ✓ Viết tốt: khoảng nửa mở, created_at đứng trần
SELECT id, account_id, amount
FROM transactions
WHERE created_at >= '2024-03-15 00:00:00'
  AND created_at <  '2024-03-16 00:00:00'
ORDER BY created_at;

Với khoảng nửa mở >= ... AND < ..., index trên created_at được dùng như range scan. Dùng < ngày_hôm_sau thay vì <= '2024-03-15 23:59:59' để tránh sai sót với phần lẻ giây (microsecond) — khoảng nửa mở luôn đúng bất kể độ phân giải thời gian.

Lưu ý về sandbox: dữ liệu mẫu rất nhỏ nên bạn sẽ không thấy khác biệt tốc độ giữa hai cách viết. Ý nghĩa của "nhanh hơn" chỉ bộc lộ ở bảng hàng triệu dòng. Ở đây ta chỉ minh hoạ dạng câu đúng.

Bẫy 2: so sánh không phân biệt hoa/thường

-- ✗ Viết xấu: lower() bọc lên cột
SELECT id, full_name FROM customers
WHERE lower(full_name) = 'nguyen van an';

Muốn dùng index cho kiểu này, bạn có hai lựa chọn. Một là expression index khớp đúng biểu thức trong WHERE:

-- Không đánh dấu chạy được: đây là DDL tạo index
CREATE INDEX idx_customers_lower_name ON customers (lower(full_name));

Khi đã có index này, WHERE lower(full_name) = '...' trở thành sargable vì planner khớp biểu thức lower(full_name) với index. Lựa chọn hai là dùng kiểu citext (case-insensitive text) cho cột ngay từ đầu. Điểm mấu chốt: hàm trên cột chỉ dùng được index khi có một index xây trên đúng biểu thức đó.

Bẫy 3: phép tính trên cột

-- ✗ Viết xấu: salary bị nhân trước khi so sánh
SELECT id, name, salary FROM employees
WHERE salary * 12 >= 600000;

Chuyển phép tính sang vế hằng số, để cột đứng trần:

▶ Chạy được trong SQL Builder

-- ✓ Viết tốt: đưa hằng số sang vế phải
SELECT id, name, salary FROM employees
WHERE salary >= 600000 / 12;

Cả hai cho kết quả như nhau, nhưng bản sau cho phép index trên salary hoạt động. Nguyên tắc chung: cột một bên, hằng số một bên.

Bẫy 4: leading wildcard trong LIKE

B-Tree index sắp text theo tiền tố (prefix). Do đó:

• WHERE account_no LIKE 'ACC-99%' — sargable, index dùng được (tìm theo tiền tố).
• WHERE account_no LIKE '%9921' — non-sargable, wildcard đứng đầu khiến index vô dụng vì không có tiền tố cố định để nhảy tới.

Nếu bắt buộc phải tìm hậu tố hay chuỗi con, cân nhắc index chuyên dụng: pg_trgm (trigram GIN index) cho tìm kiếm LIKE '%...%', hoặc full-text search (tsvector/GIN). Đó là chủ đề của bài Chỉ mục.

Bẫy 5: ép kiểu ngầm phá index

Đây là lỗi khó thấy nhất. Giả sử account_no là kiểu text (ví dụ '9921'), nhưng bạn viết:

-- ✗ Viết xấu: so text với number → ép kiểu ngầm
SELECT * FROM accounts WHERE account_no = 9921;

PostgreSQL phải ép kiểu để so sánh được. Tùy hướng ép kiểu, nó có thể biến thành account_no::numeric = 9921, tức là một hàm bọc lên cột — non-sargable. Luôn so sánh đúng kiểu: WHERE account_no = '9921'. Ngược lại, nếu cột là số mà bạn truyền chuỗi (WHERE balance = '100'), Postgres thường ép hằng chuỗi sang số (an toàn hơn), nhưng đừng dựa vào may rủi — hãy khớp kiểu ở tầng ứng dụng.

EXISTS vs IN vs JOIN

Ba cách này thường cho cùng kết quả khi kiểm tra "khách hàng có tài khoản không", nhưng hành vi khác nhau ở các cạnh biên.

Tương đương và thường được planner tối ưu như nhau (Postgres hiện đại rất giỏi biến đổi giữa chúng):

▶ Chạy được trong SQL Builder

-- Khách hàng có ít nhất một tài khoản (EXISTS — semi-join)
SELECT c.id, c.full_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM accounts a WHERE a.customer_id = c.id
)
ORDER BY c.id;

EXISTS dừng ngay khi tìm thấy dòng đầu tiên khớp (short-circuit) — planner biến nó thành semi-join. Đây là dạng nên ưu tiên cho câu hỏi "có tồn tại không".

Bẫy NULL của NOT IN — đây là điểm quan trọng nhất của mục này. Xét câu "khách hàng KHÔNG có tài khoản":

-- ✗ Nguy hiểm: NOT IN với subquery có thể trả về NULL
SELECT c.id, c.full_name
FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM accounts);

Nếu cột accounts.customer_id có bất kỳ giá trị NULL nào, NOT IN sẽ trả về... không dòng nào cả. Lý do là logic ba trị của SQL: x NOT IN (1, 2, NULL) tương đương x <> 1 AND x <> 2 AND x <> NULL, và x <> NULL cho ra UNKNOWN, làm toàn bộ điều kiện không bao giờ TRUE. Kết quả là một bug âm thầm: câu chạy không lỗi nhưng trả sai.

Cách viết đúng và an toàn là NOT EXISTS (anti-join):

▶ Chạy được trong SQL Builder

-- ✓ An toàn với NULL: NOT EXISTS (anti-join)
SELECT c.id, c.full_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM accounts a WHERE a.customer_id = c.id
)
ORDER BY c.id;

NOT EXISTS không dính bẫy NULL và được planner thực thi thành anti-join hiệu quả. Quy tắc: dùng NOT EXISTS, tránh NOT IN với subquery trừ khi bạn chắc chắn 100% cột con không bao giờ NULL.

Còn JOIN? Dùng JOIN khi bạn cần lấy cột từ bảng bên kia. Nhưng cẩn thận: nếu một khách hàng có nhiều tài khoản, INNER JOIN sẽ nhân dòng (mỗi khách hàng xuất hiện nhiều lần), buộc bạn thêm DISTINCT — vừa tốn kém vừa dễ sai. Khi chỉ cần lọc theo sự tồn tại mà không lấy cột, EXISTS sạch hơn.

Subquery vs JOIN vs CTE

CTE (WITH) giúp câu SQL dễ đọc bằng cách đặt tên cho từng bước. Nhưng có một chi tiết hiệu năng quan trọng theo phiên bản Postgres:

• Trước PostgreSQL 12: CTE luôn được materialize — nghĩa là kết quả CTE được tính xong, ghi ra một bảng tạm, rồi phần ngoài đọc từ đó. Điều này tạo "hàng rào tối ưu" (optimization fence): planner không thể đẩy điều kiện WHERE từ ngoài vào trong CTE. Đôi khi tiện (chủ động vật chất hoá), nhưng thường làm chậm.
• Từ PostgreSQL 12 trở đi: CTE có thể được inline (nội tuyến) tự động nếu nó được tham chiếu đúng một lần và không có tác dụng phụ — planner khi đó tối ưu xuyên qua CTE như subquery thường. Bạn vẫn có thể ép hành vi bằng WITH ... AS MATERIALIZED hoặc AS NOT MATERIALIZED.

▶ Chạy được trong SQL Builder

-- CTE tính tổng chi tiêu mỗi tài khoản rồi lọc
WITH spend AS (
  SELECT account_id, SUM(amount) AS total
  FROM transactions
  GROUP BY account_id
)
SELECT a.account_no, s.total
FROM accounts a
JOIN spend s ON s.account_id = a.id
ORDER BY s.total DESC;

LATERAL join giải quyết một dạng bài mà subquery thường không làm được: với mỗi dòng của bảng trái, chạy một truy vấn phụ tham chiếu đến dòng đó — ví dụ "lấy 1 giao dịch mới nhất của mỗi tài khoản":

▶ Chạy được trong SQL Builder

-- LATERAL: giao dịch mới nhất của từng tài khoản
SELECT a.account_no, t.amount, t.created_at
FROM accounts a
CROSS JOIN LATERAL (
  SELECT amount, created_at
  FROM transactions t
  WHERE t.account_id = a.id
  ORDER BY t.created_at DESC
  LIMIT 1
) t
ORDER BY a.account_no;

Từ khoá LATERAL cho phép subquery bên phải nhìn thấy a.id của dòng trái — không có nó, subquery bị cô lập.

Phân trang: OFFSET lớn rất chậm → keyset (seek) pagination

Đây là mẫu tối ưu có tác động lớn nhất trong thực tế API.

Cách phân trang phổ biến dùng LIMIT ... OFFSET ...:

-- ✗ Chậm dần theo trang: OFFSET phải quét & bỏ đi toàn bộ dòng đầu
SELECT id, account_id, amount, created_at
FROM transactions
ORDER BY id
LIMIT 20 OFFSET 100000;

Vấn đề: OFFSET 100000 không cho phép database nhảy thẳng tới dòng 100 001. Nó phải đọc, sắp xếp và vứt bỏ 100 000 dòng đầu chỉ để trả 20 dòng tiếp theo. Chi phí tăng tuyến tính theo số trang — trang cuối cùng chậm nhất. Đây là lý do "trang 5000" của một danh sách lớn có thể mất vài giây.

Keyset pagination (còn gọi seek method) dùng giá trị của dòng cuối trang trước làm mốc, thay vì đếm số dòng bỏ qua:

▶ Chạy được trong SQL Builder

-- ✓ Keyset: nhảy thẳng bằng index, không đếm dòng bỏ qua
SELECT id, account_id, amount, created_at
FROM transactions
WHERE id > 2
ORDER BY id
LIMIT 3;

Ở đây 2 là id của dòng cuối cùng trang trước. Với index trên id, database seek thẳng tới vị trí id > 2 rồi đọc 3 dòng — chi phí không đổi dù bạn ở trang 1 hay trang 5000. Client gửi kèm last_id của trang trước để lấy trang sau.

Khi sắp theo cột không duy nhất (ví dụ created_at), phải thêm cột phụ (tie-breaker) để mốc là duy nhất, dùng so sánh bộ (row comparison):

-- Sắp theo (created_at, id) — mốc là cặp giá trị của dòng cuối trang trước
SELECT id, account_id, amount, created_at
FROM transactions
WHERE (created_at, id) > ('2024-03-15 10:00:00', 42)
ORDER BY created_at, id
LIMIT 20;

Đánh đổi của keyset: bạn không nhảy tới trang bất kỳ theo số ("đi thẳng tới trang 37"). Nó phù hợp với cuộn vô hạn (infinite scroll) và nút "Trang sau" — đúng với đa số API danh sách hiện đại.

Tránh SELECT *

SELECT * kéo về mọi cột, kể cả cột lớn (text dài, JSON) bạn không dùng. Hệ quả:

1. Tăng I/O và băng thông — phải đọc và truyền các cột thừa.
2. Chặn index-only scan — nếu một index đã chứa đủ tất cả cột trong SELECT và WHERE, Postgres có thể trả kết quả chỉ từ index, không cần chạm bảng (index-only scan). SELECT * gần như luôn phá cơ hội này vì index hiếm khi chứa mọi cột.

▶ Chạy được trong SQL Builder

-- ✓ Chỉ lấy cột cần — mở đường cho index-only scan
SELECT id, account_id
FROM transactions
WHERE account_id = 1
ORDER BY id;

Nếu có index trên (account_id, id), câu trên là ứng viên hoàn hảo cho index-only scan (khi visibility map cho phép). SELECT * thì không.

N+1 query — kẻ giết hiệu năng thầm lặng

Đây là chống chỉ định số một khi dùng ORM. Kịch bản: bạn lấy danh sách N khách hàng (1 query), rồi vòng lặp trong code để với mỗi khách hàng lại bắn 1 query lấy tài khoản của họ — tổng cộng N+1 query. Với 100 khách hàng là 101 round-trip tới database.

Cách diệt N+1 là gộp dữ liệu về trong 1–2 query, dùng JOIN hoặc IN:

▶ Chạy được trong SQL Builder

-- ✓ Gộp bằng JOIN: một query thay cho N+1
SELECT c.id, c.full_name, a.account_no, a.balance
FROM customers c
JOIN accounts a ON a.customer_id = c.id
ORDER BY c.id, a.id;

Hoặc batch bằng IN — lấy danh sách id ở query 1, rồi một query lấy hết chi tiết:

-- Batch: gom id trang này rồi lấy chi tiết một lần
SELECT * FROM accounts WHERE customer_id IN (1, 2, 3, 4, 5);

Hầu hết ORM có cơ chế eager loading (JOIN FETCH trong JPA/Hibernate, selectinload/joinedload trong SQLAlchemy, includes/preload trong Rails) để tự động gộp — hãy bật nó cho các quan hệ bạn biết sẽ truy cập.

Aggregate, DISTINCT và window function

DISTINCT và GROUP BY buộc database sắp xếp hoặc hash toàn bộ tập dòng — tốn kém. Trước khi thêm DISTINCT, hỏi: tại sao có dòng trùng? Thường là do một JOIN nhân dòng mà lẽ ra nên là EXISTS.

Window function thay được nhiều self-join tốn kém. Ví dụ "xếp hạng giao dịch theo giá trị trong mỗi tài khoản" — không cần join bảng với chính nó:

▶ Chạy được trong SQL Builder

-- Window function thay self-join
SELECT
  account_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY amount DESC) AS rn
FROM transactions
ORDER BY account_id, rn;

Cuối cùng, tránh ORDER BY không cần thiết: nếu API không hiển thị theo thứ tự nào, đừng bắt database sắp xếp. Nhưng lưu ý — với keyset pagination thì ORDER BY khớp index lại bắt buộc và có lợi (index đã sắp sẵn nên "sort" gần như miễn phí).

Use case thực tế: tăng tốc API phân trang danh sách giao dịch

Endpoint GET /accounts/{id}/transactions?page=N ban đầu viết như sau:

-- Bản gốc: chậm dần theo trang, kéo hết cột
SELECT * FROM transactions
WHERE account_id = 1
ORDER BY created_at DESC
LIMIT 20 OFFSET :page * 20;

Người dùng cuộn sâu (trang 500+) và API bắt đầu timeout. Ba đòn tối ưu, theo đúng thứ tự tác động:

1. Bỏ SELECT * → chỉ lấy id, amount, kind, created_at. Giảm I/O ngay lập tức và mở đường cho index-only scan.
2. Chuyển sang keyset → API đổi từ ?page=N sang ?after=<created_at,id>; câu SQL thành WHERE account_id = 1 AND (created_at, id) < (:last_ts, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20. Chi phí không còn phụ thuộc độ sâu trang.
3. Index hỗ trợ → tạo composite index (account_id, created_at DESC, id DESC) (xem Chỉ mục) để cả điều kiện lọc lẫn thứ tự đều được index phục vụ.

Kết quả điển hình ở bảng hàng chục triệu dòng: trang sâu từ vài giây xuống vài mili-giây, và quan trọng hơn — thời gian ổn định dù người dùng ở trang 1 hay trang 5000. Sau đó dùng Giám sát với pg_stat_statements để xác nhận query này rời khỏi top truy vấn chậm.

Ghi nhớ

• Sargable là gốc rễ: để cột indexed đứng trần một vế. Tránh date(col)=, lower(col)= (trừ khi có expression index), phép tính trên cột, và leading wildcard LIKE '%x'.
• Ép kiểu ngầm phá index: luôn so sánh đúng kiểu (text với text, số với số).
• NOT EXISTS thay NOT IN khi làm việc với subquery — tránh bẫy NULL trả sai kết quả âm thầm.
• EXISTS cho câu hỏi "có tồn tại không"; JOIN khi cần lấy cột; cẩn thận JOIN nhân dòng buộc phải DISTINCT.
• CTE Postgres 12+ có thể inline; dùng MATERIALIZED/NOT MATERIALIZED khi cần ép hành vi. LATERAL cho "top-N mỗi nhóm".
• Keyset pagination thay OFFSET lớn — chi phí O(limit) thay vì O(offset). Thêm tie-breaker khi cột sắp không duy nhất.
• Không SELECT * — chỉ lấy cột cần để giảm I/O và mở đường index-only scan.
• Diệt N+1 bằng JOIN/IN hoặc eager loading của ORM.
• DISTINCT/GROUP BY tốn kém; window function thay self-join; đừng sort khi không cần.
• Luôn kiểm chứng bằng EXPLAIN — đừng đoán, hãy đọc kế hoạch thực thi.