Đọc query plan PostgreSQL từ con số 0 — cây node, cost, rows, loops, buffers

Bài đầu của series 5 bài về query plan (kế hoạch thực thi — cách PostgreSQL định chạy một câu SQL). Bài này dạy cách đọc bất kỳ plan nào: đọc theo thứ tự nào, mỗi con số nghĩa là gì, con số nào đáng tin, con số nào đánh lừa. Các bài sau đi vào từng nhóm node: bài 2 các kiểu scan, bài 3 join, bài 4 sort/aggregate/limit/CTE/partition, bài 5 chẩn đoán khi plan sai và vận hành.

Mọi ví dụ chạy trên một database e-commerce giả lập theo kiểu project laravel-livewire: users 200 nghìn khách (70% ở VN), orders 2 triệu đơn (3 năm, chèn theo thứ tự thời gian, khách VIP có hàng nghìn đơn), order_items 5 triệu dòng hàng, products 50 nghìn sản phẩm. Index đặt tên theo quy ước Laravel (orders_user_id_index…).

Thuật ngữ dùng trong bài
planner / optimizer (bộ lập kế hoạch: chọn cách chạy rẻ nhất trong nhiều cách) · executor (bộ chạy plan) · node (một bước trong plan: quét bảng, join, sắp xếp…) · cost (giá ước lượng, đơn vị ảo — không phải mili-giây) · startup cost (giá tới lúc node trả được dòng đầu tiên) · total cost (giá để node chạy hết) · rows (số dòng ước lượng node trả lên) · width (số byte trung bình mỗi dòng) · actual time / actual rows (số đo thật khi chạy) · loops (số lần node bị chạy lại) · buffer (một trang 8 KB trong bộ nhớ đệm của PostgreSQL) · selectivity (tỉ lệ dòng lọt qua điều kiện, từ 0 tới 1) · cardinality (số dòng của một tập kết quả) · statistics (thống kê về dữ liệu do ANALYZE thu, xem qua view pg_stats) · MCV (most common values — danh sách giá trị hay gặp nhất kèm tần suất) · histogram (các mốc chia giá trị thành nhóm có số dòng bằng nhau) · n_distinct (ước lượng số giá trị khác nhau của một cột).

Kiểm chứng: tài liệu lấy qua Context7 (PostgreSQL 18: using-explain, sql-explain, row-estimation-examples, planner-stats, runtime-config-query, release notes 17 và 18). Mọi plan trong bài là output thật trên PostgreSQL 18.4 (container my-test-pgdb, database tmp_qplan), dữ liệu dựng bằng labs/query-plan/setup.sql (cố định nhờ setseed). Cấu hình mặc định của image: shared_buffers 128 MB, work_mem 4 MB, random_page_cost 4, max_parallel_workers_per_gather 2; track_io_timing bật; JIT tắt cho mọi ví dụ (JIT có bài riêng ở bài 5). Máy lab 4 vCPU, 7 GB RAM, dùng chung nhiều service — số mili-giây dao động giữa các lần chạy, nên bài đọc rows, loops, buffers là chính, ms chỉ để so tương đối.

Trả lời 30 giây

Plan là một cây node. Dữ liệu chảy từ lá lên gốc: node cha gọi node con "cho tôi dòng tiếp theo". Vì vậy đọc từ node thụt vào sâu nhất đi lên. Mỗi node có 2 bộ số: ước lượng (cost=startup..total rows width) và, khi có ANALYZE, số thật (actual time rows loops). Cost là đơn vị ảo, chỉ dùng để so các plan của cùng một câu. Việc đầu tiên khi đọc: so rows ước lượng với rows thật ở từng node, từ dưới lên — node thấp nhất lệch cỡ 10 lần trở lên thường là gốc của plan dở, vì planner chọn mọi thứ phía trên dựa trên con số đó. Time và rows là trung bình mỗi loop, phải nhân với loops. Đọc thêm Buffers (đụng bao nhiêu trang, từ cache hay từ đĩa) và các dòng nằm ngoài cây (Trigger, Planning, JIT). Cuối cùng: EXPLAIN ANALYZE chạy thật, nên UPDATE/DELETE phải bọc trong BEGIN … ROLLBACK.

1. Bài toán gốc và bối cảnh ra đời

SQL là ngôn ngữ khai báo: bạn nói muốn cái gì ("đơn của khách 4242"), không nói làm thế nào. Cùng một câu có rất nhiều cách chạy: quét cả bảng hay đi qua index nào; join bảng nào trước; join bằng vòng lặp lồng nhau, bằng bảng băm hay bằng trộn hai danh sách đã sắp. Với câu join 3–4 bảng, số tổ hợp lên tới hàng nghìn. Các cách này cho cùng kết quả nhưng thời gian chạy có thể chênh nhau hàng nghìn lần — ví dụ ở bài 2: cùng một DELETE 20 đơn, có index thì 2,8 ms, thiếu index thì 10 giây.

Ý tưởng chọn cách chạy bằng cost ước lượng từ thống kê đến từ dự án IBM System R (bài báo của Selinger và cộng sự, 1979): đo dữ liệu bằng thống kê, ước lượng số dòng qua từng bước, gán giá cho từng cách, chọn cách rẻ nhất, dùng quy hoạch động để chọn thứ tự join. Planner của PostgreSQL theo đúng mô hình đó (và đổi sang tìm kiếm di truyền — GEQO — khi số bảng vượt geqo_threshold = 12).

Vấn đề: quyết định của planner là vô hình. App chỉ thấy câu SQL chạy chậm. EXPLAIN là cửa sổ duy nhất nhìn vào quyết định đó: planner đã chọn gì (cây node) và vì sao (các con số ước lượng). EXPLAIN ANALYZE thêm thực tế để so.

Tối ưu cho: cho thấy plan được chọn, ước lượng và số thật của từng node, I/O của từng node. Không làm được:

• Không in các plan bị loại và giá của chúng — muốn xem phải tự "cấm" loại node đang được chọn (enable_* = off, ví dụ 10).
• Không giải thích vì sao ước lượng ra con số đó — phải tự tính lại từ pg_stats (ví dụ 4).
• Không đo thời gian gửi dữ liệu qua mạng; mặc định cũng không đo thời gian đổi dòng thành chữ (trừ khi thêm SERIALIZE).
• Không tự ghi lại plan của query chậm ngoài production — đó là việc của auto_explain (bài 5).
• PostgreSQL lõi không có query hint (cố ý: sửa thống kê và schema thay vì ép plan). Extension pg_hint_plan có tồn tại nhưng lab này không cài — chưa verify.

2. Mô hình tư duy cốt lõi

Hai ý trung tâm. Mọi thứ khác trong bài là hệ quả của hai ý này.

Ý 1 — plan là một cây các "máy bơm dòng" kiểu kéo. Mỗi node có cùng một giao diện: "cho tôi dòng tiếp theo". Node gốc hỏi con, con hỏi cháu, tới lá thì đọc bảng hoặc index. Dòng chảy từ lá lên gốc. Có hai loại node:

• Node chảy liền (streaming): nhận một dòng là trả được một dòng — Seq Scan, Index Scan, Nested Loop, Limit. Startup cost gần 0.
• Node chặn (blocking): phải nuốt hết input rồi mới trả dòng đầu — Sort, Hash (bước dựng bảng băm), HashAggregate. Startup cost gần bằng total cost.

Hệ quả: LIMIT có thể dừng cả cây sớm nếu bên dưới toàn node chảy liền, nên planner rất thích những plan "trả dòng đầu nhanh" khi có LIMIT — và đó cũng là gốc của bẫy LIMIT ở bài 4.

