Join node — Nested Loop, Hash Join, Merge Join, Memoize và semi/anti join

Join là chỗ plan tốn kém nhất và cũng dễ sai nhất: cùng một câu, chọn nhầm thuật toán join là chênh hàng trăm lần. Bài này đọc từng loại join node trên dữ liệu lab (bảng orders 2 triệu, order_items 5 triệu, users 200 nghìn), kèm các dạng SQL mà Eloquent sinh ra: whereHas, orWhereHas, whereNotIn với subquery, leftJoin()->where(). Cách đọc số trên từng dòng (cost, rows, loops) ở bài 1; các kiểu scan làm lá của join ở bài 2.

Thuật ngữ dùng trong bài
outer / inner (đầu vào thứ nhất / thứ hai của một join node — trong plan là node con đứng trên / đứng dưới) · build / probe (dựng bảng băm từ một bên / dò bảng băm bằng bên kia) · bucket (ô của bảng băm) · batch (phần bảng băm bị tách ra ghi file tạm khi không đủ bộ nhớ) · hash_mem_multiplier (hệ số nhân work_mem cho các node dùng bảng băm, mặc định 2) · parameterized scan (scan vòng trong nhận giá trị từ dòng vòng ngoài, thấy được qua Index Cond: (order_id = o.id)) · Memoize (bộ nhớ đệm kết quả vòng trong theo giá trị tham số, PG 14+) · semi join (chỉ cần biết "có ít nhất một dòng khớp" — EXISTS, IN) · anti join (chỉ lấy dòng "không có dòng khớp" — NOT EXISTS) · SubPlan (subquery chạy lại cho từng dòng) · hashed SubPlan (subquery chạy một lần, kết quả để trong bảng băm) · join order (thứ tự ghép các bảng) · GEQO (tìm thứ tự join bằng thuật toán di truyền khi quá nhiều bảng).

Kiểm chứng: tài liệu PostgreSQL 18 qua Context7 (using-explain, explicit-joins, runtime-config-query, runtime-config-resource — work_mem/hash_mem_multiplier, release notes 17). Plan là output thật trên PostgreSQL 18.4, database tmp_qplan (bài 1), file labs/query-plan/03-*.sql, output labs/query-plan/out/03-*.txt. SQL của Eloquent lấy từ Laravel 12.64 (labs/query-plan/laravel/eloquent.php, join-sql.php). Phần lớn ví dụ tắt parallel (max_parallel_workers_per_gather = 0) cho cây gọn — có ghi rõ ở từng ví dụ.

Trả lời 30 giây

Ba thuật toán. Nested Loop: với mỗi dòng outer, tra inner — tốt khi outer ít dòng và inner có index; tổng chi phí ≈ số dòng outer × một lần tra. Hash Join: băm bên nhỏ, dò bằng bên lớn — tốt cho tập lớn, chỉ cho điều kiện =, cần work_mem × hash_mem_multiplier; thiếu thì chia batch ra đĩa (Batches > 1). Merge Join: trộn hai dòng đã sắp — tốt khi cả hai đã có thứ tự từ index, ít bộ nhớ, dừng sớm được. Memoize đệm kết quả vòng trong khi tham số lặp lại. Planner chọn theo rows ước lượng của outer — ước lượng sai thì chọn sai, và plan để lộ dấu vết: loops rất lớn ở vòng trong, hoặc Batches: 4 (originally 1). EXISTS/IN thành semi join, NOT EXISTS thành anti join; còn NOT IN (subquery) không thành anti join được vì ngữ nghĩa NULL — lab: 10 phút chưa xong, trong khi NOT EXISTS mất 1,6 giây. Với Laravel: dùng whereDoesntHave (NOT EXISTS), đừng dùng whereNotIn('id', subquery).

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

Mô hình quan hệ tách dữ liệu thành nhiều bảng để không lặp (khách, đơn, dòng hàng); câu hỏi nghiệp vụ thì cần ghép lại. Join vì thế là phép toán chiếm phần lớn chi phí của các câu "thật". Ba thuật toán cổ điển đều có trong PostgreSQL:

Nested loop — cách hiển nhiên nhất: hai vòng lặp lồng nhau. Không cần bộ nhớ, xử lý được mọi điều kiện (<, BETWEEN, hàm…). Nhanh khi vòng trong là một lần tra index.
Sort-merge — sắp hai bên theo khoá rồi đi song song như kéo khoá kéo. Hợp khi dữ liệu đã có sẵn thứ tự.
Hash join — phổ biến từ các nghiên cứu cơ sở dữ liệu thập niên 1980: băm một bên vào bộ nhớ, dò bằng bên kia. Nhanh nhất cho hai tập lớn không có thứ tự, đổi lại tốn bộ nhớ và chỉ dùng cho phép bằng.

PostgreSQL 14 thêm Memoize: nhớ kết quả vòng trong của nested loop theo giá trị tham số, để khoá lặp lại khỏi phải tra lại. Mọi join trong PostgreSQL là hai đầu vào; câu join 8 bảng là một cây 7 join node, và planner phải chọn cả thứ tự lẫn thuật toán cho từng node.

Tối ưu cho: chọn thuật toán cho từng cặp dựa trên số dòng ước lượng. Không tối ưu cho: đổi thuật toán giữa chừng khi phát hiện ước lượng sai — PostgreSQL không có "adaptive join"; Hash Join chỉ nới được số bucket/batch lúc chạy (thấy qua chữ originally, ví dụ 10). Một số hệ khác có adaptive join (vd SQL Server) — không dựng lab để so.

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

Mọi join là: với mỗi dòng bên ngoài, tìm các dòng khớp bên trong. Ba thuật toán chỉ khác nhau ở chữ "tìm":

Nested Loop: tìm bằng cách chạy lại node inner (tốt nhất là một lần tra index) → chi phí ≈ rows(outer) × chi phí một lần tra.
Hash Join: tìm trong bảng băm đã dựng sẵn từ inner → chi phí ≈ đọc inner một lần + đọc outer một lần (+ tràn đĩa nếu thiếu RAM).
Merge Join: tìm bằng cách đi song song hai danh sách đã sắp → chi phí ≈ sắp hai bên (hoặc 0 nếu index đã sắp) + một lượt đi.

Hình 1 — Ba cách "tìm dòng khớp". Nested Loop tra lại inner mỗi dòng outer (đếm bằng loops). Hash Join đọc mỗi bên đúng một lần nhưng phải dựng bảng băm trước. Merge Join đi song song hai danh sách đã sắp và dừng khi một bên hết (ví dụ 3 đọc 750 nghìn dòng order_items thay vì 5 triệu).

Hệ quả quan trọng nhất: cả ba công thức đều phụ thuộc rows ước lượng của outer. Outer ước lượng 10 dòng thì Nested Loop rẻ nhất; 300 nghìn dòng thì Hash hoặc Merge. Đọc join trong plan:

Node con đứng trên là outer, đứng dưới là inner. Với Hash Join, inner luôn là node Hash — bên bị băm (thường là bên planner nghĩ nhỏ hơn).
Điều kiện ghép: Hash Cond (Hash Join), Merge Cond (Merge Join), còn Nested Loop đẩy điều kiện xuống inner thành Index Cond: (... = o.id) — thấy tham chiếu tới cột của outer là biết inner được chạy lại theo từng dòng.
Join Filter = điều kiện không dùng để tra được, phải thử trên từng cặp (ví dụ 8).

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

3.1. Ví dụ 1 — Nested Loop

Trang "chi tiết đơn hàng" — một đơn, các dòng hàng, tên sản phẩm:

EXPLAIN (ANALYZE)
SELECT o.order_number, p.name, i.quantity, i.unit_price_cents
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p    ON p.id = i.product_id
WHERE o.id = 1234567;

Nested Loop  (cost=1.15..41.88 rows=3 width=39) (actual time=4.985..7.532 rows=4.00 loops=1)
  ->  Nested Loop  (cost=0.86..16.96 rows=3 width=29) (actual time=4.921..4.933 rows=4.00 loops=1)
        ->  Index Scan using orders_pkey on orders o  (cost=0.43..8.45 rows=1 width=21) (actual time=0.148..0.150 rows=1.00 loops=1)
              Index Cond: (id = 1234567)
              Index Searches: 1
        ->  Index Scan using order_items_order_id_index on order_items i  (cost=0.43..8.48 rows=3 width=24) (actual time=4.764..4.771 rows=4.00 loops=1)
              Index Cond: (order_id = 1234567)
              Index Searches: 1
  ->  Index Scan using products_pkey on products p  (cost=0.29..8.31 rows=1 width=26) (actual time=0.644..0.644 rows=1.00 loops=4)
        Index Cond: (id = i.product_id)
        Index Searches: 4
Planning Time: 18.337 ms
Execution Time: 7.885 ms

Hai Nested Loop lồng nhau, mọi lá đều là tra index. Đọc từ trong ra: tra đơn theo khoá chính (1 dòng) → với dòng đó, tra 4 dòng hàng theo order_id → với mỗi dòng hàng, tra sản phẩm theo khoá chính (loops=4). Mở rộng lên các đơn của khách 4242:

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

