SARGability — Index-friendly SQL

SARGable predicate: vì sao DATE(created_at) = ... giết index và bảng 50M row phải Seq Scan

Một predicate được gọi là SARGable (Search ARGument Able) khi planner có thể ánh xạ nó thẳng xuống cấu trúc index — cụ thể với B-Tree là ánh xạ vào key của leaf entry để đi tree hoặc scan range trên sibling. Khi cột bị bọc trong function hay bị ép kiểu (cast) ở vế trái của WHERE, giá trị mà planner cần so khớp không còn là giá trị lưu trong index nữa; nó phải tính function cho từng row, tức là Seq Scan. Đây là dạng "index có sẵn nhưng không dùng" gặp thường xuyên nhất trong production Postgres, và cũng là loại chậm khó nhìn nhất trong code review vì query "trông có filter". Việc sửa thường không phải thêm index — mà là viết lại predicate.

Cơ chế hoạt động

Postgres B-Tree lưu leaf entry theo giá trị nguyên bản của cột (đúng hơn: theo output của opclass đã dùng lúc CREATE INDEX). Planner nhìn WHERE clause, cố match từng predicate về dạng indexed_column OP constant để tạo Index Cond; dạng nào không match sẽ rớt xuống Filter sau khi row đã được đọc. Với WHERE created_at >= $1 AND created_at < $2, planner có thể đi thẳng vào leaf tại $1 rồi quét ngang tới $2. Với WHERE DATE(created_at) = $1, nó không có cách nào biết DATE(created_at) bằng gì nếu không đọc từng row và tính — vì index lưu timestamptz, không lưu date của nó.

-- Bảng events 50M row, có index (btree) trên created_at
CREATE INDEX events_created_at_idx ON events (created_at);

-- SARGable: predicate là range trên chính cột đã index
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events
WHERE created_at >= '2026-06-01'
  AND created_at <  '2026-06-02';
-- Index Scan using events_created_at_idx  (rows=... loops=1)
--   Index Cond: ((created_at >= ...) AND (created_at < ...))

-- Non-SARGable: cột bị bọc bởi function
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events
WHERE DATE(created_at) = '2026-06-01';
-- Seq Scan on events  (rows=... loops=1)
--   Filter: (date(created_at) = '2026-06-01'::date)
--   Rows Removed by Filter: 49_xxx_xxx

Hai điều kiện logic tương đương, plan khác nhau hoàn toàn. Postgres có hỗ trợ expression index (CREATE INDEX ON events (DATE(created_at))) để lưu chính giá trị của biểu thức và match lại được predicate — theo Postgres docs mục "Indexes on Expressions". Nhưng expression index chỉ khớp khi biểu thức trong WHERE và biểu thức trong CREATE INDEX giống hệt nhau về chuẩn hoá (cùng function, cùng cast, cùng collation). Đổi từ DATE(created_at) sang created_at::date vẫn có thể miss index nếu chưa index đúng biểu thức đó — planner không "hiểu ngữ nghĩa", nó match theo cây biểu thức đã được canonicalize.

Nguyên tắc rút ra: cột được index phải xuất hiện trần trụi ở vế trái của phép so sánh; mọi biến đổi phải đẩy sang vế phải (constant, hoặc tham số). Đó là định nghĩa thực dụng của SARGable trong Postgres.

Vấn đề gặp trong production

Failure mode 1: DATE()/date_trunc() bọc cột timestamp. Đây là mẫu phổ biến nhất từ code báo cáo và filter theo ngày.

-- Sai: bọc cột → không dùng được index trên created_at
SELECT count(*) FROM orders
WHERE DATE(created_at) = '2026-06-01';

-- Đúng: viết lại thành range half-open, giữ cột trần
SELECT count(*) FROM orders
WHERE created_at >= '2026-06-01'
  AND created_at <  '2026-06-02';