Ý 2 — planner chọn theo cost, cost tính từ số dòng ước lượng. Mọi quyết định (scan kiểu gì, join kiểu gì, thứ tự join, có song song không, sort trong RAM hay trên đĩa) đều dựa trên con số rows ước lượng ở từng node. Ước lượng sai ở dưới thì sai lan lên trên. Vì vậy cách đọc một plan chậm gần như luôn là:

flowchart TD
  A["Chạy EXPLAIN (ANALYZE, BUFFERS)
trên dữ liệu cỡ thật, chạy 2 lần"] --> B["Đi từ node sâu nhất lên:
so rows ước lượng và rows thật"]
  B --> C{"Có node lệch
từ 10 lần trở lên?"}
  C -->|"Có"| D["Gốc thường ở node lệch thấp nhất:
thống kê cũ, cột tương quan, biểu thức,
tham số generic (bài 5)"]
  C -->|"Không"| E["Ước lượng ổn → tìm node tốn nhất:
time × loops, Buffers, Rows Removed"]
  E --> F["Thiếu index? lọc sau thay vì lọc bằng index?
tràn ra đĩa? loops quá nhiều?"]
  D --> G["Sửa nguyên nhân, chạy lại, so plan cũ"]
  F --> G

3. Internals và quyết định thiết kế — đọc từng con số

3.1. Ví dụ 1 — plan đầu tiên: $user->orders

Laravel sinh SQL cho quan hệ hasMany như sau (lấy từ lab labs/query-plan/laravel/eloquent.php, Laravel 12.64):

-- 1. $user->orders (hasMany)
select * from "orders" where "orders"."user_id" = 4242 and "orders"."user_id" is not null

EXPLAIN thường — chỉ lập plan, không chạy:

EXPLAIN
SELECT * FROM orders WHERE orders.user_id = 4242 AND orders.user_id IS NOT NULL;

Bitmap Heap Scan on orders  (cost=4.57..75.61 rows=18 width=116)
  Recheck Cond: (user_id = 4242)
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0)
        Index Cond: (user_id = 4242)

Cách đọc: node gốc là Bitmap Heap Scan, con của nó (thụt vào, có mũi tên ->) là Bitmap Index Scan. Con chạy trước về mặt dữ liệu: tra index orders_user_id_index lấy vị trí các dòng, rồi cha đọc các trang dữ liệu đó. Chi tiết hai node này ở bài 2. Để ý: điều kiện IS NOT NULL mà Laravel thêm vào đã biến mất khỏi plan — cột user_id khai báo NOT NULL nên điều kiện đó luôn đúng, planner bỏ đi (cải tiến của PostgreSQL 17 theo release notes; lab chỉ chạy trên 18.4).

Giờ chạy thật với ANALYZE:

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE orders.user_id = 4242 AND orders.user_id IS NOT NULL;

Bitmap Heap Scan on orders  (cost=4.57..75.61 rows=18 width=116) (actual time=0.684..0.837 rows=37.00 loops=1)
  Recheck Cond: (user_id = 4242)
  Heap Blocks: exact=37
  Buffers: shared hit=43
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0) (actual time=0.644..0.645 rows=37.00 loops=1)
        Index Cond: (user_id = 4242)
        Index Searches: 1
        Buffers: shared hit=6
Planning Time: 0.143 ms
Execution Time: 1.007 ms

Những dòng thụt vào dưới mỗi node là chi tiết của node đó:

• Recheck Cond, Index Cond — điều kiện node áp dụng (bài 2 giải thích vì sao bitmap phải "kiểm lại").
• Heap Blocks: exact=37 — đọc 37 trang dữ liệu, mỗi đơn của khách nằm một trang khác nhau.
• Index Searches: 1 — số lần đi từ gốc B-tree xuống lá (mới có ở PG 18).
• Buffers: shared hit=43 — đụng 43 trang, cả 43 đã nằm sẵn trong shared_buffers. Số Buffers của một node đã gồm cả node con (43 = 37 trang heap + 6 trang index).
• Cuối plan: Planning Time (lập plan) và Execution Time (chạy) — tính riêng.

3.2. Ví dụ 2 — cây nhiều tầng: đọc theo thứ tự nào

Câu báo cáo "top 5 thành phố theo doanh thu đơn đã giao, nửa đầu tháng 9/2026". Bản đầu tắt parallel cho cây gọn:

EXPLAIN (ANALYZE)
SELECT u.city, count(*) AS orders, sum(o.total_cents) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-09-01' AND o.created_at < '2026-09-16'
  AND o.status = 'delivered'
GROUP BY u.city
ORDER BY revenue DESC
LIMIT 5;

Limit  (cost=11464.11..11464.12 rows=5 width=27) (actual time=156.121..156.126 rows=5.00 loops=1)
  Buffers: shared hit=3141, temp read=581 written=581
  I/O Timings: temp read=2.223 write=10.594
  ->  Sort  (cost=11464.11..11464.15 rows=16 width=27) (actual time=156.118..156.122 rows=5.00 loops=1)
        Sort Key: (sum(o.total_cents)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=3141, temp read=581 written=581
        I/O Timings: temp read=2.223 write=10.594
        ->  HashAggregate  (cost=11463.69..11463.85 rows=16 width=27) (actual time=156.056..156.063 rows=16.00 loops=1)
              Group Key: u.city
              Batches: 1  Memory Usage: 32kB
              Buffers: shared hit=3138, temp read=581 written=581
              I/O Timings: temp read=2.223 write=10.594
              ->  Hash Join  (cost=8070.43..11259.94 rows=27166 width=15) (actual time=88.069..150.127 rows=29320.00 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    Buffers: shared hit=3138, temp read=581 written=581
                    I/O Timings: temp read=2.223 write=10.594
                    ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..1680.63 rows=27166 width=12) (actual time=0.064..9.609 rows=29320.00 loops=1)
                          Index Cond: ((created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-09-16 00:00:00+00'::timestamp with time zone))
                          Filter: (status = 'delivered'::order_status)
                          Rows Removed by Filter: 4757
                          Index Searches: 1
                          Buffers: shared hit=740
                    ->  Hash  (cost=4398.00..4398.00 rows=200000 width=19) (actual time=87.084..87.085 rows=200000.00 loops=1)
                          Buckets: 131072  Batches: 2  Memory Usage: 6337kB
                          Buffers: shared hit=2398, temp written=516
                          I/O Timings: temp write=8.993
                          ->  Seq Scan on users u  (cost=0.00..4398.00 rows=200000 width=19) (actual time=0.019..27.142 rows=200000.00 loops=1)
                                Buffers: shared hit=2398
Planning:
  Buffers: shared hit=246
Planning Time: 3.317 ms
Execution Time: 157.593 ms

Đánh số theo thứ tự dữ liệu chảy (từ trong ra ngoài, từ dưới lên):

1. Seq Scan on users — đọc cả 200 000 khách (2 398 trang, đều hit).
2. Hash — dựng bảng băm từ 200 000 dòng đó. Batches: 2 nghĩa là bảng băm không vừa work_mem × hash_mem_multiplier (4 MB × 2) nên bị chia đôi, một nửa ghi ra file tạm (temp written=516). Đây là node tốn nhất: 87 ms tới khi xong.
3. Index Scan ... orders_created_at_index — đọc đơn trong 15 ngày theo index, lọc thêm status = 'delivered' sau khi đã đọc dòng (Filter, bỏ 4 757 dòng).
4. Hash Join — mỗi đơn dò bảng băm tìm khách; 29 320 dòng khớp. Ước lượng 27 166 — sát.
5. HashAggregate — gom theo thành phố, ra 16 nhóm.
6. Sort với top-N heapsort — chỉ giữ 5 dòng lớn nhất trong một heap 25 kB, không cần sắp cả 16.
7. Limit — lấy 5 dòng.

Hai dòng Buffers/I/O Timings ở Limit là tổng của cả cây: tất cả 3 141 trang đều là hit; thời gian I/O chỉ là file tạm của bước Hash. Kết luận đọc nhanh: câu này không thiếu index, ước lượng đúng, tốn chủ yếu ở việc băm cả bảng users chỉ để lấy cột city.

Cùng câu đó, cấu hình mặc định (cho phép 2 worker song song), cây khác hẳn (bỏ dòng Buffers/I/O cho gọn):

Limit  (cost=7726.93..7726.94 rows=5 width=27) (actual time=106.217..111.410 rows=5.00 loops=1)
  ->  Sort  (cost=7726.93..7726.97 rows=16 width=27) (actual time=106.215..111.406 rows=5.00 loops=1)
        Sort Key: (sum(o.total_cents)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Finalize GroupAggregate  (cost=7723.22..7726.66 rows=16 width=27) (actual time=106.178..111.381 rows=16.00 loops=1)
              Group Key: u.city
              ->  Gather Merge  (cost=7723.22..7726.30 rows=27 width=27) (actual time=106.161..111.360 rows=32.00 loops=1)
                    Workers Planned: 1
                    Workers Launched: 1
                    ->  Sort  (cost=6723.21..6723.25 rows=16 width=27) (actual time=97.358..97.365 rows=16.00 loops=2)
                          Sort Key: u.city
                          Sort Method: quicksort  Memory: 25kB
                          Worker 0:  Sort Method: quicksort  Memory: 25kB
                          ->  Partial HashAggregate  (cost=6722.73..6722.89 rows=16 width=27) (actual time=97.244..97.252 rows=16.00 loops=2)
                                Group Key: u.city
                                Batches: 1  Memory Usage: 32kB
                                Worker 0:  Batches: 1  Memory Usage: 32kB
                                ->  Parallel Hash Join  (cost=5045.49..6602.88 rows=15980 width=15) (actual time=60.915..87.866 rows=14660.00 loops=2)
                                      Hash Cond: (o.user_id = u.id)
                                      ->  Parallel Index Scan using orders_created_at_index on orders o  (cost=0.43..1515.88 rows=15980 width=12) (actual time=0.063..12.036 rows=14660.00 loops=2)
                                            Index Cond: ((created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-09-16 00:00:00+00'::timestamp with time zone))
                                            Filter: (status = 'delivered'::order_status)
                                            Rows Removed by Filter: 2378
                                            Index Searches: 1
                                      ->  Parallel Hash  (cost=3574.47..3574.47 rows=117647 width=19) (actual time=57.363..57.365 rows=100000.00 loops=2)
                                            Buckets: 262144  Batches: 1  Memory Usage: 12704kB
                                            ->  Parallel Seq Scan on users u  (cost=0.00..3574.47 rows=117647 width=19) (actual time=0.023..14.966 rows=100000.00 loops=2)
Planning Time: 0.864 ms
Execution Time: 111.836 ms

Những thứ mới phải đọc khi có parallel:

• Gather Merge — gom kết quả từ các process, giữ thứ tự. Workers Planned: 1 / Launched: 1: planner chỉ xin 1 worker (bảng nhỏ), và xin được đủ.
• loops=2 ở các node dưới Gather = leader + 1 worker. rows là trung bình mỗi process: rows=14660.00 loops=2 nghĩa là tổng 29 320 dòng — đúng bằng bản không song song.
• Aggregate tách đôi: Partial HashAggregate ở mỗi process, Finalize GroupAggregate gom lại ở leader.
• Parallel Hash — một bảng băm dùng chung cho mọi process, lần này vừa bộ nhớ (Batches: 1, 12 704 kB — bảng băm song song được cộng dồn bộ nhớ của các process).

3.3. Ví dụ 3 — cost tính ra sao: tính tay khớp từng số

Cost không phải phép màu. Với Seq Scan, công thức (trong docs using-explain) là:

cost = relpages × seq_page_cost + reltuples × cpu_tuple_cost   (+ reltuples × cpu_operator_cost cho mỗi phép so sánh trong WHERE)

SELECT relpages, reltuples FROM pg_class WHERE relname = 'products';
 relpages | reltuples
----------+-----------
      468 |     50000
(1 row)

EXPLAIN SELECT * FROM products;

Seq Scan on products  (cost=0.00..968.00 rows=50000 width=43)

SELECT relpages * current_setting('seq_page_cost')::float8
     + reltuples * current_setting('cpu_tuple_cost')::float8 AS cost_tinh_tay
FROM pg_class WHERE relname = 'products';
 cost_tinh_tay
---------------
           968
(1 row)

468 trang × 1.0 + 50 000 dòng × 0.01 = 968 — khớp. Thêm điều kiện price_cents > 4000000, mỗi dòng tốn thêm một phép so sánh (0.0025):

EXPLAIN SELECT * FROM products WHERE price_cents > 4000000;

Seq Scan on products  (cost=0.00..1093.00 rows=10126 width=43)
  Filter: (price_cents > 4000000)

SELECT relpages * current_setting('seq_page_cost')::float8
     + reltuples * (current_setting('cpu_tuple_cost')::float8 + current_setting('cpu_operator_cost')::float8) AS cost_tinh_tay
FROM pg_class WHERE relname = 'products';
 cost_tinh_tay
---------------
          1093
(1 row)

468 + 50 000 × (0.01 + 0.0025) = 1 093 — khớp. Các hằng số này đều là tham số đổi được:

Hệ quả quan trọng: cost chỉ so được giữa các plan của cùng một câu, trên cùng cấu hình. Không có quy đổi cost ra ms: ví dụ 1 có cost 75.61 và chạy 1 ms; ví dụ 6 có cost 228.88 (gấp 3) nhưng chạy 43,8 ms (gấp 43), vì phần lớn thời gian của nó là đọc đĩa — thứ cost model chỉ đoán.

3.4. Ví dụ 4 — con số rows lấy từ đâu

Đây là phần quan trọng nhất của cả series: rows ước lượng sai thì mọi thứ sai theo. ANALYZE lấy mẫu (300 × default_statistics_target = 30 000 dòng) và lưu cho mỗi cột: tỉ lệ NULL, số giá trị khác nhau, danh sách MCV kèm tần suất, và histogram cho phần còn lại. Planner dùng chúng theo 3 cách.

(a) Giá trị nằm trong MCV — lấy luôn tần suất: rows = reltuples × tần suất.

SELECT most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'orders' AND attname = 'status';
                  most_common_vals                   |                      most_common_freqs
-----------------------------------------------------+--------------------------------------------------------------
 {delivered,cancelled,refunded,shipped,pending,paid} | {0.8487,0.0985,0.039066665,0.0046,0.0045666667,0.0045666667}
(1 row)

EXPLAIN SELECT * FROM orders WHERE status = 'refunded';

Gather  (cost=1000.00..56972.21 rows=78134 width=116)
  Workers Planned: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..48158.81 rows=32556 width=116)
        Filter: (status = 'refunded'::order_status)

SELECT round(c.reltuples * s.most_common_freqs[array_position(s.most_common_vals::text::text[], 'refunded')]) AS rows_tinh_tay
FROM pg_class c, pg_stats s
WHERE c.relname = 'orders' AND s.tablename = 'orders' AND s.attname = 'status';
 rows_tinh_tay
---------------
         78134
(1 row)

2 000 027 × 0.039066665 = 78 134 — đúng số trên dòng Gather. (Node con Parallel Seq Scan ghi rows=32556: đó là số mỗi process; planner chia 78 134 cho 2.4 — leader cũng làm việc nhưng ít hơn worker.)

(b) Giá trị không nằm trong MCV — chia đều phần còn lại cho các giá trị còn lại:

SELECT null_frac, n_distinct, array_length(most_common_vals::text::text[], 1) AS so_mcv,
       (SELECT sum(f) FROM unnest(most_common_freqs) f) AS tong_tan_suat_mcv
FROM pg_stats WHERE tablename = 'orders' AND attname = 'user_id';
 null_frac | n_distinct | so_mcv | tong_tan_suat_mcv
-----------+------------+--------+-------------------
         0 |     108291 |     55 |        0.01773333
(1 row)

EXPLAIN SELECT * FROM orders WHERE user_id = 4242;

Bitmap Heap Scan on orders  (cost=4.57..75.61 rows=18 width=116)
  Recheck Cond: (user_id = 4242)
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0)
        Index Cond: (user_id = 4242)

SELECT round(c.reltuples
       * (1 - (SELECT sum(f) FROM unnest(s.most_common_freqs) f) - s.null_frac)
       / ((CASE WHEN s.n_distinct < 0 THEN -s.n_distinct * c.reltuples ELSE s.n_distinct END)
          - array_length(s.most_common_vals::text::text[], 1))) AS rows_tinh_tay
FROM pg_class c, pg_stats s
WHERE c.relname = 'orders' AND s.tablename = 'orders' AND s.attname = 'user_id';
 rows_tinh_tay
---------------
            18
(1 row)

SELECT count(*) AS rows_that FROM orders WHERE user_id = 4242;
 rows_that
-----------
        37
(1 row)

(1 − 0.0177) × 2 000 027 / (108 291 − 55) = 18 — khớp. Thực tế 37. Lệch 2 lần vì hai lý do: khách 4242 mua nhiều hơn trung bình, và n_distinct bị đoán thấp (108 291 trong khi thật có ~200 000 khách — bài 5 sửa chỗ này).

(c) Điều kiện khoảng — nội suy tuyến tính trong histogram (100 nhóm, mỗi nhóm ~1% số dòng):

EXPLAIN SELECT * FROM products WHERE price_cents > 4000000;

Seq Scan on products  (cost=0.00..1093.00 rows=10126 width=43)
  Filter: (price_cents > 4000000)

WITH h AS (
  SELECT b::int AS bound, i
  FROM pg_stats, unnest(histogram_bounds::text::text[]) WITH ORDINALITY AS t(b, i)
  WHERE tablename = 'products' AND attname = 'price_cents'
), k AS (
  SELECT max(i) AS k, (SELECT count(*) - 1 FROM h) AS nb FROM h WHERE bound <= 4000000
)
SELECT round((SELECT reltuples FROM pg_class WHERE relname = 'products')
       * (1 - ((k.k - 1) + (4000000 - lo.bound)::float8 / (hi.bound - lo.bound)) / k.nb)) AS rows_tinh_tay
FROM k JOIN h lo ON lo.i = k.k JOIN h hi ON hi.i = k.k + 1;
 rows_tinh_tay
---------------
         10126
(1 row)

Khớp: 10 126.

(d) Nhiều điều kiện — giả định độc lập. Planner nhân selectivity của từng điều kiện với nhau. Với hai cột phụ thuộc nhau (thành phố quyết định quốc gia), phép nhân này sai:

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT * FROM orders WHERE ship_country = 'SG' AND ship_city = 'Singapore';

Gather  (cost=1000.00..51407.27 rows=1651 width=116) (actual time=0.641..168.025 rows=59185.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..50242.17 rows=688 width=116) (actual time=0.027..144.061 rows=19728.33 loops=3)
        Filter: ((ship_country = 'SG'::text) AND (ship_city = 'Singapore'::text))
        Rows Removed by Filter: 646938
Planning Time: 0.172 ms
Execution Time: 170.552 ms

Ước lượng 1 651, thật 59 185 — lệch 36 lần. Tần suất SG ≈ 3%, tần suất Singapore ≈ 3%, planner nhân ra 0,09% — trong khi mọi đơn giao tới Singapore đều ở SG. Đây là gốc của rất nhiều plan dở ngoài đời; cách sửa (CREATE STATISTICS) ở bài 5.

3.5. Ví dụ 5 — startup cost và LIMIT

EXPLAIN SELECT * FROM orders ORDER BY total_cents DESC LIMIT 10;

Limit  (cost=65083.76..65084.92 rows=10 width=116)
  ->  Gather Merge  (cost=65083.76..298019.97 rows=2000028 width=116)
        Workers Planned: 2
        ->  Sort  (cost=64083.73..66167.09 rows=833345 width=116)
              Sort Key: total_cents DESC
              ->  Parallel Seq Scan on orders  (cost=0.00..46075.45 rows=833345 width=116)

EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

Limit  (cost=0.43..0.88 rows=10 width=116)
  ->  Index Scan Backward using orders_created_at_index on orders  (cost=0.43..89693.83 rows=2000027 width=116)

EXPLAIN SELECT * FROM orders ORDER BY created_at DESC;

Index Scan Backward using orders_created_at_index on orders  (cost=0.43..89693.83 rows=2000027 width=116)

Hai câu khác nhau một cột sắp xếp mà plan khác hẳn:

• ORDER BY total_cents — không có index → phải Sort. Sort là node chặn nên Limit có startup cost 65 083.76: phải đọc và sắp hết 2 triệu dòng mới có dòng đầu.
• ORDER BY created_at — index đã cho sẵn thứ tự. Limit chỉ trả tiền cho 10 dòng đầu. Tính tay: 0.43 + (89693.83 − 0.43) × 10 / 2000027 = 0.88 — đúng số trên dòng Limit.

Planner luôn giả định dòng khớp điều kiện rải đều trong thứ tự của index. Giả định đó sai thì phép "chỉ trả tiền cho 10 dòng đầu" sai theo — đó là bẫy LIMIT, có lab riêng ở bài 4 (1,3 triệu dòng bị bỏ qua để tìm 10 dòng).

3.6. Ví dụ 6 — loops: node chạy lại nhiều lần

EXPLAIN (ANALYZE)
SELECT o.id, o.created_at, i.product_id, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.user_id = 4242;

Nested Loop  (cost=5.00..228.88 rows=45 width=28) (actual time=0.839..43.462 rows=83.00 loops=1)
  Buffers: shared hit=106 read=85
  I/O Timings: shared read=40.092
  ->  Bitmap Heap Scan on orders o  (cost=4.57..75.61 rows=18 width=16) (actual time=0.723..1.408 rows=37.00 loops=1)
        Recheck Cond: (user_id = 4242)
        Heap Blocks: exact=37
        Buffers: shared hit=43
        ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0) (actual time=0.701..0.702 rows=37.00 loops=1)
              Index Cond: (user_id = 4242)
              Index Searches: 1
              Buffers: shared hit=6
  ->  Index Scan using order_items_order_id_index on order_items i  (cost=0.43..8.48 rows=3 width=20) (actual time=1.128..1.129 rows=2.24 loops=37)
        Index Cond: (order_id = o.id)
        Index Searches: 37
        Buffers: shared hit=63 read=85
        I/O Timings: shared read=40.092
Planning:
  Buffers: shared hit=310 read=5
  I/O Timings: shared read=3.388
Planning Time: 7.764 ms
Execution Time: 43.782 ms

Nested Loop chạy node con bên trong (Index Scan trên order_items) một lần cho mỗi dòng của node bên ngoài: 37 đơn → loops=37. Con số actual time=1.128..1.129 rows=2.24 là trung bình mỗi lần. Tổng thời gian node này: 1.129 × 37 ≈ 41,8 ms — gần hết 43,8 ms của cả câu. Tổng số dòng: 2.24 × 37 ≈ 83 — đúng bằng rows của Nested Loop. Ngược lại, Buffers và Index Searches: 37 là tổng của mọi lần, không phải trung bình. Dòng I/O Timings: shared read=40.092 cho biết vì sao chậm: 85 trang của order_items chưa có trong cache, phải đọc từ đĩa — chạy lần hai sẽ nhanh hơn nhiều.

3.7. Ví dụ 7 — Buffers: cùng một plan, ba trạng thái cache

Script 01-07-cold-cache.sh chạy một câu 3 lần: lần 1 đuổi bảng và index khỏi cả shared_buffers (pg_buffercache_evict_relation, mới ở PG 18) lẫn page cache của hệ điều hành (posix_fadvise DONTNEED trên đúng các file đó); lần 2 chỉ đuổi khỏi shared_buffers; lần 3 chạy lại ngay.

EXPLAIN (ANALYZE) SELECT count(*), sum(total_cents) FROM orders
WHERE created_at >= '2026-08-01' AND created_at < '2026-09-01';

== Lần 1: lạnh hẳn — không có trong shared_buffers, không có trong page cache của OS
Aggregate  (cost=3756.05..3756.06 rows=1 width=16) (actual time=134.146..134.147 rows=1.00 loops=1)
  Buffers: shared hit=8 read=1519 dirtied=7
  I/O Timings: shared read=101.819
  ->  Index Scan using orders_created_at_index on orders  (cost=0.43..3414.13 rows=68385 width=4) (actual time=8.466..120.790 rows=70045.00 loops=1)
        Index Cond: ((created_at >= '2026-08-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-09-01 00:00:00+00'::timestamp with time zone))
        Index Searches: 1
        Buffers: shared hit=8 read=1519 dirtied=7
        I/O Timings: shared read=101.819
Planning:
  Buffers: shared hit=121 read=1
  I/O Timings: shared read=1.807
Planning Time: 5.371 ms
Execution Time: 134.535 ms

== Lần 2: đuổi khỏi shared_buffers, nhưng page cache của OS vẫn còn
Aggregate  (cost=3756.05..3756.06 rows=1 width=16) (actual time=41.097..41.098 rows=1.00 loops=1)
  Buffers: shared read=1518
  I/O Timings: shared read=15.801
  ->  Index Scan using orders_created_at_index on orders  (cost=0.43..3414.13 rows=68385 width=4) (actual time=0.067..32.263 rows=70045.00 loops=1)
        Index Cond: ((created_at >= '2026-08-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-09-01 00:00:00+00'::timestamp with time zone))
        Index Searches: 1
        Buffers: shared read=1518
        I/O Timings: shared read=15.801
Planning:
  Buffers: shared hit=121 read=1
  I/O Timings: shared read=0.196
Planning Time: 3.375 ms
Execution Time: 41.288 ms

== Lần 3: chạy lại ngay — các trang đã nằm trong shared_buffers
Aggregate  (cost=3756.05..3756.06 rows=1 width=16) (actual time=19.206..19.208 rows=1.00 loops=1)
  Buffers: shared hit=1518
  ->  Index Scan using orders_created_at_index on orders  (cost=0.43..3414.13 rows=68385 width=4) (actual time=0.031..12.089 rows=70045.00 loops=1)
        Index Cond: ((created_at >= '2026-08-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-09-01 00:00:00+00'::timestamp with time zone))
        Index Searches: 1
        Buffers: shared hit=1518
Planning:
  Buffers: shared hit=122
Planning Time: 4.907 ms
Execution Time: 19.432 ms

Cùng plan, cùng 1 518 trang: 134,5 ms → 41,3 ms → 19,4 ms. Cách đọc từng chữ:

• shared hit — trang đã có trong shared_buffers. Rẻ nhất.
• shared read — PostgreSQL phải xin hệ điều hành. Có thể tới từ page cache của OS (lần 2: 1 518 trang tốn 15,8 ms) hoặc từ đĩa thật (lần 1: 101,8 ms). PostgreSQL không phân biệt được hai trường hợp này; chỉ I/O Timings (cần track_io_timing = on) cho biết.
• dirtied — trang bị sửa trong lúc đọc. Ở lần 1 là 7 trang: SELECT đặt hint bit (cờ ghi nhớ trạng thái commit của dòng) cho các dòng do những lần UPDATE … ROLLBACK ở ví dụ 8 để lại.
• written — backend phải tự ghi trang bẩn ra đĩa để lấy chỗ trống (xuất hiện ở ví dụ 7 của bài 2).
• temp read/written — file tạm khi sort/băm tràn bộ nhớ (ví dụ 2 ở trên).

Bài học: đừng kết luận từ một lần chạy. Lần đầu sau khi deploy, restart, hay sau một báo cáo lớn đẩy dữ liệu ra khỏi cache, câu này chậm gấp 7 lần dù plan không đổi. So sánh hai plan thì so Buffers (số trang đụng tới) — con số này ổn định hơn ms nhiều.

4. Đảm bảo và giới hạn

EXPLAIN thường không chạy câu lệnh, nhưng vẫn parse, lấy lock nhẹ (AccessShareLock) trên các bảng, và lập plan. An toàn với mọi câu, kể cả DELETE.

EXPLAIN ANALYZE chạy câu lệnh đầy đủ rồi vứt kết quả. Có hai chỗ nó khác lần chạy thật của app:

(1) Không gửi dữ liệu và mặc định không đổi dòng thành chữ — ví dụ 10 dùng SERIALIZE (PG 17+) để đo phần này:

EXPLAIN (ANALYZE, SERIALIZE, BUFFERS OFF)
SELECT * FROM orders WHERE created_at >= '2026-09-25';

Index Scan using orders_created_at_index on orders  (cost=0.43..512.40 rows=10684 width=116) (actual time=0.034..5.237 rows=11410.00 loops=1)
  Index Cond: (created_at >= '2026-09-25 00:00:00+00'::timestamp with time zone)
  Index Searches: 1
Planning Time: 0.661 ms
Serialization: time=16.568 ms  output=2170kB  format=text
Execution Time: 22.670 ms

Quét 11 410 đơn mất 5,2 ms, nhưng đổi chúng thành 2,1 MB chữ mất 16,6 ms — gấp 3 lần. App còn phải nhận 2,1 MB qua mạng nữa. Với câu trả về nhiều dòng, Execution Time của EXPLAIN ANALYZE thấp hơn hẳn thời gian app thấy.

(2) Đo đạc cũng tốn thời gian. Mỗi node gọi đồng hồ hệ thống mỗi lần có dòng đi qua. Ví dụ 9, đếm 5 triệu dòng:

SELECT count(*) FROM order_items;
  count
---------
 5000000
(1 row)

Time: 275.144 ms

EXPLAIN (ANALYZE, BUFFERS OFF) SELECT count(*) FROM order_items;

Aggregate  (cost=99265.50..99265.51 rows=1 width=8) (actual time=896.822..896.824 rows=1.00 loops=1)
  ->  Seq Scan on order_items  (cost=0.00..86765.40 rows=5000040 width=0) (actual time=0.034..576.748 rows=5000000.00 loops=1)
Planning Time: 0.711 ms
Execution Time: 896.885 ms

EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT count(*) FROM order_items;

Aggregate  (cost=99265.50..99265.51 rows=1 width=8) (actual rows=1.00 loops=1)
  ->  Seq Scan on order_items  (cost=0.00..86765.40 rows=5000040 width=0) (actual rows=5000000.00 loops=1)
Planning Time: 0.084 ms
Execution Time: 456.319 ms

\! pg_test_timing -d 1
Testing timing overhead for 1 second.
Per loop time including overhead: 36.67 ns

Câu chạy thường (cache nóng) 275 ms; với EXPLAIN ANALYZE 897 ms; tắt đo giờ từng node (TIMING OFF) còn 456 ms. Mỗi lần đọc đồng hồ chỉ ~37 ns, nhưng 10 triệu lần (5 triệu dòng × 2 node) là đủ đội thêm cả trăm ms. Quy tắc: khi node xử lý hàng triệu dòng, actual time của nó bị phóng đại — dùng để so tương đối giữa các node, và dùng TIMING OFF khi chỉ cần tổng.

Ước lượng chỉ là ước lượng. Mẫu 30 000 dòng, giả định độc lập giữa các cột, giả định giá trị rải đều trong khoảng — mỗi giả định có một ví dụ sai ở bài 5. ANALYZE lấy mẫu ngẫu nhiên nên chạy lại có thể ra số hơi khác và đôi khi đổi plan.

Plan bạn thấy trong psql chưa chắc là plan app nhận. Plan phụ thuộc: tham số phiên (work_mem, random_page_cost… — app có thể đặt khác qua ALTER ROLE ... SET), thống kê lúc đó, kích thước bảng thật lúc đó, giá trị tham số (custom hay generic plan — bài 5), kiểu dữ liệu tham số (ví dụ 10 ở bài 2: so bigint với số thập phân làm mất index), và bản PostgreSQL.

Khác biệt khi đọc plan ở PG 18 so với 16/17 (theo release notes 18; lab chỉ chạy 18.4):

5. Failure modes

5.1. EXPLAIN ANALYZE đổi dữ liệu thật

SELECT id, status FROM orders WHERE id = 1;
 id |  status
----+----------
  1 | refunded
(1 row)

BEGIN;
BEGIN
EXPLAIN (ANALYZE) UPDATE orders SET status = 'cancelled' WHERE id = 1;

Update on orders  (cost=0.43..8.45 rows=0 width=0) (actual time=22.094..22.096 rows=0.00 loops=1)
  Buffers: shared hit=28 read=9 dirtied=8
  I/O Timings: shared read=20.771
  ->  Index Scan using orders_pkey on orders  (cost=0.43..8.45 rows=1 width=10) (actual time=0.062..0.066 rows=1.00 loops=1)
        Index Cond: (id = 1)
        Index Searches: 1
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=3
Planning Time: 0.168 ms
Execution Time: 32.570 ms

SELECT id, status FROM orders WHERE id = 1;
 id |  status
----+-----------
  1 | cancelled
(1 row)

ROLLBACK;
ROLLBACK
SELECT id, status FROM orders WHERE id = 1;
 id |  status
----+----------
  1 | refunded
(1 row)

Trong transaction, đơn số 1 đã thật sự bị đổi sang cancelled; ROLLBACK trả lại. Không có BEGIN, câu đó đã commit. Trigger chạy, khoá ngoại được kiểm tra, sequence vẫn tăng như chạy thật. Thêm một điều: transaction mở để chờ ROLLBACK vẫn giữ khoá dòng — chạy trên production thì làm nhanh, đừng để phiên treo giữa chừng.

5.2. Thời gian nằm ngoài cây plan

BEGIN;
BEGIN
DELETE FROM order_items WHERE order_id <= 1000;
DELETE 2500
EXPLAIN (ANALYZE) DELETE FROM orders WHERE id <= 1000;

Delete on orders  (cost=0.43..53.31 rows=0 width=0) (actual time=4.768..4.769 rows=0.00 loops=1)
  Buffers: shared hit=2022 read=20 dirtied=18
  I/O Timings: shared read=2.019
  ->  Index Scan using orders_pkey on orders  (cost=0.43..53.31 rows=1022 width=6) (actual time=0.066..2.381 rows=1000.00 loops=1)
        Index Cond: (id <= 1000)
        Index Searches: 1
        Buffers: shared hit=4 read=20
        I/O Timings: shared read=2.019
Planning:
  Buffers: shared hit=5
Planning Time: 1.083 ms
Trigger for constraint order_items_order_id_foreign: time=82.669 calls=1000
Execution Time: 87.588 ms

Node Delete chỉ tốn 4,8 ms. Nhưng Execution Time là 87,6 ms: 82,7 ms còn lại nằm ở dòng Trigger for constraint — PostgreSQL kiểm tra khoá ngoại bằng trigger nội bộ, chạy 1 lần cho mỗi dòng bị xoá. Plan của câu kiểm tra đó không được in ra. Nếu bảng con thiếu index trên cột khoá ngoại, con số ở dòng này tăng vọt (bài 2: 20 dòng mất 10 giây) mà cây plan vẫn trông vô hại. Luôn đọc các dòng sau cây: Trigger, JIT, Serialization, Planning.

5.3. Câu không bao giờ xong thì EXPLAIN ANALYZE cũng không có gì

EXPLAIN ANALYZE chỉ in khi câu chạy xong. Câu NOT IN ở bài 3 chạy 10 phút chưa xong; đặt statement_timeout thì nhận về đúng một dòng lỗi, không có plan:

SET statement_timeout = '60s';
SET
EXPLAIN (ANALYZE)
SELECT count(*) FROM orders
WHERE id NOT IN (SELECT order_id FROM order_items);
ERROR:  canceling statement due to statement timeout

Lúc đó dùng EXPLAIN thường (có ngay plan, cost 59 tỷ đã nói hết) — quy trình đầy đủ ở bài 5.

5.4. Cache lạnh, đo một lần, và Planning Time của kết nối mới

Ví dụ 7 đã cho thấy cùng plan chênh 7 lần theo cache. Còn một hiện tượng nữa lab gặp nhiều lần: câu đầu tiên của một kết nối mới lập plan chậm hơn hẳn (ví dụ 3 bài 2: Planning Time: 12.647 ms cho một câu tra khoá chính, các câu sau ~0,4 ms), vì mỗi kết nối phải nạp catalog vào cache riêng của nó. Với PHP-FPM mở kết nối mới mỗi request (Laravel mặc định không dùng kết nối bền), mọi request đều trả khoản này — một lý do để dùng connection pool. Trên replica cũng vậy, và replica có thể cấu hình khác primary:

SELECT pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE user_id = 4242;

Bitmap Heap Scan on orders  (cost=4.57..75.61 rows=18 width=116) (actual time=3.946..19.458 rows=37.00 loops=1)
  Recheck Cond: (user_id = 4242)
  Heap Blocks: exact=37
  Buffers: shared hit=4 read=39
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0) (actual time=2.673..2.673 rows=37.00 loops=1)
        Index Cond: (user_id = 4242)
        Index Searches: 1
        Buffers: shared hit=4 read=2
Planning:
  Buffers: shared hit=149 read=32
Planning Time: 25.671 ms
Execution Time: 20.661 ms

BEGIN;
BEGIN
EXPLAIN (ANALYZE) UPDATE orders SET status = 'cancelled' WHERE id = 1;
ERROR:  cannot execute UPDATE in a read-only transaction
ROLLBACK;
ROLLBACK

Plan giống primary (thống kê được replicate), nhưng không có dòng I/O Timings vì replica không bật track_io_timing; Planning Time 25,7 ms vì kết nối mới; câu ghi bị từ chối.

6. Vận hành trong production

• Lệnh mặc định: EXPLAIN (ANALYZE, BUFFERS, SETTINGS). Trên PG 18 BUFFERS tự bật, ghi rõ vẫn tốt vì team có thể đang ở bản cũ. SETTINGS in các tham số planner khác mặc định — thứ hay bị quên khi so 2 plan.
• Thêm VERBOSE khi cần biết cột nào được lấy (Output:) và Query Identifier (để tìm trong pg_stat_statements — bài 5). Thêm WAL cho câu ghi. Thêm SERIALIZE khi câu trả nhiều dữ liệu.
• Bật track_io_timing; trước đó chạy pg_test_timing để chắc đồng hồ máy rẻ (lab: 36,67 ns mỗi lần đọc).
• Chạy câu nặng trên replica để khỏi đè primary — nhớ cache và cấu hình replica khác primary (ví dụ 11).
• Trên primary: BEGIN; SET LOCAL statement_timeout = '30s'; EXPLAIN (ANALYZE) ...; ROLLBACK;.
• Laravel: ->explain() chỉ là EXPLAIN thường. Muốn số thật thì ghép SQL + bindings của chính builder:

-- 11. ->explain()
Bitmap Heap Scan on orders  (cost=4.57..75.61 rows=18 width=116)
  Recheck Cond: (user_id = '4242'::bigint)
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0)
        Index Cond: (user_id = '4242'::bigint)

-- 12. EXPLAIN (ANALYZE, BUFFERS) qua DB::select
Bitmap Heap Scan on orders  (cost=4.57..75.61 rows=18 width=116) (actual time=0.057..2.653 rows=37.00 loops=1)
  Recheck Cond: (user_id = '4242'::bigint)
  Heap Blocks: exact=37
  Buffers: shared hit=7 read=33
  I/O Timings: shared read=1.812
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0) (actual time=0.038..0.039 rows=37.00 loops=1)
        Index Cond: (user_id = '4242'::bigint)
        Index Searches: 1
        Buffers: shared hit=3
Planning Time: 0.101 ms
Execution Time: 2.770 ms

// cách lấy plan có số thật từ một builder Eloquent (đã chạy trong lab)
$q = $user->orders()->getQuery()->getQuery();
DB::select('EXPLAIN (ANALYZE, BUFFERS) ' . $q->toSql(), $q->getBindings());

Một số tham số của EXPLAIN còn lại, output thật ở ví dụ 10:

EXPLAIN (VERBOSE, SETTINGS)
SELECT id, status FROM orders WHERE user_id = 4242;

Bitmap Heap Scan on public.orders  (cost=4.57..75.61 rows=18 width=12)
  Output: id, status
  Recheck Cond: (orders.user_id = 4242)
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0)
        Index Cond: (orders.user_id = 4242)
Settings: jit = 'off', work_mem = '64MB'
Query Identifier: 712469994104384312

EXPLAIN (MEMORY)
SELECT u.city, count(*) FROM orders o JOIN users u ON u.id = o.user_id GROUP BY u.city;

Finalize GroupAggregate  (cost=39805.87..39810.65 rows=16 width=19)
  Group Key: u.city
  ->  Gather Merge  (cost=39805.87..39810.30 rows=38 width=19)
        Workers Planned: 2
        ->  Sort  (cost=38805.85..38805.89 rows=16 width=19)
              Sort Key: u.city
              ->  Partial HashAggregate  (cost=38805.37..38805.53 rows=16 width=19)
                    Group Key: u.city
                    ->  Parallel Hash Join  (cost=5045.49..34638.64 rows=833345 width=11)
                          Hash Cond: (o.user_id = u.id)
                          ->  Parallel Index Only Scan using orders_user_id_index on orders o  (cost=0.43..27406.01 rows=833345 width=8)
                          ->  Parallel Hash  (cost=3574.47..3574.47 rows=117647 width=19)
                                ->  Parallel Seq Scan on users u  (cost=0.00..3574.47 rows=117647 width=19)
Planning:
  Memory: used=51kB  allocated=64kB

EXPLAIN (ANALYZE, WAL, BUFFERS OFF)
UPDATE orders SET updated_at = now() WHERE user_id = 4242;

Update on orders  (cost=4.57..75.66 rows=0 width=0) (actual time=69.668..69.671 rows=0.00 loops=1)
  WAL: records=222 fpi=149 bytes=1147097
  ->  Bitmap Heap Scan on orders  (cost=4.57..75.66 rows=18 width=14) (actual time=0.110..0.505 rows=37.00 loops=1)
        Recheck Cond: (user_id = 4242)
        Heap Blocks: exact=37
        ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0) (actual time=0.092..0.093 rows=37.00 loops=1)
              Index Cond: (user_id = 4242)
              Index Searches: 1
Planning Time: 0.206 ms
Execution Time: 69.915 ms

WAL: records=222 fpi=149 bytes=1147097: sửa 37 đơn mà sinh 1,1 MB WAL vì 149 full page image (lần đầu một trang bị sửa sau checkpoint, PostgreSQL ghi nguyên trang 8 KB vào WAL). Đây là lý do một câu UPDATE nhỏ có thể làm replica trễ.

EXPLAIN (GENERIC_PLAN)
SELECT * FROM orders WHERE user_id = $1;

Bitmap Heap Scan on orders  (cost=4.57..75.61 rows=18 width=116)
  Recheck Cond: (user_id = $1)
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0)
        Index Cond: (user_id = $1)