Nested Loop  (cost=5.29..242.74 rows=45 width=35) (actual time=2.699..141.920 rows=83.00 loops=1)
  Buffers: shared hit=220 read=217
  I/O Timings: shared read=135.763
  ->  Nested Loop  (cost=5.00..228.88 rows=45 width=25) (actual time=1.210..81.534 rows=83.00 loops=1)
        Buffers: shared hit=76 read=112
        I/O Timings: shared read=77.707
        ->  Bitmap Heap Scan on orders o  (cost=4.57..75.61 rows=18 width=21) (actual time=0.774..2.499 rows=37.00 loops=1)
              Recheck Cond: (user_id = 4242)
              Heap Blocks: exact=37
              Buffers: shared hit=19 read=21
              I/O Timings: shared read=0.634
              ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0) (actual time=0.477..0.478 rows=37.00 loops=1)
                    Index Cond: (user_id = 4242)
                    Index Searches: 1
                    Buffers: shared read=3
                    I/O Timings: shared read=0.432
        ->  Index Scan using order_items_order_id_index on order_items i  (cost=0.43..8.48 rows=3 width=20) (actual time=2.120..2.124 rows=2.24 loops=37)
              Index Cond: (order_id = o.id)
              Index Searches: 37
              Buffers: shared hit=57 read=91
              I/O Timings: shared read=77.073
  ->  Index Scan using products_pkey on products p  (cost=0.29..0.31 rows=1 width=26) (actual time=0.718..0.718 rows=1.00 loops=83)
        Index Cond: (id = i.product_id)
        Index Searches: 83
        Buffers: shared hit=144 read=105
        I/O Timings: shared read=58.056
Planning:
  Buffers: shared hit=22 read=11
  I/O Timings: shared read=5.014
Planning Time: 6.188 ms
Execution Time: 142.121 ms

37 đơn → vòng tra dòng hàng chạy 37 lần; 83 dòng hàng → vòng tra sản phẩm chạy 83 lần. Câu chạy 142 ms, nhưng I/O Timings: shared read=135.763 ở dòng gốc nói rõ: 96% thời gian là chờ đĩa — mỗi lần tra là một lần đọc ngẫu nhiên vào trang chưa có trong cache. Plan không sai; chạy lần hai (cache nóng) câu này chỉ còn vài ms. Đây là điểm yếu bẩm sinh của Nested Loop: số lần đọc ngẫu nhiên tăng theo số dòng outer.

3.2. Ví dụ 2 — Hash Join và batch

Báo cáo doanh thu tháng 9/2026 theo quốc gia của khách (tắt parallel):

EXPLAIN (ANALYZE)
SELECT u.country, count(*) AS orders, sum(o.total_cents) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-09-01' AND o.created_at < '2026-10-01'
GROUP BY u.country;

HashAggregate  (cost=13144.82..13144.88 rows=6 width=19) (actual time=203.579..203.585 rows=6.00 loops=1)
  Group Key: u.country
  Batches: 1  Memory Usage: 32kB
  Buffers: shared hit=1169 read=2661, temp read=582 written=582
  I/O Timings: shared read=6.266, temp read=1.930 write=11.102
  ->  Hash Join  (cost=7875.43..12683.89 rows=61457 width=7) (actual time=98.512..191.878 rows=65990.00 loops=1)
        Hash Cond: (o.user_id = u.id)
        Buffers: shared hit=1169 read=2661, temp read=582 written=582
        I/O Timings: shared read=6.266, temp read=1.930 write=11.102
        ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..3068.57 rows=61457 width=12) (actual time=0.121..23.500 rows=65990.00 loops=1)
              Index Cond: ((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))
              Index Searches: 1
              Buffers: shared hit=1169 read=263
              I/O Timings: shared read=3.686
        ->  Hash  (cost=4398.00..4398.00 rows=200000 width=11) (actual time=96.099..96.101 rows=200000.00 loops=1)
              Buckets: 262144  Batches: 2  Memory Usage: 6754kB
              Buffers: shared read=2398, temp written=438
              I/O Timings: shared read=2.580, temp write=7.970
              ->  Seq Scan on users u  (cost=0.00..4398.00 rows=200000 width=11) (actual time=0.924..36.286 rows=200000.00 loops=1)
                    Buffers: shared read=2398
                    I/O Timings: shared read=2.580
Planning:
  Buffers: shared hit=290 read=17
  I/O Timings: shared read=16.721
Planning Time: 21.583 ms
Execution Time: 205.116 ms

Đọc: Hash (inner) đọc cả 200 000 khách và dựng bảng băm; Index Scan trên orders (outer) đưa 65 990 đơn đi dò. Buckets: 262144 Batches: 2 Memory Usage: 6754kB: bảng băm cần hơn work_mem × hash_mem_multiplier = 4 MB × 2 nên bị chia đôi — nửa đầu ở RAM, nửa sau ghi file tạm (temp written=438), xử lý sau. Đổi work_mem:

SET work_mem = '1MB';
SET
HashAggregate  (cost=13144.82..13144.88 rows=6 width=19) (actual time=176.930..176.936 rows=6.00 loops=1)
  Group Key: u.country
  Batches: 1  Memory Usage: 32kB
  ->  Hash Join  (cost=7875.43..12683.89 rows=61457 width=7) (actual time=63.186..165.982 rows=65990.00 loops=1)
        Hash Cond: (o.user_id = u.id)
        ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..3068.57 rows=61457 width=12) (actual time=0.036..16.443 rows=65990.00 loops=1)
              Index Cond: ((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))
              Index Searches: 1
        ->  Hash  (cost=4398.00..4398.00 rows=200000 width=11) (actual time=62.668..62.670 rows=200000.00 loops=1)
              Buckets: 65536  Batches: 8  Memory Usage: 1692kB
              ->  Seq Scan on users u  (cost=0.00..4398.00 rows=200000 width=11) (actual time=0.033..19.896 rows=200000.00 loops=1)
Planning Time: 0.383 ms
Execution Time: 177.634 ms

SET work_mem = '64MB';
SET
HashAggregate  (cost=10588.82..10588.88 rows=6 width=19) (actual time=217.612..217.617 rows=6.00 loops=1)
  Group Key: u.country
  Batches: 1  Memory Usage: 32kB
  ->  Hash Join  (cost=6898.43..10127.89 rows=61457 width=7) (actual time=116.290..202.991 rows=65990.00 loops=1)
        Hash Cond: (o.user_id = u.id)
        ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..3068.57 rows=61457 width=12) (actual time=0.038..19.781 rows=65990.00 loops=1)
              Index Cond: ((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))
              Index Searches: 1
        ->  Hash  (cost=4398.00..4398.00 rows=200000 width=11) (actual time=113.529..113.531 rows=200000.00 loops=1)
              Buckets: 262144  Batches: 1  Memory Usage: 11423kB
              ->  Seq Scan on users u  (cost=0.00..4398.00 rows=200000 width=11) (actual time=0.063..33.550 rows=200000.00 loops=1)
Planning Time: 0.908 ms
Execution Time: 220.100 ms

`work_mem`	Batches	Memory Usage	temp	Execution Time
1 MB	8	1 692 kB	1 025 trang đọc + ghi	177,6 ms
4 MB (mặc định)	2	6 754 kB	582 trang	205,1 ms
64 MB	1	11 423 kB	0	220,1 ms

Kết quả trung thực: ở đây tràn đĩa gần như không tốn gì — file tạm nhỏ, nằm trong page cache của OS; chênh lệch thời gian chỉ là nhiễu của máy dùng chung. Batch đáng sợ khi file tạm lớn hơn RAM trống hoặc đĩa chậm, và khi nhiều phiên cùng tràn một lúc. Một ngộ nhận khác: "lấy ít cột cho bảng băm nhỏ lại":

EXPLAIN (ANALYZE)
SELECT u.country, count(*) AS orders, sum(o.total_cents) AS revenue
FROM orders o
JOIN (SELECT id, country FROM users) u ON u.id = o.user_id
WHERE o.created_at >= '2026-09-01' AND o.created_at < '2026-10-01'
GROUP BY u.country;

HashAggregate  (cost=13144.82..13144.88 rows=6 width=19) (actual time=227.527..227.532 rows=6.00 loops=1)
  Group Key: users.country
  Batches: 1  Memory Usage: 32kB
  ->  Hash Join  (cost=7875.43..12683.89 rows=61457 width=7) (actual time=95.762..212.752 rows=65990.00 loops=1)
        Hash Cond: (o.user_id = users.id)
        ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..3068.57 rows=61457 width=12) (actual time=0.054..19.722 rows=65990.00 loops=1)
              Index Cond: ((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))
              Index Searches: 1
        ->  Hash  (cost=4398.00..4398.00 rows=200000 width=11) (actual time=93.508..93.510 rows=200000.00 loops=1)
              Buckets: 262144  Batches: 2  Memory Usage: 6754kB
              ->  Seq Scan on users  (cost=0.00..4398.00 rows=200000 width=11) (actual time=0.285..24.405 rows=200000.00 loops=1)
Planning Time: 0.547 ms
Execution Time: 229.336 ms

Không đổi gì: width=11 như cũ. Planner vốn chỉ đưa vào bảng băm các cột cần dùng (id, country), viết subquery không giúp. Bản song song (cấu hình mặc định):

