Tối ưu 1 — Đọc execution plan với EXPLAIN

Có một sự thật khiến người mới học tối ưu SQL bối rối: bạn không hề bảo database phải làm gì. Bạn viết SELECT ... WHERE ... JOIN ..., tức là bạn mô tả kết quả mong muốn — nhưng ai quyết đị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? Đó là việc của một thành phần vô hình bên trong database gọi là trình tối ưu truy vấn (query optimizer / planner).

Vì thế, tối ưu SQL là cuộc đối thoại với planner: nhìn được planner đang định làm gì, hiểu vì sao, rồi can thiệp đúng chỗ (thêm index, viết lại truy vấn, cập nhật thống kê). Công cụ để nhìn vào bộ óc đó là EXPLAIN. Đây là kỹ năng nền tảng nhất của loạt bài tối ưu: không đọc được plan thì mọi nỗ lực chỉ là đoán mò.

SQL là ngôn ngữ khai báo (declarative)

Hai cách nghĩ:

• Mệnh lệnh (imperative) — như Python, Java: bạn viết từng bước "làm thế nào". Máy làm đúng y như bạn ra lệnh.
• Khai báo (declarative) — như SQL: bạn viết "cái gì" bạn muốn (SELECT khách có tổng giao dịch > 1 tỷ), không nói database quét bảng nào, dùng index gì, ghép bảng ra sao.

Khoảng trống giữa "cái gì" (câu SQL) và "làm thế nào" (các bước thực thi vật lý) chính là nơi planner làm việc. Với cùng một câu SQL, planner có thể sinh nhiều kế hoạch thực thi (execution plan) cùng cho đúng kết quả nhưng chênh nhau hàng nghìn lần về tốc độ. Nhiệm vụ của nó là ước lượng chi phí (cost) từng phương án 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ê thay đổi khiến planner đổi plan. Muốn hiểu và kiểm soát điều đó, bạn phải đọc được plan.

Câu lệnh đi qua đâu trước khi chạy

Trước khi có plan, một câu SQL trong PostgreSQL đi qua chuỗi giai đoạn sau:

Điểm mấu chốt: planner không nhìn dữ liệu thật để quyết định. Nó nhìn thống kê — những con số tóm tắt về bảng (số dòng, độ chọn lọc của cột, giá trị phổ biến) được lưu trong pg_statistic và cập nhật bởi lệnh ANALYZE. Nếu thống kê sai/cũ, planner sẽ ước lượng sai và chọn plan tệ. Đây là nguyên nhân số một của các query "tự nhiên chậm".

EXPLAIN vs EXPLAIN ANALYZE

Có hai công cụ cần phân biệt rõ:

• EXPLAIN <câu lệnh> — planner sinh plan và trả về ước lượng (estimate). Nó không chạy câu lệnh. Nhanh, an toàn, nhưng mọi con số chỉ là dự đoán của planner.
• EXPLAIN ANALYZE <câu lệnh> — planner sinh plan rồi thực sự chạy câu lệnh, đo thời gian và số dòng thật ở mỗi node. Bạn nhận được cả estimated lẫn actual — so sánh hai bên là kỹ thuật chẩn đoán quan trọng nhất.

Cảnh báo: vì EXPLAIN ANALYZE chạy thật, nếu bạn EXPLAIN ANALYZE một câu UPDATE/DELETE thì dữ liệu sẽ bị thay đổi. Với SELECT thì vô hại (trừ chi phí thời gian). Sandbox của Knowledge Base chỉ cho phép SELECT/WITH/EXPLAIN nên bạn an toàn.

Các option hữu ích (đặt trong ngoặc, phong cách PostgreSQL hiện đại):

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) SELECT ...;

• ANALYZE — chạy thật, lấy actual time/rows/loops.
• BUFFERS — hiển thị số block đọc từ cache (shared hit) và từ đĩa (read). Rất giá trị để biết query có bị I/O đĩa hay không.
• VERBOSE — in thêm danh sách cột output, tên schema đầy đủ.
• FORMAT — TEXT (mặc định, dễ đọc), hoặc JSON/YAML/XML cho công cụ phân tích tự động.

Đọc một plan tree: từ trong ra ngoài

Kết quả EXPLAIN là một cây các node. Quy tắc đọc:

