Đọc EXPLAIN ANALYZE từ một truy vấn chọn đơn hàng

Câu hỏi bài này trả lời: truy vấn đọc ít đơn nhưng chạy chậm; làm sao tìm công việc thừa trong plan, đối chiếu ước lượng với số đo và kiểm thay đổi có giúp giảm công việc đó?

Cần biết trước: SQL SELECT, WHERE, index cơ bản và cách dùng shell. Bài dùng PostgreSQL 18.6 với dữ liệu giả ở lab database. Phần chạy kiểm dùng server cục bộ trên macOS arm64; Docker/Linux của lab đang chờ kiểm. Không suy kết quả này sang một database khác.

Chuẩn bị cùng một dữ liệu

Tạo thư mục trống. Chép bốn file từ cách A của bài lab: lab-common.sh, lab-local.sh, seed-pg.sql, seed-mysql.sql. Các file nằm ngay trong bài lab, không cần tải asset. Các lệnh dưới đây chạy bằng bash; mỗi khối dùng một shell riêng nên đều nạp lại hàm client.

Nếu đang có lab của bài trước, dùng thư mục mới để giữ phép thử độc lập: lab_seed xóa và tạo lại schema của lab. Cả hai engine được dựng theo fixture chung, nhưng bài này chỉ đo plan của PostgreSQL.

. ./lab-local.sh
lab_up
lab_seed
lab_whoami

Truy vấn giữ nguyên trong cả hai lượt:

SELECT id, total_cents
FROM wiki_lab.orders
WHERE customer_id = 1 AND status = 'paid';

Nó trả 1.000 hàng. Con số này còn kiểm được từ công thức seed: customer_id = 1 khi bình phương số thứ tự chia hết cho 2.000, tức số thứ tự là bội của 100; có 1.000 bội như vậy từ 1 tới 100.000, tất cả thuộc trạng thái paid vì chia dư cho 100 bằng 0.

. ./lab-local.sh
rows=$(lab_psql -At -c "SELECT count(*) FROM wiki_lab.orders WHERE customer_id=1 AND status='paid'")
test "$rows" = 1000
echo "rows $rows"

EXPLAIN: xem ước lượng trước khi thực thi

. ./lab-local.sh
{ printf 'EXPLAIN '; cat query.sql; } | lab_psql -At

EXPLAIN thông thường tạo plan cho câu truy vấn nhưng không chạy phần thực thi của nó. Nó vẫn có thể lấy khóa và thực hiện công việc lập kế hoạch; đừng coi đây là phép thử không có tác động trên hệ thống đang có tải. Ví dụ này chỉ dùng câu SELECT trên fixture.

Trong một dòng dạng Seq Scan ... (cost=... rows=... width=...):

• Seq Scan là cách đọc bảng. Filter nằm ở dòng dưới: mỗi hàng đọc lên được kiểm điều kiện.
• Hai số cost là chi phí ước lượng trước khi có thể trả hàng đầu và chi phí nếu node chạy hết. Đơn vị do tham số cost của planner quyết định; cost không phải milliseconds.
• rows là số hàng node dự kiến đưa lên node cha mỗi lần gọi, không phải số hàng nó phải đọc.
• width là số byte trung bình của một hàng đầu ra theo ước lượng.

Cost ở node cha đã gồm cost của các node con. Cộng chúng lại sẽ tính trùng. Khi có LIMIT, node cha có thể ngừng lấy hàng trước khi node con chạy hết.

ANALYZE và BUFFERS: đối chiếu với công việc thật

Thu cả bản text để đọc và JSON để kiểm cấu trúc. Hai lệnh này thực thi truy vấn hai lần; cache có thể đã ấm ở lần thứ hai.

. ./lab-local.sh
{ printf 'EXPLAIN (ANALYZE, BUFFERS) '; cat query.sql; } | lab_psql -At | tee before.txt
{ printf 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) '; cat query.sql; } | lab_psql -At > before.json
lab_psql -At -f query.sql > rows-before.txt

Output ở lượt kiểm ngày 2026-10-03 (thời gian và estimate thay đổi ở lượt chạy khác):