Finalize GroupAggregate  (cost=9226.74..9228.02 rows=6 width=19) (actual time=113.028..119.649 rows=6.00 loops=1)
  Group Key: u.country
  ->  Gather Merge  (cost=9226.74..9227.88 rows=10 width=19) (actual time=113.020..119.636 rows=12.00 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        ->  Sort  (cost=8226.73..8226.75 rows=6 width=19) (actual time=106.064..106.067 rows=6.00 loops=2)
              Sort Key: u.country
              Sort Method: quicksort  Memory: 25kB
              Worker 0:  Sort Method: quicksort  Memory: 25kB
              ->  Partial HashAggregate  (cost=8226.60..8226.66 rows=6 width=19) (actual time=105.982..105.987 rows=6.00 loops=2)
                    Group Key: u.country
                    Batches: 1  Memory Usage: 32kB
                    Worker 0:  Batches: 1  Memory Usage: 32kB
                    ->  Parallel Hash Join  (cost=5045.49..7955.46 rows=36151 width=7) (actual time=62.124..98.104 rows=32995.00 loops=2)
                          Hash Cond: (o.user_id = u.id)
                          ->  Parallel Index Scan using orders_created_at_index on orders o  (cost=0.43..2815.51 rows=36151 width=12) (actual time=0.132..9.644 rows=32995.00 loops=2)
                                Index Cond: ((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))
                                Index Searches: 1
                          ->  Parallel Hash  (cost=3574.47..3574.47 rows=117647 width=11) (actual time=60.445..60.446 rows=100000.00 loops=2)
                                Buckets: 262144  Batches: 1  Memory Usage: 11456kB
                                ->  Parallel Seq Scan on users u  (cost=0.00..3574.47 rows=117647 width=11) (actual time=0.021..18.579 rows=100000.00 loops=2)
Planning Time: 0.581 ms
Execution Time: 119.859 ms

Parallel Hash: các process cùng dựng một bảng băm dùng chung (trong shared memory). Nó vừa bộ nhớ (Batches: 1, 11 456 kB) dù vượt 8 MB của một process — ngân sách bộ nhớ được cộng theo số process tham gia. Chính vì nằm trong shared memory nên trong Docker (/dev/shm 64 MB) nó có thể chết khi tăng work_mem — lab ở bài 5.

Hình 2 — Batches: N với N > 1 nghĩa là cả hai bên của join đều bị ghi ra file tạm theo từng phần rồi đọc lại. Cái giá phụ thuộc dung lượng file tạm so với RAM trống — ở lab nhỏ thì gần như không đáng kể, ở báo cáo lớn chạy song song nhiều phiên thì rất đáng kể.

3.3. Ví dụ 3 — Merge Join

Tổng tiền dòng hàng của 300 nghìn đơn đầu tiên. Cả hai bên đã có thứ tự theo khoá join nhờ index:

EXPLAIN (ANALYZE)
SELECT o.id, sum(i.quantity * i.unit_price_cents) AS items_total
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.id <= 300000
GROUP BY o.id;

GroupAggregate  (cost=9.70..189375.75 rows=303814 width=16) (actual time=0.392..1420.821 rows=300000.00 loops=1)
  Group Key: o.id
  ->  Merge Join  (cost=9.70..180640.99 rows=759549 width=16) (actual time=0.383..1049.995 rows=750000.00 loops=1)
        Merge Cond: (o.id = i.order_id)
        ->  Index Only Scan using orders_pkey on orders o  (cost=0.43..8653.17 rows=303814 width=8) (actual time=0.064..150.360 rows=300000.00 loops=1)
              Index Cond: (id <= 300000)
              Heap Fetches: 0
              Index Searches: 1
        ->  Index Scan using order_items_order_id_index on order_items i  (cost=0.43..151133.03 rows=5000040 width=16) (actual time=0.105..580.270 rows=750001.00 loops=1)
              Index Searches: 1
Planning Time: 2.514 ms
Execution Time: 1445.602 ms

SET enable_mergejoin = off;
SET
HashAggregate  (cost=214102.87..224558.48 rows=303814 width=16) (actual time=3458.407..3664.381 rows=300000.00 loops=1)
  Group Key: o.id
  Planned Partitions: 4  Batches: 5  Memory Usage: 8249kB  Disk Usage: 19472kB
  ->  Hash Join  (cost=13637.85..163545.39 rows=759549 width=16) (actual time=108.567..3148.143 rows=750000.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.038..1039.176 rows=5000000.00 loops=1)
        ->  Hash  (cost=8653.17..8653.17 rows=303814 width=8) (actual time=106.029..106.032 rows=300000.00 loops=1)
              Buckets: 262144  Batches: 2  Memory Usage: 7912kB
              ->  Index Only Scan using orders_pkey on orders o  (cost=0.43..8653.17 rows=303814 width=8) (actual time=0.084..30.051 rows=300000.00 loops=1)
                    Index Cond: (id <= 300000)
                    Heap Fetches: 0
                    Index Searches: 1
Planning Time: 0.186 ms
Execution Time: 3689.428 ms

Ba điều đọc được. (1) Không có node Sort nào: orders_pkey và order_items_order_id_index đã cho sẵn thứ tự. (2) Index Scan bên order_items chỉ đọc 750 001 dòng chứ không phải 5 triệu — khi bên orders hết (id > 300 000), Merge Join dừng, không cần đọc tiếp. (3) Ép sang Hash Join: phải quét cả 5 triệu dòng order_items, và cả bước HashAggregate phía trên cũng tràn đĩa (Planned Partitions: 4 Batches: 5 ... Disk Usage: 19472kB) — 3,7 giây so với 1,4 giây. Merge Join hợp nhất khi cả hai bên đã sắp sẵn và join theo khoảng lớn; phải Sort cả hai bên thì nó hiếm khi thắng Hash Join.

3.4. Ví dụ 4 — Memoize

Báo cáo đơn tháng 9 của 50 khách VIP, kèm tổng số đơn trọn đời của từng khách (LATERAL subquery — vòng trong nhận o.user_id):

EXPLAIN (ANALYZE)
SELECT o.id, o.user_id, s.lifetime_orders
FROM orders o
CROSS JOIN LATERAL (
  SELECT count(*) AS lifetime_orders FROM orders p WHERE p.user_id = o.user_id
) s
WHERE o.created_at >= '2026-09-01' AND o.user_id <= 50;

Nested Loop  (cost=5.22..7924.94 rows=1011 width=24) (actual time=2.321..23.220 rows=1034.00 loops=1)
  ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..3068.57 rows=1011 width=16) (actual time=0.086..14.160 rows=1034.00 loops=1)
        Index Cond: (created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone)
        Filter: (user_id <= 50)
        Rows Removed by Filter: 64956
        Index Searches: 1
  ->  Memoize  (cost=4.80..4.81 rows=1 width=8) (actual time=0.008..0.008 rows=1.00 loops=1034)
        Cache Key: o.user_id
        Cache Mode: binary
        Hits: 984  Misses: 50  Evictions: 0  Overflows: 0  Memory Usage: 6kB
        ->  Aggregate  (cost=4.79..4.80 rows=1 width=8) (actual time=0.135..0.135 rows=1.00 loops=50)
              ->  Index Only Scan using orders_user_id_index on orders p  (cost=0.43..4.74 rows=18 width=0) (actual time=0.022..0.069 rows=631.66 loops=50)
                    Index Cond: (user_id = o.user_id)
                    Heap Fetches: 0
                    Index Searches: 50
Planning Time: 2.726 ms
Execution Time: 23.581 ms

SET enable_memoize = off;
SET
Nested Loop  (cost=5.21..7928.95 rows=1011 width=24) (actual time=0.445..183.379 rows=1034.00 loops=1)
  ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..3068.57 rows=1011 width=16) (actual time=0.034..11.300 rows=1034.00 loops=1)
        Index Cond: (created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone)
        Filter: (user_id <= 50)
        Rows Removed by Filter: 64956
        Index Searches: 1
  ->  Aggregate  (cost=4.79..4.80 rows=1 width=8) (actual time=0.165..0.166 rows=1.00 loops=1034)
        ->  Index Only Scan using orders_user_id_index on orders p  (cost=0.43..4.74 rows=18 width=0) (actual time=0.006..0.098 rows=1277.84 loops=1034)
              Index Cond: (user_id = o.user_id)
              Heap Fetches: 0
              Index Searches: 1034
Planning Time: 0.216 ms
Execution Time: 183.618 ms

1 034 đơn nhưng chỉ 50 khách khác nhau. Memoize giữ kết quả theo Cache Key: o.user_id: 50 lần Misses (chạy thật vòng trong — loops=50 ở Aggregate) và 984 lần Hits (lấy từ bộ nhớ đệm). 23,6 ms so với 183,6 ms khi tắt. Đọc thêm: Evictions (phải bỏ bớt mục vì hết bộ nhớ) và Overflows (một khoá có kết quả quá lớn, không đệm được) khác 0 là dấu hiệu Memoize đang làm việc vô ích. Cache Mode: binary nghĩa là so khoá theo từng byte (theo comment trong source PostgreSQL, dùng cho LATERAL vì subquery có thể "thấy" khác biệt giữa hai giá trị bằng nhau như 1.0 và 1.00 — chưa đối chiếu docs). Planner chỉ chọn Memoize khi ước lượng số giá trị khác nhau của khoá nhỏ so với số dòng — nếu n_distinct đoán sai, Memoize có thể được chọn mà không có hit nào.

Hình 3 — Memoize đứng giữa Nested Loop và vòng trong. Khoá đã gặp thì trả kết quả đã nhớ; khoá mới thì chạy vòng trong rồi lưu lại. Lợi khi khoá lặp nhiều (khách VIP); vô ích khi gần như mọi khoá đều khác nhau.

3.5. Ví dụ 5 — join qua khoá ngoại không có index