1. Node thụt lề sâu nhất chạy trước. Đọc từ trong ra ngoài, từ dưới lên. Node cha nhận dữ liệu từ node con.
2. Mỗi node có dòng thông tin dạng:

Seq Scan on transactions t  (cost=0.00..21.30 rows=1130 width=44)

Giải nghĩa từng phần:

• cost=0.00..21.30 — hai số: startup cost (chi phí trước khi trả dòng đầu tiên) .. total cost (chi phí trả hết toàn bộ). Đơn vị là "đơn vị cost" trừu tượng của PostgreSQL (mặc định lấy chi phí đọc 1 page tuần tự = 1.0), không phải mili-giây. Dùng để so sánh tương đối giữa các plan, không phải để đọc ra thời gian.
• rows=1130 — số dòng planner ước lượng node này trả ra.
• width=44 — độ rộng trung bình mỗi dòng (byte). Ảnh hưởng bộ nhớ cho sort/hash.

Khi có ANALYZE, mỗi node thêm phần actual:

Seq Scan on transactions t  (cost=0.00..21.30 rows=1130 width=44)
                            (actual time=0.008..0.042 rows=1000 loops=1)

• actual time=0.008..0.042 — thời gian thật (ms): tới dòng đầu .. tới dòng cuối.
• rows=1000 — số dòng thật node trả ra.
• loops=1 — node này được thực thi mấy lần (quan trọng với Nested Loop — xem dưới). Actual time và actual rows là giá trị trung bình MỖI loop; muốn tổng thực tế phải nhân với loops.

Kỹ thuật vàng: so sánh rows ước lượng với rows thật. Nếu planner ước lượng 10 dòng nhưng thực tế 500.000 dòng, thống kê đang sai → planner có thể đã chọn plan tồi. Lệch một chút là bình thường; lệch hàng chục, hàng trăm lần là dấu hiệu báo động.

Các node quét bảng (scan)

Trực giác: Index Scan hiệu quả khi lấy ít dòng. Số dòng thoả tăng dần, việc "nhảy" ngẫu nhiên vào heap tốn kém đến mức PostgreSQL chuyển sang Bitmap Scan (đọc heap có trật tự hơn). Khi số dòng thoả quá lớn (ví dụ > 20-30% bảng), Seq Scan lại rẻ hơn vì đọc tuần tự luôn nhanh hơn nhảy ngẫu nhiên.

Các node join

Khi ghép hai bảng, planner chọn 1 trong 3 thuật toán:

• Nested Loop — với mỗi dòng của bảng ngoài, tra bảng trong. Rẻ khi bảng ngoài rất ít dòng và bảng trong có index trên khoá join. Đây là lúc loops tăng: bảng trong bị quét lại loops = số dòng bảng ngoài lần. Nếu bảng ngoài bất ngờ có nhiều dòng (do ước lượng sai) → thảm hoạ N×M.
• Hash Join — dựng bảng băm (hash table) từ bảng nhỏ hơn trong bộ nhớ (work_mem), rồi quét bảng kia dò băm. Tốt cho join giữa hai bảng lớn với điều kiện đẳng thức (=). Nếu hash table không vừa work_mem, nó "spill" ra đĩa (chậm — plan hiện "Batches > 1").
• Merge Join — sắp cả hai đầu vào theo khoá join rồi trộn như phéo trộn hai danh sách đã sắp. Tốt khi dữ liệu đã sẵn sắp xếp (ví dụ đến từ Index Scan) hoặc chi phí sort chấp nhận được.

Quy tắc trực giác: bảng ngoài nhỏ + index bên trong → Nested Loop; hai bảng lớn, join = → Hash Join; dữ liệu đã sắp sẵn → Merge Join.

Các node khác thường gặp

• Sort — sắp xếp. Plan cho biết Sort Method: quicksort Memory: 25kB (trong RAM, tốt) hay external merge Disk: 5000kB (tràn ra đĩa — dấu hiệu work_mem quá nhỏ so với dữ liệu).
• Aggregate / HashAggregate / GroupAggregate — gom nhóm cho GROUP BY, COUNT, SUM. HashAggregate gom bằng bảng băm (không cần sắp trước); GroupAggregate cần đầu vào đã sắp.
• Limit — dừng sớm khi đủ số dòng. Kết hợp với Index Scan cho ORDER BY ... LIMIT là mẫu tối ưu kinh điển (không cần sắp cả bảng).
• Gather / Gather Merge — điểm gộp kết quả của parallel query (nhiều worker chạy song song). Xuất hiện với bảng lớn khi PostgreSQL bật parallel workers.