Cách viết range còn xử lý đúng cả timezone và tiết kiệm không dùng function per-row. Nếu thực sự cần query theo ngày và không sửa được app, tạo expression index: CREATE INDEX ON orders ((created_at AT TIME ZONE 'UTC')::date) — nhưng phải khớp hệt biểu thức trong WHERE.

Failure mode 2: LOWER(email) cho case-insensitive lookup. Rất hay xuất hiện ở table users với query login.

-- Sai: LOWER() ở vế trái → Seq Scan trên bảng users triệu row
SELECT id FROM users WHERE LOWER(email) = LOWER($1);

Hai lựa chọn:

-- Cách 1: expression index (giữ nguyên query app)
CREATE INDEX users_email_lower_idx ON users (LOWER(email));

-- Cách 2: đổi kiểu cột sang citext (case-insensitive text)
ALTER TABLE users ALTER COLUMN email TYPE citext;
CREATE INDEX users_email_idx ON users (email);
-- Query giữ nguyên WHERE email = $1, so sánh tự động case-insensitive

Chọn citext nếu app luôn muốn ngữ nghĩa case-insensitive; chọn expression index nếu chỉ vài query cần.

Failure mode 3: implicit cast ở vế trái. Cột kiểu bigint bị so sánh với text, hoặc ngược lại, khiến Postgres phải cast cột.

-- Sai: cast tường minh cột sang text để so string
SELECT * FROM orders WHERE user_id::text = '12345';

Postgres đôi khi cast constant sang kiểu cột (SARGable giữ nguyên), nhưng nếu vế trái có cast tường minh thì index không match. Bắt lỗi này thường phải nhìn EXPLAIN cho ra Filter: ((user_id)::text = '12345'::text) — dấu ngoặc quanh (user_id)::text là chỉ báo cột đã bị cast.

Failure mode 4: số học trên cột. WHERE price * 1.1 > 100 hay WHERE created_at + interval '30 days' < now() đều đẩy tính toán vào từng row.

-- Sai: cộng interval vào cột
SELECT * FROM subscriptions
WHERE created_at + interval '30 days' < now();

-- Đúng: chuyển sang vế phải
SELECT * FROM subscriptions
WHERE created_at < now() - interval '30 days';

Vế phải now() - interval '30 days' được đánh giá một lần thành constant tại thời điểm plan, index trên created_at dùng được ngay.

Failure mode 5: LIKE '%foo%' và leading wildcard. B-Tree phục vụ prefix LIKE 'foo%' (với opclass phù hợp) nhưng không phục vụ leading wildcard '%foo' hay '%foo%' — không có prefix để đi tree.

-- Sai: leading wildcard, index trên name không giúp
SELECT id FROM products WHERE name LIKE '%widget%';

-- Đúng cho substring/fuzzy: GIN + pg_trgm
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm_idx ON products USING GIN (name gin_trgm_ops);
SELECT id FROM products WHERE name ILIKE '%widget%';

Failure mode 6: OR bắc cầu qua nhiều cột. WHERE user_id = $1 OR email = $2 khiến planner khó dùng chung một index; nó có thể chọn BitmapOr nếu có index trên cả hai cột, nhưng nếu chỉ có index trên user_id thì cả câu Seq Scan. Cách sửa gọn nhất: viết lại thành UNION ALL hai câu, mỗi câu là SARGable đơn lẻ.

Cách debug và monitor

Triệu chứng: một query "trông đơn giản" chậm bất thường khi bảng lớn dần; latency P99 tăng tuyến tính theo số row bảng chứ không theo số row trả về; CPU của Postgres cao mà buffer hit rate vẫn tốt (vì đang scan sequential mà data đã trong cache).

Công cụ để dò:

• EXPLAIN (ANALYZE, BUFFERS) — dấu hiệu cần bắt: Seq Scan kèm Filter: (some_func(col) = ...), hoặc Rows Removed by Filter lớn hơn nhiều lần rows cuối. So predicate xuất hiện ở Index Cond: (tốt) hay Filter: (rơi ra sau khi đọc row) — predicate rơi vào Filter là dấu hiệu không SARGable.
• pg_stat_user_indexes — cột idx_scan. Nếu một index được kỳ vọng dùng cho query nóng mà idx_scan gần bằng 0 sau khi workload đã chạy, khả năng cao query bên app đang viết non-SARGable. Đối chiếu với pg_stat_user_tables.seq_scan của bảng đó: seq_scan cao + idx_scan thấp trên cùng bảng là chỉ báo mạnh.
• pg_stat_statements — sort theo total_exec_time hoặc mean_exec_time. Query top thường lộ ra DATE(), LOWER(), ::text bọc cột. Đây là input chính để refactor.
• auto_explain — bật auto_explain.log_min_duration để log plan của query chậm vào Postgres log; giúp bắt câu ad-hoc mà pg_stat_statements chỉ giữ dạng normalized.

Rule phòng ngừa ở tầng code:

• Code review bắt mọi WHERE clause có function/cast ở vế trái. Trong Python/Node ORM, cẩn thận helper kiểu Order.filter(created_at__date=today) — nó thường generate ra DATE(created_at) = .... Ưu tiên helper dạng __range hoặc __gte/__lt.
• Với cột case-insensitive, quyết định một lần ở schema: citext hoặc expression index — đừng để nửa app dùng LOWER() nửa app dùng citext.
• Với cột timestamptz, thống nhất chuẩn hoá về UTC ở app, filter luôn dạng range half-open [start, end).

Tradeoff

Hai hướng xử lý non-SARGable: viết lại SQL hoặc thêm expression index. Viết lại SQL rẻ hơn về mọi mặt (không tốn storage, không cost write, không cần deploy migration), nhưng đòi hỏi sửa code app hoặc ORM, và mọi call site phải đồng bộ. Expression index giữ nguyên query nhưng thêm một index thật vào bảng — mỗi INSERT/UPDATE trên cột base phải update index này; nếu expression phức tạp (function chậm, immutable-only), cost write tăng đáng kể. Ngoài ra expression index chỉ match khi biểu thức trong WHERE giống hệt biểu thức lúc CREATE INDEX (sau canonicalize) — dễ bị "index có mà không dùng" nếu app đổi cách viết. Với cột updated_at/created_at write-heavy, viết lại SQL luôn thắng expression index. Với legacy code không sửa được và một biểu thức ổn định (LOWER(email)), expression index là lựa chọn đúng. Quy tắc thực tế: mặc định đổi SQL cho SARGable; chỉ thêm expression index khi không thể sửa call site và biểu thức là stable + immutable.

Câu hỏi phỏng vấn

Query SELECT * FROM orders WHERE DATE(created_at) = '2026-06-01' chạy chậm dù đã có index btree(created_at). Vì sao, và có mấy cách sửa?

B-Tree lưu leaf entry theo giá trị timestamptz nguyên bản của created_at; planner match predicate về dạng col OP const để tạo Index Cond. Ở đây vế trái là DATE(created_at) — một biểu thức trên cột, không phải cột trần — nên không có Index Cond khớp được, planner rơi xuống Seq Scan với DATE(created_at) = ... nằm trong Filter, tức là đọc hết bảng rồi mới lọc. Đây là non-SARGable predicate: cột bị bọc function ở vế trái. Có hai cách sửa. Cách một, viết lại thành range half-open created_at >= '2026-06-01' AND created_at < '2026-06-02'; predicate lúc này SARGable, dùng Index Scan trên index sẵn có, cost giảm từ O(N) xuống O(log N + k). Cách hai, tạo expression index CREATE INDEX ON orders (DATE(created_at)); khi ấy leaf lưu chính giá trị date, predicate cũ match được — đổi lại phải trả cost write cho index mới và biểu thức trong WHERE phải khớp hệt biểu thức khi tạo index. Điểm ăn điểm là nêu được cả ba: cơ chế (index lưu giá trị cột nguyên bản, function ở vế trái phá match), hậu quả production (Seq Scan bảng lớn → P99 tăng theo size chứ không theo kết quả), và trade-off giữa viết lại SQL (rẻ, ưu tiên) và expression index (giữ nguyên app, đắt hơn write) — mặc định chọn cách một.