Màn hình admin "đơn pending từ 29/9 giao tới SG, kèm dòng hàng", trên bảng order_items không có index order_id (như project thật — bài 2):

BEGIN;
BEGIN
DROP INDEX order_items_order_id_index;
DROP INDEX
EXPLAIN (ANALYZE)
SELECT o.order_number, i.product_id, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending' AND o.created_at >= '2026-09-29' AND o.ship_country = 'SG';

Hash Join  (cost=116.54..100007.08 rows=3 width=25) (actual time=905.349..906.129 rows=66.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=1.401..486.543 rows=5000000.00 loops=1)
  ->  Hash  (cost=116.53..116.53 rows=1 width=21) (actual time=0.635..0.637 rows=26.00 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 10kB
        ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..116.53 rows=1 width=21) (actual time=0.115..0.610 rows=26.00 loops=1)
              Index Cond: (created_at >= '2026-09-29 00:00:00+00'::timestamp with time zone)
              Filter: ((status = 'pending'::order_status) AND (ship_country = 'SG'::text))
              Rows Removed by Filter: 2257
              Index Searches: 1
Planning Time: 5.328 ms
Execution Time: 906.273 ms

Ước lượng outer chỉ 1 dòng (thật 26 — ba điều kiện có tương quan, bài 5) nên planner băm 1 dòng đó rồi quét cả 5 triệu dòng hàng để dò: 906 ms. Vì sao không Nested Loop? Cấm Hash/Merge để xem giá:

SET enable_hashjoin = off;
SET
SET enable_mergejoin = off;
SET
EXPLAIN
SELECT o.order_number, i.product_id, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending' AND o.created_at >= '2026-09-29' AND o.ship_country = 'SG';

Nested Loop  (cost=0.43..149382.43 rows=3 width=25)
  Join Filter: (o.id = i.order_id)
  ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..116.53 rows=1 width=21)
        Index Cond: (created_at >= '2026-09-29 00:00:00+00'::timestamp with time zone)
        Filter: ((status = 'pending'::order_status) AND (ship_country = 'SG'::text))
  ->  Seq Scan on order_items i  (cost=0.00..86765.40 rows=5000040 width=20)

Nested Loop + Seq Scan có giá 149 382 (đắt hơn Hash 100 007): không có index thì điều kiện ghép thành Join Filter phải thử trên 5 triệu dòng cho mỗi dòng outer. Planner tránh được thảm hoạ Nested Loop quét lại cả bảng — nhưng vẫn phải quét cả bảng một lần. Có index:

EXPLAIN (ANALYZE)
SELECT o.order_number, i.product_id, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending' AND o.created_at >= '2026-09-29' AND o.ship_country = 'SG';

Nested Loop  (cost=0.86..125.05 rows=3 width=25) (actual time=0.081..0.579 rows=66.00 loops=1)
  ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..116.53 rows=1 width=21) (actual time=0.055..0.449 rows=26.00 loops=1)
        Index Cond: (created_at >= '2026-09-29 00:00:00+00'::timestamp with time zone)
        Filter: ((status = 'pending'::order_status) AND (ship_country = 'SG'::text))
        Rows Removed by Filter: 2257
        Index Searches: 1
  ->  Index Scan using order_items_order_id_index on order_items i  (cost=0.43..8.48 rows=3 width=20) (actual time=0.004..0.004 rows=2.54 loops=26)
        Index Cond: (order_id = o.id)
        Index Searches: 26
Planning Time: 2.291 ms
Execution Time: 0.659 ms

0,66 ms — nhanh hơn 1 400 lần. Câu chuyện của cả series lặp lại: index trên cột khoá ngoại không phải tối ưu tuỳ chọn.

3.6. Ví dụ 6 — semi join, anti join và cái bẫy NOT IN

SQL Eloquent sinh ra (lab laravel/eloquent.php, join-sql.php):

-- 7. whereHas
select * from "orders" where exists (select * from "order_items" where "orders"."id" = "order_items"."order_id" and "product_id" = 7)

-- 8. whereHas OR orWhereHas
select * from "orders" where exists (select * from "order_items" where "orders"."id" = "order_items"."order_id" and "product_id" = 7) or exists (select * from "order_items" where "orders"."id" = "order_items"."order_id" and "quantity" > 4)

-- 9. whereNotIn subquery
select * from "orders" where "id" not in (select "order_id" from "order_items")
-- whereDoesntHave('items')
select * from "orders" where not exists (select * from "order_items" where "orders"."id" = "order_items"."order_id")

-- doesntHave('items')
select * from "orders" where not exists (select * from "order_items" where "orders"."id" = "order_items"."order_id")

-- whereIn('id', subquery)
select * from "orders" where "id" in (select "order_id" from "order_items" where "product_id" = 7)

whereHas → EXISTS → semi join. Planner không cần ghép từng cặp, chỉ cần biết "có ít nhất một":

EXPLAIN (ANALYZE)
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items i WHERE o.id = i.order_id AND i.product_id = 7);

Gather  (cost=25930.24..75432.11 rows=11833 width=116) (actual time=180.202..668.401 rows=12949.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Hash Semi Join  (cost=24930.24..73248.81 rows=4930 width=116) (actual time=163.070..634.506 rows=4316.33 loops=3)
        Hash Cond: (o.id = i.order_id)
        ->  Parallel Seq Scan on orders o  (cost=0.00..46076.25 rows=833325 width=116) (actual time=0.181..205.872 rows=666666.67 loops=3)
        ->  Parallel Hash  (cost=24868.61..24868.61 rows=4930 width=8) (actual time=160.923..160.925 rows=4328.67 loops=3)
              Buckets: 16384  Batches: 1  Memory Usage: 672kB
              ->  Parallel Bitmap Heap Scan on order_items i  (cost=136.14..24868.61 rows=4930 width=8) (actual time=7.412..151.924 rows=4328.67 loops=3)
                    Recheck Cond: (product_id = 7)
                    Heap Blocks: exact=5241
                    Worker 0:  Heap Blocks: exact=2963
                    Worker 1:  Heap Blocks: exact=2734
                    ->  Bitmap Index Scan on order_items_product_id_index  (cost=0.00..133.18 rows=11833 width=0) (actual time=14.734..14.734 rows=12986.00 loops=1)
                          Index Cond: (product_id = 7)
                          Index Searches: 1
Planning Time: 5.629 ms
Execution Time: 669.859 ms

whereHas(...)->orWhereHas(...) → EXISTS OR EXISTS: hai EXISTS nối bằng OR không gộp thành join được, nên thành SubPlan:

Seq Scan on orders o  (cost=0.00..34027403.10 rows=1499985 width=116) (actual time=301.613..1296.063 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=8.090..29.249 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.582..2.582 rows=12986.00 loops=1)
                Index Cond: (product_id = 7)
                Index Searches: 1
  SubPlan 4
    ->  Gather  (cost=1000.00..75123.68 rows=113168 width=8) (actual time=0.997..194.759 rows=107252.00 loops=1)
          Workers Planned: 2
          Workers Launched: 2
          ->  Parallel Seq Scan on order_items i_1  (cost=0.00..62806.88 rows=47153 width=8) (actual time=0.354..218.640 rows=35750.67 loops=3)
                Filter: (quantity > 4)
                Rows Removed by Filter: 1630916
Planning Time: 0.311 ms
Execution Time: 1306.087 ms

May là cả hai subquery đều nhỏ đủ để "hashed": chạy mỗi subquery một lần, đưa kết quả vào bảng băm, rồi mỗi đơn chỉ tra bảng băm — 1,3 giây. Để ý cost 34 triệu: planner tính giá cho trường hợp xấu (chạy subquery theo từng dòng) — cost lớn kiểu này còn kéo theo JIT (bài 5).

whereDoesntHave → NOT EXISTS → anti join:

Finalize Aggregate  (cost=153126.45..153126.46 rows=1 width=8) (actual time=1494.377..1612.220 rows=1.00 loops=1)
  ->  Gather  (cost=153126.23..153126.45 rows=2 width=8) (actual time=1492.832..1612.211 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=152126.23..152126.24 rows=1 width=8) (actual time=1481.194..1481.205 rows=1.00 loops=3)
              ->  Parallel Hash Anti Join  (cost=91779.80..151622.21 rows=201610 width=0) (actual time=1481.189..1481.200 rows=0.00 loops=3)
                    Hash Cond: (o.id = i.order_id)
                    ->  Parallel Index Only Scan using orders_pkey on orders o  (cost=0.43..40277.58 rows=833325 width=8) (actual time=0.162..146.389 rows=666666.67 loops=3)
                          Heap Fetches: 0
                          Index Searches: 1
                    ->  Parallel Hash  (cost=57598.50..57598.50 rows=2083350 width=8) (actual time=718.152..718.153 rows=1666666.67 loops=3)
                          Buckets: 262144  Batches: 64  Memory Usage: 5152kB
                          ->  Parallel Seq Scan on order_items i  (cost=0.00..57598.50 rows=2083350 width=8) (actual time=0.885..302.604 rows=1666666.67 loops=3)
Planning Time: 0.776 ms
Execution Time: 1612.339 ms

whereNotIn('id', subquery) → NOT IN. Nghe giống NOT EXISTS, nhưng planner không được đổi nó thành anti join: nếu subquery trả về dù chỉ một NULL thì x NOT IN (...) không bao giờ đúng (so sánh với NULL ra "không biết"). Danh sách 5 triệu order_id không vừa hash_mem → SubPlan thường:

EXPLAIN
SELECT count(*) FROM orders
WHERE id NOT IN (SELECT order_id FROM order_items);

Finalize Aggregate  (cost=59915153579.01..59915153579.02 rows=1 width=8)
  ->  Gather  (cost=59915153578.79..59915153579.00 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=59915152578.79..59915152578.80 rows=1 width=8)
              ->  Parallel Index Only Scan using orders_pkey on orders  (cost=0.43..59915151537.14 rows=416662 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)

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

Cost 59,9 tỷ. Chạy thật: lần đầu chạy lab, câu này chạy 10 phút chưa xong và phải huỷ tay; đặt timeout 60 giây thì bị huỷ. Để thấy vì sao, chỉ lấy 10 đơn:

EXPLAIN (ANALYZE)
SELECT count(*) FROM orders
WHERE id > 1999990 AND id NOT IN (SELECT order_id FROM order_items);

Aggregate  (cost=647094.27..647094.28 rows=1 width=8) (actual time=9642.723..9642.726 rows=1.00 loops=1)
  Buffers: shared hit=9924 read=26824, temp read=98622 written=10966
  I/O Timings: shared read=18.398, temp read=347.405 write=203.610
  ->  Index Only Scan using orders_pkey on orders  (cost=0.43..647094.26 rows=5 width=0) (actual time=9642.717..9642.719 rows=0.00 loops=1)
        Index Cond: (id > 1999990)
        Filter: (NOT (ANY (id = (SubPlan 1).col1)))
        Rows Removed by Filter: 10
        Heap Fetches: 0
        Index Searches: 1
        Buffers: shared hit=9924 read=26824, temp read=98622 written=10966
        I/O Timings: shared read=18.398, temp read=347.405 write=203.610
        SubPlan 1
          ->  Materialize  (cost=0.00..131297.60 rows=5000040 width=8) (actual time=0.038..609.238 rows=4986931.60 loops=10)
                Storage: Disk  Maximum Storage: 87662kB
                Buffers: shared hit=9920 read=26824, temp read=98622 written=10966
                I/O Timings: shared read=18.398, temp read=347.405 write=203.610
                ->  Seq Scan on order_items  (cost=0.00..86765.40 rows=5000040 width=8) (actual time=0.015..753.757 rows=4986944.00 loops=1)
                      Buffers: shared hit=9920 read=26824
                      I/O Timings: shared read=18.398
Planning:
  Buffers: shared hit=7
Planning Time: 0.432 ms
Execution Time: 9666.134 ms

10 đơn mất 9,7 giây: mỗi đơn (loops=10) quét lại trung bình 5 triệu dòng đã Materialize (lưu trên đĩa 87 MB) để tìm xem id có trong đó không. 2 triệu đơn thì phải hàng tuần. Cho đủ bộ nhớ thì thành hashed SubPlan:

SET work_mem = '256MB';
SET
Finalize Aggregate  (cost=143668.26..143668.27 rows=1 width=8) (actual time=3041.957..3050.470 rows=1.00 loops=1)
  ->  Gather  (cost=143668.05..143668.26 rows=2 width=8) (actual time=3012.381..3050.456 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=142668.05..142668.06 rows=1 width=8) (actual time=2992.501..2992.504 rows=1.00 loops=3)
              ->  Parallel Index Only Scan using orders_pkey on orders  (cost=99265.93..141626.39 rows=416662 width=0) (actual time=2992.495..2992.497 rows=0.00 loops=3)
                    Filter: (NOT (ANY (id = (hashed SubPlan 1).col1)))
                    Rows Removed by Filter: 666667
                    Heap Fetches: 0
                    Index Searches: 1
                    SubPlan 1
                      ->  Seq Scan on order_items  (cost=0.00..86765.40 rows=5000040 width=8) (actual time=0.332..751.644 rows=5000000.00 loops=3)
Planning Time: 0.124 ms
Execution Time: 3072.026 ms

3,1 giây — chạy được, nhưng vẫn chậm hơn anti join (1,6 s), và để ý loops=3: mỗi process song song tự dựng một bảng băm riêng, quét 5 triệu dòng 3 lần. Và cái bẫy ngữ nghĩa:

SELECT count(*) FROM orders WHERE id NOT IN (VALUES (1::bigint), (NULL));
 count
-------
     0
(1 row)

SELECT count(*) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM (VALUES (1::bigint), (NULL)) v(x) WHERE v.x = o.id);
  count