Seq Scan on orders  (cost=0.00..2236.00 rows=712 width=8) (actual time=0.008..2.810 rows=1000.00 loops=1)
  Filter: ((customer_id = 1) AND (status = 'paid'::text))
  Rows Removed by Filter: 99000
  Buffers: shared hit=736
Planning:
  Buffers: shared hit=77
Planning Time: 0.169 ms
Execution Time: 2.843 ms

Ước lượng 712 hàng, thực tế 1.000 hàng: estimate thấp khoảng 1,4 lần. Scan đã xét 100.000 hàng để giữ lại 1.000; 99.000 hàng bị loại. Cả 736 lượt truy cập block ở node scan là hit, vì lab vừa nạp dữ liệu, không phải vì đọc ít dữ liệu.

Đọc plan từ nguồn dữ liệu ở dưới lên. Với một scan đơn giản, bắt đầu bằng:

1. Tên node: đọc toàn bảng hay đi qua index?
2. rows trong phần cost: planner dự kiến bao nhiêu hàng đầu ra?
3. actual ... rows ... loops: đo được bao nhiêu hàng mỗi vòng, và node được gọi bao nhiêu vòng?
4. Rows Removed by Filter: đã đọc rồi bỏ bao nhiêu hàng?
5. Buffers: đã truy cập những khối dữ liệu nào; phần nào là hit, phần nào là read?

actual time=a..b dùng milliseconds: thời gian tới hàng đầu và tới khi hoàn tất node, trung bình mỗi vòng nếu có nhiều loops. Thời gian node cha bao gồm thời gian con; không cộng mọi dòng thành tổng thời gian truy vấn. Việc đo thời gian từng node cũng có overhead.

shared hit là block đã có trong shared buffer của PostgreSQL. shared read là block phải nạp vào đó, nhưng dữ liệu vẫn có thể đến từ cache của hệ điều hành; read không chứng minh đã đọc đĩa vật lý. Tổng này đếm lượt truy cập block, không phải số block khác nhau. Trong plan nhiều tầng, buffer của node cha bao gồm con nên cũng không cộng lại tùy tiện.

Planning Time đo lập kế hoạch; Execution Time đo thực thi trong server, không bao gồm mọi chi phí truyền dữ liệu và xử lý ở ứng dụng. Không dùng số này thay trực tiếp độ trễ API.

Đổi một yếu tố: thêm index, giữ nguyên truy vấn

Index sau chỉ là công cụ cho phép thử; cách chọn thứ tự cột là chủ đề riêng. Không đổi điều kiện, lượng dữ liệu hay cưỡng ép planner bằng cách tắt sequential scan.

. ./lab-local.sh
lab_whoami
lab_psql -c 'CREATE INDEX orders_customer_status ON wiki_lab.orders(customer_id, status)'
{ printf 'EXPLAIN (ANALYZE, BUFFERS) '; cat query.sql; } | lab_psql -At | tee after.txt
{ printf 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) '; cat query.sql; } | lab_psql -At > after.json
lab_psql -At -f query.sql > rows-after.txt
LC_ALL=C sort rows-before.txt > sorted-before.txt
LC_ALL=C sort rows-after.txt > sorted-after.txt
cmp sorted-before.txt sorted-after.txt
echo 'cùng 1000 hàng'

Output cùng lượt kiểm:

Bitmap Heap Scan on orders  (cost=11.59..779.37 rows=712 width=8) (actual time=0.127..0.876 rows=1000.00 loops=1)
  Recheck Cond: ((customer_id = 1) AND (status = 'paid'::text))
  Heap Blocks: exact=736
  Buffers: shared hit=736 read=2
  ->  Bitmap Index Scan on orders_customer_status  (cost=0.00..11.41 rows=712 width=0) (actual time=0.082..0.082 rows=1000.00 loops=1)
        Index Cond: ((customer_id = 1) AND (status = 'paid'::text))
        Index Searches: 1
        Buffers: shared read=2
Planning:
  Buffers: shared hit=100 read=1
Planning Time: 0.244 ms
Execution Time: 0.914 ms
cùng 1000 hàng

