Khi plan sai — ước lượng lệch, thống kê, generic plan, cost model và vận hành

Bốn bài trước dạy đọc plan. Bài này dạy chẩn đoán và sửa khi plan sai, và cách vận hành để biết plan sai trước khi người dùng biết: pg_stat_statements, auto_explain, quy trình lúc 3 giờ sáng. Mỗi nguyên nhân có lab tái hiện trên dữ liệu e-commerce của series (bài 1), kèm cách sửa và cái giá của cách sửa.

Thuật ngữ dùng trong bài
misestimate (lệch ước lượng: rows ước lượng khác xa rows thật) · stale statistics (thống kê cũ — dữ liệu đã đổi mà chưa ANALYZE) · extended statistics (thống kê nhiều cột hoặc trên biểu thức, tạo bằng CREATE STATISTICS: dependencies, ndistinct, mcv) · statistics target (cỡ mẫu và độ chi tiết của thống kê; mặc định 100 → mẫu 30 000 dòng) · prepared statement (câu lệnh được parse một lần, chạy nhiều lần với tham số) · custom plan / generic plan (plan lập riêng cho giá trị tham số cụ thể / plan dùng chung cho mọi giá trị, thấy $1 trong plan) · plan_cache_mode (chọn custom/generic tự động hay ép) · cost constants (hằng số giá: random_page_cost…) · JIT (Just-In-Time — biên dịch biểu thức ra mã máy bằng LLVM lúc chạy) · auto_explain (module ghi plan của câu chậm vào log) · pg_stat_statements (thống kê thời gian/số lần chạy theo từng dạng câu) · queryid (mã băm dạng câu, nối pg_stat_statements với EXPLAIN VERBOSE) · DSM (dynamic shared memory — bộ nhớ dùng chung giữa các process song song, nằm trong /dev/shm).

Kiểm chứng: tài liệu PostgreSQL 18 qua Context7 (planner-stats 14.2.2 Extended Statistics, sql-createstatistics, multivariate-statistics-examples, sql-explain GENERIC_PLAN, runtime-config-query plan_cache_mode và cost constants, auto-explain, plpgsql-implementation 41.11.2 Plan Caching, jit-decision). Plan là output thật trên PostgreSQL 18.4, database tmp_qplan, file labs/query-plan/05-*.sql|sh, output labs/query-plan/out/05-*.txt. Laravel 12.64 + PHP 8.3 (pdo_pgsql) cho phần prepared statement. Lưu ý: bài này chạy ANALYZE và CREATE STATISTICS trên bảng dùng chung, nên các số ước lượng ở đây có thể lệch nhẹ so với cùng câu ở bài 1–4 (ANALYZE lấy mẫu ngẫu nhiên) — mỗi output tự nhất quán với chính nó.

Trả lời 30 giây

Plan sai có ba nhóm nguyên nhân. (1) Ước lượng rows sai — thống kê cũ sau khi nhập lô (lab: pending ước lượng 11 368, thật 309 639), các cột phụ thuộc nhau (SG + Singapore: 1 651 so với 59 185 — CREATE STATISTICS sửa thành ~53 nghìn), biểu thức không có thống kê (JSON đoán 0,5% — thống kê trên biểu thức sửa được), n_distinct đoán thấp (111 519 so với 199 647). (2) Ước lượng đúng nhưng cost model sai — random_page_cost = 4 trên dữ liệu nằm trong RAM: Hash Join 1,4 giây, đặt 1.1 thì Nested Loop 0,16 giây; JIT bật theo cost, có câu tốn 380 ms chỉ để biên dịch. (3) Plan đúng cho giá trị này, sai cho giá trị khác — prepared statement chạy tới lần thứ 6 có thể chuyển sang generic plan (thấy $1 trong plan). Laravel + pdo_pgsql tạo prepared statement mới cho mỗi câu nên luôn custom plan (lab). Vận hành: pg_stat_statements tìm câu đáng xem, auto_explain bắt plan thật (nhưng log_timing làm mọi câu chậm gấp đôi), và EXPLAIN thường cho câu đang chạy mãi không xong.

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

Planner chỉ tốt bằng đầu vào của nó. Plan là kết quả của bốn thứ, và cả bốn đều có thể sai:

Thống kê — lấy mẫu, có thể cũ, chỉ mô tả từng cột riêng lẻ.
Giả định — các điều kiện độc lập nhau, giá trị rải đều trong khoảng, biểu thức lạ thì đoán một tỉ lệ cố định.
Hằng số cost — mô hình phần cứng (đọc ngẫu nhiên đắt gấp 4 đọc tuần tự…), có thể không khớp máy thật.
Giá trị tham số — biết (custom plan) hay không biết (generic plan).

Nghiên cứu học thuật nổi tiếng nhất về chuyện này (Leis và cộng sự, "How Good Are Query Optimizers, Really?", VLDB 2015) kết luận lỗi ước lượng số dòng là nguồn gây plan dở lớn hơn nhiều so với lỗi của cost model — bài này không dựng lại benchmark đó, nhưng lab bên dưới cho thấy cả hai loại lỗi. PostgreSQL chọn triết lý không có query hint trong lõi: thay vì ép plan, cho planner thông tin tốt hơn (thống kê mở rộng, chỉnh statistics target, chỉnh hằng số cost). Extension pg_hint_plan tồn tại cho ai cần ép — lab không cài, chưa verify.

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

Plan = f(thống kê, giả định, hằng số cost, giá trị tham số). Chẩn đoán là tìm xem đầu vào nào sai. Câu hỏi đầu tiên luôn là: ở node lá, rows ước lượng có khớp rows thật không?