Hands-on

Mục tiêu: dựng một bảng đủ lớn, tái hiện non-SARGable → Seq Scan, đo EXPLAIN, refactor sang SARGable, so sánh, rồi thử luôn expression index để thấy trade-off.

docker run -d --name pg-sarg -e POSTGRES_PASSWORD=pw -p 5433:5432 postgres:16
sleep 5

docker exec -i pg-sarg psql -U postgres <<'SQL'
CREATE TABLE events (
  id          bigserial PRIMARY KEY,
  user_id     bigint      NOT NULL,
  email       text        NOT NULL,
  created_at  timestamptz NOT NULL
);

INSERT INTO events (user_id, email, created_at)
SELECT (random()*1000000)::bigint,
       'user' || (random()*1000000)::bigint || '@Example.COM',
       now() - (random() * interval '365 days')
FROM generate_series(1, 5000000);

CREATE INDEX events_created_at_idx ON events (created_at);
CREATE INDEX events_email_idx      ON events (email);
ANALYZE events;
SQL

Chạy phiên bản non-SARGable và ghi plan:

docker exec -i pg-sarg psql -U postgres -c "
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE DATE(created_at) = (now() - interval '10 days')::date;"

Kỳ vọng: Seq Scan on events, Filter: (date(created_at) = ...), Rows Removed by Filter gần bằng row count bảng, thời gian hàng trăm ms tới vài giây.

Refactor sang SARGable:

docker exec -i pg-sarg psql -U postgres -c "
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE created_at >= (now() - interval '10 days')::date
  AND created_at <  (now() - interval '9 days')::date;"

Kỳ vọng: Index Scan (hoặc Bitmap Index Scan) trên events_created_at_idx, Index Cond chứa hai vế range, thời gian sub-millisecond tới vài ms.

Thử case LOWER(email):

docker exec -i pg-sarg psql -U postgres -c "
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events WHERE LOWER(email) = '[e-mail]';"

Seq Scan như dự đoán. Thêm expression index và rerun:

docker exec -i pg-sarg psql -U postgres -c "
CREATE INDEX events_email_lower_idx ON events (LOWER(email));
ANALYZE events;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events WHERE LOWER(email) = '[e-mail]';"

Plan chuyển sang Index Scan using events_email_lower_idx. Kiểm chứng cost write: đo TPS insert trước và sau khi có expression index:

cat > /tmp/insert.sql <<'EOF'
INSERT INTO events (user_id, email, created_at)
VALUES ((random()*1000000)::bigint,
        'user' || (random()*1000000)::bigint || '@Example.COM',
        now());
EOF
docker cp /tmp/insert.sql pg-sarg:/tmp/insert.sql

docker exec -i pg-sarg psql -U postgres -c "DROP INDEX events_email_lower_idx;"
docker exec pg-sarg pgbench -U postgres -n -f /tmp/insert.sql -c 8 -j 2 -T 20 postgres

docker exec -i pg-sarg psql -U postgres -c "CREATE INDEX events_email_lower_idx ON events (LOWER(email));"
docker exec pg-sarg pgbench -U postgres -n -f /tmp/insert.sql -c 8 -j 2 -T 20 postgres

TPS lần hai sẽ thấp hơn lần đầu — đây là chi phí thật của expression index, đối trọng để cân nhắc so với việc refactor query.

Kiểm tra index usage tổng thể:

docker exec -i pg-sarg psql -U postgres -c "
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;"

Index nào idx_scan = 0 dù có query kỳ vọng dùng nó → soi lại call site xem WHERE có bọc cột không. Dọn:

docker rm -f pg-sarg