Index tìm 1.000 vị trí, nhưng chúng rải khắp bảng: vẫn phải chạm 736 heap block. Mức giảm công việc ở đây là bỏ qua việc lọc 99.000 hàng, không phải giảm số block của bảng. Bitmap Index Scan tạo tập vị trí; Bitmap Heap Scan lấy các hàng theo block. Hai read ở index cũng đã nằm trong thống kê của node cha.

SQL không có ORDER BY không đảm bảo thứ tự trả hàng, nên so sánh hai tập sau khi sort. Plan nhanh hơn mà thiếu hoặc đổi hàng là một thay đổi sai.

Ở phép thử này, điều cần so là cách đọc và lượng công việc: trước đó đọc rồi lọc toàn bảng; sau đó index tìm các vị trí phù hợp trước khi lấy hàng. Không kết luận rằng mọi truy vấn đều cần index: điều kiện trả phần lớn bảng có thể vẫn khiến planner chọn scan, và index làm phát sinh chi phí ghi, dung lượng và bảo trì.

Loops: một node có thể chạy nhiều lần

Một truy vấn nhỏ minh họa node con được gọi theo từng khách:

. ./lab-local.sh
lab_psql -At <<'SQL'
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, o.id
FROM wiki_lab.customers c
CROSS JOIN LATERAL (
  SELECT id FROM wiki_lab.orders WHERE customer_id=c.id LIMIT 1
) o
WHERE c.id IN (1, 2);
SQL
lab_psql -At <<'SQL' > loops.json
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT c.id, o.id
FROM wiki_lab.customers c
CROSS JOIN LATERAL (
  SELECT id FROM wiki_lab.orders WHERE customer_id=c.id LIMIT 1
) o
WHERE c.id IN (1, 2);
SQL

Phần node con ở lượt kiểm:

  ->  Limit  (cost=0.29..3.35 rows=1 width=4) (actual time=0.003..0.003 rows=1.00 loops=2)
        Buffers: shared hit=6
        ->  Index Scan using orders_customer_status on orders  (cost=0.29..1444.55 rows=472 width=4) (actual time=0.003..0.003 rows=1.00 loops=2)
              Index Cond: (customer_id = c.id)
              Index Searches: 2
              Buffers: shared hit=6

Node con trả một hàng mỗi vòng và chạy hai vòng, nên đã trả tổng hai hàng. Với số hàng hiển thị đã làm tròn, phép nhân rows × loops chỉ là xấp xỉ. Với nested loop lớn, node con trả rất ít hàng vẫn có thể tốn nhiều công việc vì bị gọi quá nhiều lần.

Kiểm tự động phần kết luận, không khóa thời gian

Script dưới kiểm output JSON của hai lần đo: query trả đúng 1.000 hàng, có estimate/actual/loops/buffer, bản đầu đọc tuần tự và lọc 99.000 hàng, bản sau sử dụng index. Nó không khóa thời gian hay số buffer theo máy này; nếu planner của máy bạn chọn một plan khác thì kiểm sẽ đỏ để bạn đọc lại kết luận.

import json
from pathlib import Path

def nodes(plan):
    yield plan
    for child in plan.get('Plans', []):
        yield from nodes(child)

before = json.loads(Path('before.json').read_text())[0]['Plan']
after = json.loads(Path('after.json').read_text())[0]['Plan']
for plan in (before, after):
    assert plan['Actual Rows'] == 1000
    assert plan['Actual Loops'] == 1
    assert 'Plan Rows' in plan
    assert 'Shared Hit Blocks' in plan
    assert 'Shared Read Blocks' in plan
assert before['Node Type'] == 'Seq Scan'
assert before['Rows Removed by Filter'] == 99000
assert any(node.get('Index Name') == 'orders_customer_status' for node in nodes(after))
assert after['Total Cost'] < before['Total Cost']
loop_plan = json.loads(Path('loops.json').read_text())[0]['Plan']
assert loop_plan['Actual Rows'] == 2
assert any(node['Node Type'] == 'Limit' and node['Actual Loops'] == 2
           and node['Actual Rows'] == 1 for node in nodes(loop_plan))
print('plan và số hàng đạt')

python3 check-plan.py

Reset schema, chạy lại từ dữ liệu mới để kiểm fixture không phụ thuộc thay đổi từ lượt trước. Reset này cũng gỡ index vừa thêm.