Thống kê và planner: trái tim của mọi quyết định

Planner chỉ tốt bằng thống kê nó có. Thống kê nằm trong pg_statistic (xem qua pg_stats), gồm:

• n_distinct — số giá trị phân biệt ước lượng của cột. Quyết định độ chọn lọc: cột currency chỉ vài giá trị → lọc currency='VND' vẫn ra rất nhiều dòng → index ít hữu ích; cột account_no gần như duy nhất → lọc ra 1 dòng → index cực tốt.
• most_common_vals / most_common_freqs — danh sách giá trị phổ biến nhất và tần suất. Giúp planner ước lượng đúng cả khi dữ liệu lệch (skew): WHERE kind='transfer' có thể ra 5% hoặc 80% số dòng tuỳ phân bố.
• Histogram — phân bố giá trị cho các cột dùng trong so sánh khoảng (created_at > ...).

Thống kê được cập nhật bởi lệnh ANALYZE (và tự động bởi tiến trình autovacuum). Nếu bạn vừa nạp hàng triệu dòng mà chưa ANALYZE, planner có thể vẫn tưởng bảng rỗng → chọn Nested Loop thảm hoạ.

Hai tham số cấu hình ảnh hưởng lớn đến lựa chọn của planner:

• random_page_cost (mặc định 4.0) — chi phí ước lượng của một lần đọc page ngẫu nhiên so với đọc tuần tự (1.0). Trên SSD, đọc ngẫu nhiên rẻ hơn nhiều so với đĩa quay, nên nhiều nơi hạ xuống 1.1. Hạ giá trị này khiến planner ưu ái Index Scan hơn.
• work_mem — bộ nhớ mỗi thao tác sort/hash được dùng. Quá nhỏ → Sort/Hash tràn ra đĩa (chậm). Tăng lên giúp giữ trong RAM, nhưng nhân với số kết nối đồng thời nên cần thận trọng.

(MySQL có EXPLAIN và EXPLAIN ANALYZE với khái niệm tương tự; thống kê cập nhật qua ANALYZE TABLE. Oracle dùng EXPLAIN PLAN + DBMS_XPLAN.DISPLAY, hoặc xem V$SQL_PLAN, thống kê qua DBMS_STATS. Nguyên lý "planner chọn plan dựa trên thống kê chi phí" là chung.)

Thử trên SQL Builder — và một lưu ý quan trọng về dữ liệu nhỏ

Sandbox PostgreSQL của Knowledge Base có schema mẫu:

• customers(id, full_name, city, created_at date)
• accounts(id, customer_id, account_no, balance, currency)
• transactions(id, account_id, amount, kind, created_at timestamp)
• employees(id, name, department_id, salary, hired_at date)
• departments(id, name)

Điều tối quan trọng cần hiểu trước: các bảng này chỉ có vài dòng dữ liệu. Với bảng nhỏ như vậy, planner gần như luôn chọn Seq Scan — vì quét vài dòng còn rẻ hơn mở index. Đừng ngạc nhiên khi không thấy Index Scan: đó là quyết định đúng của planner với dữ liệu nhỏ, không phải lỗi. Trên bảng thật hàng triệu dòng, cùng câu lệnh này sẽ ra plan hoàn toàn khác (Index Scan, Bitmap Scan, Hash Join...). Hãy dùng các ví dụ dưới để luyện đọc cấu trúc plan và cú pháp, không phải để kết luận về hiệu năng.

Ví dụ 1 — EXPLAIN đơn giản (chỉ ước lượng)

▶ Chạy được trong SQL Builder

EXPLAIN SELECT * FROM transactions WHERE amount > 1000000;

Bạn sẽ thấy một Seq Scan on transactions kèm Filter: (amount > 1000000) và dòng cost=... rows=... width=.... Đây là ước lượng thuần tuý, câu lệnh không thực sự chạy. Vì bảng nhỏ, planner chọn Seq Scan; trên bảng lớn, nếu có index trên amount và điều kiện chọn lọc, bạn sẽ thấy Index Scan hoặc Bitmap Scan thay thế.

