Sort, Aggregate, Limit, CTE, SubPlan, Window, Partition — phần trên của cây plan

Scan (bài 2) và join (bài 3) lấy và ghép dòng. Phần trên của cây làm việc với các dòng đó: sắp xếp, gom nhóm, cắt bớt, phân trang, tính window function, chạy subquery — và với bảng partition thì quyết định partition nào khỏi phải đọc. Đây là chỗ work_mem, LIMIT và cách viết SQL của ORM ảnh hưởng mạnh nhất. Ví dụ đắt nhất của bài: một câu "10 đơn chuyển khoản gần nhất" bỏ qua 1,3 triệu dòng để tìm 10 dòng.

Thuật ngữ dùng trong bài
Sort Method (cách sắp: quicksort trong RAM, top-N heapsort khi có LIMIT nhỏ, external merge trên đĩa) · Incremental Sort (dữ liệu đã sắp sẵn theo cột đầu, chỉ sắp phần còn lại trong từng nhóm) · HashAggregate / GroupAggregate (gom nhóm bằng bảng băm / bằng input đã sắp) · Partial / Finalize Aggregate (gom từng phần ở mỗi worker / gom lại ở leader) · Planned Partitions, Batches, Disk Usage (HashAggregate chia phần ra đĩa) · keyset pagination (phân trang theo con trỏ: "lấy sau id X" thay vì "bỏ qua N dòng") · CTE (Common Table Expression — khối WITH) · MATERIALIZED (bắt CTE tính xong, lưu lại rồi mới dùng) · SubPlan / InitPlan (subquery chạy lại mỗi dòng / chạy một lần) · WindowAgg (node tính window function như row_number()) · Run Condition (điều kiện cho WindowAgg dừng sớm) · partition pruning (bỏ qua partition chắc chắn không có dòng cần) · Subplans Removed (số partition bị bỏ lúc executor khởi động).

Kiểm chứng: tài liệu PostgreSQL 18 qua Context7 (using-explain, ddl-partitioning mục 5.12.4 Partition Pruning, runtime-config-resource, release notes 18 — dòng Storage cho Material/WindowAgg/CTE). Plan là output thật trên PostgreSQL 18.4, database tmp_qplan (bài 1), file labs/query-plan/04-*.sql, output labs/query-plan/out/04-*.txt. Bảng orders_p là bản partition theo tháng (36 partition) của cùng dữ liệu. SQL của Laravel lấy từ lab laravel/eloquent.php (Laravel 12.64).

Trả lời 30 giây

Sort: đọc dòng Sort Method — quicksort trong RAM, top-N heapsort khi LIMIT nhỏ (chỉ giữ N dòng), external merge khi vượt work_mem. Aggregate: HashAggregate cần bộ nhớ và có thể tràn (Planned Partitions/Batches/Disk Usage), GroupAggregate cần input đã sắp. Planner chọn theo cost và cost có thể sai rất xa: lab, hai phương án cost gần bằng nhau (211 125 so với 211 450) mà chạy thật 10 giây so với 2,3 giây. LIMIT làm planner ưu tiên plan "ra dòng đầu nhanh", với giả định dòng khớp rải đều theo thứ tự index — sai giả định là bẫy: 1,57 giây thay vì 0,09 ms. OFFSET đọc rồi vứt mọi dòng phía trước; keyset thì trang nào cũng như trang đầu. CTE được inline từ PG 12 trừ khi MATERIALIZED hoặc dùng 2 lần. SubPlan chạy mỗi dòng, InitPlan chạy một lần. Partition pruning có 3 dạng nhìn thấy được trong plan: partition vắng mặt (lúc lập plan), Subplans Removed (lúc executor khởi động), (never executed) (lúc chạy).

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

Mỗi mệnh đề SQL "xử lý sau" thành một hoặc vài node: ORDER BY → Sort (hoặc dùng thứ tự có sẵn của index); GROUP BY → HashAggregate/GroupAggregate; DISTINCT → Unique/HashAggregate; LIMIT/OFFSET → Limit; WITH → CTE Scan hoặc được inline; subquery → SubPlan/InitPlan hoặc join; window function → WindowAgg. Phần lớn các node này là node chặn và tốn bộ nhớ theo work_mem — giới hạn đặt ra để một câu không ăn hết RAM, và khi vượt thì ghi ra đĩa thay vì chết.

Partition khai báo (declarative partitioning) giải bài toán khác: bảng rất lớn chia theo thời gian (hoặc theo khoá) để xoá dữ liệu cũ bằng DROP partition, VACUUM từng phần nhỏ, và để planner bỏ qua partition không liên quan. Việc bỏ qua đó — pruning — xảy ra ở ba thời điểm khác nhau và plan cho thấy cả ba (ví dụ 9).

Tối ưu cho: chạy liền khi có thể (dùng thứ tự của index, Incremental Sort, top-N heapsort), bộ nhớ có giới hạn và tràn đĩa có kiểm soát. Không tối ưu cho: phân trang sâu bằng OFFSET (vẫn phải đọc mọi dòng phía trước); tự đổi subquery tương quan trong SELECT thành join (vẫn là SubPlan — ví dụ 7); đoán số nhóm của biểu thức không có thống kê (ví dụ 3).

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

Ý 1 — node chặn có giới hạn bộ nhớ. Sort, HashAggregate, Hash, Materialize, CTE, WindowAgg đều phải giữ dữ liệu. Mỗi node được tới work_mem (node dùng bảng băm: work_mem × hash_mem_multiplier); vượt thì ghi ra đĩa và plan in ra Disk: / Batches / Storage: Disk. Một câu có nhiều node như vậy dùng nhiều lần work_mem.

Ý 2 — LIMIT là một canh bạc của planner. Với LIMIT, planner chỉ "trả tiền" cho phần đầu của plan con (bài 1, ví dụ 5), nên rất thích plan đi theo thứ tự index và lọc dần. Phép tính đó giả định dòng khớp rải đều theo thứ tự index. Dữ liệu thật hiếm khi rải đều theo thời gian: phương thức thanh toán bị ngừng, trạng thái chỉ có ở đơn mới, khách chỉ mua trong một mùa.

Hình 1 — Bẫy LIMIT. Planner đi ngược index created_at và lọc từng dòng, vì tính rằng đi chừng vài trăm dòng là đủ 10 đơn bank. Nhưng đơn bank không rải đều: tất cả nằm trước 2025. Con trỏ phải đi qua 1 327 399 đơn mới gặp đơn bank đầu tiên. Số liệu là output thật của ví dụ 4.

3. Internals và quyết định thiết kế — từng loại node

3.1. Ví dụ 1 — Sort: trong RAM, top-N, và tràn đĩa

EXPLAIN (ANALYZE)
SELECT id, total_cents FROM orders WHERE created_at >= '2026-09-01' ORDER BY total_cents DESC;