---------
 1999999
(1 row)

Chỉ một NULL trong danh sách là NOT IN trả về 0 dòng, không báo lỗi. Quy tắc: luôn dùng NOT EXISTS (whereDoesntHave), không dùng NOT IN (subquery). (PostgreSQL 17 có thêm khả năng đổi một số IN subquery tương quan thành join — theo release notes 17; NOT IN vẫn không đổi được vì lý do NULL.)

3.6b. Lầm tưởng phổ biến: LIMIT/DISTINCT có chặn flatten EXISTS không?

Nhiều tài liệu truyền miệng nói LIMIT, ORDER BY + LIMIT, hay DISTINCT đặt bên trong EXISTS sẽ chặn planner "làm phẳng" (flatten) thành semi join như ở ví dụ 6, buộc nó rơi về SubPlan chạy riêng cho từng dòng ngoài. Trên PostgreSQL 18.4 (lab tmp_qplan, cùng bảng orders/order_items và điều kiện product_id = 7 như ví dụ 6), test trực tiếp bằng EXPLAIN cho cả ba biến thể — EXISTS (...) LIMIT 1, EXISTS (...) ORDER BY i.id LIMIT 1, và EXISTS (SELECT DISTINCT i.product_id ...) — cho ra đúng cost=25930.24..75432.14 giống hệt EXISTS thuần, và plan đầy đủ xác nhận vẫn là Parallel Hash Semi Join:

EXPLAIN SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items i WHERE o.id = i.order_id AND i.product_id = 7 ORDER BY i.id LIMIT 1);

Gather  (cost=25930.24..75432.14 rows=11833 width=116)
  Workers Planned: 2
  ->  Parallel Hash Semi Join  (cost=24930.24..73248.84 rows=4930 width=116)
        Hash Cond: (o.id = i.order_id)
        ->  Parallel Seq Scan on orders o  (cost=0.00..46076.27 rows=833327 width=116)
        ->  Parallel Hash  (cost=24868.61..24868.61 rows=4930 width=8)
              ->  Parallel Bitmap Heap Scan on order_items i  (cost=136.14..24868.61 rows=4930 width=8)
                    Recheck Cond: (product_id = 7)
                    ->  Bitmap Index Scan on order_items_product_id_index  (cost=0.00..133.18 rows=11833 width=0)
                          Index Cond: (product_id = 7)

Lầm tưởng này sai trên PG 18: planner đủ thông minh để thấy LIMIT/ORDER BY/DISTINCT bên trong EXISTS không làm thay đổi giá trị true/false của "có tồn tại hay không" (chỉ cần biết có ít nhất 1 dòng, không quan tâm dòng nào hay thứ tự nào), nên vẫn flatten bình thường. Cái chặn thật sự là khi cấu trúc bên trong EXISTS không còn tương ứng 1-1 với phép kiểm tra tồn tại đơn giản nữa — một hàm aggregate không có GROUP BY (phải tính tại chỗ cho từng dòng ngoài, không gộp được), hoặc UNION (hai nhánh phải hợp nhất trước khi biết có tồn tại hay không):

-- aggregate không GROUP BY bên trong EXISTS -> không flatten
EXPLAIN SELECT * FROM orders o
WHERE EXISTS (SELECT max(i.id) FROM order_items i WHERE o.id = i.order_id AND i.product_id = 7);

Seq Scan on orders o  (cost=0.00..17067615.27 rows=999992 width=116)
  Filter: EXISTS(SubPlan 1)
  SubPlan 1
    ->  Aggregate  (cost=8.49..8.50 rows=1 width=8)
          ->  Index Scan using order_items_order_id_index on order_items i  (cost=0.43..8.49 rows=1 width=8)
                Index Cond: (order_id = o.id)
                Filter: (product_id = 7)

Cost nhảy từ ~75.432 (flatten) lên 17.067.615 — gấp khoảng 226 lần — chỉ vì thêm một hàm max() không GROUP BY vào trong EXISTS, dù kết quả true/false với dữ liệu lab này hoàn toàn giống EXISTS thuần. UNION bên trong EXISTS cũng vậy, cost nhảy lên 34.077.487 vì phải Sort + Unique để hợp nhất hai nhánh trước khi trả true/false. Cùng nhóm với trường hợp whereHas(...)->orWhereHas(...) ở ví dụ 6 (OR ở top-level của WHERE cũng không flatten được, rơi về SubPlan), bộ chặn thật sự đã xác nhận bằng lab là: aggregate không GROUP BY, UNION, hàm volatile (ví dụ random()), và OR ở top-level của WHERE — không phải LIMIT/DISTINCT như lầm tưởng phổ biến.

Cách nói khi phỏng vấn: "EXISTS nhanh không phải vì nó tự thân nhanh, mà vì Postgres thường đổi được nó thành một phép join bình thường (semi join) nếu thấy an toàn. LIMIT hay DISTINCT bên trong không phải dấu hiệu chặn việc đó — tôi test trực tiếp bằng EXPLAIN và cost ra giống y hệt EXISTS thuần. Cái thật sự chặn là khi bên trong EXISTS có aggregate không nhóm, UNION, hàm không xác định trước kết quả, hoặc một OR ở ngoài cùng — những thứ khiến Postgres không còn chắc việc gộp thành join vẫn cho đúng kết quả, nên phải chạy lại subquery riêng cho từng dòng ngoài."

