SQL nâng cao 8 — Tối ưu truy vấn & đọc EXPLAIN

Bảy bài trước của series đã cho bạn công cụ viết ra những truy vấn phân tích mạnh: window functions, khung dữ liệu chạy tích lũy, gaps & islands, cohort, funnel, pivot, và CTE đệ quy. Nhưng một truy vấn đúng chưa chắc là truy vấn nhanh. Khi báo cáo tổng hợp cuối tháng của NCB chạy 40 giây thay vì 400ms, sự khác biệt không nằm ở kết quả — mà ở kế hoạch thực thi (execution plan) mà PostgreSQL chọn để tính ra kết quả đó.

Bài kết series này dạy bạn kỹ năng nền tảng nhất của tối ưu: đọc được planner đang định làm gì, tìm ra nút thắt, rồi can thiệp đúng chỗ bằng index hoặc viết lại truy vấn. Không đọc được plan thì mọi nỗ lực tối ưu chỉ là đoán mò. Bài này bổ trợ cho Tối ưu — query tuning và Kiến trúc PostgreSQL; ở đây ta tập trung vào truy vấn phân tích (aggregate, window, join nhiều bảng).

SQL là ngôn ngữ khai báo

Bạn viết SELECT ... WHERE ... JOIN ... — tức mô tả kết quả mong muốn, không hề ra lệnh đọc bảng nào trước, dùng index hay quét cả bảng, ghép hai bảng theo thuật toán nào. Khoảng trống giữa "cái gì" và "làm thế nào" là nơi planner (trình tối ưu) làm việc: với cùng một câu SQL, nó có thể sinh nhiều plan cùng cho đúng kết quả nhưng chênh nhau hàng nghìn lần về tốc độ, rồi ước lượng chi phí (cost) và chọn cái rẻ nhất.

Hệ quả thực tế: cùng một câu SQL, cùng dữ liệu, có thể chạy 3ms hôm nay và 30 giây tuần sau — chỉ vì thống kê (statistics) thay đổi khiến planner đổi plan. Muốn kiểm soát điều đó, phải đọc được plan bằng EXPLAIN.

EXPLAIN vs EXPLAIN ANALYZE

• EXPLAIN <query>: chỉ ước lượng plan, không chạy truy vấn thật. Cho biết planner định làm gì và cost ước tính.
• EXPLAIN ANALYZE <query>: chạy thật rồi báo cáo thời gian và số dòng thực tế. Chính xác hơn nhưng tốn tài nguyên (với INSERT/UPDATE phải bọc trong transaction để rollback).

Hãy đọc một plan tổng hợp giao dịch thật trên sandbox:

-- ▶ Chạy được
EXPLAIN SELECT a.currency, SUM(t.amount) AS total
FROM transactions t
JOIN accounts a ON a.id = t.account_id
WHERE t.kind = 'credit'
GROUP BY a.currency;

Kết quả (rút gọn):

GroupAggregate  (cost=40.37..40.45 rows=4 width=64)
  Group Key: a.currency
  ->  Sort  (cost=40.37..40.38 rows=4 width=50)
        Sort Key: a.currency
        ->  Hash Join  (cost=20.80..40.33 rows=4 width=50)
              Hash Cond: (a.id = t.account_id)
              ->  Seq Scan on accounts a  (cost=0.00..16.90 rows=690 ...)
              ->  Hash  (cost=20.75..20.75 rows=4 width=22)
                    ->  Seq Scan on transactions t  (cost=0.00..20.75 rows=4 ...)
                          Filter: (kind = 'credit'::text)

Cách đọc: plan là một cây, đọc từ trong ra ngoài, từ dưới lên. Lá là nơi lấy dữ liệu (Seq Scan), nút cha xử lý dòng do con trả lên. Ở đây: quét transactions, lọc kind='credit', xây bảng băm rồi Hash Join với accounts, sắp xếp theo currency, cuối cùng gộp SUM.

Mỗi nút có bốn con số trong ngoặc:

Giờ thêm ANALYZE để so ước tính với thực tế:

-- ▶ Chạy được
EXPLAIN ANALYZE SELECT a.currency, SUM(t.amount) AS total
FROM transactions t
JOIN accounts a ON a.id = t.account_id
WHERE t.kind = 'credit'
GROUP BY a.currency;

Bây giờ mỗi nút có thêm (actual time=0.044..0.046 rows=4 loops=1) và cuối plan có:

Planning Time: 1.363 ms
Execution Time: 0.179 ms

Ba điều cần soi:

1. rows ước tính vs actual: Seq Scan on accounts ước tính rows=690 nhưng thực tế chỉ rows=4. Lệch lớn (ở đây do bảng vừa tạo chưa ANALYZE) là cờ đỏ — planner đang mù thống kê và có thể chọn nhầm plan. Chạy ANALYZE <table> để cập nhật.
2. actual time và loops: thời gian là của một loop; tổng thực = actual time × loops. Một Nested Loop với loops=100000 mà mỗi vòng 0.1ms là thảm họa ẩn.
3. cost vs time: cost dùng để so sánh các plan, không phải mili-giây. Đừng quy đổi cost ra thời gian.

Thêm EXPLAIN (ANALYZE, BUFFERS) để thấy buffers: shared hit (đọc trúng cache) vs read (phải đọc từ đĩa). Nhiều read = I/O là nút thắt; đó là tín hiệu mạnh cần index hoặc thu hẹp dữ liệu quét.

Từ điển các nút hay gặp

• Seq Scan (sequential scan): đọc tuần tự cả bảng. Nhanh nhất khi bảng nhỏ hoặc cần phần lớn số dòng.
• Index Scan: dùng B-tree để nhảy tới đúng dòng. Tốt khi lọc/tra ra ít dòng. Index Only Scan: lấy đủ dữ liệu ngay trong index, khỏi chạm bảng — rất nhanh (cần covering index).
• Bitmap Index Scan + Bitmap Heap Scan: dùng index dựng bản đồ bit các trang cần đọc, rồi đọc bảng theo thứ tự trang. Hợp khi số dòng khớp ở mức trung bình — nhiều để Index Scan tra lẻ tốn kém, nhưng chưa nhiều tới mức phải Seq Scan.
• Nested Loop: với mỗi dòng bảng ngoài, tra bảng trong (thường qua index). Tốt khi bảng ngoài rất ít dòng.
• Hash Join: băm bảng nhỏ vào bộ nhớ rồi dò từng dòng bảng lớn. Chuẩn cho join hai tập lớn không sắp xếp trước.
• Merge Join: cần hai đầu vào đã sắp theo khóa join; trộn như kéo khóa. Rẻ nếu dữ liệu vốn đã sort (hoặc có index phù hợp).
• Sort: sắp xếp cho ORDER BY, GROUP BY, Merge Join, hoặc DISTINCT. Chú ý Sort Method: quicksort Memory: 25kB (trong RAM) vs external merge Disk: ... (tràn ra đĩa — chậm, cân nhắc tăng work_mem).
• Aggregate: HashAggregate (gộp bằng bảng băm, không cần sort) vs GroupAggregate (cần đầu vào đã sort). WindowAgg thực thi window function — hầu như luôn kèm một Sort phía dưới.

Chỉ mục (index): khi nào giúp

Index mặc định của PostgreSQL là B-tree — cây cân bằng cho tra cứu =, <, >, BETWEEN, ORDER BY, và prefix LIKE 'abc%'. Index giúp ở ba tình huống:

1. Lọc chọn lọc cao (WHERE): trả về ít dòng so với tổng bảng.
2. Join: index trên khóa ngoại giúp Nested Loop tra nhanh (như customers_pkey được dùng dưới đây).
3. Sắp xếp (ORDER BY): B-tree vốn đã có thứ tự, planner đọc theo index để khỏi phải Sort.

Trong plan dưới đây, index khóa chính được dùng thật:

-- ▶ Chạy được
EXPLAIN SELECT DISTINCT c.full_name
FROM customers c
JOIN accounts a ON a.customer_id = c.id
WHERE a.currency = 'USD';

Cho ra Index Scan using customers_pkey on customers c / Index Cond: (id = a.customer_id) — Nested Loop tra customers qua khóa chính vì phía accounts chỉ lọc ra vài dòng USD.

Composite index & thứ tự cột

Index nhiều cột (kind, account_id) khác (account_id, kind). Quy tắc leftmost prefix: index (a, b, c) phục vụ được WHERE a=..., WHERE a=... AND b=..., nhưng không phục vụ WHERE b=... đơn lẻ. Đặt cột hay dùng để lọc bằng nhau (equality) trước, cột dùng cho khoảng/sort sau. Với truy vấn báo cáo lọc WHERE kind='credit' AND created_at >= ... rồi ORDER BY created_at, index (kind, created_at) vừa lọc vừa cấp sẵn thứ tự.

Covering, partial, expression index

• Covering index (INCLUDE): CREATE INDEX ... ON transactions(account_id) INCLUDE (amount) cho phép Index Only Scan — lấy luôn amount từ index, không chạm bảng.
• Partial index: CREATE INDEX ... ON transactions(created_at) WHERE kind='credit' — chỉ đánh index phần dòng quan tâm, nhỏ và nhanh hơn.
• Expression index: nếu buộc phải lọc date_trunc('month', created_at), tạo index trên chính biểu thức đó: CREATE INDEX ... ON transactions(date_trunc('month', created_at)).

Vì sao index không phải lúc nào cũng được dùng