Sort  (cost=7803.00..7956.64 rows=61457 width=12) (actual time=60.672..71.416 rows=65990.00 loops=1)
  Sort Key: total_cents DESC
  Sort Method: quicksort  Memory: 3818kB
  ->  Index Scan using orders_created_at_index on orders  (cost=0.43..2914.92 rows=61457 width=12) (actual time=0.096..38.681 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: 5.077 ms
Execution Time: 74.765 ms

EXPLAIN (ANALYZE)
SELECT id, total_cents FROM orders WHERE created_at >= '2026-09-01' ORDER BY total_cents DESC LIMIT 20;

Limit  (cost=4550.27..4550.32 rows=20 width=12) (actual time=21.881..21.885 rows=20.00 loops=1)
  ->  Sort  (cost=4550.27..4703.92 rows=61457 width=12) (actual time=21.879..21.881 rows=20.00 loops=1)
        Sort Key: total_cents DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Index Scan using orders_created_at_index on orders  (cost=0.43..2914.92 rows=61457 width=12) (actual time=0.040..14.677 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: 0.123 ms
Execution Time: 21.987 ms

66 nghìn dòng hẹp (12 byte) sắp trong 3,8 MB RAM. Thêm LIMIT 20: top-N heapsort — chỉ giữ 20 dòng lớn nhất trong một heap 26 kB, không sắp cả tập; 22 ms thay vì 75 ms. Dòng Sort vẫn có startup cost ≈ total cost: phải nhìn hết input mới biết 20 dòng lớn nhất là gì.

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE created_at >= '2026-01-01' ORDER BY total_cents DESC;

Sort  (cost=159235.62..160730.20 rows=597832 width=116) (actual time=878.810..999.903 rows=600374.00 loops=1)
  Sort Key: total_cents DESC
  Sort Method: external merge  Disk: 79256kB
  ->  Index Scan using orders_created_at_index on orders  (cost=0.43..28312.49 rows=597832 width=116) (actual time=0.107..204.531 rows=600374.00 loops=1)
        Index Cond: (created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone)
        Index Searches: 1
Planning Time: 0.462 ms
Execution Time: 1048.814 ms

SET work_mem = '256MB';
SET
Sort  (cost=85672.62..87167.20 rows=597832 width=116) (actual time=570.233..835.745 rows=600374.00 loops=1)
  Sort Key: total_cents DESC
  Sort Method: quicksort  Memory: 107127kB
  ->  Index Scan using orders_created_at_index on orders  (cost=0.43..28312.49 rows=597832 width=116) (actual time=0.077..252.225 rows=600374.00 loops=1)
        Index Cond: (created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone)
        Index Searches: 1
Planning Time: 0.110 ms
Execution Time: 878.284 ms

600 nghìn dòng đủ cột (116 byte) vượt 4 MB → external merge Disk: 79256kB: sắp từng đoạn vừa bộ nhớ, ghi ra file tạm, rồi trộn các đoạn (Hình 2). Tăng work_mem lên 256 MB thì sắp trong RAM, nhưng chỉ nhanh hơn ~16% — và để ý bản trong RAM tốn 107 MB, lớn hơn bản trên đĩa (79 MB): dòng trong bộ nhớ có thêm phần quản lý. Tràn đĩa không phải lúc nào cũng là thảm hoạ; nó đáng lo khi file tạm lớn và nhiều phiên cùng tràn.

Hình 2 — External merge sort. Sort không bao giờ vượt work_mem; vượt thì đổi sang sắp từng phần trên đĩa. Thời gian thêm vào phụ thuộc dung lượng file tạm và tốc độ đĩa — lab: 79 MB, chậm hơn bản trong RAM chừng 16%.

3.2. Ví dụ 2 — Incremental Sort

EXPLAIN (ANALYZE)
SELECT id, user_id, created_at FROM orders
WHERE user_id <= 1000
ORDER BY user_id, created_at DESC
LIMIT 100;

Limit  (cost=2.37..116.06 rows=100 width=24) (actual time=47.286..47.302 rows=100.00 loops=1)
  ->  Incremental Sort  (cost=2.37..158220.23 rows=139167 width=24) (actual time=47.283..47.290 rows=100.00 loops=1)
        Sort Key: user_id, created_at DESC
        Presorted Key: user_id
        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 29kB  Peak Memory: 29kB
        Pre-sorted Groups: 1  Sort Method: top-N heapsort  Average Memory: 39kB  Peak Memory: 39kB
        ->  Index Scan using orders_user_id_index on orders  (cost=0.43..154038.78 rows=139167 width=24) (actual time=0.898..45.553 rows=4386.00 loops=1)
              Index Cond: (user_id <= 1000)
              Index Searches: 1
Planning Time: 2.020 ms
Execution Time: 47.411 ms

Index chỉ có user_id; câu cần ORDER BY user_id, created_at DESC. Thay vì đọc 139 nghìn dòng rồi sắp hết, planner đọc theo index (đã đúng thứ tự user_id — Presorted Key) và chỉ sắp created_at trong từng nhóm khách. Với LIMIT 100, nhóm đầu (khách 1, 4 385 đơn) đã đủ: chỉ đọc 4 386 dòng rồi dừng. Full-sort Groups / Pre-sorted Groups cho biết bao nhiêu nhóm đã được sắp theo mỗi cách.

3.3. Ví dụ 3 — HashAggregate, GroupAggregate, và khi cost model sai 4 lần

EXPLAIN (ANALYZE)
SELECT date_trunc('day', created_at) AS d, count(*), sum(total_cents)
FROM orders WHERE created_at >= '2026-01-01'
GROUP BY 1;

HashAggregate  (cost=70987.45..84374.01 rows=601221 width=24) (actual time=433.611..433.841 rows=273.00 loops=1)
  Group Key: date_trunc('day'::text, created_at)
  Planned Partitions: 16  Batches: 1  Memory Usage: 1065kB
  ->  Index Scan using orders_created_at_index on orders  (cost=0.43..30968.67 rows=601221 width=12) (actual time=0.145..342.680 rows=600374.00 loops=1)
        Index Cond: (created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone)
        Index Searches: 1
Planning Time: 2.617 ms
Execution Time: 435.158 ms

Gom đơn năm 2026 theo ngày. Planner ước lượng 601 221 nhóm (bằng số dòng — date_trunc(...) không có thống kê, nó đoán mỗi dòng một nhóm), thật có 273. Hệ quả nhìn thấy ngay: Planned Partitions: 16 — planner đã chuẩn bị chia bảng băm làm 16 phần để tràn đĩa, nhưng chạy thật chỉ cần 1 batch, 1 MB. Ước lượng số nhóm sai còn kéo theo sai ở mọi node phía trên.

EXPLAIN (ANALYZE)
SELECT user_id, count(*) FROM orders WHERE user_id <= 1000 GROUP BY user_id;

GroupAggregate  (cost=0.43..4941.05 rows=78917 width=16) (actual time=0.777..23.920 rows=1000.00 loops=1)
  Group Key: user_id
  ->  Index Only Scan using orders_user_id_index on orders  (cost=0.43..3449.78 rows=140420 width=8) (actual time=0.081..13.151 rows=141723.00 loops=1)
        Index Cond: (user_id <= 1000)
        Heap Fetches: 0
        Index Searches: 1
Planning Time: 0.358 ms
Execution Time: 24.081 ms

Input đã có thứ tự theo user_id (Index Only Scan) → GroupAggregate: không cần bảng băm, gặp user_id mới là nhóm cũ xong. Giờ gom toàn bảng theo khách, work_mem 4 MB:

EXPLAIN (ANALYZE)
SELECT user_id, count(*), sum(total_cents) FROM orders GROUP BY user_id;

GroupAggregate  (cost=0.43..211125.05 rows=105394 width=24) (actual time=31.697..10022.058 rows=199647.00 loops=1)
  Group Key: user_id
  Buffers: shared hit=849243 read=1153183
  I/O Timings: shared read=6092.534
  ->  Index Scan using orders_user_id_index on orders  (cost=0.43..195071.37 rows=1999966 width=12) (actual time=0.047..9464.818 rows=2000000.00 loops=1)
        Index Searches: 1
        Buffers: shared hit=849243 read=1153183
        I/O Timings: shared read=6092.534
Planning Time: 0.126 ms
Execution Time: 10040.707 ms

SET enable_indexscan = off;
SET
HashAggregate  (cost=190865.40..211450.25 rows=105394 width=24) (actual time=1958.289..2287.707 rows=199647.00 loops=1)
  Group Key: user_id
  Planned Partitions: 4  Batches: 5  Memory Usage: 9785kB  Disk Usage: 27736kB
  Buffers: shared hit=15637 read=22106, temp read=3244 written=5885
  I/O Timings: shared read=42.256, temp read=11.623 write=106.842
  ->  Seq Scan on orders  (cost=0.00..57742.66 rows=1999966 width=12) (actual time=0.032..572.772 rows=2000000.00 loops=1)
        Buffers: shared hit=15637 read=22106
        I/O Timings: shared read=42.256
Planning Time: 0.128 ms
Execution Time: 2306.226 ms

SET work_mem = '64MB';
SET
HashAggregate  (cost=72742.40..73796.35 rows=105394 width=24) (actual time=1880.931..1939.461 rows=199647.00 loops=1)
  Group Key: user_id
  Batches: 1  Memory Usage: 20505kB
  ->  Seq Scan on orders  (cost=0.00..57742.66 rows=1999966 width=12) (actual time=0.042..501.475 rows=2000000.00 loops=1)
Planning Time: 0.118 ms
Execution Time: 1951.708 ms

Đây là ví dụ đáng nhớ nhất bài. Với 4 MB, bảng băm 200 nghìn nhóm không vừa, nên planner chọn đường "đã có thứ tự" — đi hết index user_id (2 triệu dòng, nhảy ngẫu nhiên vào heap) rồi GroupAggregate: cost 211 125. Phương án HashAggregate tràn đĩa có cost 211 450 — gần như bằng. Chạy thật: 10,0 giây so với 2,3 giây. Dòng Buffers giải thích: read=1153183 — 1,15 triệu lần xin trang từ hệ điều hành, tốn 6,1 giây I/O. Cost model giả định trang heap đọc ngẫu nhiên phần lớn đã nằm trong cache (effective_cache_size mặc định 4 GB, bảng chỉ 295 MB) nên tính rẻ; thực tế mỗi lần qua lại hệ điều hành vẫn tốn vài micro-giây và shared_buffers 128 MB không giữ nổi bảng. Bản đủ bộ nhớ (64 MB): HashAggregate trên Seq Scan, 1 batch, 2,0 giây.

EXPLAIN (ANALYZE)
SELECT status, count(*), sum(total_cents) FROM orders GROUP BY status;

Finalize GroupAggregate  (cost=53326.25..53328.04 rows=6 width=20) (actual time=290.930..298.758 rows=6.00 loops=1)
  Group Key: status
  ->  Gather Merge  (cost=53326.25..53327.88 rows=14 width=20) (actual time=290.921..298.744 rows=18.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=52326.22..52326.24 rows=6 width=20) (actual time=280.848..280.849 rows=6.00 loops=3)
              Sort Key: status
              Sort Method: quicksort  Memory: 25kB
              Worker 0:  Sort Method: quicksort  Memory: 25kB
              Worker 1:  Sort Method: quicksort  Memory: 25kB
              ->  Partial HashAggregate  (cost=52326.08..52326.14 rows=6 width=20) (actual time=280.661..280.663 rows=6.00 loops=3)
                    Group Key: status
                    Batches: 1  Memory Usage: 32kB
                    Worker 0:  Batches: 1  Memory Usage: 32kB
                    Worker 1:  Batches: 1  Memory Usage: 32kB
                    ->  Parallel Seq Scan on orders  (cost=0.00..46076.19 rows=833319 width=8) (actual time=0.129..125.561 rows=666666.67 loops=3)
Planning Time: 0.477 ms
Execution Time: 299.097 ms

Song song: mỗi process gom phần của mình (Partial HashAggregate, 6 nhóm), Gather Merge nhận 18 dòng (3 process × 6), leader gom lần cuối (Finalize GroupAggregate).

3.4. Ví dụ 4 — bẫy ORDER BY + LIMIT

"10 đơn chuyển khoản ngân hàng gần nhất" — trang admin đối soát. Phương thức bank ngừng nhận từ 2025:

SELECT max(created_at) AS don_bank_moi_nhat, count(*) FROM orders WHERE payment_method = 'bank';
       don_bank_moi_nhat       | count
-------------------------------+-------
 2024-12-31 23:28:25.230643+00 | 33318
(1 row)

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE payment_method = 'bank' ORDER BY created_at DESC LIMIT 10;

Limit  (cost=0.43..29.30 rows=10 width=116) (actual time=1565.630..1565.655 rows=10.00 loops=1)
  ->  Index Scan Backward using orders_created_at_index on orders  (cost=0.43..94694.08 rows=32800 width=116) (actual time=1565.628..1565.651 rows=10.00 loops=1)
        Filter: (payment_method = 'bank'::text)
        Rows Removed by Filter: 1327399
        Index Searches: 1
Planning Time: 0.830 ms
Execution Time: 1565.714 ms

Tính lại cost của planner: 0.43 + (94694.08 − 0.43) × 10 / 32800 = 29.30. Planner nghĩ 32 800 đơn bank rải đều trên 2 triệu đơn, nên đi ngược index ~610 dòng là đủ 10. Thực tế phải đi qua 1 327 399 dòng — toàn bộ đơn từ 2025 tới nay — 1,57 giây (Hình 1). Bỏ LIMIT thì planner chọn khác hẳn, vì không còn "trả tiền một phần" được:

EXPLAIN
SELECT * FROM orders WHERE payment_method = 'bank' ORDER BY created_at DESC;

Gather Merge  (cost=50098.40..53918.50 rows=32800 width=116)
  Workers Planned: 2
  ->  Sort  (cost=49098.38..49132.54 rows=13667 width=116)
        Sort Key: created_at DESC
        ->  Parallel Seq Scan on orders  (cost=0.00..48159.56 rows=13667 width=116)
              Filter: (payment_method = 'bank'::text)

Hai cách sửa:

BEGIN;
BEGIN
CREATE INDEX orders_payment_method_created_at_index ON orders (payment_method, created_at);
CREATE INDEX
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE payment_method = 'bank' ORDER BY created_at DESC LIMIT 10;

Limit  (cost=0.43..26.15 rows=10 width=116) (actual time=0.050..0.056 rows=10.00 loops=1)
  ->  Index Scan Backward using orders_payment_method_created_at_index on orders  (cost=0.43..84378.06 rows=32800 width=116) (actual time=0.049..0.054 rows=10.00 loops=1)
        Index Cond: (payment_method = 'bank'::text)
        Index Searches: 1
Planning Time: 0.374 ms
Execution Time: 0.090 ms

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE payment_method = 'bank' ORDER BY created_at + interval '0 second' DESC LIMIT 10;

Limit  (cost=49489.09..49490.26 rows=10 width=124) (actual time=193.311..201.131 rows=10.00 loops=1)
  ->  Gather Merge  (cost=49489.09..53309.31 rows=32801 width=124) (actual time=193.309..201.125 rows=10.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=48489.07..48523.24 rows=13667 width=124) (actual time=180.492..180.494 rows=7.00 loops=3)
              Sort Key: ((created_at + '00:00:00'::interval)) DESC
              Sort Method: top-N heapsort  Memory: 30kB
              Worker 0:  Sort Method: top-N heapsort  Memory: 30kB
              Worker 1:  Sort Method: top-N heapsort  Memory: 30kB
              ->  Parallel Seq Scan on orders  (cost=0.00..48193.73 rows=13667 width=124) (actual time=83.573..174.698 rows=11106.00 loops=3)
                    Filter: (payment_method = 'bank'::text)
                    Rows Removed by Filter: 655561
Planning Time: 0.284 ms
Execution Time: 201.244 ms

Index ghép (payment_method, created_at) — cột lọc trước, cột sắp sau: nhảy thẳng tới đơn bank mới nhất, 0,09 ms. Đây là cách sửa đúng.
Mẹo ORDER BY created_at + interval '0 second' — biến cột sắp xếp thành biểu thức để index không còn "cho sẵn thứ tự", planner phải lọc trước rồi top-N: 201 ms. Dùng tạm khi chưa thêm được index, và phải để lại comment giải thích — người sau sẽ tưởng là code thừa.

Bẫy này xuất hiện ở mọi màn hình "mới nhất + lọc theo một cột có phân bố lệch theo thời gian". Bài 2 ví dụ 12 gặp đúng bẫy này với status = 'shipped' ORDER BY created_at LIMIT 50 (đơn shipped chỉ có ở 2 tuần cuối): bỏ 1 968 199 dòng, 654 ms.

3.5. Ví dụ 5 — OFFSET và keyset

Laravel Order::latest()->paginate(20) ở trang 5 000 gửi hai câu (query log của lab):

-- 10.3 query log
select count(*) as aggregate from "orders"   -- bindings: []

-- 10.4 query log
select * from "orders" order by "created_at" desc limit 20 offset 99980   -- bindings: []

EXPLAIN (ANALYZE)
SELECT count(*) AS aggregate FROM orders;

Finalize Aggregate  (cost=30489.11..30489.12 rows=1 width=8) (actual time=159.694..164.921 rows=1.00 loops=1)
  ->  Gather  (cost=30488.89..30489.10 rows=2 width=8) (actual time=159.451..164.911 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=29488.89..29488.90 rows=1 width=8) (actual time=146.411..146.412 rows=1.00 loops=3)
              ->  Parallel Index Only Scan using orders_user_id_index on orders  (cost=0.43..27405.58 rows=833325 width=0) (actual time=0.109..107.976 rows=666666.67 loops=3)
                    Heap Fetches: 0
                    Index Searches: 1
Planning Time: 2.242 ms
Execution Time: 165.079 ms

EXPLAIN (ANALYZE)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 99980;

Limit  (cost=4484.26..4485.16 rows=20 width=116) (actual time=38.723..38.728 rows=20.00 loops=1)
  ->  Index Scan Backward using orders_created_at_index on orders  (cost=0.43..89694.13 rows=1999980 width=116) (actual time=0.066..34.910 rows=100000.00 loops=1)
        Index Searches: 1
Planning Time: 0.865 ms
Execution Time: 38.868 ms

count(*) cho số trang: quét song song index nhỏ nhất (orders_user_id_index, 28 MB) — 165 ms mỗi lần mở trang. OFFSET 99980: Index Scan đọc 100 000 dòng rồi vứt 99 980 — trang càng sâu càng chậm, tuyến tính. Còn cursorPaginate (keyset):

-- 10.5 query log
select * from "orders" order by "id" desc limit 21   -- bindings: []

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE id < 1900020 ORDER BY id DESC LIMIT 21;

Limit  (cost=0.43..1.42 rows=21 width=116) (actual time=0.107..0.111 rows=21.00 loops=1)
  ->  Index Scan Backward using orders_pkey on orders  (cost=0.43..90250.54 rows=1906178 width=116) (actual time=0.105..0.108 rows=21.00 loops=1)
        Index Cond: (id < 1900020)
        Index Searches: 1
Planning Time: 0.261 ms
Execution Time: 0.143 ms

0,14 ms, và trang thứ 5 000 cũng như trang đầu. Đổi lại: không nhảy tới "trang 5 000" được, cột sắp xếp phải có khoá duy nhất làm tiêu chí phụ (id), và cần index khớp với thứ tự đó.

3.6. Ví dụ 6 — CTE: inline hay materialize

EXPLAIN (ANALYZE)
WITH recent AS (SELECT * FROM orders WHERE created_at >= '2026-09-01')
SELECT * FROM recent WHERE user_id = 4242;

Bitmap Heap Scan on orders  (cost=4.56..75.65 rows=1 width=116) (actual time=6.883..6.884 rows=0.00 loops=1)
  Recheck Cond: (user_id = 4242)
  Filter: (created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone)
  Rows Removed by Filter: 37
  Heap Blocks: exact=37
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0) (actual time=0.105..0.106 rows=37.00 loops=1)
        Index Cond: (user_id = 4242)
        Index Searches: 1
Planning Time: 1.749 ms
Execution Time: 8.058 ms

EXPLAIN (ANALYZE)
WITH recent AS MATERIALIZED (SELECT * FROM orders WHERE created_at >= '2026-09-01')
SELECT * FROM recent WHERE user_id = 4242;

CTE Scan on recent  (cost=2914.92..4297.71 rows=1 width=208) (actual time=165.603..165.604 rows=0.00 loops=1)
  Filter: (user_id = 4242)
  Rows Removed by Filter: 65990
  Storage: Disk  Maximum Storage: 8680kB
  CTE recent
    ->  Index Scan using orders_created_at_index on orders  (cost=0.43..2914.92 rows=61457 width=116) (actual time=0.113..71.552 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: 0.218 ms
Execution Time: 168.390 ms

Từ PostgreSQL 12, CTE không đệ quy, không có tác dụng phụ, dùng một lần thì được inline như subquery: điều kiện user_id = 4242 đẩy xuống được, plan dùng index user_id — 8 ms. Viết MATERIALIZED là dựng một bức tường: tính xong 65 990 đơn tháng 9, ghi ra đĩa (Storage: Disk Maximum Storage: 8680kB — dòng mới của PG 18), rồi mới lọc — 168 ms. CTE dùng hai lần thì mặc định được materialize:

EXPLAIN (ANALYZE)
WITH daily AS (
  SELECT created_at::date AS d, sum(total_cents) AS revenue
  FROM orders WHERE created_at >= '2026-09-01' GROUP BY 1
)
SELECT d, revenue FROM daily WHERE revenue > (SELECT avg(revenue) FROM daily);

CTE Scan on daily  (cost=5526.86..7063.28 rows=20486 width=12) (actual time=38.349..38.357 rows=29.00 loops=1)
  Filter: ((revenue)::numeric > (InitPlan 2).col1)
  Rows Removed by Filter: 1
  Storage: Memory  Maximum Storage: 18kB
  CTE daily
    ->  HashAggregate  (cost=3375.85..4144.06 rows=61457 width=12) (actual time=38.050..38.320 rows=30.00 loops=1)
          Group Key: (orders.created_at)::date
          Batches: 1  Memory Usage: 2081kB
          ->  Index Scan using orders_created_at_index on orders  (cost=0.43..3068.57 rows=61457 width=8) (actual time=0.070..28.071 rows=65990.00 loops=1)
                Index Cond: (created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone)
                Index Searches: 1
  InitPlan 2
    ->  Aggregate  (cost=1382.79..1382.80 rows=1 width=32) (actual time=0.288..0.288 rows=1.00 loops=1)
          ->  CTE Scan on daily daily_1  (cost=0.00..1229.14 rows=61457 width=8) (actual time=0.001..0.276 rows=30.00 loops=1)
                Storage: Memory  Maximum Storage: 18kB
Planning Time: 0.996 ms
Execution Time: 40.765 ms

daily tính một lần (30 dòng, 18 kB trong RAM), đọc hai lần: một lần trong InitPlan 2 (trung bình), một lần ở CTE Scan chính. (InitPlan 2).col1 là cách PG 18 in tham chiếu tới kết quả InitPlan.

3.7. Ví dụ 7 — SubPlan và InitPlan (withCount của Laravel)

-- 6. withCount
select "orders".*, (select count(*) from "order_items" where "orders"."id" = "order_items"."order_id") as "items_count" from "orders" where "user_id" = 4242

EXPLAIN (ANALYZE)
SELECT o.id, (SELECT count(*) FROM order_items WHERE o.id = order_items.order_id) AS items_count
FROM orders o WHERE o.created_at >= '2026-09-01';

Index Scan using orders_created_at_index on orders o  (cost=0.43..279625.07 rows=61457 width=16) (actual time=0.161..185.755 rows=65990.00 loops=1)
  Index Cond: (created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone)
  Index Searches: 1
  SubPlan 1
    ->  Aggregate  (cost=4.49..4.50 rows=1 width=8) (actual time=0.002..0.002 rows=1.00 loops=65990)
          ->  Index Only Scan using order_items_order_id_index on order_items  (cost=0.43..4.49 rows=3 width=0) (actual time=0.001..0.001 rows=2.50 loops=65990)
                Index Cond: (order_id = o.id)
                Heap Fetches: 0
                Index Searches: 65990
Planning Time: 1.082 ms
Execution Time: 188.907 ms

EXPLAIN (ANALYZE)
SELECT o.id, count(i.id) AS items_count
FROM orders o LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= '2026-09-01'
GROUP BY o.id;

HashAggregate  (cost=104341.90..104956.47 rows=61457 width=16) (actual time=1476.324..1492.308 rows=65990.00 loops=1)
  Group Key: o.id
  Batches: 1  Memory Usage: 4889kB
  ->  Hash Right Join  (cost=3683.14..103573.68 rows=153645 width=16) (actual time=1335.810..1430.774 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=16) (actual time=0.698..485.392 rows=5000000.00 loops=1)
        ->  Hash  (cost=2914.92..2914.92 rows=61457 width=8) (actual time=34.967..34.970 rows=65990.00 loops=1)
              Buckets: 131072 (originally 65536)  Batches: 1 (originally 1)  Memory Usage: 3602kB
              ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..2914.92 rows=61457 width=8) (actual time=0.074..15.037 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: 1.124 ms
Execution Time: 1498.513 ms

withCount('items') thành subquery tương quan trong SELECT → SubPlan chạy mỗi dòng: loops=65990, mỗi lần một Index Only Scan 0,001 ms — 189 ms cho 66 nghìn đơn (cache nóng). Lời khuyên quen thuộc "viết lại bằng JOIN cho nhanh" ở đây lại chậm hơn 8 lần: planner chọn Hash Right Join và quét cả 5 triệu dòng hàng (1,5 s). SubPlan không xấu tự thân; nó xấu khi loops lớn và mỗi vòng đắt (cache lạnh, không có index). Đọc plan thì tính: loops × thời gian một vòng.

EXPLAIN (ANALYZE)
SELECT id, total_cents FROM orders
WHERE created_at >= '2026-09-01'
  AND total_cents > (SELECT avg(total_cents) * 5 FROM orders WHERE created_at >= '2026-09-01');

Index Scan using orders_created_at_index on orders  (cost=3069.01..6290.79 rows=20486 width=12) (actual time=35.893..35.896 rows=0.00 loops=1)
  Index Cond: (created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone)
  Filter: ((total_cents)::numeric > (InitPlan 1).col1)
  Rows Removed by Filter: 65990
  Index Searches: 1
  InitPlan 1
    ->  Aggregate  (cost=3068.57..3068.58 rows=1 width=32) (actual time=18.069..18.072 rows=1.00 loops=1)
          ->  Index Scan using orders_created_at_index on orders orders_1  (cost=0.43..2914.92 rows=61457 width=4) (actual time=0.046..10.989 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: 0.408 ms
Execution Time: 36.140 ms

Subquery không phụ thuộc dòng ngoài (trung bình cả tháng × 5) → InitPlan, chạy đúng một lần (loops=1) rồi dùng như hằng số.

3.8. Ví dụ 8 — window function: "đơn mới nhất của mỗi khách"

EXPLAIN (ANALYZE)
SELECT * FROM (
  SELECT o.id, o.user_id, o.created_at,
         row_number() OVER (PARTITION BY o.user_id ORDER BY o.created_at DESC) AS rn
  FROM orders o WHERE o.user_id <= 1000
) t WHERE rn = 1;

Subquery Scan on t  (cost=55938.93..60461.84 rows=696 width=32) (actual time=454.035..482.598 rows=1000.00 loops=1)
  Filter: (t.rn = 1)
  ->  WindowAgg  (cost=55938.93..58722.25 rows=139167 width=32) (actual time=454.032..482.483 rows=1000.00 loops=1)
        Window: w1 AS (PARTITION BY o.user_id ORDER BY o.created_at ROWS UNBOUNDED PRECEDING)
        Run Condition: (row_number() OVER w1 <= 1)
        Storage: Memory  Maximum Storage: 17kB
        ->  Sort  (cost=55938.91..56286.83 rows=139167 width=24) (actual time=453.597..471.238 rows=141723.00 loops=1)
              Sort Key: o.user_id, o.created_at DESC
              Sort Method: external merge  Disk: 4728kB
              ->  Bitmap Heap Scan on orders o  (cost=1710.97..41193.56 rows=139167 width=24) (actual time=34.658..338.805 rows=141723.00 loops=1)
                    Recheck Cond: (user_id <= 1000)
                    Heap Blocks: exact=36954
                    ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..1676.18 rows=139167 width=0) (actual time=22.940..22.940 rows=141723.00 loops=1)
                          Index Cond: (user_id <= 1000)
                          Index Searches: 1
Planning Time: 1.735 ms
Execution Time: 485.412 ms

EXPLAIN (ANALYZE)
SELECT DISTINCT ON (user_id) id, user_id, created_at
FROM orders WHERE user_id <= 1000
ORDER BY user_id, created_at DESC;

Unique  (cost=55938.91..56634.75 rows=79708 width=24) (actual time=668.603..718.891 rows=1000.00 loops=1)
  ->  Sort  (cost=55938.91..56286.83 rows=139167 width=24) (actual time=668.599..706.711 rows=141723.00 loops=1)
        Sort Key: user_id, created_at DESC
        Sort Method: external merge  Disk: 4728kB
        ->  Bitmap Heap Scan on orders  (cost=1710.97..41193.56 rows=139167 width=24) (actual time=25.942..545.666 rows=141723.00 loops=1)
              Recheck Cond: (user_id <= 1000)
              Heap Blocks: exact=36954
              ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..1676.18 rows=139167 width=0) (actual time=18.215..18.216 rows=141723.00 loops=1)
                    Index Cond: (user_id <= 1000)
                    Index Searches: 1
Planning Time: 0.127 ms
Execution Time: 722.180 ms

BEGIN;
BEGIN
CREATE INDEX orders_user_id_created_at_index ON orders (user_id, created_at);
CREATE INDEX
EXPLAIN (ANALYZE)
SELECT u.id AS user_id, o.id, o.created_at
FROM users u
CROSS JOIN LATERAL (
  SELECT id, created_at FROM orders WHERE orders.user_id = u.id ORDER BY created_at DESC LIMIT 1
) o
WHERE u.id <= 1000;

Nested Loop  (cost=0.85..4786.94 rows=1017 width=24) (actual time=0.115..8.207 rows=1000.00 loops=1)
  ->  Index Only Scan using users_pkey on users u  (cost=0.42..30.22 rows=1017 width=8) (actual time=0.057..0.320 rows=1000.00 loops=1)
        Index Cond: (id <= 1000)
        Heap Fetches: 0
        Index Searches: 1
  ->  Limit  (cost=0.43..4.67 rows=1 width=16) (actual time=0.007..0.008 rows=1.00 loops=1000)
        ->  Index Scan Backward using orders_user_id_created_at_index on orders  (cost=0.43..76.74 rows=18 width=16) (actual time=0.007..0.007 rows=1.00 loops=1000)
              Index Cond: (user_id = u.id)
              Index Searches: 1000
Planning Time: 5.188 ms
Execution Time: 8.324 ms

Cách	Node chính	Thời gian	Vì sao
`row_number() ... WHERE rn = 1`	Sort → WindowAgg (`Run Condition`)	485 ms	Đọc và sắp 141 723 đơn của 1 000 khách
`DISTINCT ON (user_id)`	Sort → Unique	722 ms	Cũng phải sắp hết
`LATERAL (... LIMIT 1)` + index `(user_id, created_at)`	Nested Loop → Limit → Index Scan Backward	8,3 ms	1 000 lần tra, mỗi lần dừng ở dòng đầu

Run Condition cho WindowAgg ngừng tính khi chắc chắn mọi dòng sau đều không thoả rn <= 1 — nhưng nó không bớt được việc đọc và sắp input. Khi số nhóm nhỏ so với số dòng, "mỗi nhóm một lần tra index" thắng xa.

3.9. Ví dụ 9 — partition pruning: ba thời điểm

EXPLAIN (ANALYZE)
SELECT count(*) FROM orders_p WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01';

Aggregate  (cost=1640.82..1640.83 rows=1 width=8) (actual time=627.402..627.404 rows=1.00 loops=1)
  Buffers: shared read=486
  I/O Timings: shared read=371.746
  ->  Seq Scan on orders_p_2026_09 orders_p  (cost=0.00..1475.85 rows=65990 width=0) (actual time=3.065..610.082 rows=65990.00 loops=1)
        Filter: ((created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-10-01 00:00:00+00'::timestamp with time zone))
        Buffers: shared read=486
        I/O Timings: shared read=371.746
Planning:
  Buffers: shared hit=260 read=40 dirtied=1
  I/O Timings: shared read=24.616
Planning Time: 33.509 ms
Execution Time: 627.526 ms

Lúc lập plan: khoảng thời gian cụ thể → plan chỉ có orders_p_2026_09; 35 partition kia không xuất hiện. (Partition này được Seq Scan vì khoảng phủ trọn partition. 627 ms là do đọc lạnh từ đĩa — read=486 trang mất 371 ms; Planning Time 33,5 ms vì kết nối mới phải nạp catalog của 36 partition.) Không lọc theo khoá partition thì không bỏ được gì:

EXPLAIN
SELECT count(*) FROM orders_p WHERE user_id = 4242;

Aggregate  (cost=155.36..155.37 rows=1 width=8)
  ->  Append  (cost=0.29..155.27 rows=36 width=0)
        ->  Index Only Scan using orders_p_2023_10_user_id_idx on orders_p_2023_10 orders_p_1  (cost=0.29..4.30 rows=1 width=0)
              Index Cond: (user_id = 4242)
        ->  Index Only Scan using orders_p_2023_11_user_id_idx on orders_p_2023_11 orders_p_2  (cost=0.29..4.30 rows=1 width=0)
              Index Cond: (user_id = 4242)
        … (lược bớt 66 dòng)
        ->  Index Only Scan using orders_p_2026_09_user_id_idx on orders_p_2026_09 orders_p_36  (cost=0.29..4.31 rows=1 width=0)
              Index Cond: (user_id = 4242)

36 lần tra index, một lần cho mỗi partition. Partition không miễn phí cho câu không có khoá partition.

PREPARE since(timestamptz) AS SELECT count(*) FROM orders_p WHERE created_at >= $1;
PREPARE
SET plan_cache_mode = force_generic_plan;
SET
Finalize Aggregate  (cost=19471.54..19471.55 rows=1 width=8) (actual time=44.800..45.305 rows=1.00 loops=1)
  ->  Gather  (cost=19471.33..19471.54 rows=2 width=8) (actual time=44.788..45.297 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=18471.33..18471.34 rows=1 width=8) (actual time=28.881..28.883 rows=1.00 loops=3)
              ->  Parallel Append  (cost=0.29..17776.88 rows=277778 width=0) (actual time=1.782..27.167 rows=21996.67 loops=3)
                    Subplans Removed: 35
                    ->  Parallel Index Only Scan using orders_p_2026_09_created_at_idx on orders_p_2026_09 orders_p_1  (cost=0.29..542.66 rows=12939 width=0) (actual time=1.781..24.869 rows=21996.67 loops=3)
                          Index Cond: (created_at >= $1)
                          Heap Fetches: 0
                          Index Searches: 1
Planning Time: 3.306 ms
Execution Time: 45.470 ms

Lúc executor khởi động: generic plan chưa biết $1 nên plan chứa đủ 36 partition; khi chạy, giá trị đã biết, executor bỏ 35 cái — plan in Subplans Removed: 35. (Docs lưu ý: partition bị bỏ ở bước này vẫn bị lock lúc bắt đầu thực thi.)

BEGIN;
BEGIN
CREATE TEMP TABLE campaigns (name text, starts_at timestamptz, ends_at timestamptz);
CREATE TABLE
INSERT INTO campaigns VALUES ('Sale 9/9',   '2026-09-09 12:00', '2026-09-09 14:00'),
                             ('Sale 10/10', '2025-10-10 12:00', '2025-10-10 14:00');
INSERT 0 2
ANALYZE campaigns;
ANALYZE
SET max_parallel_workers_per_gather = 0;
SET
HashAggregate  (cost=22427.62..22427.65 rows=2 width=18) (actual time=18.105..18.118 rows=2.00 loops=1)
  Group Key: c.name
  Batches: 1  Memory Usage: 32kB
  ->  Nested Loop  (cost=0.29..20205.40 rows=444444 width=10) (actual time=0.129..18.018 rows=361.00 loops=1)
        ->  Seq Scan on campaigns c  (cost=0.00..1.02 rows=2 width=26) (actual time=0.028..0.033 rows=2.00 loops=1)
        ->  Append  (cost=0.29..7879.98 rows=222221 width=8) (actual time=8.472..8.970 rows=180.50 loops=2)
              ->  Index Only Scan using orders_p_2023_10_created_at_idx on orders_p_2023_10 o_1  (cost=0.29..81.91 rows=2581 width=8) (never executed)
                    Index Cond: ((created_at >= c.starts_at) AND (created_at < c.ends_at))
                    Heap Fetches: 0
                    Index Searches: 0
              ->  Index Only Scan using orders_p_2023_11_created_at_idx on orders_p_2023_11 o_2  (cost=0.29..104.97 rows=3434 width=8) (never executed)
                    Index Cond: ((created_at >= c.starts_at) AND (created_at < c.ends_at))
                    Heap Fetches: 0
                    Index Searches: 0
              … (lược bớt 88 dòng)
              ->  Index Only Scan using orders_p_2025_10_created_at_idx on orders_p_2025_10 o_25  (cost=0.29..215.21 rows=7146 width=8) (actual time=16.850..17.778 rows=172.00 loops=1)
                    Index Cond: ((created_at >= c.starts_at) AND (created_at < c.ends_at))
                    Heap Fetches: 0
                    Index Searches: 1
              … (lược bớt 40 dòng)
              ->  Index Only Scan using orders_p_2026_09_created_at_idx on orders_p_2026_09 o_36  (cost=0.29..222.93 rows=7332 width=8) (actual time=0.077..0.109 rows=189.00 loops=1)
                    Index Cond: ((created_at >= c.starts_at) AND (created_at < c.ends_at))
                    Heap Fetches: 0
                    Index Searches: 1
Planning Time: 16.866 ms
Execution Time: 40.832 ms

Lúc chạy, theo từng vòng: giá trị đến từ vòng ngoài Nested Loop (khung giờ của từng chiến dịch), nên executor chọn partition cho mỗi vòng. Chỉ 2025_10 và 2026_09 được chạy (loops=1 mỗi cái); 34 partition còn lại in (never executed) (output gốc liệt kê đủ cả 36 — ở trên đã lược bớt). Đọc plan có partition, ba dấu hiệu này cho biết pruning có hoạt động hay không.

Hình 3 — Partition pruning có ba thời điểm và mỗi thời điểm để lại một dấu hiệu khác nhau trong plan. Không thấy dấu hiệu nào nghĩa là câu đang đọc mọi partition.

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

Mỗi node chặn được tới work_mem (bảng băm: × hash_mem_multiplier); một câu có nhiều node dùng nhiều lần, nhân số phiên. Vượt thì tràn đĩa — không chết, chỉ chậm. (Ngoại lệ: Parallel Hash trong container — bài 5.)
Không có ORDER BY thì không có thứ tự. LIMIT không có ORDER BY trả "10 dòng nào đó". ORDER BY trên cột không duy nhất cũng không cố định thứ tự giữa các dòng bằng nhau — phân trang OFFSET trên cột đó có thể lặp hoặc sót dòng giữa các trang; luôn thêm khoá duy nhất làm tiêu chí phụ.
OFFSET N luôn phải đọc N dòng (ví dụ 5: 100 000 dòng cho trang 5 000).
CTE được inline khi: không đệ quy, không có tác dụng phụ (không INSERT/UPDATE/DELETE, không hàm VOLATILE), được dùng một lần. MATERIALIZED/NOT MATERIALIZED ép hai chiều.
Subquery tương quan trong SELECT giữ nguyên là SubPlan (không được đổi thành join tự động — ví dụ 7).
Partition pruning cần điều kiện trên khoá partition; tham số/giá trị lúc chạy dùng runtime pruning; số partition lớn làm Planning Time và số lock tăng (lab: 33,5 ms planning cho câu đầu tiên trên 36 partition).
Ước lượng số nhóm của biểu thức không có thống kê có thể sai cả nghìn lần (ví dụ 3: 601 221 so với 273).

5. Failure modes

Triệu chứng	Nguyên nhân	Ví dụ lab	Sửa
Limit trên Index Scan + `Filter` + Rows Removed hàng triệu	Bẫy LIMIT: dòng khớp không rải đều	4: 1,57 s	Index ghép (cột lọc, cột sắp)
Limit với OFFSET lớn, rows của node con = offset + limit	Phân trang sâu	5: đọc 100 000 dòng	Keyset (`cursorPaginate`)
`Sort Method: external merge`	Sort vượt work_mem	1: 79 MB	Ít cột hơn, index cho thứ tự, work_mem theo role
`Planned Partitions` / `Batches` / `Disk Usage`	HashAggregate tràn — hoặc chỉ là ước lượng nhóm sai	3	Thống kê biểu thức; work_mem
GroupAggregate trên Index Scan toàn bảng, `read=` hàng triệu	Cost model đánh giá thấp đọc ngẫu nhiên	3: 10 s vs 2,3 s	work_mem đủ cho HashAggregate; xem random_page_cost (bài 5)
`CTE Scan` + `Rows Removed by Filter` lớn	MATERIALIZED chặn đẩy điều kiện	6: 168 ms vs 8 ms	Bỏ MATERIALIZED nếu không cần
SubPlan loops lớn × mỗi vòng đắt	withCount/subquery tương quan trên tập lớn	7	Đo loops × time; LATERAL/JOIN khi có lợi
Append qua mọi partition	Thiếu điều kiện khoá partition	9: 36 lần tra	Thêm khoảng thời gian vào WHERE

6. Vận hành trong production

Tràn đĩa: bật log_temp_files (ngưỡng vd 10 MB), theo dõi pg_stat_database.temp_bytes — lab ở bài 5.
work_mem theo role: role báo cáo 64–256 MB, role web giữ mặc định; không tăng toàn cục.
Phân trang: API công khai dùng keyset; trang admin có OFFSET thì giới hạn số trang tối đa. count(*) chính xác trên bảng lớn tốn 165 ms mỗi lần — nhiều app hiển thị "khoảng N kết quả" từ pg_class.reltuples hoặc bỏ tổng số trang.
Màn hình "mới nhất + lọc": với mỗi bộ lọc hay dùng, kiểm tra plan có rơi vào bẫy LIMIT không; index ghép là lời giải chuẩn.
Partition: mọi câu trên bảng partition nên có điều kiện trên khoá partition; tạo trước partition cho kỳ tới; theo dõi Planning Time khi số partition tăng.

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

Bài toán	Cách tốt	Cách tránh	Số lab
Phân trang danh sách lớn	Keyset (`cursorPaginate`)	OFFSET sâu + count(*) mỗi trang	0,14 ms vs 38,9 ms + 165 ms
Mới nhất + lọc theo cột lệch	Index ghép (cột lọc, cột sắp)	Index đơn trên cột sắp	0,09 ms vs 1 566 ms
Dòng mới nhất mỗi nhóm	LATERAL + LIMIT 1 + index ghép	row_number / DISTINCT ON trên tập lớn	8,3 ms vs 485 / 722 ms
Đếm con cho trang danh sách	withCount (SubPlan) khi trang nhỏ	JOIN + GROUP BY quét cả bảng con	189 ms vs 1 499 ms
Dùng lại kết quả trung gian	CTE inline (mặc định)	MATERIALIZED không lý do	8 ms vs 168 ms
Bảng sự kiện theo thời gian rất lớn	Partition theo tháng + WHERE có khoảng thời gian	Partition rồi query không có khoá	1 partition vs 36

Dùng partition khi bảng lớn tới mức cần xoá theo kỳ và query luôn có khoá thời gian; tránh partition chỉ vì bảng "to", khi phần lớn câu không lọc theo khoá partition — plan sẽ đi qua mọi partition.

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

Chuẩn phân trang cho cả team: API dùng cursor; ghi vào guideline cùng ví dụ số đo (OFFSET trang sâu tuyến tính theo số trang).
Thiết kế index theo màn hình: mỗi danh sách "lọc + sắp + LIMIT" có index ghép tương ứng, ghi lý do trong migration.
Review câu báo cáo: xem Sort Method, Disk Usage, số nhóm ước lượng; chạy trên replica với role riêng.
Mẹo + interval '0' là nợ kỹ thuật — cho phép như biện pháp tạm, kèm ticket thêm index.

Mẫu ADR ngắn: "Mọi API danh sách dùng keyset pagination (cursorPaginate, khoá phụ id). Trang admin dùng OFFSET tối đa 100 trang. Mọi danh sách 'lọc theo X, mới nhất trước' phải có index (X, created_at). Lý do: lab cho thấy OFFSET trang 5 000 đọc 100 000 dòng, và bẫy LIMIT làm câu '10 đơn bank mới nhất' mất 1,6 giây thay vì 0,09 ms."

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

OFFSET cho phân trang sâu, cộng count(*) mỗi lần mở trang.
ORDER BY cột không duy nhất + OFFSET — trang lặp/sót dòng.
Index đơn trên created_at cho mọi màn hình "mới nhất" — bẫy LIMIT với bộ lọc lệch.
WITH ... AS MATERIALIZED như "tối ưu" mà không đo.
DISTINCT để chữa dòng trùng do join sai — tốn một Sort/HashAggregate để che lỗi logic.
Rewrite SubPlan thành JOIN theo phản xạ — ví dụ 7 chậm hơn 8 lần.
Partition bảng rồi query không có khoá partition.

Postmortem công khai — GoCardless ("Debugging the Postgres query planner", Lawrence Jones, 2018, gocardless.com): câu ... WHERE payout_id = ? ORDER BY payment_transitions.id ASC LIMIT 1 trên bảng khoảng nửa tỷ dòng. Planner chọn đi khoá chính theo thứ tự id và lọc payout_id (đúng bẫy của ví dụ 4) thay vì dùng index payout_id, phải quét khoảng 17,5 triệu dòng. Gốc: n_distinct của payout_id bị ước lượng 25 650 trong khi thật khoảng 3,5 triệu, nên planner tưởng mỗi payout có ~2 600 dòng và "đi theo id kiểu gì cũng gặp sớm". Cách sửa của họ: ALTER TABLE ... ALTER COLUMN payout_id SET STATISTICS 5000. pganalyze tổng kết cùng loại bẫy ("Postgres Planner Quirks: The impact of ORDER BY + LIMIT on index usage") với các cách sửa: tăng statistics target, mẹo ORDER BY col + 0, bỏ ORDER BY không cần, index phù hợp. Bài 5 lặp lại phép đo n_distinct trên dữ liệu lab.

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

Sort Method có những loại nào?: quicksort (RAM), top-N heapsort (LIMIT nhỏ — chỉ giữ N dòng), external merge (vượt work_mem — sắp từng đoạn ra đĩa rồi trộn). Incremental Sort khi input đã sắp theo tiền tố.
Vì sao thêm LIMIT lại làm câu chậm đi?: LIMIT làm planner chọn plan "ra dòng đầu nhanh", giả định dòng khớp rải đều theo thứ tự index. Dòng khớp dồn về một phía (lab: đơn bank chỉ có trước 2025) thì phải lọc hàng triệu dòng. Sửa bằng index ghép.
HashAggregate hay GroupAggregate?: Hash khi số nhóm vừa bộ nhớ và input chưa sắp; Group khi input đã sắp (index) hoặc số nhóm quá lớn. Cost có thể gần bằng mà thực tế chênh 4 lần (lab).
OFFSET phân trang có vấn đề gì?: Đọc và bỏ mọi dòng trước offset (tuyến tính theo số trang); không ổn định khi dữ liệu thay đổi giữa các trang. Keyset ổn định và nhanh, đổi lại không nhảy trang được.
CTE có phải "optimization fence"?: Trước PG 12 thì có. Từ 12, CTE dùng một lần, không tác dụng phụ được inline; muốn chặn thì viết MATERIALIZED.
SubPlan khác InitPlan?: SubPlan phụ thuộc dòng ngoài, chạy mỗi dòng (loops = số dòng); InitPlan không phụ thuộc, chạy một lần.
Nhìn plan sao biết partition pruning có chạy?: Partition vắng mặt (lúc lập plan), Subplans Removed: N (lúc khởi động), (never executed) (lúc chạy theo vòng).

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

Môi trường: như bài 1 (PostgreSQL 18.4, tmp_qplan, JIT tắt). Bảng orders_p tạo trong setup.sql (36 partition tháng, index created_at và user_id trên từng partition).

cd labs/query-plan
./run.sh 04-*.sql      # → out/04-*.txt (04-03 chạy ~20 giây vì có câu GroupAggregate 10 giây)

Chưa verify: phiên bản bắt đầu có Run Condition cho WindowAgg và cách in (InitPlan N).colN ở các bản trước 18 (lab chỉ có 18.4); lock trên partition bị loại lúc khởi động (theo docs, chưa đo số lock); hành vi lặp/sót dòng của OFFSET với cột sắp không duy nhất (nguyên tắc SQL, không dựng lab).