3.7. Ví dụ 7 — LEFT JOIN bị biến thành INNER JOIN

-- leftJoin + where trên bảng phải
select "orders"."id", "order_items"."product_id" from "orders" left join "order_items" on "order_items"."order_id" = "orders"."id" where "orders"."user_id" = 4242 and "order_items"."product_id" = 7

EXPLAIN
SELECT o.id, i.product_id
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.user_id = 4242 AND i.product_id = 7;

Nested Loop  (cost=5.00..228.66 rows=1 width=16)
  ->  Bitmap Heap Scan on orders o  (cost=4.57..75.61 rows=18 width=8)
        Recheck Cond: (user_id = 4242)
        ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0)
              Index Cond: (user_id = 4242)
  ->  Index Scan using order_items_order_id_index on order_items i  (cost=0.43..8.49 rows=1 width=16)
        Index Cond: (order_id = o.id)
        Filter: (product_id = 7)

EXPLAIN
SELECT o.id, i.product_id
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id AND i.product_id = 7
WHERE o.user_id = 4242;

Nested Loop Left Join  (cost=5.00..228.66 rows=18 width=16)
  ->  Bitmap Heap Scan on orders o  (cost=4.57..75.61 rows=18 width=8)
        Recheck Cond: (user_id = 4242)
        ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0)
              Index Cond: (user_id = 4242)
  ->  Index Scan using order_items_order_id_index on order_items i  (cost=0.43..8.49 rows=1 width=16)
        Index Cond: (order_id = o.id)
        Filter: (product_id = 7)

Plan để lộ một lỗi logic: câu viết LEFT JOIN nhưng node là Nested Loop thường. Điều kiện order_items.product_id = 7 ở WHERE loại mọi dòng mà phía phải là NULL — tức là loại đúng những đơn LEFT JOIN muốn giữ — nên planner chuyển thành INNER JOIN cho rẻ. Kết quả: đơn không có sản phẩm 7 biến mất khỏi danh sách. Đặt điều kiện vào ON thì mới là Nested Loop Left Join. Và một tối ưu ngược lại:

EXPLAIN
SELECT o.id, o.total_cents
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE o.user_id = 4242;

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

LEFT JOIN users mà không dùng cột nào của users, khoá join là khoá chính → planner bỏ hẳn join (không có node nào về users). Gặp câu ORM "join cho chắc" thì plan cho thấy join đó có bị bỏ hay không.

3.8. Ví dụ 8 — điều kiện không phải phép bằng: Join Filter

BEGIN;
BEGIN
CREATE TEMP TABLE campaigns (id int, name text, starts_at timestamptz, ends_at timestamptz);
CREATE TABLE
INSERT INTO campaigns
SELECT g, 'Flash sale ' || g,
       timestamptz '2026-09-01' + (g - 1) * interval '1 day' + interval '12 hours',
       timestamptz '2026-09-01' + (g - 1) * interval '1 day' + interval '14 hours'
FROM generate_series(1, 20) g;
INSERT 0 20
ANALYZE campaigns;
ANALYZE
EXPLAIN (ANALYZE)
SELECT c.name, count(*), sum(o.total_cents)
FROM orders o
JOIN campaigns c ON o.created_at >= c.starts_at AND o.created_at < c.ends_at
WHERE o.created_at >= '2026-09-01' AND o.created_at < '2026-10-01'
GROUP BY c.name;

HashAggregate  (cost=17266.33..17266.53 rows=20 width=29) (actual time=2.755..2.759 rows=20.00 loops=1)
  Group Key: c.name
  Batches: 1  Memory Usage: 32kB
  ->  Nested Loop  (cost=0.43..16242.05 rows=136571 width=17) (actual time=0.243..2.061 rows=3789.00 loops=1)
        ->  Seq Scan on campaigns c  (cost=0.00..1.20 rows=20 width=29) (actual time=0.014..0.018 rows=20.00 loops=1)
        ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..743.75 rows=6829 width=12) (actual time=0.032..0.069 rows=189.45 loops=20)
              Index Cond: ((created_at >= c.starts_at) AND (created_at < c.ends_at) AND (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))
              Index Searches: 20
Planning Time: 2.037 ms
Execution Time: 3.423 ms

Join "đơn rơi vào khung flash sale nào" (so sánh khoảng). Hash và Merge không dùng được, nhưng planner vẫn khéo: Nested Loop với vòng trong là Index Scan nhận khoảng từ dòng ngoài (Index Cond: ((created_at >= c.starts_at) AND (created_at < c.ends_at) ...), loops=20, 3,4 ms. Khi không có index để tra:

CREATE TEMP TABLE order_tiers (name text, min_cents int, max_cents int);
CREATE TABLE
INSERT INTO order_tiers VALUES ('nhỏ', 0, 500000), ('vừa', 500000, 2000000),
                               ('lớn', 2000000, 5000000), ('rất lớn', 5000000, 100000000);
INSERT 0 4
ANALYZE order_tiers;
ANALYZE
SET max_parallel_workers_per_gather = 0;
SET
HashAggregate  (cost=7508.18..7508.22 rows=4 width=15) (actual time=83.762..83.765 rows=4.00 loops=1)
  Group Key: t.name
  Batches: 1  Memory Usage: 32kB
  ->  Nested Loop  (cost=0.43..7371.61 rows=27314 width=7) (actual time=0.091..71.773 rows=65990.00 loops=1)
        Join Filter: ((o.total_cents >= t.min_cents) AND (o.total_cents < t.max_cents))
        Rows Removed by Join Filter: 197970
        ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..3068.57 rows=61457 width=4) (actual time=0.057..15.008 rows=65990.00 loops=1)
              Index Cond: ((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))
              Index Searches: 1
        ->  Materialize  (cost=0.00..1.06 rows=4 width=15) (actual time=0.000..0.000 rows=4.00 loops=65990)
              Storage: Memory  Maximum Storage: 17kB
              ->  Seq Scan on order_tiers t  (cost=0.00..1.04 rows=4 width=15) (actual time=0.015..0.015 rows=4.00 loops=1)
Planning Time: 0.672 ms
Execution Time: 83.893 ms

Mỗi đơn thử với cả 4 hạng (Materialize giữ 4 dòng trong 17 kB, loops=65990): 263 960 cặp, loại 197 970. Với 4 hạng thì không sao; với bảng phải hàng nghìn dòng, Rows Removed by Join Filter sẽ là con số đầu tiên cần nhìn.

3.9. Ví dụ 9 — thứ tự join và thời gian lập plan

EXPLAIN (SUMMARY)
SELECT count(*)
FROM orders o
JOIN users u        ON u.id = o.user_id
JOIN order_items i1 ON i1.order_id = o.id
JOIN products p1    ON p1.id = i1.product_id
JOIN categories c1  ON c1.id = p1.category_id
JOIN order_items i2 ON i2.order_id = o.id
JOIN products p2    ON p2.id = i2.product_id
JOIN categories c2  ON c2.id = p2.category_id
WHERE o.user_id = 4242;

Aggregate  (cost=321.42..321.43 rows=1 width=8)
  ->  Hash Join  (cost=13.08..321.14 rows=113 width=0)
        Hash Cond: (p2.category_id = c2.id)
        ->  Nested Loop  (cost=6.58..314.33 rows=113 width=4)
              ->  Nested Loop  (cost=6.29..279.53 rows=113 width=8)
                    ->  Nested Loop  (cost=5.85..254.68 rows=45 width=16)
                          ->  Nested Loop  (cost=5.71..247.36 rows=45 width=20)
                                ->  Nested Loop  (cost=5.42..233.50 rows=45 width=24)
                                      ->  Nested Loop  (cost=4.99..80.23 rows=18 width=8)
                                            ->  Index Only Scan using users_pkey on users u  (cost=0.42..4.44 rows=1 width=8)
                                                  Index Cond: (id = 4242)
                                            ->  Bitmap Heap Scan on orders o  (cost=4.57..75.61 rows=18 width=16)
                                                  Recheck Cond: (user_id = 4242)
                                                  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0)
                                                        Index Cond: (user_id = 4242)
                                      ->  Index Scan using order_items_order_id_index on order_items i1  (cost=0.43..8.48 rows=3 width=16)
                                            Index Cond: (order_id = o.id)
                                ->  Index Scan using products_pkey on products p1  (cost=0.29..0.31 rows=1 width=12)
                                      Index Cond: (id = i1.product_id)
                          ->  Index Only Scan using categories_pkey on categories c1  (cost=0.14..0.16 rows=1 width=4)
                                Index Cond: (id = p1.category_id)
                    ->  Index Scan using order_items_order_id_index on order_items i2  (cost=0.43..0.52 rows=3 width=16)
                          Index Cond: (order_id = i1.order_id)
              ->  Index Scan using products_pkey on products p2  (cost=0.29..0.31 rows=1 width=12)
                    Index Cond: (id = i2.product_id)
        ->  Hash  (cost=4.00..4.00 rows=200 width=4)
              ->  Seq Scan on categories c2  (cost=0.00..4.00 rows=200 width=4)
Planning Time: 4.785 ms