Trên sandbox, truy vấn lọc theo khoảng thời gian vẫn ra Seq Scan dù có thể tạo index:

-- ▶ Chạy được
EXPLAIN SELECT id FROM transactions
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';

Lý do: bảng chỉ vài dòng nằm trong 1-2 trang. Đọc cả trang rẻ hơn đi qua index rồi nhảy lại bảng. Planner chọn Seq Scan là đúng khi: bảng nhỏ, độ chọn lọc thấp (truy vấn lấy phần lớn số dòng), hoặc thống kê lỗi thời. Đừng ép index bằng mọi giá — hãy sửa nguyên nhân (cập nhật ANALYZE, thu hẹp điều kiện lọc).

SARGable: đừng bọc hàm lên cột lọc

SARGable (Search ARGument able) = predicate mà index có thể tận dụng. Điều kiện: cột lọc phải "trần", không bị hàm bao quanh.

-- ▶ Chạy được
EXPLAIN SELECT id FROM transactions
WHERE date_trunc('month', created_at) = '2024-03-01';

Filter: (date_trunc('month'::text, created_at) = ...) — planner phải tính date_trunc cho từng dòng, không index B-tree thường nào trên created_at giúp được. Viết lại thành khoảng created_at >= '2024-03-01' AND created_at < '2024-04-01' (như mục trên) để cột trần, index vào cuộc. Tương tự: tránh WHERE amount * 1.1 > 1000 (viết amount > 909.09), tránh WHERE CAST(created_at AS date) = ....

Tối ưu window & analytic query

Window function gần như luôn kéo theo một Sort. Xem plan:

-- ▶ Chạy được
EXPLAIN SELECT id, account_id, amount,
       SUM(amount) OVER (PARTITION BY account_id ORDER BY created_at) AS running
FROM transactions;

Cho ra WindowAgg -> Sort (Sort Key: account_id, created_at) -> Seq Scan. Ba đòn bẩy:

1. Lọc sớm, giảm dữ liệu trước khi window chạy: đưa WHERE (không phụ thuộc kết quả window) xuống dưới cùng để Sort/WindowAgg xử lý ít dòng hơn. Nếu chỉ cần một tháng, lọc tháng đó trước, đừng tính window cả năm rồi mới cắt.
2. Index khớp thứ tự window: nếu có index (account_id, created_at) đúng theo PARTITION BY ... ORDER BY ..., planner có thể đọc sẵn theo thứ tự và bỏ được nút Sort.
3. Materialize hợp lý: nhiều window function cùng một khung OVER (...) chia sẻ chung một lần sort — gom chúng lại thay vì mỗi cái một khung khác nhau (mỗi khung khác là thêm một Sort). Với chuỗi biến đổi nhiều tầng, cân nhắc materialize bước trung gian (bảng tạm/MATERIALIZED CTE) nếu nó được dùng lại nhiều lần.

So sánh hai cách viết cùng kết quả

Cùng "khách có tài khoản USD", hai cách viết cho hai plan khác nhau. Cách dùng IN (subquery):

-- ▶ Chạy được
EXPLAIN SELECT c.full_name
FROM customers c
WHERE c.id IN (SELECT a.customer_id FROM accounts a WHERE a.currency = 'USD');

Ra Hash Semi Join — planner đủ thông minh để không nhân đôi dòng. Cách JOIN ... DISTINCT (ở mục index phía trên) ra Nested Loop + Unique. Bài học: planner thường tự viết lại subquery thành join, nhưng không phải luôn luôn — với dữ liệu lớn, chủ động viết JOIN (hoặc EXISTS) thay cho subquery tương quan chạy lặp thường cho plan tốt và ổn định hơn. Luôn EXPLAIN cả hai để chọn.

Mẹo tổng hợp

• Tránh SELECT *: chỉ lấy cột cần — giảm width, mở đường cho Index Only Scan, giảm I/O và mạng.
• LIMIT sớm: nếu chỉ cần top-N, ORDER BY ... LIMIT cho planner dùng plan "top-N heapsort" thay vì sort toàn bộ.
• Cập nhật thống kê: chạy ANALYZE sau khi nạp lô lớn; autovacuum làm tự động nhưng có độ trễ. rows ước tính lệch xa actual = dấu hiệu cần ANALYZE.
• Tránh N+1: đừng chạy 1 truy vấn/khách trong vòng lặp ứng dụng; gộp thành một truy vấn JOIN/IN.
• Viết lại subquery tương quan → join: subquery chạy lại cho từng dòng ngoài thường thành Nested Loop đắt.
• Tránh DISTINCT thừa và ORDER BY không cần thiết trong subquery/CTE — mỗi cái là một Sort tiềm năng.

Use case thực tế