EXPLAIN SELECT * FROM orders WHERE total_cents > 9000000;

Gather  (cost=1000.00..59919.11 rows=107603 width=116)
  Workers Planned: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..48158.81 rows=44835 width=116)
        Disabled: true
        Filter: (total_cents > 9000000)

FORMAT JSON cho các trang vẽ plan (explain.dalibo.com, explain.depesz.com) — các trang này lưu plan trên server của họ, đừng dán câu chứa dữ liệu khách hàng.

7. So sánh và ma trận quyết định

Không có "công cụ đọc plan tốt nhất" — mỗi cách nhìn trả lời một câu hỏi khác:

Dùng EXPLAIN (ANALYZE, BUFFERS) trên dữ liệu cỡ thật, chạy ít nhất 2 lần, khi cần biết vì sao một câu chậm. Tránh chạy nó trên DML ngoài transaction, trên câu có thể chạy hàng phút trên primary giờ cao điểm, và trên database dev vài nghìn dòng (plan ở đó không đại diện cho production).

8. Góc nhìn tech lead: tác động tổ chức

• Đọc plan là kỹ năng của cả team, không của một người. Người trực đêm phải tự đọc được rows/loops/buffers. Nên có buổi đọc plan chung với chính các câu chậm của hệ thống.
• Quy ước review: PR thêm hoặc sửa query trên bảng lớn (ví dụ trên 1 triệu dòng) phải kèm EXPLAIN (ANALYZE, BUFFERS) chạy trên dữ liệu cỡ production, chạy 2 lần, ghi rõ bản PostgreSQL.
• Môi trường: cần một database staging có dữ liệu cỡ thật (đã ẩn danh). Plan trên 1 000 dòng gần như luôn là Seq Scan và không nói gì về production.
• Đo ở production: pg_stat_statements + auto_explain có ngưỡng — chi tiết và cái giá ở bài 5.
• Lock-in: gần như không — EXPLAIN là chuẩn có sẵn, JSON đọc được bằng mọi tool. Rủi ro dài hạn thật sự là kiến thức: plan thay đổi theo bản PostgreSQL (bảng ở mục 4), nên tài liệu nội bộ phải ghi bản.