SET join_collapse_limit = 1;
SET
Aggregate  (cost=321.42..321.43 rows=1 width=8)
  ->  Hash Join  (cost=13.08..321.14 rows=113 width=0)
        Hash Cond: (p2.category_id = c2.id)
        ->  Nested Loop  (cost=6.58..314.33 rows=113 width=4)
              ->  Nested Loop  (cost=6.29..279.53 rows=113 width=8)
                    ->  Nested Loop  (cost=5.85..254.68 rows=45 width=16)
                          ->  Nested Loop  (cost=5.71..247.36 rows=45 width=20)
                                ->  Nested Loop  (cost=5.42..233.50 rows=45 width=24)
                                      ->  Nested Loop  (cost=4.99..80.23 rows=18 width=8)
                                            ->  Index Only Scan using users_pkey on users u  (cost=0.42..4.44 rows=1 width=8)
                                                  Index Cond: (id = 4242)
                                            ->  Bitmap Heap Scan on orders o  (cost=4.57..75.61 rows=18 width=16)
                                                  Recheck Cond: (user_id = 4242)
                                                  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0)
                                                        Index Cond: (user_id = 4242)
                                      ->  Index Scan using order_items_order_id_index on order_items i1  (cost=0.43..8.48 rows=3 width=16)
                                            Index Cond: (order_id = o.id)
                                ->  Index Scan using products_pkey on products p1  (cost=0.29..0.31 rows=1 width=12)
                                      Index Cond: (id = i1.product_id)
                          ->  Index Only Scan using categories_pkey on categories c1  (cost=0.14..0.16 rows=1 width=4)
                                Index Cond: (id = p1.category_id)
                    ->  Index Scan using order_items_order_id_index on order_items i2  (cost=0.43..0.52 rows=3 width=16)
                          Index Cond: (order_id = i1.order_id)
              ->  Index Scan using products_pkey on products p2  (cost=0.29..0.31 rows=1 width=12)
                    Index Cond: (id = i2.product_id)
        ->  Hash  (cost=4.00..4.00 rows=200 width=4)
              ->  Seq Scan on categories c2  (cost=0.00..4.00 rows=200 width=4)
Planning Time: 1.442 ms

Join 8 bảng: planner thử các thứ tự (quy hoạch động, tối đa join_collapse_limit = 8 bảng mỗi nhóm; từ geqo_threshold = 12 bảng chuyển sang GEQO). Lần lập plan đầu trong phiên 15,2 ms (gồm nạp catalog), lần hai 4,8 ms. Với join_collapse_limit = 1 — "giữ đúng thứ tự viết" — còn 1,4 ms, ra cùng plan vì thứ tự viết tình cờ đã tốt. Đọc cây lớn: đếm số join node bằng số bảng trừ 1, đi từ lá sâu nhất lên, và để ý Seq Scan on categories c2 (200 dòng — Hash Join với bảng nhỏ là đúng). Câu ORM join 15–20 bảng thì Planning Time có thể lớn hơn Execution Time; đó là lúc nhìn join_collapse_limit.

3.10. Ví dụ 10 — ước lượng lệch 120 lần: Nested Loop có thảm hoạ không?

Laravel where('meta->source', 'app') trên đơn năm 2026, join dòng hàng. Biểu thức JSON không có thống kê, planner đoán 0,5%:

EXPLAIN (ANALYZE)
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=54750.35..54750.36 rows=1 width=16) (actual time=1474.991..1474.993 rows=1.00 loops=1)
  Buffers: shared hit=1432829 read=26459 written=21
  I/O Timings: shared read=301.673 write=1.317
  ->  Nested Loop  (cost=0.86..54712.98 rows=7473 width=4) (actual time=1.649..1406.909 rows=900761.00 loops=1)
        Buffers: shared hit=1432829 read=26459 written=21
        I/O Timings: shared read=301.673 write=1.317
        ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..31301.65 rows=2989 width=8) (actual time=0.077..385.820 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
              Buffers: shared hit=40 read=12974 written=8
              I/O Timings: shared read=136.638 write=0.407
        ->  Index Scan using order_items_order_id_index on order_items i  (cost=0.43..7.80 rows=3 width=12) (actual time=0.002..0.002 rows=2.50 loops=360583)
              Index Cond: (order_id = o.id)
              Index Searches: 360583
              Buffers: shared hit=1432789 read=13485 written=13
              I/O Timings: shared read=165.036 write=0.910
Planning:
  Buffers: shared hit=317
Planning Time: 3.290 ms
Execution Time: 1475.178 ms

Ước lượng 2 989 đơn, thật 360 583 — lệch 120 lần. Vòng trong chạy 360 583 lần. Thảm hoạ? Không hẳn: mỗi lần chỉ 0,002 ms vì trang đã có trong RAM, và các đơn liền nhau có dòng hàng nằm liền nhau (correlation tốt). Ép Hash Join để so:

SET enable_nestloop = off;
SET
Aggregate  (cost=131266.91..131266.92 rows=1 width=16) (actual time=3336.207..3336.213 rows=1.00 loops=1)
  Buffers: shared hit=5351 read=44428, temp read=25003 written=25003
  I/O Timings: shared read=108.534, temp read=78.509 write=358.010
  ->  Hash Join  (cost=31339.01..131229.55 rows=7473 width=4) (actual time=1619.605..3273.955 rows=900761.00 loops=1)
        Hash Cond: (i.order_id = o.id)
        Buffers: shared hit=5351 read=44428, temp read=25003 written=25003
        I/O Timings: shared read=108.534, temp read=78.509 write=358.010
        ->  Seq Scan on order_items i  (cost=0.00..86765.40 rows=5000040 width=12) (actual time=0.186..444.316 rows=5000000.00 loops=1)
              Buffers: shared hit=5311 read=31454
              I/O Timings: shared read=7.253
        ->  Hash  (cost=31301.65..31301.65 rows=2989 width=8) (actual time=571.227..571.229 rows=360583.00 loops=1)
              Buckets: 262144 (originally 4096)  Batches: 4 (originally 1)  Memory Usage: 6145kB
              Buffers: shared hit=40 read=12974, temp written=924
              I/O Timings: shared read=101.281, temp write=27.135
              ->  Index Scan using orders_created_at_index on orders o  (cost=0.43..31301.65 rows=2989 width=8) (actual time=0.106..382.886 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
                    Buffers: shared hit=40 read=12974
                    I/O Timings: shared read=101.281
Planning:
  Buffers: shared hit=5 read=11
  I/O Timings: shared read=0.906
Planning Time: 1.486 ms
Execution Time: 3337.498 ms

Hash Join chậm hơn (3,3 s so với 1,5 s). Và dòng Buckets: 262144 (originally 4096) Batches: 4 (originally 1) là dấu vết kinh điển của ước lượng sai: bảng băm được chuẩn bị cho 2 989 dòng, nhận 360 583 dòng, phải nới bucket và chia batch giữa chừng.

Bài học trung thực: lệch ước lượng không tự động ra plan dở. Nhưng nhìn Buffers của Nested Loop: shared hit=1432789 — 1,4 triệu lần chạm buffer cho 900 nghìn dòng. Ở đây chúng nằm trong RAM; trên production với dữ liệu lớn hơn RAM, đó là tới 1,4 triệu lần đọc ngẫu nhiên — và cùng plan này sẽ chậm hàng chục lần. Plan "đang nhanh" nhờ cache là plan mong manh. Bài 5 sửa ước lượng của ví dụ này và kết quả còn bất ngờ hơn.

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

	Nested Loop	Hash Join	Merge Join	+ Memoize
Điều kiện ghép	mọi điều kiện	chỉ phép bằng (toán tử băm được)	phép bằng trên kiểu sắp được	như Nested Loop
Bộ nhớ	gần 0	`work_mem × hash_mem_multiplier`, tràn → batch	ít (trừ khi phải Sort)	`work_mem × hash_mem_multiplier`
Startup	thấp — trả dòng đầu ngay	cao — dựng xong bảng băm	thấp nếu không Sort	thấp
Thứ tự kết quả	theo outer	không giữ	theo khoá	theo outer
Hỏng khi	outer bị đánh giá thấp; inner không có index; I/O ngẫu nhiên	bên băm bị đánh giá thấp (nới batch); thiếu RAM	phải sắp hai bên lớn	khoá ít lặp (miss nhiều)

Mọi join là hai đầu vào; planner tìm thứ tự tốt nhất bằng quy hoạch động trong giới hạn join_collapse_limit/from_collapse_limit (8), chuyển sang GEQO từ 12 bảng — không còn đảm bảo tối ưu.
OUTER JOIN hạn chế việc đổi thứ tự; planner chỉ đổi khi chứng minh được tương đương (ví dụ 7: đổi LEFT thành INNER khi WHERE loại NULL).
Semi/anti join: EXISTS, IN (subquery), NOT EXISTS được đổi thành join. NOT IN (subquery) và EXISTS nối bằng OR thì không.
Hashed SubPlan chỉ được chọn khi kết quả subquery ước lượng vừa hash_mem; không vừa thì quét lại theo từng dòng.
Parallel Hash dùng shared memory; ngân sách bộ nhớ cộng theo số process — và bị giới hạn bởi /dev/shm trong container.

5. Failure modes

Triệu chứng trong plan	Chuyện gì xảy ra	Ví dụ lab	Sửa
`loops` rất lớn ở vòng trong Nested Loop, rows outer ước lượng nhỏ	Ước lượng outer quá thấp	10: 2 989 vs 360 583	Sửa thống kê (bài 5); xem Buffers để biết plan có mong manh không
`Batches: N (originally 1)`	Bên băm bị đánh giá thấp, nới lúc chạy	10	Sửa ước lượng
`Batches` > 1, `temp read/written`	Bảng băm không vừa bộ nhớ	2, 3, 6	Giảm cột/dòng; `work_mem` riêng cho báo cáo
`Seq Scan` bảng lớn làm inner/outer của join chỉ để lấy vài dòng	Thiếu index cột FK	5: 906 ms vs 0,66 ms	Index cột FK
`SubPlan` (không có chữ hashed) dưới Filter, cost cực lớn	NOT IN / OR EXISTS / subquery tương quan chạy theo từng dòng	6: 10 phút chưa xong	NOT EXISTS; tách OR thành UNION; viết lại thành join
`Nested Loop` trong câu viết `LEFT JOIN`	WHERE trên bảng phải biến LEFT thành INNER — sai kết quả	7	Chuyển điều kiện vào `ON`
`Rows Removed by Join Filter` rất lớn	Join theo điều kiện không tra được	8	Index phục vụ điều kiện; tiền xử lý dữ liệu
`Planning Time` > `Execution Time`	Join quá nhiều bảng	9	`join_collapse_limit`; tách query

6. Vận hành trong production

Hash tràn đĩa không cần EXPLAIN từng câu: log_temp_files + pg_stat_database.temp_bytes (lab ở bài 5).
work_mem là cho mỗi node của mỗi phiên (một câu có 3 Hash Join dùng tới 3 × 8 MB, nhân số phiên). Tăng cho role báo cáo (ALTER ROLE report SET work_mem = '64MB'), đừng tăng toàn cục.
enable_nestloop, enable_hashjoin… chỉ để chẩn đoán trong một phiên (như các ví dụ trên). Đặt trong postgresql.conf là tắt một công cụ cho mọi câu để chữa một câu.
statement_timeout theo role cho app web (vd 5 s): câu NOT IN của ví dụ 6 bị huỷ thay vì giữ một worker và một kết nối hàng giờ.
Câu báo cáo nặng chạy trên replica — nhưng nhớ replica cũng có giới hạn (bài 1: replica cấu hình khác, cache khác).

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

Bạn không chọn thuật toán join — planner chọn. Việc của bạn là cho planner đủ điều kiện để chọn đúng, và viết SQL không chặn đường nó:

Viết thế này (Eloquent / SQL)	Plan nhận được	Đánh giá
`whereHas('items', ...)` → EXISTS	Semi Join	Tốt
`whereIn('id', subquery)` → IN (subquery)	Semi Join	Tốt
`whereDoesntHave('items')` → NOT EXISTS	Anti Join	Tốt
`whereNotIn('id', subquery)` → NOT IN	SubPlan (hashed nếu vừa RAM, không thì theo từng dòng)	Tránh — chậm và sai khi có NULL
`whereHas(...)->orWhereHas(...)`	2 SubPlan	Chấp nhận khi subquery nhỏ; lớn thì tách UNION
`leftJoin(...)->where('bang_phai.cot', ...)`	Inner join	Sai logic — đưa điều kiện vào ON
`with('items')` (eager load)	2 câu riêng, câu sau `order_id IN (...)` → Index Scan với nhiều Index Searches	Tốt cho danh sách có phân trang
`withCount('items')`	SubPlan theo từng dòng	Tốt cho trang nhỏ; bài 4 đo trên 66 nghìn đơn

Dùng EXISTS/NOT EXISTS cho câu hỏi "có/không có"; join thật khi cần cột của bảng kia. Tránh NOT IN với subquery, LEFT JOIN rồi lọc bảng phải ở WHERE, và tắt một thuật toán join toàn cục để "sửa" một câu.

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

Luật review: cấm whereNotIn(..., subquery) (dùng whereDoesntHave); leftJoin kèm where trên bảng phải phải giải thích. Hai lỗi này đều "chạy được" khi test với dữ liệu nhỏ.
Giới hạn tài nguyên theo role: app web có statement_timeout ngắn, role báo cáo có work_mem lớn hơn và timeout dài hơn, chạy trên replica.
Câu nặng nhất thường đến từ màn hình admin/báo cáo — ít người dùng, ít ai review, nhưng một lần bấm có thể chiếm hết worker.
Rủi ro dài hạn: plan "đang nhanh nhờ cache" (ví dụ 10). Khi dữ liệu vượt RAM, nhiều câu cùng chậm một lúc. Theo dõi tỉ lệ cache hit và shared read theo thời gian, không chỉ p95 latency.

Mẫu ADR ngắn: "Không dùng NOT IN (subquery) trong code và migration dữ liệu; dùng NOT EXISTS (whereDoesntHave). Lý do: ngữ nghĩa NULL làm planner không đổi được thành anti join; lab: NOT IN trên 2 triệu × 5 triệu dòng không xong sau 10 phút, NOT EXISTS 1,6 giây; một NULL trong subquery làm kết quả rỗng mà không báo lỗi. Role web đặt statement_timeout = '5s'."

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

NOT IN (subquery) — chậm theo cấp số nhân khi không vừa RAM, sai khi có NULL.
LEFT JOIN + lọc bảng phải ở WHERE — mất dòng âm thầm.
Tắt enable_nestloop toàn cục vì một sự cố — ví dụ 10 cho thấy có lúc Nested Loop lại nhanh hơn.
Tăng work_mem toàn cục để hết batch — nhân với số node và số phiên; trong Docker còn làm Parallel Hash chết (bài 5).
Viết subquery "lấy ít cột" để bảng băm nhỏ lại — planner đã làm rồi (ví dụ 2, width không đổi).
Bỏ qua chữ originally trong dòng Hash — nó là bằng chứng ước lượng sai ngay trong plan.
Kết luận "plan tốt" khi cache nóng mà không nhìn Buffers (ví dụ 10: 1,4 triệu buffer).

Postmortem công khai — GitLab.com, 09/03/2021 (production#3875): theo issue, optimizer của PostgreSQL 11 ước lượng sai cardinality của vòng ngoài Nested Loop khi IN (...) trả hơn 200 dòng; hệ quả là statement timeout hàng loạt và site gần như sập 2,5 giờ; chạy ANALYZE trên bảng liên quan để planner có thống kê mới là cách cứu tức thời. Đúng cơ chế của bài này: join method và thứ tự join chọn theo ước lượng của outer; ước lượng outer sai là sai cả cây.

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

Khi nào planner chọn Nested Loop, Hash Join, Merge Join?: NL khi outer ít dòng và inner tra được bằng index (hoặc điều kiện không phải phép bằng). Hash khi hai bên lớn, điều kiện bằng, không có thứ tự sẵn. Merge khi hai bên đã có thứ tự theo khoá (index) hoặc kết quả cần thứ tự đó.
Trong plan, bên nào được băm?: Node con thứ hai, nằm dưới node Hash — thường là bên planner nghĩ nhỏ hơn. Nếu ước lượng sai, bên "nhỏ" có thể là bên lớn (thấy originally).
Batches: 4 nghĩa là gì, có sao không?: Bảng băm không vừa work_mem × hash_mem_multiplier, bị chia 4 phần, 3 phần ghi file tạm. Chậm bao nhiêu tuỳ dung lượng file tạm và đĩa; lab nhỏ gần như không đáng kể.
NOT IN khác NOT EXISTS thế nào?: Ngữ nghĩa NULL: NOT IN gặp NULL thì không trả dòng nào. Vì vậy NOT IN không đổi được thành anti join; không vừa RAM thì thành SubPlan theo từng dòng (lab: không xong sau 10 phút). NOT EXISTS → Hash Anti Join, 1,6 s.
Memoize là gì, khi nào vô ích?: Bộ nhớ đệm kết quả vòng trong theo khoá tham số (PG 14+). Vô ích khi khoá ít lặp — thấy Misses ≈ loops, hoặc Evictions lớn.
Vì sao LEFT JOIN trong plan lại thành Nested Loop thường?: Điều kiện WHERE trên bảng phải loại các dòng NULL, nên planner đổi thành inner join — thường là lỗi logic của câu SQL.
Join 20 bảng thì sao?: Planner không thử hết được: quy hoạch động theo nhóm 8 bảng (join_collapse_limit), GEQO từ 12 bảng. Planning Time tăng mạnh, plan không còn đảm bảo tối ưu; cân nhắc tách câu hoặc cố định thứ tự.

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

Môi trường: như bài 1 (PostgreSQL 18.4, database tmp_qplan, JIT tắt). Hầu hết ví dụ tắt parallel trong phiên; ví dụ 5 bỏ index FK trong BEGIN … ROLLBACK; ví dụ 8 dùng bảng TEMP trong transaction.

cd labs/query-plan
./run.sh 03-*.sql                 # → out/03-*.txt  (03-06 chạy ~2 phút vì có câu NOT IN chờ timeout 60s)
docker run --rm --network container:my-test-pgdb -v /var/www/chiennn/laravel-livewire:/var/www/html:ro \
  -v "$PWD/laravel":/lab:ro --entrypoint php laravel-app /lab/join-sql.php    # SQL của whereDoesntHave, leftJoin…

Chưa verify: adaptive join của hệ khác; ý nghĩa chính xác của Cache Mode: binary/logical (giải thích theo comment trong source); hành vi đổi IN subquery tương quan thành join của PG 17 (theo release notes); hành vi trên PG 16/17 nói chung.