Bối cảnh. Đội báo cáo NCB có truy vấn "tổng tiền ghi có theo loại tiền tệ" phục vụ dashboard vận hành, chạy mỗi 5 phút. Trên môi trường thật (transactions ~80 triệu dòng), truy vấn mất ~18 giây, làm nghẽn cả pool kết nối.

Bước 1 — Đo bằng EXPLAIN. Analyst chạy plan tương đương trên sandbox:

-- ▶ Chạy được
EXPLAIN SELECT a.currency, SUM(t.amount) AS total
FROM transactions t
JOIN accounts a ON a.id = t.account_id
WHERE t.kind = 'credit'
GROUP BY a.currency;

Đọc plan thấy hai nút thắt: Seq Scan on transactions với Filter: (kind='credit') (quét toàn bảng 80 triệu dòng dù chỉ ~55% là credit) và một Sort trước GroupAggregate. Trên production, EXPLAIN (ANALYZE, BUFFERS) cho thấy hàng trăm nghìn block read từ đĩa — I/O bound.

Bước 2 — Tìm nút thắt. Vấn đề: (a) không có index phục vụ lọc kind, (b) GroupAggregate phải Sort vì đầu vào chưa có thứ tự.

Bước 3 — Can thiệp. Tạo partial index đúng cho tải này:

-- (DDL — chạy trên DB có quyền ghi, KHÔNG chạy trên sandbox read-only)
CREATE INDEX idx_txn_credit ON transactions(account_id)
  WHERE kind = 'credit';

Index này (i) nhỏ vì chỉ chứa dòng credit, (ii) biến Seq Scan + Filter thành Index/Bitmap Scan, (iii) cấp account_id sẵn cho join. Đồng thời chạy ANALYZE transactions để planner có thống kê đúng. Vì chỉ có 4 loại tiền tệ (currency), planner chuyển sang HashAggregate (không cần Sort) khi dữ liệu vào đã giảm mạnh.

Bước 4 — Đo lại. Chạy lại EXPLAIN ANALYZE: plan mới là HashAggregate -> Hash Join -> Bitmap Heap Scan (idx_txn_credit), không còn nút Sort toàn cục, read giảm ~20 lần. Thời gian production rơi từ ~18 giây xuống ~0.6 giây. Không đổi một dòng logic nghiệp vụ nào — chỉ đọc plan, thêm đúng một index, cập nhật thống kê.

Bài học vận hành. Quy trình lặp lại được cho mọi báo cáo chậm:

Ghi nhớ

• EXPLAIN chỉ ước lượng plan; EXPLAIN ANALYZE chạy thật, cho actual time, rows, loops; thêm BUFFERS để thấy I/O (shared hit vs read).
• Đọc plan từ trong ra ngoài, dưới lên; cost dùng để so sánh plan, không phải mili-giây. Tổng thời gian một nút = actual time × loops.
• rows ước tính lệch xa actual = thống kê lỗi thời → chạy ANALYZE. Đây là nguyên nhân số một khiến planner chọn nhầm plan.
• Nhận diện nút: Seq Scan (quét cả bảng, tốt khi bảng nhỏ/độ chọn lọc thấp), Index/Index Only/Bitmap Scan, Nested Loop / Hash Join / Merge Join, Sort (coi chừng external merge Disk), HashAggregate vs GroupAggregate, WindowAgg (luôn kèm Sort).
• Index B-tree giúp khi lọc chọn lọc cao, join, và sắp xếp. Composite index theo leftmost prefix: equality trước, range/sort sau. Biết dùng covering (INCLUDE), partial, expression index.
• Planner chọn Seq Scan không phải lúc nào cũng sai — với bảng nhỏ hoặc lấy phần lớn dòng, đó là lựa chọn tối ưu.
• Viết predicate SARGable: đừng bọc hàm lên cột lọc (date_trunc(created_at) → viết lại thành khoảng >= ... AND < ...).
• Tối ưu window query: lọc sớm, dùng index khớp PARTITION BY/ORDER BY để bỏ Sort, gom các window cùng khung để chia sẻ một lần sort.
• Mẹo chung: tránh SELECT *, dùng LIMIT sớm, viết lại subquery tương quan thành join, tránh N+1, giữ thống kê tươi bằng ANALYZE.
• Quy trình tối ưu là vòng lặp: đo (EXPLAIN) → tìm nút thắt → index/viết lại → đo lại. Xem thêm query tuning và kiến trúc PostgreSQL.

Nguồn tham khảo

• PostgreSQL Documentation — Using EXPLAIN
• PostgreSQL Documentation — Planner/Optimizer
• PostgreSQL Documentation — EXPLAIN (SQL command reference)
• PostgreSQL Documentation — Indexes (Chapter 11: Index Types, Multicolumn, Index-Only Scans, Partial Indexes)
• PostgreSQL Documentation — ANALYZE / Statistics Used by the Planner
• Use The Index, Luke! — Markus Winand