5 anti-pattern khiến planner chọn plan tệ
Function on indexed column, implicit cast type mismatch, OR predicate, leading wildcard LIKE, và parameter sniffing — 5 anti-pattern gây slow query trong production. Nhận biết và fix từng cái với EXPLAIN before/after.
Query chạy chậm. Bạn chạy EXPLAIN ANALYZE — thấy Seq Scan, thấy cost cao, thấy actual time vượt SLA. Nhưng tại sao planner chọn Seq Scan dù đã có index? Index tồn tại — planner vẫn không dùng. Hoặc dùng sai.
Có 5 anti-pattern trong cách viết query khiến planner không thể tận dụng index dù index đúng đã có. Biết 5 cái này tránh được ~80% slow query trong production. Bài này đi qua từng pattern: nhận diện — EXPLAIN before — fix — EXPLAIN after.
1. Analogy — Sai bài thi đa số học sinh đều mắc
Giáo viên chấm hàng nghìn bài thi hàng năm. Sau nhiều năm, nhận ra 5 lỗi sai quay đi quay lại nhiều nhất — cộng lại chiếm ~80% điểm mất. Học sinh giỏi không nhất thiết biết nhiều hơn — họ chỉ biết 5 lỗi đó và tránh được.
Anti-pattern trong query optimization cũng vậy:
| Lỗi bài thi | Anti-pattern query |
|---|---|
| Viết nhầm công thức vì nhớ sai form | Function on indexed column — wrap index trong hàm |
| Đổi đơn vị sai chiều (kg → lb nhưng kết quả để là kg) | Implicit cast — type mismatch giữa column và literal |
| Dùng "hoặc" trong câu trả lời tự luận — giám khảo chấm nửa điểm | OR predicate — planner không thể combine thành 1 index range |
| Ghi tên từng phần từ cuối lên đầu khi điền form | Leading wildcard LIKE — B-tree chỉ đọc được từ đầu |
| Học thuộc bài mẫu nhưng đề thi dùng số khác hẳn | Parameter sniffing — generic plan tệ với giá trị bất thường |
| Cứ gặp bài khó là tô bừa, không đọc đề | Blind tuning — thêm index mà không EXPLAIN trước |
Biết tên anti-pattern = biết lỗi ở đâu. Biết cách phát hiện = tự chấm được bài mình viết. 5 pattern này không cần học thuộc — cần biết đủ để nhận ra khi đọc EXPLAIN output.
2. Pattern 1 — Function on indexed column
Triệu chứng: Index tồn tại trên column, nhưng query wrap column trong hàm — planner không thể match index vì index lưu raw value, không phải kết quả hàm.
-- Co index tren email (raw value)
CREATE INDEX idx_users_email ON users(email);
-- BAD: LOWER() wrap column -> index khong apply
EXPLAIN ANALYZE SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Seq Scan on users (cost=0.00..2500.00 rows=50 width=100)
-- (actual time=0.025..18.340 rows=1 loops=1)
-- Filter: (lower(email) = '[email protected]'::text)
-- Rows Removed by Filter: 49999
-- Planning Time: 0.085 ms
-- Execution Time: 18.380 ms
Planner thấy LOWER(email) — một expression trên column, không phải column trực tiếp. Index idx_users_email lưu raw email values, không lưu LOWER(email). Không có cách nào dùng index này để tìm LOWER(email) = '[email protected]' mà không scan toàn bộ.
Fix 1 — Expression index:
-- Tao index tren chinh expression do
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Gio query dung duoc index moi
EXPLAIN ANALYZE SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Index Scan using idx_users_email_lower on users
-- (cost=0.42..8.44 rows=1 width=100)
-- (actual time=0.028..0.031 rows=1 loops=1)
-- Index Cond: (lower(email) = '[email protected]'::text)
-- Planning Time: 0.092 ms
-- Execution Time: 0.055 ms
Execution time: 18.380 ms → 0.055 ms. Expression index lưu LOWER(email) đã tính sẵn — planner match trực tiếp.
Fix 2 — App cast (normalize tại INSERT):
-- Store email lowercase ngay khi insert
INSERT INTO users (email, ...) VALUES (lower($1), ...);
-- Sau do query dung column truc tiep, dung idx_users_email goc
SELECT * FROM users WHERE email = '[email protected]'; -- khong can LOWER()
Fix 2 đơn giản hơn về mặt schema — không tạo thêm index. Tradeoff: cần đảm bảo mọi INSERT/UPDATE đều normalize. Với hệ thống mới: dùng Fix 2. Với hệ thống cũ có data hỗn hợp: dùng Fix 1.
Pattern tương tự cần tránh:
-- Cac function khac cung gay ra cung van de
WHERE DATE_TRUNC('day', created_at) = '2026-01-15' -- dung range thay the:
-- Fix: WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16'
WHERE UPPER(country_code) = 'VN' -- expression index hoac normalize data
WHERE EXTRACT(year FROM created_at) = 2026 -- range thay the
-- Fix: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
3. Pattern 2 — Implicit cast type mismatch
Triệu chứng: Column và literal value có type khác nhau — PostgreSQL tự động cast một trong hai, đôi khi cast phía column → phá vỡ khả năng dùng index.
Case OK — PG cast literal sang column type:
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id); -- BIGINT
-- String literal '5' -> PG cast sang BIGINT -> index van dung duoc
EXPLAIN ANALYZE SELECT * FROM tasks WHERE assignee_id = '5';
-- Index Scan using idx_tasks_assignee on tasks
-- (cost=0.42..8.44 rows=2 width=120)
-- (actual time=0.018..0.022 rows=2 loops=1)
-- Index Cond: (assignee_id = '5'::bigint)
PostgreSQL cast '5'::text → bigint — cast xảy ra một lần trên literal, không trên column. Index vẫn apply.
Case BAD — PG cast column sang type của literal:
CREATE INDEX idx_users_phone ON users(phone); -- VARCHAR(20)
-- Numeric literal 904123456 khong co quote -> PG phai cast column sang numeric
EXPLAIN ANALYZE SELECT * FROM users WHERE phone = 904123456;
-- Seq Scan on users (cost=0.00..1250.00 rows=25 width=80)
-- (actual time=0.020..9.150 rows=1 loops=1)
-- Filter: ((phone)::numeric = '904123456'::numeric)
-- Rows Removed by Filter: 24999
-- Planning Time: 0.072 ms
-- Execution Time: 9.185 ms
(phone)::numeric là function trên column — giống Pattern 1. Planner không thể dùng idx_users_phone (lưu raw varchar) để tìm kết quả sau cast.
Fix — Quote literal đúng type:
-- Dung string literal co quote
EXPLAIN ANALYZE SELECT * FROM users WHERE phone = '904123456';
-- Index Scan using idx_users_phone on users
-- (cost=0.42..8.44 rows=1 width=80)
-- (actual time=0.019..0.022 rows=1 loops=1)
-- Index Cond: (phone = '904123456'::character varying)
-- Planning Time: 0.068 ms
-- Execution Time: 0.045 ms
Với ORM/JDBC — set parameter type explicit:
// Bad (Java JDBC): PreparedStatement.setObject(1, "904123456")
// -> driver co the bind sai type
// Good: set type explicit
PreparedStatement.setString(1, "904123456"); // VARCHAR column
PreparedStatement.setLong(1, 5L); // BIGINT column
Type mismatch hay xảy ra nhất khi ORM auto-detect parameter type từ Java/Python type — int trong code → integer literal trong SQL → mismatch với VARCHAR column.
4. Pattern 3 — OR predicate không dùng được composite index
Triệu chứng: WHERE a = 1 OR b = 2 — composite index (a, b) không apply cho OR, và planner phải chọn giữa Bitmap OR hoặc Seq Scan.
CREATE INDEX idx_tasks_proj_status ON tasks(project_id, status);
-- BAD: OR -> composite index khong apply
EXPLAIN ANALYZE
SELECT * FROM tasks WHERE project_id = 1 OR status = 'urgent';
-- Bitmap Heap Scan on tasks (cost=312.50..845.20 rows=1250 width=120)
-- (actual time=2.450..8.320 rows=1180 loops=1)
-- Recheck Cond: ((project_id = 1) OR (status = 'urgent'::text))
-- -> BitmapOr (cost=312.50..312.50 rows=1250 width=0)
-- -> Bitmap Index Scan on idx_tasks_proj_status
-- Index Cond: (project_id = 1)
-- -> Seq Scan on tasks -- khong co index rieng tren status!
-- Filter: (status = 'urgent'::text)
-- Planning Time: 0.180 ms
-- Execution Time: 8.520 ms
Planner cố dùng Bitmap OR: combine kết quả bitmap của 2 branch. Branch project_id = 1 dùng được index, nhưng branch status = 'urgent' không có index đơn riêng → Seq Scan. Tổng thời gian tệ.
Nếu selectivity cao (>10% bảng) — Seq Scan toàn bộ:
-- Neu project_id = 1 chiem 30% bảng -> Seq Scan toan bo rẻ hon
-- Seq Scan on tasks (cost=0.00..3125.00 rows=25000 width=120)
-- Filter: ((project_id = 1) OR (status = 'urgent'::text))
Fix — Rewrite thành UNION ALL:
-- Moi nhanh dung index rieng, khong co duplicate
EXPLAIN ANALYZE
SELECT * FROM tasks WHERE project_id = 1
UNION ALL
SELECT * FROM tasks WHERE status = 'urgent' AND project_id != 1;
-- Append (cost=0.42..42.50 rows=320 width=120)
-- (actual time=0.025..1.840 rows=298 loops=1)
-- -> Index Scan using idx_tasks_proj_status on tasks
-- (cost=0.42..18.44 rows=180 width=120)
-- (actual time=0.025..0.820 rows=175 loops=1)
-- Index Cond: (project_id = 1)
-- -> Index Scan using idx_tasks_status on tasks
-- (cost=0.42..24.06 rows=140 width=120)
-- (actual time=0.018..0.650 rows=123 loops=1)
-- Index Cond: (status = 'urgent'::text)
-- Filter: (project_id <> 1)
-- Planning Time: 0.210 ms
-- Execution Time: 1.920 ms
Mệnh đề project_id != 1 trong branch thứ hai loại duplicate — row match cả project_id = 1 lẫn status = 'urgent' chỉ xuất hiện trong branch đầu. Nếu duplicate OK (ứng dụng xử lý được): bỏ điều kiện đó và dùng UNION ALL thuần túy.
Lưu ý về index cho fix này: Fix hiệu quả nhất khi có index đơn trên từng column cần (project_id và status riêng lẻ), không chỉ composite. Bài 6 của module này đề cập thêm chiến lược index cho OR workload.
5. Pattern 4 — Leading wildcard LIKE '%foo'
Triệu chứng: LIKE '%keyword' hoặc LIKE '%keyword%' — prefix không xác định, B-tree index không thể dùng vì không biết bắt đầu từ đâu trong sorted order.
CREATE INDEX idx_users_email ON users(email);
-- BAD: leading wildcard -> B-tree mo (sorted order) khong help
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Seq Scan on users (cost=0.00..1875.00 rows=250 width=100)
-- (actual time=0.018..14.520 rows=8420 loops=1)
-- Filter: ((email)::text ~~ '%@gmail.com'::text)
-- Rows Removed by Filter: 41580
-- Planning Time: 0.065 ms
-- Execution Time: 14.880 ms
B-tree index trên email lưu values theo alphabetical order. LIKE '%@gmail.com' yêu cầu tìm mọi string kết thúc bằng @gmail.com — không có cách nào binary search trên B-tree mà không scan toàn bộ.
Fix 1 — GIN trigram index (bài 4 Module 5 của khoá này):
-- Kich hoat extension pg_trgm
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- GIN index tren trigram cua email
CREATE INDEX idx_users_email_trgm ON users USING gin(email gin_trgm_ops);
-- Leading wildcard gio dung duoc GIN index
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Bitmap Heap Scan on users (cost=84.50..320.80 rows=250 width=100)
-- (actual time=0.380..1.240 rows=8420 loops=1)
-- Recheck Cond: ((email)::text ~~ '%@gmail.com'::text)
-- -> Bitmap Index Scan on idx_users_email_trgm
-- (cost=0.00..84.44 rows=250 width=0)
-- (actual time=0.290..0.290 rows=8420 loops=1)
-- Index Cond: ((email)::text ~~ '%@gmail.com'::text)
-- Planning Time: 0.145 ms
-- Execution Time: 1.580 ms
GIN trigram chia string thành các trigram (3-character substring) và index tất cả. '%@gmail.com' có trigrams như @gm, gma, mai, ail, il., l.c, .co, com — GIN tìm intersection của tất cả trigrams → filter ứng viên nhanh mà không scan toàn bộ.
Fix 2 — Reverse column + reverse pattern (cho suffix search cụ thể):
-- Index tren reversed email
CREATE INDEX idx_users_email_rev ON users(reverse(email));
-- Reverse pattern: '%@gmail.com' -> 'moc.liamg@%' (prefix anchored)
EXPLAIN ANALYZE SELECT * FROM users WHERE reverse(email) LIKE reverse('%@gmail.com');
-- = WHERE reverse(email) LIKE 'moc.liamg@%'
-- Index Scan using idx_users_email_rev on users
-- (cost=0.42..280.50 rows=250 width=100)
-- (actual time=0.025..0.920 rows=8420 loops=1)
-- Index Cond: (reverse(email) ~~ 'moc.liamg@%'::text)
-- Planning Time: 0.088 ms
-- Execution Time: 1.100 ms
reverse('%@gmail.com') = 'moc.liamg@%' — bây giờ là prefix-anchored (moc.liamg@ là prefix cố định), B-tree có thể binary search. Fix 2 nhanh hơn Fix 1 nhưng chỉ áp dụng cho suffix search cụ thể, không dùng được cho contains search (LIKE '%keyword%').
Chọn fix nào:
- Contains search (
LIKE '%foo%') hoặc iLIKE: dùngpg_trgmGIN. - Suffix search thuần túy (
LIKE '%suffix'): reverse index nhanh hơn và nhẹ hơn. - Full-text search phức tạp hơn: cân nhắc
tsvector(bài 4 Module 5 của khoá này).
6. Pattern 5 — Parameter sniffing: prepared statement generic plan
Triệu chứng: Prepared statement chạy tốt với giá trị phổ biến nhưng chậm với giá trị hiếm — planner đã cache generic plan không phù hợp.
Cơ chế PostgreSQL prepared statement:
-- Status distribution: todo 70%, doing 20%, done 8%, archived 2%
PREPARE q(text) AS SELECT * FROM tasks WHERE status = $1;
-- Lan 1-5: custom plan (PG re-plan moi lan voi actual param value)
EXPLAIN EXECUTE q('todo');
-- Seq Scan on tasks (cost=0.00..3125.00 rows=17500 width=120)
-- Filter: ((status)::text = 'todo'::text)
-- (70% selectivity -> Seq Scan dung)
EXPLAIN EXECUTE q('archived');
-- Index Scan using idx_tasks_status on tasks
-- (cost=0.42..280.50 rows=500 width=120)
-- Index Cond: (status = 'archived'::text)
-- (2% selectivity -> Index Scan dung)
5 lần đầu: PostgreSQL re-plan mỗi lần với actual param value — mỗi call chọn plan tối ưu cho giá trị đó.
-- Lan thu 6+: PG so sanh cost custom vs generic plan
-- Generic plan dung average selectivity (khong biet param value cu the)
-- Neu generic plan khong qua dat -> switch sang cache generic plan
EXPLAIN EXECUTE q('archived'); -- lan thu 6, 7, 8...
-- Seq Scan on tasks (cost=0.00..3125.00 rows=12500 width=120)
-- Filter: ((status)::text = $1)
-- Generic plan dung average selectivity ~25% -> chon Seq Scan
-- Nhung 'archived' chi 2% -> Index Scan se nhanh hon 10x
-- SLOW!
Generic plan dùng estimated average selectivity — không biết actual value là gì. Với status = 'archived' (2% selectivity), generic plan vẫn dùng Seq Scan vì average cao hơn ngưỡng index benefit.
Phát hiện — nhận ra generic plan trong EXPLAIN:
-- Generic plan co $1 thay vi actual value trong Index Cond / Filter
EXPLAIN EXECUTE q('archived');
-- Filter: ((status)::text = $1) <- day la dau hieu generic plan
-- Custom plan co actual value
EXPLAIN EXECUTE q('archived');
-- Index Cond: (status = 'archived'::text) <- actual value, custom plan
Fix 1 — Force custom plan cho session hoặc transaction:
-- Cho session cu the (e.g. dashboard endpoint):
SET plan_cache_mode = force_custom_plan;
-- Tat ca prepared statement trong session nay dung custom plan
-- Hoac: reset ve auto (default PG 12+):
SET plan_cache_mode = auto;
-- 'auto': PG tu quyet dinh custom vs generic theo cost comparison
Fix 2 — DEALLOCATE và PREPARE lại:
-- Reset plan cache cho prepared statement
DEALLOCATE q;
PREPARE q(text) AS SELECT * FROM tasks WHERE status = $1;
-- Lap lai 5-lan-custom-plan cycle
Fix 2 phù hợp với connection pooling khi cần periodic reset mà không thay đổi server config.
Fix 3 — Bypass prepared statement (tradeoff):
-- Dung literal truc tiep, khong qua prepared statement
SELECT * FROM tasks WHERE status = 'archived';
-- Planner luon re-plan voi actual value -> chon plan dung
-- Tradeoff: mat loi ich planning time cache cua prepared statement
-- (~0.1–5 ms planning overhead cho moi query)
Fix 3 phù hợp với query critical có skewed distribution rõ ràng, nơi planning overhead chấp nhận được so với execution time sai vì generic plan.
Bảng so sánh 3 fix:
| Fix | Overhead | Phù hợp khi |
|---|---|---|
force_custom_plan | Re-plan mỗi query (~0.1–5 ms) | Session/endpoint cụ thể có skewed param |
DEALLOCATE + PREPARE | Re-plan 5 lần đầu | Connection pool reset theo định kỳ |
| Literal (bypass prepared) | Re-plan mỗi query | Query ít gọi, planning overhead chấp nhận được |
7. Pitfall — blind tuning không EXPLAIN trước
Workflow đúng:
1. Reproduce slow query trong dev/staging với data representative.
2. EXPLAIN (ANALYZE, BUFFERS) -> baseline: plan, actual time, I/O.
3. Xác định bottleneck node: actual time cao nhất, rows estimate sai xa,
BUFFERS read cao (disk I/O).
4. Đặt hypothesis: function on column? type mismatch? OR? wildcard? param sniffing?
5. Apply 1 fix, re-run EXPLAIN, so sánh actual time trước/sau.
6. Lặp step 4-5 cho vấn đề tiếp theo nếu còn.
Anti-workflow — những gì không nên làm:
- "Add index" mù mắt: bloat write workload, không giải quyết root cause
nếu vấn đề là function wrap hoặc type mismatch.
- "Increase work_mem": giảm OLTP throughput (memory share giữa sessions),
không fix được OR predicate hay leading wildcard.
- "VACUUM FULL": gây downtime (full table lock), không fix được plan issue.
- "SET enable_seqscan = off": force Index Scan bất kể cost
-> có thể chậm hơn nếu thực sự cần Seq Scan (high selectivity).
Baseline EXPLAIN là bằng chứng — không phải đoán. Mọi fix cần được verify bằng EXPLAIN after. Nếu actual time không giảm, fix sai target.
8. Applied — dashboard query M02.7 audit 5 pattern
Query dashboard từ bài 7 Module 2 của khoá này. Kiểm tra lần lượt 5 pattern trước khi optimize bất kỳ thứ gì:
-- Original dashboard query (bai 7 Module 2 cua khoa nay)
SELECT status, count(*) FROM tasks
WHERE assignee_id = $1 AND status != 'archived'
GROUP BY status;
Audit từng pattern:
-- (1) Function on indexed column?
-- assignee_id: dung truc tiep, KHONG co function wrap -> OK
-- status: dung truc tiep, KHONG co function wrap -> OK
-- (2) Implicit cast type mismatch?
-- assignee_id BIGINT, $1 bind tu ORM -> kiem tra ORM bind type la BIGINT
-- Neu dung JDBC: PreparedStatement.setLong(1, userId) -> OK
-- Neu bind nham string: setString(1, "123") -> co the bi cast issue
-- Kiem tra: EXPLAIN nen show "Index Cond: (assignee_id = $1)" khong co cast
-- (3) OR predicate?
-- KHONG co OR trong WHERE clause -> OK
-- (4) Leading wildcard LIKE?
-- KHONG co LIKE trong query -> OK
-- (5) Parameter sniffing?
-- CO su dung prepared statement ($1) + assignee_id co the phan bo skewed:
-- Mot so user co 5000 task (manager), phan lon user co <10 task
-- Generic plan co the chon Seq Scan vi average selectivity cao
-- -> check: sau 6+ executions, EXPLAIN EXECUTE show generic plan?
Verify pattern 5 cụ thể:
PREPARE dashboard_q(bigint) AS
SELECT status, count(*) FROM tasks
WHERE assignee_id = $1 AND status != 'archived'
GROUP BY status;
-- Chay 6 lan de trigger generic plan switch
DO $$
BEGIN
FOR i IN 1..6 LOOP
EXECUTE 'EXECUTE dashboard_q(5)';
END LOOP;
END $$;
-- Check plan hien tai
EXPLAIN EXECUTE dashboard_q(5);
-- Neu thay "Index Cond: (assignee_id = $1)" -> generic plan, co the bi sniff
-- Neu thay "Index Cond: (assignee_id = 5)" -> custom plan, OK
Fix nếu phát hiện generic plan tệ:
-- Set force_custom_plan cho dashboard endpoint session
SET plan_cache_mode = force_custom_plan;
-- Hoac tren application side: sau khi establish connection, chay:
-- SET plan_cache_mode = force_custom_plan;
-- Truoc khi execute dashboard query
Kết luận audit:
Pattern 1 (function on column): SACH
Pattern 2 (implicit cast): KIEM TRA ORM bind type
Pattern 3 (OR predicate): SACH
Pattern 4 (leading wildcard): SACH
Pattern 5 (param sniffing): KIEM TRA sau 5+ executions neu assignee skewed
9. Deep Dive
- PostgreSQL 14 Internals — Egor Rogov, Part IV (Query Optimization) — free PDF. Part IV giải thích cơ chế planner chọn plan theo cost, tại sao function wrap phá vỡ index lookup, và cách PostgreSQL handle expression index. Nền tảng lý thuyết cho cả 5 pattern.
- Use The Index, Luke — "The Where Clause" — Markus Winand. Giải thích tại sao B-tree index không thể handle leading wildcard, implicit cast, và function wrap — với diagram trực quan về B-tree structure. Đọc chương "Functions" và "Bind Parameters" trước.
- Use The Index, Luke — "Performance Aspects of Prepared Statements" — Markus Winand. Giải thích sâu về parameter sniffing, generic vs custom plan, và khi nào literal tốt hơn bind parameter. Thực tế và có ví dụ từ nhiều database.
10. Tóm tắt
- Pattern 1 — Function on indexed column:
WHERE LOWER(email) = 'x'không dùng index trênemail. Fix: expression indexCREATE INDEX ON users(LOWER(email))hoặc normalize data tại INSERT. - Pattern 2 — Implicit cast type mismatch:
WHERE varchar_col = 123(numeric literal) → PG cast column sang numeric → Seq Scan. Fix: quote literal đúng type ('123'); với ORM set parameter type explicit. - Pattern 3 — OR predicate:
WHERE a = 1 OR b = 2không dùng composite index(a, b). Fix: rewrite thànhUNION ALLhai query — mỗi nhánh dùng index riêng. - Pattern 4 — Leading wildcard LIKE:
LIKE '%suffix'không dùng được B-tree. Fix:pg_trgmGIN index cho contains/suffix search (bài 4 Module 5 của khoá này), hoặc reverse column + reverse pattern cho suffix search thuần túy. - Pattern 5 — Parameter sniffing: Sau 5 lần execute, prepared statement có thể switch sang generic plan với average selectivity — tệ cho param value hiếm. Fix:
SET plan_cache_mode = force_custom_planhoặcDEALLOCATE+PREPARElại. - Workflow bắt buộc:
EXPLAIN (ANALYZE, BUFFERS)trước khi fix bất kỳ thứ gì. Baseline plan là bằng chứng. So sánh actual time trước/sau mỗi fix. - Nhận biết generic plan: Filter/Index Cond có
$1thay vì actual value → generic plan đang được dùng. - Không blind tune: Thêm index mù, tăng
work_membừa, hoặcVACUUM FULLkhông giải quyết root cause 5 pattern này.
11. Tự kiểm tra
Q1Query `WHERE LOWER(username) = 'john_doe'` không dùng index trên `username`. Giải thích tại sao B-tree index trên `username` không apply, và viết 2 cách fix khác nhau với tradeoff của mỗi cách.▸
B-tree index trên username lưu raw value (e.g., 'John_Doe', 'ADMIN'). Khi query dùng LOWER(username), planner cần tra cứu theo kết quả hàm, không phải raw value. Index không lưu LOWER(username) — planner không thể binary search trên B-tree gốc cho expression này, buộc phải Seq Scan và apply hàm trên từng row.
Fix 1 — Expression index: CREATE INDEX ON users(LOWER(username)); rồi giữ nguyên query. Index lưu kết quả LOWER(username) đã tính sẵn — planner match trực tiếp. Tradeoff: thêm 1 index tốn disk space và write overhead; không cần thay đổi data hay application INSERT logic.
Fix 2 — Normalize tại INSERT/UPDATE: Store username lowercase ngay khi insert (INSERT INTO users(username) VALUES (lower($1))), rồi query dùng WHERE username = 'john_doe' không cần LOWER(). Tradeoff: phải đảm bảo tất cả INSERT/UPDATE đều normalize — rủi ro nếu có nhiều code path; không cần index thêm, dùng được index gốc.
Q2Bảng `products` có column `sku VARCHAR(50)` với index. Query `WHERE sku = 12345` (không có quote) chạy Seq Scan dù index tồn tại. Giải thích cơ chế PostgreSQL cast, và fix đúng cách.▸
PostgreSQL gặp comparison giữa VARCHAR column (sku) và integer literal (12345). Để so sánh được, PG cần convert về cùng type. Quy tắc: khi không rõ hướng cast, PG thường cast column sang type của literal. Kết quả: (sku)::numeric = 12345::numeric — hàm ::numeric wrap column, phá vỡ index lookup y hệt Pattern 1.
Fix: Quote literal: WHERE sku = '12345'. Bây giờ literal là text/varchar, PG cast literal sang varchar ('12345'::varchar) — cast xảy ra một lần trên literal, không trên column. Index idx_products_sku apply bình thường.
Với ORM: dùng setString(1, "12345") thay vì setInt(1, 12345) khi bind parameter cho VARCHAR column.
Q3Query `WHERE project_id = 5 OR status = 'urgent'` cần chạy nhanh nhất có thể. Index hiện có: composite `(project_id, status)` và single-column `(status)`. Rewrite query bằng UNION ALL và giải thích tại sao nhanh hơn.▸
Rewrite:
SELECT * FROM tasks WHERE project_id = 5
UNION ALL
SELECT * FROM tasks WHERE status = 'urgent' AND project_id != 5;Tại sao nhanh hơn: OR version buộc planner chọn giữa Bitmap OR (combine bitmap từ 2 index) hoặc Seq Scan — cả hai đều scan nhiều data hơn cần thiết.
UNION ALL version tách thành 2 query độc lập: Branch 1 dùng composite index (project_id, status) với project_id = 5 — Index Scan chính xác. Branch 2 dùng single-column index (status) với status = 'urgent' rồi filter thêm project_id != 5 — Index Scan + filter nhẹ, không Seq Scan. Mỗi branch có plan tối ưu riêng. Điều kiện project_id != 5 ở branch 2 loại duplicate (row match cả hai điều kiện chỉ xuất hiện ở branch 1).
Q4Giải thích tại sao `LIKE '%@gmail.com'` không dùng được B-tree index nhưng `LIKE 'user%'` (trailing wildcard) dùng được. Điều kiện gì cần đúng để B-tree apply cho LIKE?▸
B-tree index lưu values theo sorted alphabetical order. Tìm kiếm trong B-tree hoạt động bằng binary search — cần biết prefix (điểm bắt đầu) để xác định range trong sorted tree.
LIKE 'user%' (trailing wildcard): Prefix 'user' xác định — binary search đến 'user' trong B-tree, scan forward đến khi gặp giá trị không còn bắt đầu bằng 'user'. B-tree apply hoàn toàn.
LIKE '%@gmail.com' (leading wildcard): Không có prefix cố định — '[email protected]', '[email protected]', '[email protected]' đều match. B-tree không biết bắt đầu binary search từ đâu → phải scan toàn bộ.
Điều kiện để B-tree apply LIKE: (1) Pattern bắt đầu bằng ký tự literal (không phải % hay _). (2) Database collation là C hoặc column dùng text_pattern_ops operator class. Nếu không có (2), PG có thể vẫn không dùng index dù pattern prefix-anchored — cần CREATE INDEX ON users(email text_pattern_ops).
Q5Prepared statement `PREPARE q(text) AS SELECT * FROM tasks WHERE status = $1` đã chạy 10 lần. EXPLAIN EXECUTE q('archived') cho thấy `Filter: (status = $1)` thay vì `Index Cond: (status = 'archived')`. Điều này có nghĩa gì? Nguy hiểm ra sao với giá trị 'archived'?▸
Ý nghĩa: PostgreSQL đang dùng generic plan — plan được cache sau lần thứ 5, dùng placeholder $1 thay vì actual value. Generic plan ước tính selectivity theo average distribution, không biết actual value là 'archived'.
Nguy hiểm với 'archived': Nếu distribution là {todo: 70%, doing: 20%, done: 8%, archived: 2%}, average selectivity ~25%. Generic plan nghĩ ~25% row sẽ pass → chọn Seq Scan (reasonable cho 25%). Nhưng 'archived' thực tế chỉ 2% → Index Scan sẽ nhanh hơn ~10x. Generic plan giam query vào Seq Scan không cần thiết mỗi lần status = 'archived' được query.
Fix: SET plan_cache_mode = force_custom_plan; cho session — PG re-plan mỗi lần với actual value, luôn chọn Index Scan cho 'archived'. Hoặc DEALLOCATE q; PREPARE q(text) AS ...; để reset về 5-lần-custom-plan cycle.
Q6Team dev nói 'query chậm, thêm index là xong'. Bạn phản biện bằng cách nào để thuyết phục họ chạy EXPLAIN trước? Liệt kê ít nhất 3 tình huống mà thêm index không giải quyết được vấn đề.▸
Phản biện bằng bằng chứng: Thêm index mù có thể không fix vấn đề và tạo thêm overhead write. EXPLAIN cho biết chính xác bottleneck — fix đúng target.
3 tình huống thêm index không giải quyết được:
1. Function on column (Pattern 1): Có index trên email, nhưng query dùng LOWER(email). Thêm index thứ 2 trên email không help — cần expression index trên LOWER(email) đúng cách hoặc normalize data.
2. Type mismatch (Pattern 2): Index trên phone VARCHAR, nhưng query bind numeric. Thêm thêm index varchar vẫn không apply vì PG cast column sang numeric. Fix là quote literal đúng — không phải thêm index.
3. Generic plan (Pattern 5): Index đúng đã tồn tại và được dùng trong custom plan, nhưng generic plan cached sau lần thứ 5 switch sang Seq Scan. Thêm index không thay đổi gì — vấn đề là plan cache behavior, fix bằng plan_cache_mode.
Cách thuyết phục: Chạy EXPLAIN ANALYZE — nếu output cho thấy index scan đang được dùng rồi mà vẫn chậm (high actual time, nhiều loops, disk I/O), vấn đề không phải index thiếu. Nếu Seq Scan vì function wrap — thêm index thường trên column sẽ không help.
Q7Dashboard query của M02.7 đã audit xong 5 pattern. Pattern nào cần action ngay (nếu phát hiện), pattern nào chỉ cần monitor? Viết checklist audit 5-bước để áp dụng cho query mới trong tương lai.▸
Cần action ngay khi phát hiện:
Pattern 1 (function on column): Index không được dùng → Seq Scan ngay lập tức → fix ngay.
Pattern 2 (type mismatch): Tương tự Pattern 1 — mỗi query bị ảnh hưởng → fix ngay.
Pattern 4 (leading wildcard): Seq Scan cho mọi wildcard query → fix ngay nếu query thường xuyên.
Monitor, action khi có evidence:
Pattern 3 (OR): Phụ thuộc selectivity — Bitmap OR có thể OK nếu selectivity thấp. Monitor actual time; fix khi vượt SLA.
Pattern 5 (param sniffing): Chỉ biểu hiện sau >5 executions với skewed param. Monitor sau deploy; fix khi phát hiện generic plan tệ với giá trị hiếm.
Checklist audit 5-bước cho query mới:
1. EXPLAIN (ANALYZE, BUFFERS) baseline -> lưu actual time + plan
2. Scan WHERE clause: có function wrap column? (Pattern 1)
-> function(col) = X thay vi col = X
3. Scan type: column type vs literal type match? (Pattern 2)
-> varchar col vs numeric literal (no quote)?
4. Scan OR: có OR predicate? selectivity từng nhánh? (Pattern 3)
-> Bitmap OR ok? hay cần UNION ALL?
5. Scan LIKE: có leading wildcard? (Pattern 4)
-> LIKE '%...' -> pg_trgm hoặc reverse index
6. Scan prepared: query dùng $1? Distribution skewed? (Pattern 5)
-> Chạy 6+ lần, EXPLAIN EXECUTE -> $1 hay actual value?Bài tiếp theo: Tuning toolbox — 6 cách fix bad plan theo hierarchy chi phí
Bài này có giúp bạn hiểu bản chất không?