Ví dụ 2 — EXPLAIN ANALYZE với BUFFERS (chạy thật, so estimated vs actual)

▶ Chạy được trong SQL Builder

EXPLAIN (ANALYZE, BUFFERS) SELECT city, COUNT(*) FROM customers GROUP BY city;

Ở đây bạn thấy thêm actual time/rows và một node Aggregate (hoặc HashAggregate) nằm trên node Seq Scan. Hãy so rows ước lượng với rows thật ở mỗi node — với dữ liệu nhỏ chúng thường khớp. Dòng Buffers: shared hit=... cho biết dữ liệu đọc từ cache. Cuối plan có Planning Time và Execution Time (đây mới là mili-giây thật, khác với "cost").

Ví dụ 3 — JOIN hai bảng (đọc node join)

▶ Chạy được trong SQL Builder

EXPLAIN ANALYZE SELECT a.account_no, t.amount, t.kind
FROM transactions t
JOIN accounts a ON a.id = t.account_id
WHERE t.amount > 500000;

Plan có một node join (Nested Loop hoặc Hash Join) với hai node con là scan của transactions và accounts. Với dữ liệu nhỏ, planner thường chọn Nested Loop hoặc Hash Join trên hai Seq Scan. Chú ý loops ở node bảng trong nếu là Nested Loop. Trên dữ liệu lớn với index khoá ngoại, plan này sẽ khác hẳn: Nested Loop + Index Scan (nếu lọc ra ít giao dịch) hoặc Hash Join (nếu nhiều).

Ví dụ 4 — JOIN + GROUP BY + ORDER BY (nhiều node)

▶ Chạy được trong SQL Builder

EXPLAIN ANALYZE
SELECT d.name, COUNT(*) AS so_nhan_vien, AVG(e.salary) AS luong_tb
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.name
ORDER BY luong_tb DESC;

Đây là plan nhiều tầng: scan → join → Aggregate/HashAggregate (cho GROUP BY) → Sort (cho ORDER BY). Quan sát node Sort có Sort Method. Với dữ liệu nhỏ nó sắp trong RAM (quicksort); trên bảng lớn nếu work_mem nhỏ bạn sẽ thấy external merge Disk: ... — dấu hiệu cần tăng work_mem hoặc thêm index hỗ trợ sắp xếp.

Các minh hoạ KHÔNG chạy trong sandbox

Những lệnh sau chỉ để minh hoạ (sandbox chỉ chạy một câu SELECT/WITH/EXPLAIN, không DDL/DML/SET), đừng dán vào SQL Builder:

-- Thêm index hỗ trợ lọc chọn lọc trên bảng lớn
CREATE INDEX idx_tx_amount ON transactions (amount);

-- Ép planner ưu ái index (mô phỏng SSD) rồi xem plan đổi thế nào
SET random_page_cost = 1.1;

-- Cập nhật thống kê sau khi nạp dữ liệu lớn
ANALYZE transactions;

Use case thực tế: truy vấn "báo cáo tổng giao dịch" đột nhiên chậm

Bối cảnh (dữ liệu thật, không phải sandbox): một job báo cáo chạy hàng đêm, tính tổng giao dịch mỗi tài khoản trong tháng:

SELECT a.account_no, SUM(t.amount)
FROM accounts a
JOIN transactions t ON t.account_id = a.id
WHERE t.created_at >= '2026-06-01' AND t.created_at < '2026-07-01'
GROUP BY a.account_no;

Job vốn chạy 2 giây, nay chạy 4 phút. Chạy EXPLAIN (ANALYZE, BUFFERS) và đọc plan, ta thấy node join:

Nested Loop  (cost=... rows=42 ...) (actual ... rows=1850000 loops=1)
  ->  Seq Scan on transactions t  (rows=42 estimated) (actual rows=1850000 ...)
        Filter: (created_at >= ... AND created_at < ...)
  ->  Index Scan on accounts a  (... loops=1850000)

Ba dấu hiệu tố cáo nguyên nhân:

1. Estimate lệch actual khủng khiếp: planner ước lượng transactions khớp filter ra 42 dòng, thực tế 1.850.000 dòng. Lệch ~44.000 lần.
2. Vì tưởng chỉ 42 dòng, planner chọn Nested Loop — tra accounts một lần cho mỗi dòng transactions. Nhưng thực tế nó phải tra 1.850.000 lần (loops=1850000). Đây là thảm hoạ N×M.
3. Node transactions là Seq Scan dù filter theo khoảng ngày — gợi ý thiếu index trên created_at (hoặc index có nhưng thống kê sai khiến planner không dùng).

Nguyên nhân gốc: sau đợt nạp dữ liệu lớn cuối tháng, ANALYZE chưa chạy nên pg_statistic của transactions vẫn phản ánh trạng thái cũ (bảng gần rỗng). Planner ước lượng sai → chọn sai chiến lược join.

Cách xử lý (theo thứ tự): (1) chạy ANALYZE transactions; để làm mới thống kê — planner sẽ ước lượng đúng ~1,85 triệu dòng và tự chuyển sang Hash Join, thời gian tụt về vài giây; (2) thêm index trên transactions(created_at) hoặc (account_id, created_at) để hỗ trợ cả lọc lẫn join trên dữ liệu lớn; (3) đảm bảo autovacuum/auto-analyze bật để tránh lặp lại. Chi tiết về (2) sẽ ở bài Chỉ mục, về viết lại truy vấn ở bài Viết lại truy vấn, và cách phát hiện tự động các query như vậy ở bài Giám sát.

Dấu hiệu một plan "có mùi"

Khi đọc plan, hãy cảnh giác với các tín hiệu sau:

• Estimate lệch actual nhiều lần (rows dự đoán vs thật) — gần như luôn do thống kê cũ; chạy ANALYZE. Đây là tín hiệu quan trọng nhất.
• Seq Scan trên bảng lớn có filter chọn lọc — nếu filter chỉ ra ít dòng mà vẫn quét cả bảng, có thể thiếu index, hoặc điều kiện không "sargable" (bọc cột trong hàm như WHERE DATE(created_at)=... khiến index vô dụng).
• Nested Loop với loops lớn — bảng ngoài nhiều dòng hơn planner tưởng; kiểm tra estimate và thống kê.
• Sort/Hash tràn đĩa (external merge Disk, Batches > 1) — work_mem quá nhỏ so với khối lượng, hoặc đang sort/hash lượng dữ liệu không cần thiết.
• width lớn bất thường — có thể đang SELECT * kéo theo cột lớn không cần, làm phình bộ nhớ sort/hash.

Nguyên tắc: đừng sửa plan bằng cảm tính. Đo bằng EXPLAIN ANALYZE, xác định node tốn nhiều thời gian thật nhất (nhìn actual time × loops), rồi tấn công đúng node đó.

Ghi nhớ

• SQL là khai báo: bạn nói "cái gì", planner quyết "làm thế nào". Không đọc được plan thì không tối ưu được — chỉ đoán.
• EXPLAIN = ước lượng (không chạy); EXPLAIN ANALYZE = chạy thật, có actual time/rows/loops. Thêm BUFFERS để thấy I/O, VERBOSE để thấy cột.
• Đọc plan tree từ trong ra ngoài. cost=startup..total là đơn vị trừu tượng để so sánh, không phải ms; Execution Time mới là ms thật.
• Kỹ thuật số một: so sánh rows ước lượng với rows thật. Lệch nhiều = thống kê sai = nguồn cơn của plan tồi.
• Nhớ mặt các node: Seq Scan / Index Scan / Bitmap Scan (quét); Nested Loop / Hash Join / Merge Join (ghép); Sort / Aggregate / Limit / Gather.
• Planner sống nhờ thống kê (pg_statistic, cập nhật bởi ANALYZE). n_distinct, most_common_vals quyết định độ chọn lọc. random_page_cost và work_mem uốn nắn lựa chọn của planner.
• Trong sandbox này dữ liệu nhỏ nên plan hầu như là Seq Scan — đó là bình thường. Hãy dùng để luyện đọc plan, không phải đo hiệu năng.

Đọc được plan là bước một. Bước hai — biến hiểu biết đó thành hành động — bắt đầu từ Chỉ mục.