Mẫu ADR ngắn: "Mọi thay đổi query trên các bảng orders, order_items, users phải kèm EXPLAIN (ANALYZE, BUFFERS, SETTINGS) trên staging-full, chạy 2 lần, dán vào PR. Reviewer kiểm: (1) rows ước lượng và rows thật không lệch quá 10 lần ở node lá; (2) không có Seq Scan trên bảng lớn trong đường request; (3) không có Batches > 1 / external merge trong đường request; (4) đọc dòng Trigger nếu là DML. Lý do: 3 sự cố gần nhất đều do plan, và đều thấy được bằng EXPLAIN trước khi deploy."

9. Anti-patterns và bài học thực tế

1. Đọc cost như mili-giây. Cost 75.61 chạy 1 ms; cost 228.88 chạy 43,8 ms (ví dụ 6, cache lạnh). Không quy đổi được.
2. So cost giữa 2 câu khác nhau rồi kết luận câu nào nhanh hơn. Cost chỉ so giữa các plan của cùng một câu.
3. Quên nhân loops (ví dụ 6) — và với node song song, quên nhân số process (ví dụ 2: rows=14660.00 loops=2).
4. Chỉ nhìn Execution Time, bỏ qua dòng Trigger / JIT / Planning (ví dụ 8: 95% thời gian nằm ngoài cây).
5. Chạy EXPLAIN trên DB dev ít dữ liệu, hoặc chạy đúng 1 lần lúc cache lạnh rồi kết luận theo ms.
6. EXPLAIN ANALYZE một câu UPDATE trên production không có BEGIN/ROLLBACK.
7. Thấy Seq Scan là hoảng. Seq Scan là lựa chọn đúng khi lấy phần lớn bảng (ví dụ 7 bài 2: 85% bảng thì Seq Scan nhanh hơn mọi index).
8. Dán plan chứa dữ liệu nhạy cảm lên trang vẽ plan công khai.