flowchart TD
  A["Plan chậm: EXPLAIN (ANALYZE, BUFFERS)"] --> B{"Node lá: rows ước lượng
lệch thật từ 10 lần?"}
  B -->|"Có"| C{"Lệch kiểu gì?"}
  C --> C1["Dữ liệu vừa đổi nhiều
→ thống kê cũ → ANALYZE"]
  C --> C2["Nhiều điều kiện trên cột liên quan
→ CREATE STATISTICS"]
  C --> C3["Điều kiện trên biểu thức, JSON
→ thống kê/index biểu thức"]
  C --> C4["Giá trị = tham số $1 trong plan
→ generic plan → plan_cache_mode"]
  C --> C5["Cột phân bố lệch, n_distinct sai
→ SET STATISTICS / n_distinct"]
  B -->|"Không"| D{"Ước lượng đúng mà chọn plan chậm?"}
  D --> D1["Đọc ngẫu nhiên bị đánh giá sai
→ random_page_cost, effective_cache_size"]
  D --> D2["Dòng JIT tốn nhiều
→ jit_above_cost / tắt JIT"]
  D --> D3["Batches, external merge
→ work_mem theo role"]

Hình 1 — Lỗi ở node lá đi lên cả cây: số dòng của join được tính từ số dòng của lá, rồi loại join được chọn từ đó. Số liệu là output thật (bài 3 ví dụ 10 và ví dụ 2 dưới đây). Phần "nhưng 3,6 s" là bài học thứ hai của bài: sửa ước lượng là một nửa, nửa kia là hằng số cost.

3. Internals và quyết định thiết kế — từng nguyên nhân, từng cách sửa

3.1. Ví dụ 1 — các cột phụ thuộc nhau: `CREATE STATISTICS`

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

Finalize Aggregate  (cost=51244.81..51244.82 rows=1 width=8) (actual time=193.081..200.904 rows=1.00 loops=1)
  ->  Gather  (cost=51244.60..51244.81 rows=2 width=8) (actual time=193.070..200.895 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=50244.60..50244.61 rows=1 width=8) (actual time=183.827..183.828 rows=1.00 loops=3)
              ->  Parallel Seq Scan on orders  (cost=0.00..50242.88 rows=688 width=0) (actual time=0.256..182.149 rows=19728.33 loops=3)
                    Filter: ((ship_country = 'SG'::text) AND (ship_city = 'Singapore'::text))
                    Rows Removed by Filter: 646938
Planning Time: 1.363 ms
Execution Time: 201.163 ms

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT count(*) FROM orders WHERE ship_country = 'TH' AND ship_city = 'Hà Nội';

Finalize Aggregate  (cost=51280.31..51280.32 rows=1 width=8) (actual time=172.481..183.388 rows=1.00 loops=1)
  ->  Gather  (cost=51280.10..51280.31 rows=2 width=8) (actual time=172.262..183.379 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=50280.10..50280.11 rows=1 width=8) (actual time=165.916..165.917 rows=1.00 loops=3)
              ->  Parallel Seq Scan on orders  (cost=0.00..50242.88 rows=14889 width=0) (actual time=165.910..165.911 rows=0.00 loops=3)
                    Filter: ((ship_country = 'TH'::text) AND (ship_city = 'Hà Nội'::text))
                    Rows Removed by Filter: 666667
Planning Time: 0.102 ms
Execution Time: 183.569 ms

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT ship_country, ship_city, count(*) FROM orders GROUP BY ship_country, ship_city;

HashAggregate  (cost=72742.65..72743.61 rows=96 width=22) (actual time=977.625..977.630 rows=16.00 loops=1)
  Group Key: ship_country, ship_city
  Batches: 1  Memory Usage: 32kB
  ->  Seq Scan on orders  (cost=0.00..57742.80 rows=1999980 width=14) (actual time=0.483..355.140 rows=2000000.00 loops=1)
Planning Time: 2.567 ms
Execution Time: 977.691 ms

Ba kiểu sai cùng một gốc — giả định độc lập: (a) SG + Singapore ước lượng 688 mỗi process (~1 651 tổng), thật 59 185 — thấp 36 lần; (b) Thái Lan + Hà Nội — tổ hợp không thể có — ước lượng ~36 nghìn, thật 0 — cao vô hạn; (c) GROUP BY 2 cột ước lượng 96 nhóm (6 nước × 16 thành phố), thật 16. Tạo thống kê cho cặp cột:

CREATE STATISTICS orders_ship_stats (dependencies, ndistinct, mcv) ON ship_country, ship_city FROM orders;
CREATE STATISTICS
ANALYZE orders;
ANALYZE
SELECT statistics_name, n_distinct, dependencies
FROM pg_stats_ext WHERE statistics_name = 'orders_ship_stats';
  statistics_name  |  n_distinct  |               dependencies
-------------------+--------------+------------------------------------------
 orders_ship_stats | {"7, 8": 16} | {"7 => 8": 0.026533, "8 => 7": 1.000000}
(1 row)

Finalize Aggregate  (cost=51298.30..51298.31 rows=1 width=8) (actual time=193.931..204.396 rows=1.00 loops=1)
  ->  Gather  (cost=51298.09..51298.30 rows=2 width=8) (actual time=193.627..204.385 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=50298.09..50298.10 rows=1 width=8) (actual time=185.030..185.031 rows=1.00 loops=3)
              ->  Parallel Seq Scan on orders  (cost=0.00..50242.81 rows=22111 width=0) (actual time=0.234..183.025 rows=19728.33 loops=3)
                    Filter: ((ship_country = 'SG'::text) AND (ship_city = 'Singapore'::text))
                    Rows Removed by Filter: 646938
Planning Time: 0.764 ms
Execution Time: 204.511 ms

Aggregate  (cost=51242.92..51242.93 rows=1 width=8) (actual time=191.022..200.963 rows=1.00 loops=1)
  ->  Gather  (cost=1000.00..51242.91 rows=1 width=0) (actual time=191.015..200.954 rows=0.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Parallel Seq Scan on orders  (cost=0.00..50242.81 rows=1 width=0) (actual time=182.737..182.738 rows=0.00 loops=3)
              Filter: ((ship_country = 'TH'::text) AND (ship_city = 'Hà Nội'::text))
              Rows Removed by Filter: 666667
Planning Time: 0.599 ms
Execution Time: 201.022 ms

HashAggregate  (cost=72742.48..72742.64 rows=16 width=22) (actual time=1010.055..1010.059 rows=16.00 loops=1)
  Group Key: ship_country, ship_city
  Batches: 1  Memory Usage: 32kB
  ->  Seq Scan on orders  (cost=0.00..57742.70 rows=1999970 width=14) (actual time=0.462..373.870 rows=2000000.00 loops=1)
Planning Time: 0.230 ms
Execution Time: 1010.099 ms

Đọc pg_stats_ext: cột 7 là ship_country, cột 8 là ship_city; "8 => 7": 1.000000 nghĩa là thành phố quyết định hoàn toàn quốc gia; n_distinct {"7, 8": 16} — 16 tổ hợp. Sau đó: (a) 22 111 mỗi process (~53 nghìn tổng, thật 59 185), (b) 1 dòng, (c) đúng 16 nhóm. Giới hạn, cũng có lab:

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT count(*) FROM orders WHERE status = 'pending' AND created_at >= '2026-09-16';

Aggregate  (cost=1547.12..1547.13 rows=1 width=8) (actual time=6.345..6.347 rows=1.00 loops=1)
  ->  Index Scan using orders_created_at_index on orders  (cost=0.43..1546.75 rows=150 width=0) (actual time=0.037..5.876 rows=9639.00 loops=1)
        Index Cond: (created_at >= '2026-09-16 00:00:00+00'::timestamp with time zone)
        Filter: (status = 'pending'::order_status)
        Rows Removed by Filter: 22274
        Index Searches: 1
Planning Time: 0.798 ms
Execution Time: 6.388 ms

Đơn pending chỉ có ở 2 tuần cuối — tương quan giữa trạng thái và khoảng thời gian. Thống kê mở rộng trên (status, created_at) không giúp: dependencies chỉ dùng cho điều kiện bằng, mcv vô dụng với cột thời gian gần như không lặp. Ước lượng 150, thật 9 639. Loại tương quan này sửa bằng index phù hợp (partial index cho đơn đang mở — bài 2) chứ không bằng thống kê.

3.2. Ví dụ 2 — biểu thức không có thống kê

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT count(*) FROM orders WHERE meta->>'source' = 'pos';

Finalize Aggregate  (cost=51253.57..51253.58 rows=1 width=8) (actual time=923.904..934.875 rows=1.00 loops=1)
  ->  Gather  (cost=51253.36..51253.57 rows=2 width=8) (actual time=919.589..934.863 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=50253.36..50253.37 rows=1 width=8) (actual time=900.278..900.279 rows=1.00 loops=3)
              ->  Parallel Seq Scan on orders  (cost=0.00..50242.94 rows=4167 width=0) (actual time=1.984..896.975 rows=33221.33 loops=3)
                    Filter: ((meta ->> 'source'::text) = 'pos'::text)
                    Rows Removed by Filter: 633445
Planning Time: 1.757 ms
Execution Time: 935.121 ms

CREATE STATISTICS orders_meta_source_stats ON ((meta->>'source')) FROM orders;
CREATE STATISTICS
ANALYZE orders;
ANALYZE
Finalize Aggregate  (cost=51350.90..51350.91 rows=1 width=8) (actual time=374.836..382.752 rows=1.00 loops=1)
  ->  Gather  (cost=51350.68..51350.89 rows=2 width=8) (actual time=373.694..382.740 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=50350.68..50350.69 rows=1 width=8) (actual time=338.877..338.878 rows=1.00 loops=3)
              ->  Parallel Seq Scan on orders  (cost=0.00..50243.18 rows=43000 width=0) (actual time=0.572..336.384 rows=33221.33 loops=3)
                    Filter: ((meta ->> 'source'::text) = 'pos'::text)
                    Rows Removed by Filter: 633445
Planning Time: 0.660 ms
Execution Time: 382.875 ms

Không có thống kê, planner đoán 0,5% cho meta->>'source' = 'pos' (4 167 mỗi process ≈ 10 000). CREATE STATISTICS trên biểu thức (PostgreSQL 14+) + ANALYZE: 43 000 mỗi process ≈ 103 nghìn, thật 99 664. Expression index cũng cho thống kê như vậy (sau ANALYZE) nhưng tốn thêm chỗ và chi phí ghi; chỉ cần ước lượng đúng thì CREATE STATISTICS rẻ hơn. Câu join ở bài 3 ví dụ 10 giờ thế nào?

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT count(*), sum(i.quantity)
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.meta->>'source' = 'app' AND o.created_at >= '2026-01-01';

Aggregate  (cost=191409.00..191409.01 rows=1 width=16) (actual time=3553.728..3553.733 rows=1.00 loops=1)
  ->  Hash Join  (cost=36843.83..186956.36 rows=890527 width=4) (actual time=1921.043..3500.294 rows=900761.00 loops=1)
        Hash Cond: (i.order_id = o.id)
        ->  Seq Scan on order_items i  (cost=0.00..86765.40 rows=5000040 width=12) (actual time=0.169..923.590 rows=5000000.00 loops=1)
        ->  Hash  (cost=30999.17..30999.17 rows=356213 width=8) (actual time=450.356..450.358 rows=360583.00 loops=1)
              Buckets: 262144  Batches: 4  Memory Usage: 5563kB
              ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..30999.17 rows=356213 width=8) (actual time=0.147..315.469 rows=360583.00 loops=1)
                    Index Cond: (created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone)
                    Filter: ((meta ->> 'source'::text) = 'app'::text)
                    Rows Removed by Filter: 239791
                    Index Searches: 1
Planning Time: 2.039 ms
Execution Time: 3555.438 ms

Ước lượng giờ gần đúng (356 213 so với 360 583), planner đổi sang Hash Join — và câu chậm hơn: 3,6 giây so với 1,5 giây của Nested Loop khi ước lượng còn sai (Hình 1). Sửa ước lượng không sai; vấn đề là phần còn lại của phép tính — cost model (ví dụ 7).

3.3. Ví dụ 3 — thống kê cũ sau khi nhập lô

Job đồng bộ 300 nghìn đơn pending từ sàn TMĐT, query ngay sau khi nhập (trong cùng transaction, chưa ANALYZE):

BEGIN;
BEGIN
INSERT INTO orders (user_id, order_number, status, payment_method, total_cents, ship_country, ship_city,
                    meta, created_at, updated_at)
SELECT 1 + (g % 200000), 'MKP-' || lpad(g::text, 8, '0'), 'pending', 'wallet', 150000, 'VN', 'Hà Nội',
       '{"source": "marketplace"}', timestamptz '2026-10-01' + g * interval '8 seconds',
       timestamptz '2026-10-01' + g * interval '8 seconds'
FROM generate_series(1, 300000) g;
INSERT 0 300000

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT count(*) FROM orders WHERE created_at >= '2026-10-01';

Aggregate  (cost=606.95..606.96 rows=1 width=8) (actual time=83.504..83.524 rows=1.00 loops=1)
  ->  Index Only Scan using orders_created_at_index on orders  (cost=0.43..564.17 rows=17111 width=0) (actual time=0.112..66.119 rows=300000.00 loops=1)
        Index Cond: (created_at >= '2026-10-01 00:00:00+00'::timestamp with time zone)
        Heap Fetches: 300000
        Index Searches: 1
Planning Time: 0.780 ms
Execution Time: 83.575 ms

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT count(*) FROM orders WHERE status = 'pending';

Aggregate  (cost=71835.86..71835.87 rows=1 width=8) (actual time=535.495..535.497 rows=1.00 loops=1)
  ->  Seq Scan on orders  (cost=0.00..71807.44 rows=11368 width=0) (actual time=5.789..517.732 rows=309639.00 loops=1)
        Filter: (status = 'pending'::order_status)
        Rows Removed by Filter: 1990361
Planning Time: 0.255 ms
Execution Time: 535.534 ms

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT u.country, count(*)
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
GROUP BY u.country;

GroupAggregate  (cost=81545.07..81630.39 rows=6 width=11) (actual time=544.226..589.818 rows=6.00 loops=1)
  Group Key: u.country
  ->  Sort  (cost=81545.07..81573.49 rows=11368 width=3) (actual time=540.269..567.262 rows=309639.00 loops=1)
        Sort Key: u.country
        Sort Method: external merge  Disk: 2128kB
        ->  Hash Join  (cost=7875.00..80779.28 rows=11368 width=3) (actual time=94.217..473.638 rows=309639.00 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=0.00..71807.44 rows=11368 width=8) (actual time=5.182..222.948 rows=309639.00 loops=1)
                    Filter: (status = 'pending'::order_status)
                    Rows Removed by Filter: 1990361
              ->  Hash  (cost=4398.00..4398.00 rows=200000 width=11) (actual time=87.218..87.220 rows=200000.00 loops=1)
                    Buckets: 262144  Batches: 2  Memory Usage: 6754kB
                    ->  Seq Scan on users u  (cost=0.00..4398.00 rows=200000 width=11) (actual time=0.568..26.750 rows=200000.00 loops=1)
Planning Time: 2.140 ms
Execution Time: 591.866 ms

created_at >= '2026-10-01': mọi giá trị mới nằm ngoài histogram cũ (max cũ 30/9). Planner vẫn ước lượng được 17 111 chứ không phải 1 — nó dò index để lấy giá trị lớn nhất thật khi điều kiện vượt ra ngoài histogram (hành vi nội bộ của selfuncs, chưa đối chiếu docs) — nhưng vẫn thấp 17 lần.
status = 'pending': MCV cũ nói 0,46% → 11 368; thật 309 639 (27 lần).
Join: ước lượng nhỏ nên planner chọn Sort + GroupAggregate, và Sort tràn đĩa vì nhận 309 nghìn dòng thay vì 11 nghìn.

ANALYZE orders;
ANALYZE
Aggregate  (cost=72724.23..72724.24 rows=1 width=8) (actual time=270.939..270.941 rows=1.00 loops=1)
  ->  Seq Scan on orders  (cost=0.00..71946.25 rows=311193 width=0) (actual time=1.927..256.552 rows=309639.00 loops=1)
        Filter: (status = 'pending'::order_status)
        Rows Removed by Filter: 1990361
Planning Time: 0.559 ms
Execution Time: 270.980 ms

HashAggregate  (cost=85603.12..85603.18 rows=6 width=11) (actual time=646.900..646.907 rows=6.00 loops=1)
  Group Key: u.country
  Batches: 1  Memory Usage: 32kB
  ->  Hash Join  (cost=7875.00..84047.15 rows=311193 width=3) (actual time=81.049..598.791 rows=309639.00 loops=1)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (cost=0.00..71946.25 rows=311193 width=8) (actual time=2.932..343.993 rows=309639.00 loops=1)
              Filter: (status = 'pending'::order_status)
              Rows Removed by Filter: 1990361
        ->  Hash  (cost=4398.00..4398.00 rows=200000 width=11) (actual time=76.314..76.316 rows=200000.00 loops=1)
              Buckets: 262144  Batches: 2  Memory Usage: 6754kB
              ->  Seq Scan on users u  (cost=0.00..4398.00 rows=200000 width=11) (actual time=0.060..23.210 rows=200000.00 loops=1)
Planning Time: 0.600 ms
Execution Time: 648.564 ms

Sau ANALYZE (chạy được ngay trong transaction — nó thấy dòng mình vừa chèn): ước lượng đúng, plan gom nhóm đổi sang HashAggregate. Ba điều thực tế: (1) autovacuum chỉ tự ANALYZE khi số dòng đổi vượt autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × số dòng (mặc định 50 + 10% — với 2 triệu dòng là 200 nghìn), và chỉ sau khi transaction commit; câu chạy ngay sau lô nhập luôn dùng thống kê cũ → job nhập dữ liệu nên tự gọi ANALYZE bang ở cuối. (2) ANALYZE ghi số dòng/số trang vào pg_class tại chỗ, không rollback được — file lab phải ANALYZE lại sau ROLLBACK. (3) VACUUM sau một lần xoá lớn cũng làm lệch reltuples: lần chạy tay trong lab, VACUUM chỉ quét ~13% số trang (phần vừa dọn) rồi ngoại suy ra 1 747 487 dòng cho một bảng 2 triệu dòng — đến lượt ANALYZE mới sửa lại.

3.4. Ví dụ 4 — n_distinct đoán thấp trên cột phân bố lệch

SELECT n_distinct AS n_distinct_uoc_luong,
       (SELECT count(DISTINCT user_id) FROM orders) AS so_khach_thuc_te
FROM pg_stats WHERE tablename = 'orders' AND attname = 'user_id';
 n_distinct_uoc_luong | so_khach_thuc_te
----------------------+------------------
               111519 |           199647
(1 row)

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT * FROM orders WHERE user_id = 123456;

Bitmap Heap Scan on orders  (cost=4.57..75.69 rows=18 width=116) (actual time=0.442..0.529 rows=6.00 loops=1)
  Recheck Cond: (user_id = 123456)
  Heap Blocks: exact=7
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0) (actual time=0.340..0.341 rows=7.00 loops=1)
        Index Cond: (user_id = 123456)
        Index Searches: 1
Planning Time: 0.496 ms
Execution Time: 0.723 ms

Khách VIP có hàng nghìn đơn, đa số khách có vài đơn. Mẫu 30 000 dòng thấy VIP lặp đi lặp lại, ít thấy khách thường → ước lượng 111 519 khách, thật 199 647. Hệ quả: mọi khách "thường" bị ước lượng ~18 đơn (thật 6). Tăng mẫu cho riêng cột này, hoặc ghi đè:

ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000;
ALTER TABLE
ANALYZE orders (user_id);
WARNING:  statistics object "public.orders_ship_stats" could not be computed for relation "public.orders"
ANALYZE
SELECT n_distinct FROM pg_stats WHERE tablename = 'orders' AND attname = 'user_id';
 n_distinct
------------
     169202
(1 row)
Bitmap Heap Scan on orders  (cost=4.51..48.12 rows=11 width=116) (actual time=0.054..0.066 rows=6.00 loops=1)
  Recheck Cond: (user_id = 123456)
  Heap Blocks: exact=7
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.51 rows=11 width=0) (actual time=0.034..0.034 rows=7.00 loops=1)
        Index Cond: (user_id = 123456)
        Index Searches: 1
Planning Time: 0.833 ms
Execution Time: 0.111 ms

ALTER TABLE orders ALTER COLUMN user_id SET (n_distinct = -0.1);
ALTER TABLE
ANALYZE orders (user_id);
WARNING:  statistics object "public.orders_ship_stats" could not be computed for relation "public.orders"
ANALYZE
SELECT n_distinct FROM pg_stats WHERE tablename = 'orders' AND attname = 'user_id';
 n_distinct
------------
       -0.1
(1 row)
Index Scan using orders_user_id_index on orders  (cost=0.43..40.58 rows=9 width=116) (actual time=0.058..0.071 rows=6.00 loops=1)
  Index Cond: (user_id = 123456)
  Index Searches: 1
Planning Time: 0.600 ms
Execution Time: 0.094 ms

SET STATISTICS 1000 (mẫu 300 nghìn dòng): 169 202. SET (n_distinct = -0.1) (số âm = tỉ lệ so với số dòng): đúng 10% số dòng, ước lượng 9 đơn, plan đổi từ Bitmap sang Index Scan. Bẫy kèm theo, lab gặp thật: ANALYZE orders (user_id) chỉ phân tích một cột nên in WARNING: statistics object "public.orders_ship_stats" could not be computed — thống kê mở rộng của ví dụ 1 không được tính lại trong lần ANALYZE đó. Đây chính là loại lỗi trong postmortem GoCardless (mục 9): n_distinct sai làm planner tưởng mỗi giá trị có hàng nghìn dòng, rồi chọn đi khoá chính theo thứ tự + LIMIT.

3.5. Ví dụ 5 — prepared statement và generic plan

PostgreSQL lập custom plan cho 5 lần EXECUTE đầu của một prepared statement (thấy giá trị thật). Từ lần thứ 6, nó so giá của generic plan (không phụ thuộc giá trị) với giá trung bình của các custom plan đã lập; generic rẻ hơn hoặc ngang thì dùng generic từ đó về sau — đỡ tốn thời gian lập plan.

BEGIN;
BEGIN
CREATE INDEX orders_status_index ON orders (status);
CREATE INDEX
PREPARE by_status(order_status) AS
  SELECT count(*), sum(total_cents) FROM orders WHERE status = $1;
PREPARE
…  (5 lần EXECUTE by_status('delivered'))
SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;
   name    | generic_plans | custom_plans
-----------+---------------+--------------
 by_status |             0 |            5
(1 row)

EXPLAIN (ANALYZE, BUFFERS OFF) EXECUTE by_status('pending');

Finalize Aggregate  (cost=50341.25..50341.26 rows=1 width=16) (actual time=18.399..23.585 rows=1.00 loops=1)
  ->  Gather  (cost=50341.03..50341.24 rows=2 width=16) (actual time=16.825..23.571 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=49341.03..49341.04 rows=1 width=16) (actual time=5.193..5.194 rows=1.00 loops=3)
              ->  Parallel Bitmap Heap Scan on orders  (cost=3715.10..48646.77 rows=138853 width=4) (actual time=1.673..4.728 rows=3213.00 loops=3)
                    Recheck Cond: (status = $1)
                    Heap Blocks: exact=592
                    Worker 1:  Heap Blocks: exact=11
                    ->  Bitmap Index Scan on orders_status_index  (cost=0.00..3631.79 rows=333248 width=0) (actual time=4.075..4.076 rows=9639.00 loops=1)
                          Index Cond: (status = $1)
                          Index Searches: 1
Planning Time: 0.395 ms
Execution Time: 23.752 ms

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;
   name    | generic_plans | custom_plans
-----------+---------------+--------------
 by_status |             1 |            5
(1 row)

SET plan_cache_mode = force_custom_plan;
SET
Aggregate  (cost=22069.70..22069.71 rows=1 width=16) (actual time=4.647..4.648 rows=1.00 loops=1)
  ->  Index Scan using orders_status_index on orders  (cost=0.43..22014.38 rows=11064 width=4) (actual time=0.050..3.281 rows=9639.00 loops=1)
        Index Cond: (status = 'pending'::order_status)
        Index Searches: 1
Planning Time: 0.149 ms
Execution Time: 4.695 ms

Dấu hiệu nhận ra generic plan: $1 trong plan thay vì giá trị thật, và pg_prepared_statements.generic_plans tăng. Generic plan ước lượng rows=333248 = 2 triệu / 6 giá trị — trung bình, không đúng cho giá trị nào. Vì sao chuyển? Lab đo cost của từng phương án:

Finalize Aggregate  (cost=52697.28..52697.29 rows=1 width=16)
  ->  Gather  (cost=52697.06..52697.27 rows=2 width=16)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=51697.06..51697.07 rows=1 width=16)
              ->  Parallel Seq Scan on orders  (cost=0.00..48159.59 rows=707495 width=4)
                    Filter: (status = 'delivered'::order_status)

Aggregate  (cost=18331.55..18331.56 rows=1 width=16)
  ->  Index Scan using orders_status_index on orders  (cost=0.43..18284.55 rows=9400 width=4)
        Index Cond: (status = 'pending'::order_status)

Finalize Aggregate  (cost=44889.50..44889.51 rows=1 width=16)
  ->  Gather  (cost=44889.28..44889.49 rows=2 width=16)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=43889.28..43889.29 rows=1 width=16)
              ->  Parallel Bitmap Heap Scan on orders  (cost=3715.74..43194.84 rows=138888 width=4)
                    Recheck Cond: (status = $1)
                    ->  Bitmap Index Scan on orders_status_index  (cost=0.00..3632.41 rows=333331 width=0)
                          Index Cond: (status = $1)

Năm lần đầu với 'delivered': custom plan giá ~52 697. Generic plan giá ~44 890 — rẻ hơn → chuyển. Từ đó 'pending' cũng chạy generic: 23,8 ms thay vì 4,7 ms (gấp 5), và với câu có LIMIT + partial index như bài 2 ví dụ 12 thì 465 ms so với 0,54 ms — gần 900 lần. Ngược lại, 5 lần đầu với 'pending': custom ~18 332 < generic ~44 890 → giữ custom:

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements ORDER BY name;
    name     | generic_plans | custom_plans
-------------+---------------+--------------
 by_status   |             1 |            6
 by_status_2 |             0 |            6
(2 rows)

Tức là plan mà một phiên dùng mãi về sau phụ thuộc vào 5 giá trị đầu tiên nó gặp sau khi kết nối — kiểu lỗi "lúc nhanh lúc chậm, restart app thì hết" rất khó tái hiện. Xem trước generic plan không cần PREPARE (PG 16+): EXPLAIN (GENERIC_PLAN) ... $1 (bài 1, ví dụ 10).

Laravel có bị không? Lab với pdo_pgsql (Laravel mặc định không giả lập prepare):

== Output của PHP
PDO::ATTR_EMULATE_PREPARES = false
lần 1 (delivered): count=35, pg_prepared_statements đang có 1 statement
    pdo_stmt_00000003  generic=1 custom=0  select name, statement, generic_plans, custom_plans from pg_
lần 2 (delivered): count=35, pg_prepared_statements đang có 1 statement
    pdo_stmt_00000005  generic=1 custom=0  select name, statement, generic_plans, custom_plans from pg_
lần 3 (delivered): count=35, pg_prepared_statements đang có 1 statement
    pdo_stmt_00000007  generic=1 custom=0  select name, statement, generic_plans, custom_plans from pg_
…
== Log server (log_min_duration_statement = 0), lọc các dòng của query count
LOG:  duration: 6.952 ms  parse pdo_stmt_00000002: select count(*) as aggregate from "orders" where "status" = $1 and "user_id" = $2
LOG:  duration: 2.978 ms  bind pdo_stmt_00000002: select count(*) as aggregate from "orders" where "status" = $1 and "user_id" = $2
LOG:  duration: 0.213 ms  execute pdo_stmt_00000002: select count(*) as aggregate from "orders" where "status" = $1 and "user_id" = $2
LOG:  duration: 0.146 ms  parse pdo_stmt_00000004: select count(*) as aggregate from "orders" where "status" = $1 and "user_id" = $2
LOG:  duration: 0.296 ms  bind pdo_stmt_00000004: select count(*) as aggregate from "orders" where "status" = $1 and "user_id" = $2
LOG:  duration: 0.150 ms  execute pdo_stmt_00000004: select count(*) as aggregate from "orders" where "status" = $1 and "user_id" = $2

Mỗi câu Eloquent là một prepared statement mới (pdo_stmt_00000002, …04, …06…) được parse, bind, execute đúng một lần rồi giải phóng — không bao giờ tới lần thực thi thứ 6, nên luôn là custom plan. Ai thật sự bị: hàm PL/pgSQL (docs ghi rõ PL/pgSQL cache plan và có thể chuyển sang generic), các driver/pool giữ prepared statement để chạy lại nhiều lần (nhiều driver có chế độ tự prepare — cần kiểm tra driver đang dùng; lab chưa verify các driver khác PHP). Cách sửa có chủ đích: SET plan_cache_mode = force_custom_plan cho role/phiên có câu nhạy với giá trị.

Hình 2 — Quy tắc 5 lần của plan cache. Số liệu là output thật của ví dụ 5 và 5b (cost đo bằng EXPLAIN EXECUTE với plan_cache_mode ép từng kiểu). Nhận ra generic plan trong output: $1 thay cho giá trị.

3.6. Ví dụ 6 — JIT bật theo cost, không theo thời gian

SHOW jit_above_cost;
 jit_above_cost
----------------
 100000
(1 row)

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT product_id % 100 AS bucket, sum(quantity * unit_price_cents), avg(unit_price_cents)
FROM order_items GROUP BY 1;

HashAggregate  (cost=149265.90..149690.77 rows=28325 width=48) (actual time=1704.001..1704.089 rows=100.00 loops=1)
  Group Key: (product_id % '100'::bigint)
  Batches: 1  Memory Usage: 545kB
  ->  Seq Scan on order_items  (cost=0.00..99265.50 rows=5000040 width=16) (actual time=11.757..884.630 rows=5000000.00 loops=1)
Planning Time: 1.307 ms
JIT:
  Functions: 9
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 1.182 ms (Deform 0.306 ms), Inlining 0.000 ms, Optimization 0.692 ms, Emission 11.067 ms, Total 12.941 ms
Execution Time: 1732.974 ms

Seq Scan on orders o  (cost=0.00..34027165.17 rows=1499974 width=116) (actual time=1024.015..1861.310 rows=117092.00 loops=1)
  Filter: ((ANY (id = (hashed SubPlan 2).col1)) OR (ANY (id = (hashed SubPlan 4).col1)))
  Rows Removed by Filter: 1882908
  SubPlan 2
    ->  Bitmap Heap Scan on order_items i  (cost=136.14..24954.90 rows=11833 width=8) (actual time=30.565..48.575 rows=12986.00 loops=1)
          Recheck Cond: (product_id = 7)
          Heap Blocks: exact=10938
          ->  Bitmap Index Scan on order_items_product_id_index  (cost=0.00..133.18 rows=11833 width=0) (actual time=2.612..2.613 rows=12986.00 loops=1)
                Index Cond: (product_id = 7)
                Index Searches: 1
  SubPlan 4
    ->  Seq Scan on order_items i_1  (cost=0.00..99265.50 rows=113168 width=8) (actual time=29.282..561.995 rows=107252.00 loops=1)
          Filter: (quantity > 4)
          Rows Removed by Filter: 4892748
Planning Time: 1.567 ms
JIT:
  Functions: 32
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.866 ms (Deform 0.528 ms), Inlining 108.754 ms, Optimization 126.421 ms, Emission 143.584 ms, Total 380.625 ms
Execution Time: 1869.360 ms

Seq Scan on orders o  (cost=0.00..34027165.17 rows=1499974 width=116) (actual time=819.569..1775.014 rows=117092.00 loops=1)
  Filter: ((ANY (id = (hashed SubPlan 2).col1)) OR (ANY (id = (hashed SubPlan 4).col1)))
  Rows Removed by Filter: 1882908
  SubPlan 2
    ->  Bitmap Heap Scan on order_items i  (cost=136.14..24954.90 rows=11833 width=8) (actual time=5.333..21.637 rows=12986.00 loops=1)
          Recheck Cond: (product_id = 7)
          Heap Blocks: exact=10938
          ->  Bitmap Index Scan on order_items_product_id_index  (cost=0.00..133.18 rows=11833 width=0) (actual time=3.165..3.165 rows=12986.00 loops=1)
                Index Cond: (product_id = 7)
                Index Searches: 1
  SubPlan 4
    ->  Seq Scan on order_items i_1  (cost=0.00..99265.50 rows=113168 width=8) (actual time=0.043..715.169 rows=107252.00 loops=1)
          Filter: (quantity > 4)
          Rows Removed by Filter: 4892748
Planning Time: 0.476 ms
Execution Time: 1781.938 ms

JIT bật khi tổng cost vượt jit_above_cost (100 000); inlining và optimization (phần đắt nhất) bật khi vượt 500 000. Câu gom 5 triệu dòng: JIT cơ bản, biên dịch 12,9 ms — không đáng kể. Câu whereHas ... orWhereHas (bài 3): cost bị thổi lên 34 triệu nên bật đủ cả bộ, 380 ms chỉ để biên dịch, chạy chậm hơn bản tắt JIT. Với câu OLTP thật chỉ chạy 20–50 ms mà cost ước lượng cao (do ước lượng sai), phần JIT có thể lớn hơn cả phần chạy. Đọc dòng JIT: cuối plan; nhiều hệ OLTP nâng jit_above_cost hoặc tắt JIT cho role web (khuyến nghị dựa trên lab này, không phải số liệu thống kê chung).

3.7. Ví dụ 7 — cost model: `random_page_cost`

SHOW random_page_cost;
 random_page_cost
------------------
 4
(1 row)
EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT o.id, i.product_id, i.quantity
FROM orders o JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= '2026-09-01';

Hash Join  (cost=4098.70..103989.24 rows=166207 width=20) (actual time=1358.755..1437.977 rows=164975.00 loops=1)
  Hash Cond: (i.order_id = o.id)
  ->  Seq Scan on order_items i  (cost=0.00..86765.40 rows=5000040 width=20) (actual time=0.024..517.759 rows=5000000.00 loops=1)
  ->  Hash  (cost=3267.69..3267.69 rows=66481 width=8) (actual time=40.234..40.237 rows=65990.00 loops=1)
        Buckets: 131072  Batches: 1  Memory Usage: 3602kB
        ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..3267.69 rows=66481 width=8) (actual time=0.127..21.021 rows=65990.00 loops=1)
              Index Cond: (created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone)
              Index Searches: 1
Planning Time: 4.931 ms
Execution Time: 1445.078 ms

SET random_page_cost = 1.1;
SET
Nested Loop  (cost=0.86..86120.83 rows=166207 width=20) (actual time=0.061..152.073 rows=164975.00 loops=1)
  ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..2651.51 rows=66481 width=8) (actual time=0.034..13.465 rows=65990.00 loops=1)
        Index Cond: (created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone)
        Index Searches: 1
  ->  Index Scan using order_items_order_id_index on order_items i  (cost=0.43..1.23 rows=3 width=20) (actual time=0.001..0.002 rows=2.50 loops=65990)
        Index Cond: (order_id = o.id)
        Index Searches: 65990
Planning Time: 0.424 ms
Execution Time: 158.382 ms

Đơn tháng 9 kèm dòng hàng: với random_page_cost = 4 (giả định đĩa quay: đọc ngẫu nhiên đắt gấp 4 đọc tuần tự), planner sợ 66 nghìn lần tra index và chọn quét cả 5 triệu dòng hàng — 1,4 giây. Đặt 1.1 (giá trị thường dùng cho SSD hoặc khi dữ liệu nằm trong RAM): Nested Loop, 0,16 giây. Nhưng không phải phép màu:

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT count(*), sum(i.quantity)
FROM orders o JOIN order_items i ON i.order_id = o.id
WHERE o.meta->>'source' = 'app' AND o.created_at >= '2026-01-01';

Aggregate  (cost=193106.83..193106.84 rows=1 width=16) (actual time=3047.570..3047.575 rows=1.00 loops=1)
  ->  Hash Join  (cost=38428.95..188568.49 rows=907668 width=4) (actual time=1495.220..2992.687 rows=900761.00 loops=1)
        Hash Cond: (i.order_id = o.id)
        ->  Seq Scan on order_items i  (cost=0.00..86765.40 rows=5000040 width=12) (actual time=0.031..673.197 rows=5000000.00 loops=1)
        ->  Hash  (cost=32471.73..32471.73 rows=363058 width=8) (actual time=411.021..411.023 rows=360583.00 loops=1)
              Buckets: 262144  Batches: 4  Memory Usage: 5563kB
              ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..32471.73 rows=363058 width=8) (actual time=1.134..297.376 rows=360583.00 loops=1)
                    Index Cond: (created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone)
                    Filter: ((meta ->> 'source'::text) = 'app'::text)
                    Rows Removed by Filter: 239791
                    Index Searches: 1
Planning Time: 0.626 ms
Execution Time: 3048.908 ms

Aggregate  (cost=187598.06..187598.07 rows=1 width=16) (actual time=3101.077..3101.082 rows=1.00 loops=1)
  ->  Hash Join  (cost=32920.18..183059.72 rows=907668 width=4) (actual time=1668.086..3048.055 rows=900761.00 loops=1)
        Hash Cond: (i.order_id = o.id)
        ->  Seq Scan on order_items i  (cost=0.00..86765.40 rows=5000040 width=12) (actual time=0.024..775.238 rows=5000000.00 loops=1)
        ->  Hash  (cost=26962.96..26962.96 rows=363058 width=8) (actual time=332.992..332.994 rows=360583.00 loops=1)
              Buckets: 262144  Batches: 4  Memory Usage: 5563kB
              ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..26962.96 rows=363058 width=8) (actual time=0.244..228.992 rows=360583.00 loops=1)
                    Index Cond: (created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone)
                    Filter: ((meta ->> 'source'::text) = 'app'::text)
                    Rows Removed by Filter: 239791
                    Index Searches: 1
Planning Time: 0.500 ms
Execution Time: 3102.342 ms

Câu JSON của ví dụ 2 (360 nghìn vòng nếu Nested Loop) vẫn giữ Hash Join kể cả ở 1.1 — với từng ấy vòng, cost model vẫn tính Hash Join rẻ hơn, trong khi thực tế Nested Loop nhanh gấp đôi nhờ dữ liệu nằm trong RAM và các vòng tra đi theo thứ tự vật lý. Đó là giới hạn của một mô hình chỉ có vài hằng số. Đổi random_page_cost là đổi cho mọi câu: thử trong phiên, đo bằng pg_stat_statements trước/sau, rồi mới đặt toàn cục hoặc theo tablespace.

3.8. Ví dụ 8 — tăng work_mem trong Docker: Parallel Hash Join chết

\! df -h /dev/shm
Filesystem                Size      Used Available Use% Mounted on
shm                      64.0M      1.0M     63.0M   2% /dev/shm
SHOW dynamic_shared_memory_type;
 dynamic_shared_memory_type
----------------------------
 posix
(1 row)

SET work_mem = '256MB';
SET
EXPLAIN
SELECT count(*) FROM orders o JOIN order_items i ON i.order_id = o.id WHERE o.total_cents > 100000;

Finalize Aggregate  (cost=126757.13..126757.14 rows=1 width=8)
  ->  Gather  (cost=126756.91..126757.12 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=125756.91..125756.92 rows=1 width=8)
              ->  Parallel Hash Join  (cost=57846.15..120913.46 rows=1937382 width=0)
                    Hash Cond: (i.order_id = o.id)
                    ->  Parallel Seq Scan on order_items i  (cost=0.00..57598.50 rows=2083350 width=8)
                    ->  Parallel Hash  (cost=48159.49..48159.49 rows=774933 width=8)
                          ->  Parallel Seq Scan on orders o  (cost=0.00..48159.49 rows=774933 width=8)
                                Filter: (total_cents > 100000)

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT count(*) FROM orders o JOIN order_items i ON i.order_id = o.id WHERE o.total_cents > 100000;
ERROR:  could not resize shared memory segment "/PostgreSQL.683970758" to 16777216 bytes: No space left on device

RESET work_mem;
RESET
Finalize Aggregate  (cost=149091.13..149091.14 rows=1 width=8) (actual time=2318.105..2430.025 rows=1.00 loops=1)
  ->  Gather  (cost=149090.91..149091.12 rows=2 width=8) (actual time=2318.081..2430.007 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=148090.91..148090.92 rows=1 width=8) (actual time=2302.233..2302.238 rows=1.00 loops=3)
              ->  Parallel Hash Join  (cost=60874.15..143247.46 rows=1937382 width=0) (actual time=1294.485..2179.333 rows=1548556.33 loops=3)
                    Hash Cond: (i.order_id = o.id)
                    ->  Parallel Seq Scan on order_items i  (cost=0.00..57598.50 rows=2083350 width=8) (actual time=0.085..370.580 rows=1666666.67 loops=3)
                    ->  Parallel Hash  (cost=48159.49..48159.49 rows=774933 width=8) (actual time=378.944..378.945 rows=619515.67 loops=3)
                          Buckets: 262144  Batches: 16  Memory Usage: 6656kB
                          ->  Parallel Seq Scan on orders o  (cost=0.00..48159.49 rows=774933 width=8) (actual time=0.025..211.142 rows=619515.67 loops=3)
                                Filter: (total_cents > 100000)
                                Rows Removed by Filter: 47151
Planning Time: 0.494 ms
Execution Time: 2430.115 ms

Container Docker mặc định có /dev/shm 64 MB; PostgreSQL để bộ nhớ dùng chung giữa các process song song (DSM) ở đó. Ai đó "tối ưu" bằng work_mem = 256MB → Parallel Hash xin vùng nhớ chung lớn hơn → ERROR: could not resize shared memory segment ... No space left on device. EXPLAIN thường không báo trước gì. Với work_mem mặc định, bảng băm chia 16 batch ra file tạm và câu chạy xong (2,4 s). Sửa: đặt shm_size cho container (compose) hoặc --shm-size; hoặc giữ work_mem vừa phải; hoặc tắt parallel cho role đó.

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

Thống kê là mẫu: 300 × statistics target dòng (mặc định 30 000). ANALYZE chạy lại cho số hơi khác, đôi khi đổi plan. Target lớn hơn → ANALYZE chậm hơn, planner tốn hơn một chút.
Extended statistics: dependencies chỉ cho điều kiện bằng; mcv giúp tổ hợp giá trị hay gặp; ndistinct cho GROUP BY nhiều cột. Không có thống kê giữa hai bảng (join chỉ dùng thống kê từng bảng + biết khoá ngoại). Tương quan theo khoảng (ví dụ 1 cuối) không sửa được.
Generic plan: cân nhắc từ lần 6; quyết định theo cost ước lượng, không theo thời gian thật; plan_cache_mode ép được theo phiên/role.
Cost constants là toàn cục (hoặc theo tablespace với random_page_cost, seq_page_cost, effective_io_concurrency), không theo bảng.
JIT quyết định theo cost; jit_above_cost, jit_inline_above_cost, jit_optimize_above_cost.
Không có hint trong lõi: các "ép" có sẵn là enable_* (thô, theo phiên), join_collapse_limit, mẹo viết query (+ interval '0', CTE MATERIALIZED) — đều là công cụ chẩn đoán hoặc tạm thời.

5. Failure modes

Triệu chứng	Nguyên nhân	Ví dụ lab	Sửa	Cái giá của cách sửa
Ước lượng thấp/cao hàng chục lần với nhiều điều kiện cùng bảng	Cột phụ thuộc nhau	1	`CREATE STATISTICS`	ANALYZE lâu hơn chút; phải nhớ tạo cho từng nhóm cột
`rows=10000`-kiểu-0,5% trên biểu thức / JSON	Không có thống kê biểu thức	2	Thống kê biểu thức; cột generated thật	Như trên; cột thật tốn chỗ
Chậm ngay sau job nhập/xoá lớn	Thống kê cũ	3	`ANALYZE` cuối job; hạ scale_factor theo bảng	ANALYZE quét mẫu, vài giây với bảng lớn
Mỗi giá trị thường bị ước lượng cao	`n_distinct` thấp	4	`SET STATISTICS` / `n_distinct`	Ghi đè cố định có thể sai khi dữ liệu đổi
`$1` trong plan, lúc nhanh lúc chậm theo phiên	Generic plan	5	`plan_cache_mode = force_custom_plan`	Lập plan mỗi lần (thường < 1 ms)
Dòng `JIT:` tốn hàng trăm ms	Cost bị thổi lên	6	Nâng `jit_above_cost`; tắt JIT cho role web	Mất lợi ích JIT cho câu phân tích lớn
Ước lượng đúng mà vẫn quét cả bảng thay vì tra index	`random_page_cost` không khớp phần cứng	7	Đặt ~1.1 cho SSD sau khi đo	Ảnh hưởng mọi câu — có câu sẽ đổi plan
`could not resize shared memory segment`	`/dev/shm` nhỏ + work_mem lớn + parallel	8	`shm_size`; work_mem vừa phải	—

6. Vận hành trong production

6.1. pg_stat_statements — tìm câu đáng xem

SELECT queryid, calls, round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms, rows,
       shared_blks_hit + shared_blks_read AS blocks, left(query, 72) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND query ILIKE '%orders%' AND query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 5;
       queryid        | calls | total_ms | mean_ms | rows | blocks |                                  query
----------------------+-------+----------+---------+------+--------+--------------------------------------------------------------------------
  6633073171748810625 |     2 |    426.8 |   213.4 |   20 |  28780 | SELECT id FROM orders WHERE payment_method = $1 ORDER BY created_at DESC
 -5246827559702458558 |     3 |      1.2 |     0.4 |    3 |     17 | SELECT count(*) FROM orders WHERE user_id = $1
  4127494335334954216 |     1 |      0.2 |     0.2 |    1 |     12 | SELECT count(*) FROM orders WHERE created_at >= $1
(3 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM orders WHERE payment_method = 'bank' ORDER BY created_at DESC LIMIT 10;

Limit
  Output: id, created_at
  ->  Index Scan Backward using orders_created_at_index on public.orders
        Output: id, created_at
        Filter: (orders.payment_method = 'bank'::text)
Query Identifier: 6633073171748810625

Câu được chuẩn hoá ($1 thay giá trị), cộng dồn theo dạng. Sắp theo total_exec_time để thấy câu tốn tài nguyên nhất tổng cộng (câu 0,4 ms chạy triệu lần có thể đáng hơn câu 2 giây chạy 1 lần). Query Identifier trong EXPLAIN (VERBOSE) khớp queryid — nối hai công cụ. Muốn dùng phải có shared_preload_libraries = 'pg_stat_statements' (container lab bật sẵn).

6.2. auto_explain — bắt plan thật của câu chậm

LOAD 'auto_explain';
LOAD
SET auto_explain.log_min_duration = '200ms';
SET
SET auto_explain.log_analyze = on;
SET
SET auto_explain.log_buffers = on;
SET
SET auto_explain.log_timing = off;
SET
SET auto_explain.log_settings = on;
SET

== Log server (docker logs my-test-pgdb)
LOG:  duration: 521.175 ms  plan:
	Query Text: SELECT id, created_at FROM orders WHERE payment_method = 'bank' ORDER BY created_at DESC LIMIT 10;
	Limit  (cost=0.43..29.94 rows=10 width=16) (actual rows=10.00 loops=1)
	  Buffers: shared hit=127 read=28646 written=1
	  I/O Timings: shared read=228.066 write=0.088
	  ->  Index Scan Backward using orders_created_at_index on orders  (cost=0.43..97986.56 rows=33199 width=16) (actual rows=10.00 loops=1)
	        Filter: (payment_method = 'bank'::text)
	        Rows Removed by Filter: 1327399
	        Index Searches: 1
	        Buffers: shared hit=127 read=28646 written=1
	        I/O Timings: shared read=228.066 write=0.088
	Settings: jit = 'off'

Câu chậm hơn ngưỡng được ghi cả plan, kèm số thật (actual rows, Buffers, I/O) — đúng plan app nhận, với tham số thật. Cái giá:

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

Time: 316.230 ms
SELECT count(*) FROM order_items;
  count
---------
 5000000
(1 row)

Time: 360.428 ms
LOAD 'auto_explain';
LOAD
Time: 0.927 ms
SET auto_explain.log_min_duration = '10s';
SET
Time: 0.203 ms
SET auto_explain.log_analyze = on;
SET
Time: 0.122 ms
SET auto_explain.log_timing = on;
SET
Time: 0.116 ms
SELECT count(*) FROM order_items;
  count
---------
 5000000
(1 row)

Time: 744.472 ms
SET auto_explain.log_timing = off;
SET
Time: 2.501 ms
SELECT count(*) FROM order_items;
  count
---------
 5000000
(1 row)

Time: 336.146 ms

Câu đếm 5 triệu dòng: 316–360 ms bình thường; bật log_analyze + log_timing → 744 ms dù câu này không bị ghi log (dưới ngưỡng 10 s) — vì phải đo trên mọi câu để biết câu nào vượt ngưỡng. Tắt log_timing: 336 ms, gần như không tốn. Cấu hình production hợp lý: session_preload_libraries = 'auto_explain' (hoặc shared_preload), log_min_duration 0,5–2 s, log_analyze = on, log_timing = off, log_buffers = on; thêm auto_explain.sample_rate nếu tải rất cao và log_nested_statements nếu dùng hàm PL/pgSQL.

6.3. Phát hiện tràn đĩa

SELECT temp_files, pg_size_pretty(temp_bytes) AS temp_bytes
FROM pg_stat_database WHERE datname = current_database();
 temp_files | temp_bytes
------------+------------
        964 | 2913 MB
(1 row)
SET log_temp_files = 0;   -- ghi log mọi file tạm (production: đặt ngưỡng, vd '10MB')
SET
SELECT count(*) FROM (
  SELECT * FROM orders WHERE created_at >= '2026-01-01' ORDER BY total_cents DESC OFFSET 0
) s;
 count
--------
 600374
(1 row)
SELECT temp_files, pg_size_pretty(temp_bytes) AS temp_bytes
FROM pg_stat_database WHERE datname = current_database();
 temp_files | temp_bytes
------------+------------
        965 | 2925 MB
(1 row)
== Log server
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp1990854.0", size 13287424
STATEMENT:  SELECT count(*) FROM (

pg_stat_database.temp_files/temp_bytes tăng là có câu tràn đĩa; log_temp_files ghi kèm câu gây ra. (Thống kê được đẩy theo lô — pg_stat_force_next_flush() để đọc ngay trong cùng phiên.)

6.4. Ba giờ sáng: câu chạy mãi không xong

flowchart TD
  A["Cảnh báo: latency tăng, pool cạn kết nối"] --> B["pg_stat_activity: câu nào active lâu nhất,
application_name, wait_event"]
  B --> C["Lấy đúng text câu đó → EXPLAIN (không ANALYZE)
có plan ngay, không phải chờ"]
  C --> D{"Plan có dấu hiệu quen?
SubPlan, Seq Scan bảng lớn, $1, cost khổng lồ"}
  D --> E["pg_cancel_backend(pid) — huỷ câu, giữ kết nối
(pg_terminate_backend nếu treo trong transaction)"]
  E --> F["Chặn tái phát ngay: statement_timeout cho role,
tắt tính năng/feature flag, ANALYZE nếu thống kê cũ"]
  F --> G["Sáng hôm sau: auto_explain + pg_stat_statements,
sửa gốc, viết postmortem"]

== Phiên A (app 'laravel-worker') chạy query NOT IN — sẽ không bao giờ xong (bài 3)
== Phiên B (on-call): ai đang chạy lâu?
   pid   | application_name |  backend_type   |    chay_duoc    |                       query
---------+------------------+-----------------+-----------------+----------------------------------------------------
 1990967 | laravel-worker   | client backend  | 00:00:03.062964 | SELECT count(*) FROM orders WHERE id NOT IN (SELEC
 1990968 | laravel-worker   | parallel worker | 00:00:03.062964 | SELECT count(*) FROM orders WHERE id NOT IN (SELEC
 1990969 | laravel-worker   | parallel worker | 00:00:03.062964 | SELECT count(*) FROM orders WHERE id NOT IN (SELEC
(3 rows)

== EXPLAIN (KHÔNG ANALYZE) đúng câu đó: có plan ngay, không phải chạy query
Finalize Aggregate  (cost=59914737460.90..59914737460.91 rows=1 width=8)
  ->  Gather  (cost=59914737460.69..59914737460.90 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=59914736460.69..59914736460.70 rows=1 width=8)
              ->  Parallel Index Only Scan using orders_pkey on orders  (cost=0.43..59914735419.04 rows=416660 width=0)
                    Filter: (NOT (ANY (id = (SubPlan 1).col1)))
                    SubPlan 1
                      ->  Materialize  (cost=0.00..131297.60 rows=5000040 width=8)
                            ->  Seq Scan on order_items  (cost=0.00..86765.40 rows=5000040 width=8)

== Huỷ query (pg_cancel_backend: chỉ huỷ câu đang chạy, không đóng kết nối)
 pg_cancel_backend
-------------------
 t
(1 row)

Câu NOT IN của bài 3 đang chạy (leader + 2 parallel worker). EXPLAIN thường trên đúng text câu đó cho plan ngay với cost 59,9 tỷ — đủ để biết đây là SubPlan quét lại theo từng dòng mà không phải đợi. pg_cancel_backend huỷ câu, kết nối của app vẫn sống.

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

Cách sửa	Phạm vi ảnh hưởng	Bền khi dữ liệu đổi?	Rủi ro	Dùng khi
`ANALYZE`	1 bảng	Tới lần đổi lớn sau	Thấp	Sau nhập/xoá lớn; luôn thử đầu tiên
`CREATE STATISTICS`	Câu dùng nhóm cột đó	Có (ANALYZE cập nhật)	Thấp	Cột phụ thuộc nhau, biểu thức
`SET STATISTICS n`	1 cột	Có	ANALYZE chậm hơn	Cột phân bố lệch, n_distinct sai
`SET (n_distinct = ...)`	1 cột	Không — số cố định	Sai khi dữ liệu đổi	Biết chắc tỉ lệ ổn định
Index mới (ghép, partial, biểu thức)	Câu dùng được index	Có	Chi phí ghi, dung lượng	Plan thiếu đường truy cập tốt
Viết lại query	1 câu	Có	Thấp, cần test	NOT IN, OR EXISTS, whereDate…
`plan_cache_mode`	Phiên/role	Có	Planning mỗi lần	Generic plan sai
`random_page_cost`, `effective_cache_size`	Mọi câu	Có	Có câu đổi plan theo hướng xấu	Phần cứng khác giả định (SSD)
`SET LOCAL enable_* = off`	1 transaction	—	Thô, có thể làm câu khác tệ	Chữa cháy, chẩn đoán
`pg_hint_plan` (extension)	Câu có hint	Không — plan bị đóng băng	Lock-in, plan cũ khi dữ liệu đổi	Đường cùng (chưa verify trong lab)

Dùng theo thứ tự từ trên xuống: thông tin tốt hơn cho planner trước, ép plan sau cùng. Tránh đổi tham số toàn cục để chữa một câu, và tránh "đóng băng" plan khi dữ liệu còn đang lớn lên.

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

Quan sát được trước khi sự cố: pg_stat_statements + auto_explain (log_timing off) bật sẵn ở production; dashboard top câu theo total time; cảnh báo khi temp_bytes tăng.
Job dữ liệu có trách nhiệm với thống kê: mọi job nhập/xoá lớn kết thúc bằng ANALYZE bảng đã đổi — ghi vào checklist review job.
Nâng cấp PostgreSQL bản lớn = plan có thể đổi: chạy bộ câu quan trọng (lấy từ pg_stat_statements) trên staging bản mới, so plan và thời gian trước khi nâng cấp production. Thống kê sau pg_upgrade: từ PostgreSQL 18 phần lớn thống kê được giữ lại, trừ thống kê tạo bằng CREATE STATISTICS (release notes 18, docs pgupgrade) — chính loại sửa plan ở ví dụ 1 và 2 sẽ biến mất cho tới khi chạy vacuumdb --all --analyze-in-stages --missing-stats-only rồi vacuumdb --all --analyze-only như docs hướng dẫn. Bản cũ hơn không giữ thống kê nào: phải ANALYZE trước khi mở tải.
Văn hoá "không hint": sửa gốc (thống kê, index, query); nếu buộc phải ép thì có ticket gỡ và ngày hết hạn.
Người trực: runbook mục 6.4, quyền chạy pg_cancel_backend, và thói quen EXPLAIN thường trước khi làm gì khác.

Mẫu ADR ngắn: "Bật pg_stat_statements và auto_explain (min_duration 1s, log_analyze on, log_timing off, log_buffers on) trên mọi cluster production. Mọi job ghi > 10% một bảng phải gọi ANALYZE bảng đó khi xong. Role web: statement_timeout = 5s, jit = off. Không dùng enable_* hay hint ở cấp toàn cục. Lý do: lab cho thấy log_timing làm mọi câu chậm gấp đôi, thống kê cũ sau lô nhập làm ước lượng sai 27 lần, và JIT tốn 380 ms cho một câu có cost bị thổi phồng."

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

Tắt enable_nestloop/enable_seqscan toàn cục sau một sự cố.
Tăng work_mem toàn cục — nhân theo node × phiên; trong Docker còn làm câu chết (ví dụ 8).
Chạy VACUUM FULL/REINDEX để "sửa plan" khi vấn đề là thống kê — ANALYZE mới là việc cần.
Ghi đè n_distinct rồi quên — dữ liệu đổi, con số cố định thành sai.
ANALYZE một cột mà bỏ qua WARNING về extended statistics (ví dụ 4).
Bật auto_explain với log_timing trên production tải cao (ví dụ 6.2: mọi câu chậm gấp đôi).
Đổi random_page_cost trên production chỉ dựa vào một câu — không đo ảnh hưởng tới các câu khác.
Kết luận "ước lượng đúng rồi thì plan phải nhanh" — ví dụ 2 và 7.

Postmortem công khai 1 — GoCardless ("Debugging the Postgres query planner", Lawrence Jones, 2018, gocardless.com): câu ORDER BY id LIMIT 1 trên bảng ~nửa tỷ dòng quét ~17,5 triệu dòng vì n_distinct của payout_id bị ước lượng 25 650 thay vì ~3,5 triệu (lỗi lấy mẫu trên dữ liệu phân bố lệch — đúng hiện tượng của ví dụ 4). Sửa bằng ALTER TABLE ... ALTER COLUMN payout_id SET STATISTICS 5000.

Postmortem công khai 2 — GitLab.com, 09/03/2021 (production#3875): ~2,5 giờ gián đoạn do statement timeout hàng loạt; theo issue, optimizer PostgreSQL 11 ước lượng sai vòng ngoài Nested Loop với IN (...) trên 200 phần tử; cứu tức thời bằng ANALYZE namespaces;. Hai sự cố, cùng bài học: khi một câu "tự nhiên chậm" mà code không đổi, nhìn ước lượng trước, và ANALYZE là liều thuốc đầu tiên.

Nguồn bổ sung: pganalyze, "Postgres Planner Quirks: The impact of ORDER BY + LIMIT on index usage" (pganalyze.com) — tổng hợp các cách sửa bẫy LIMIT; Cybertec, "EXPLAIN that parameterized statement in PostgreSQL!" (cybertec-postgresql.com) — cách xem generic plan.

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

Một câu đột nhiên chậm mà không ai deploy gì. Bạn làm gì?: pg_stat_activity tìm câu; EXPLAIN thường xem plan; so với plan cũ (auto_explain/log). Nghi thống kê cũ hoặc dữ liệu tăng → ANALYZE bảng liên quan. Nghi generic plan ($1 trong plan) → force_custom_plan. Ghi lại plan trước/sau.
Extended statistics giải quyết gì, không giải quyết gì?: Tương quan giữa các cột cùng bảng với điều kiện bằng (dependencies, mcv), số nhóm GROUP BY nhiều cột (ndistinct), thống kê biểu thức. Không giải quyết tương quan theo khoảng hay giữa hai bảng.
Generic plan là gì, khi nào gây hại?: Plan dùng chung cho mọi giá trị tham số, được cân nhắc từ lần EXECUTE thứ 6 nếu không đắt hơn trung bình custom. Hại khi dữ liệu lệch: giá trị hiếm/phổ biến cần plan khác nhau. Nhận ra qua $1 trong plan.
Laravel có bị generic plan không?: Với pdo_pgsql mặc định: không — mỗi câu một prepared statement mới, chạy một lần (lab: pdo_stmt_00000002, …04…). PL/pgSQL và driver giữ prepared statement thì có.
random_page_cost nên đặt bao nhiêu?: Mặc định 4 là cho đĩa quay. SSD/dữ liệu nằm trong RAM thường đặt khoảng 1.1 — nhưng đo trước/sau bằng pg_stat_statements vì nó ảnh hưởng mọi câu (lab: một câu nhanh 9 lần, một câu không đổi).
auto_explain có làm chậm production không?: Có nếu bật log_timing cùng log_analyze: lab chậm gấp đôi trên mọi câu, kể cả câu không bị ghi. Tắt log_timing thì gần như không tốn; vẫn có rows/buffers thật.
Vừa nâng cấp lên PostgreSQL 18 bằng pg_upgrade, vài câu chậm hẳn. Nghi gì đầu tiên?: Thống kê. PG 18 giữ phần lớn thống kê qua pg_upgrade nhưng không giữ thống kê của CREATE STATISTICS — các câu từng được sửa bằng extended statistics quay về ước lượng sai cho tới khi ANALYZE (docs: vacuumdb --all --analyze-in-stages --missing-stats-only). Sau đó mới tới khác biệt planner giữa hai bản.
Có nên dùng pg_hint_plan?: Đường cùng, có thời hạn. Hint đóng băng plan khi dữ liệu còn thay đổi và tạo phụ thuộc vào extension; ưu tiên sửa thống kê, index, query.

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

Môi trường: như bài 1 (PostgreSQL 18.4, tmp_qplan, JIT tắt trừ ví dụ 6). Bài này đổi thống kê của bảng orders (ANALYZE, CREATE STATISTICS orders_ship_stats và orders_meta_source_stats được giữ lại) — chạy lại bài 1–4 sau bài 5 sẽ thấy ước lượng hơi khác.

cd labs/query-plan
./run.sh 05-0[1-5]*.sql 05-0[89]*.sql 05-1[01]*.sql    # → out/05-*.txt
./05-06-laravel-prepared.sh       # bật log_min_duration_statement=0 cho DB tmp_qplan trong lúc chạy, rồi trả lại
./05-07-auto-explain.sh           # LOAD auto_explain theo phiên, đọc docker logs
./05-12-temp-file.sh              # log_temp_files + pg_stat_database
./05-13-3h-sang.sh                # chạy câu NOT IN nền, EXPLAIN nó, rồi pg_cancel_backend

Chưa verify: pg_hint_plan; hành vi tự prepare của driver ngoài PHP; nghiên cứu Leis et al. (dẫn theo tài liệu, không dựng benchmark); cơ chế dò index lấy max thật khi vượt histogram (giải thích theo source, lab chỉ thấy hệ quả 17 111 thay vì 1); số liệu khuyến nghị random_page_cost cho từng loại đĩa (lab chỉ có một máy).