. ./lab-local.sh
lab_seed
test "$(lab_psql -At -c "SELECT count(*) FROM wiki_lab.orders WHERE customer_id=1 AND status='paid'")" = 1000
test -z "$(lab_psql -At -c "SELECT to_regclass('wiki_lab.orders_customer_status')")"
{ printf 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) '; cat query.sql; } | lab_psql -At > before.json
lab_psql -c 'CREATE INDEX orders_customer_status ON wiki_lab.orders(customer_id, status)'
{ printf 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) '; cat query.sql; } | lab_psql -At > after.json
python3 check-plan.py
echo 'reset đạt'

. ./lab-local.sh
lab_clean
test ! -e lab.env

Cùng thuật ngữ, khác engine

MySQL hỗ trợ EXPLAIN FORMAT=TREE và EXPLAIN ANALYZE với cây có cost, số hàng và số vòng. Nó không dùng cú pháp PostgreSQL EXPLAIN (ANALYZE, BUFFERS) và không có phần BUFFERS tương đương trong cây đó. Không mang cách đọc shared hit/read sang MySQL, cũng không so trực tiếp cost giữa hai engine.

Phần MySQL này dựa trên tài liệu EXPLAIN của MySQL 8.4; bài không chạy đo plan MySQL 26.7 và không khẳng định mọi chi tiết của 8.4 áp dụng cho 26.7.

Giới hạn và lỗi thường gặp

• ANALYZE thực sự chạy câu lệnh. Với UPDATE, DELETE hay lời gọi hàm có tác dụng phụ, nó có thể thay dữ liệu. Bọc transaction rồi rollback bảo vệ được nhiều thay đổi dữ liệu nhưng không hoàn tác mọi tác động, chẳng hạn cấp số sequence hoặc một thao tác ra hệ thống ngoài. Chỉ dùng SELECT trên fixture ở bài này.
• Cache và thống kê thay đổi. ANALYZE lấy mẫu, nên estimate có thể hơi khác giữa các lần reset. Lần chạy sau thường đã có cache; bài không đo cold cache hay tốc độ ổ đĩa. Không gọi việc reset dữ liệu là xóa cache.
• Số đo là ví dụ, không phải cam kết tốc độ. Hai plan công bố thuộc phiên bản, dữ liệu và máy kiểm này. Một lần chạy không đủ chứng minh mức cải thiện ổn định; muốn benchmark cần nhiều lượt, cùng điều kiện và đo cả workload ghi.
• relation does not exist: kiểm đã chép đủ file fixture, gọi lab_up, lab_seed và dùng tên wiki_lab.orders.
• Plan không có index: kiểm dữ liệu, điều kiện lọc, index có tồn tại và thống kê; đừng tắt scan chỉ để có hình đúng như bài.
• rows ước lượng lệch xa actual rows: rà thống kê và tương quan dữ liệu trước khi kết luận thiếu index. Planner dự đoán sai selectivity có thể chọn đường đi sai.

Học tiếp

Thử đổi customer_id=1 thành một khách khác hoặc bỏ điều kiện khách để quan sát planner đổi quyết định. Giữ truy vấn và kết quả cố định khi so hai cấu hình; bài composite index tiếp theo sẽ so hai thứ tự cột thay vì thêm mọi index có thể nghĩ ra.

• Lab database dùng chung: dữ liệu, reset, cách xác nhận đúng lab và dọn tài nguyên.
• N+1 với EF Core: đếm nhiều câu SQL ở tầng ứng dụng trước khi tối ưu từng plan.
• Tiêu chí hoàn tất có thể kiểm chứng: viết kiểm hành vi và ghi giới hạn của bằng chứng.

Nguồn tham khảo

• PostgreSQL 18, Using EXPLAIN, phần EXPLAIN Basics, EXPLAIN ANALYZE và Caveats; đọc ngày 2026-10-03.
• PostgreSQL 18, EXPLAIN, mô tả ANALYZE, BUFFERS, FORMAT và tác dụng phụ; đọc ngày 2026-10-03.
• MySQL 8.4, EXPLAIN Statement, cú pháp TREE và ANALYZE; đọc ngày 2026-10-03, không thay bằng chứng chạy engine 26.7.