Postmortem công khai — GitLab.com, 09/03/2021 (gitlab-com/gl-infra/production#3875): statement timeout hàng loạt làm cạn pool kết nối, site gần như sập khoảng 2,5 giờ. Nguyên nhân gốc theo issue: optimizer của PostgreSQL 11 ước lượng quá cao số dòng của vòng ngoài Nested Loop khi một IN (...) trả hơn 200 dòng (lỗi đã sửa ở PostgreSQL 12). Cách khắc phục ngay lúc đó là chạy ANALYZE namespaces; để planner có thống kê mới. Bài học đúng với cả series: plan chạy theo ước lượng; khi một câu đột nhiên chậm mà code không đổi, câu hỏi đầu tiên là "ước lượng ở node nào đã đổi, và vì sao".

Điều docs không nói rõ mà lab thấy: Planning Time của câu đầu mỗi kết nối cao gấp chục lần; Buffers của node cha đã gồm node con nên đừng cộng dồn; rows=0.33 ở node song song là chuyện bình thường (trung bình mỗi process).

10. Câu hỏi phụ interviewer hay hỏi tiếp

Cost trong EXPLAIN là gì, đơn vị gì?

Giá ước lượng theo đơn vị ảo, gốc là "đọc 1 trang tuần tự" (seq_page_cost = 1). Tính từ số trang, số dòng ước lượng và các hằng số cost. Chỉ so được giữa các plan của cùng một câu.

Startup cost khác total cost thế nào, khi nào quan trọng?

Startup là giá tới dòng đầu, total là giá chạy hết. Quan trọng khi có LIMIT, EXISTS, cursor — planner chỉ "trả tiền" cho phần đầu, nên plan có startup thấp được ưu tiên (và đó là gốc của bẫy LIMIT).

EXPLAIN ANALYZE khác EXPLAIN chỗ nào, rủi ro gì?

Nó chạy thật: DML đổi dữ liệu, trigger chạy, khoá được giữ. Kết quả không gửi đi nên thiếu thời gian mạng và serialize; lại có overhead đo giờ. Bọc DML trong BEGIN/ROLLBACK, đặt statement_timeout.

Thấy actual time=0.005..0.006 rows=2.50 loops=360583 thì đọc sao?

Mỗi lần ~0,006 ms và 2,5 dòng; tổng ~2 giây và ~900 nghìn dòng. Time/rows là trung bình mỗi loop; Buffers là tổng.

Plan chậm, bắt đầu từ đâu?

Từ node sâu nhất: so rows ước lượng với rows thật. Node thấp nhất lệch ≥ 10 lần thường là gốc. Không lệch thì tìm node tốn nhất (time × loops, Buffers, Rows Removed by Filter).

Vì sao câu nhanh trong psql mà chậm trong app?

Khác tham số phiên, khác giá trị tham số (generic plan), khác kiểu dữ liệu tham số, cache khác, app phải nhận và serialize nhiều dữ liệu, hoặc chờ lock. Bắt plan thật của app bằng auto_explain.

Buffers shared read có nghĩa là đọc đĩa không?

Không chắc. Chỉ nghĩa là không có trong shared_buffers; có thể tới từ page cache của OS. Xem I/O Timings để biết (ví dụ 7: cùng 1 518 trang read, 15,8 ms hay 101,8 ms).

PostgreSQL 18 đổi gì khi đọc plan?

BUFFERS tự bật với ANALYZE, rows có số lẻ, thêm Index Searches, Disabled: true thay cho cost phạt, thêm dòng Storage cho Material/CTE/WindowAgg.

Phụ lục: lab đã chạy

Môi trường: PostgreSQL 18.4 (postgres:18-alpine, container my-test-pgdb của repo postgresql-learning, replica my-test-pgstandby), database riêng tmp_qplan; Laravel 12.64 / PHP 8.3.33 (image laravel-app, project laravel-livewire mount read-only). Cấu hình như mục "Kiểm chứng"; autovacuum tắt trên các bảng lab để thống kê không tự đổi giữa các ví dụ.

cd labs/query-plan
./run.sh setup.sql                 # dựng tmp_qplan (~2 phút, ~1,1 GB)
./run.sh 01-*.sql                  # các ví dụ SQL của bài này → out/01-*.txt
./01-07-cold-cache.sh              # ví dụ 7 (cần quyền root trên host để posix_fadvise)
./01-11-replica.sh                 # ví dụ 11, chạy trên replica
laravel/run.sh 4242                # SQL mà Eloquent sinh ra + explain() → out/laravel-eloquent.txt
# dọn: docker exec my-test-pgdb psql -U postgres -c 'DROP DATABASE tmp_qplan'

Chưa verify: hành vi trên PostgreSQL 16/17 (bảng ở mục 4 dựa vào release notes); pg_hint_plan; các trang vẽ plan (không gửi dữ liệu lab ra ngoài).