Scan node — Seq, Index, Index Only, Bitmap: đọc từng loại và vì sao planner chọn

Scan node là lá của cây plan: cách lấy dòng ra khỏi một bảng. Mọi plan, dù dài trăm dòng, cuối cùng đều đứng trên vài scan node; chọn sai ở đây thì phía trên không cứu được. Bài này đi qua 4 kiểu scan và các bản song song của chúng, mỗi kiểu kèm plan thật, rồi tới các tình huống index có mà không dùng được — phần lớn bắt nguồn từ cách Laravel sinh SQL (foreignId()->constrained(), whereDate, whereYear, cột JSON, LIKE). Cách đọc một dòng plan nói chung ở bài 1.

Thuật ngữ dùng trong bài
heap (phần lưu dữ liệu chính của bảng, gồm các trang 8 KB, dòng nằm theo thứ tự chèn) · TID / ctid (địa chỉ một dòng: số trang + vị trí trong trang) · B-tree (cây tìm kiếm có thứ tự — loại index mặc định) · visibility map (bản đồ 1 bit mỗi trang: trang này mọi dòng đều "ai cũng thấy" chưa) · Heap Fetches (số lần Index Only Scan vẫn phải đọc heap) · bitmap (bản đồ đánh dấu trang/dòng cần đọc) · exact / lossy (bitmap nhớ đúng từng dòng / chỉ nhớ "trang này có dòng cần") · recheck (kiểm lại điều kiện trên dòng thật) · correlation (thứ tự giá trị trong cột khớp với thứ tự vật lý trong heap tới mức nào, từ −1 tới 1) · partial index (index chỉ chứa các dòng thoả một điều kiện) · expression index (index trên kết quả một biểu thức, không phải cột) · operator class (bộ phép so sánh index dùng, vd text_pattern_ops) · trigram (cụm 3 ký tự liên tiếp — cách pg_trgm tìm chuỗi con) · skip scan (PG 18: dùng index nhiều cột dù không lọc cột đầu) · Gather (node gom dòng từ các worker song song).

Kiểm chứng: tài liệu PostgreSQL 18 qua Context7 (using-explain, indexes-index-only-scans, release notes 18 — skip scan, Index Searches). Mọi plan là output thật trên PostgreSQL 18.4, database tmp_qplan (xem bài 1), file SQL labs/query-plan/02-*.sql, output ở labs/query-plan/out/02-*.txt. Các ví dụ cần index mới đều tạo index trong BEGIN … ROLLBACK (DDL của PostgreSQL có transaction) để dữ liệu dùng chung không đổi. Phần Laravel chạy trên Laravel 12.64 (labs/query-plan/laravel/).

Trả lời 30 giây

Seq Scan đọc mọi trang theo thứ tự — rẻ cho từng trang, chọn khi cần phần lớn bảng hoặc không có index dùng được. Index Scan đi theo B-tree rồi nhảy tới từng dòng — tốt khi ít dòng, hoặc khi cần sẵn thứ tự cho ORDER BY … LIMIT. Bitmap Scan gom vị trí từ một hay nhiều index thành bitmap rồi đọc heap theo thứ tự vật lý, mỗi trang một lần — cho vùng giữa và để kết hợp OR/AND nhiều index. Index Only Scan bỏ qua heap khi trang đã all-visible — phụ thuộc VACUUM. Trong plan, Index Cond là phần index lo được; Filter là lọc sau khi đã đọc, và Rows Removed by Filter là công sức bỏ đi. Planner chọn theo rows ước lượng — lab: 0,5% bảng → Index Scan, 4% → Bitmap, 85% → Seq Scan. Với Laravel trên PostgreSQL, bẫy số một là foreignId()->constrained() không tạo index: SELECT theo khoá ngoại thành quét cả bảng, và DELETE 20 đơn mất 10 giây.

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

Dữ liệu một bảng nằm trong heap: các trang 8 KB, dòng xếp theo thứ tự được chèn (bảng orders của lab: 37 742 trang, ~53 dòng mỗi trang). Index là một cấu trúc riêng, đã sắp theo giá trị, mỗi entry trỏ về địa chỉ dòng (TID) trong heap. Đọc qua index nghĩa là: tìm trong cây (vài trang), rồi nhảy tới trang heap chứa dòng — mỗi dòng có thể ở một trang khác nhau. Đọc tuần tự thì ngược lại: không nhảy, hệ điều hành đọc trước (read-ahead) được, nhưng phải đọc mọi trang.

Vì vậy không có kiểu scan "tốt nhất" — chỉ có kiểu hợp với tỉ lệ dòng cần lấy và cách các dòng đó nằm trong heap. PostgreSQL có 4 kiểu, mỗi kiểu sinh ra để vá một điểm yếu của kiểu trước:

• Seq Scan — đọc hết. Không cần cấu trúc phụ. Tệ khi chỉ cần vài dòng.
• Index Scan — đọc ít trang khi cần ít dòng, và trả dòng theo thứ tự của index. Tệ khi cần nhiều dòng nằm rải rác: mỗi dòng một lần nhảy ngẫu nhiên, có trang bị đọc nhiều lần.
• Bitmap Scan — vá điểm yếu đó: gom hết vị trí trước, sắp theo thứ tự trang, đọc mỗi trang một lần; và cho phép kết hợp nhiều index bằng AND/OR. Đổi lại mất thứ tự của index và phải dựng xong bitmap mới trả dòng đầu.
• Index Only Scan — khi mọi cột cần đều nằm trong index, khỏi đọc heap… nếu biết chắc dòng đó "ai cũng thấy" (MVCC — mỗi dòng có phiên bản, index không biết phiên bản nào còn hiệu lực). Visibility map trả lời câu đó theo từng trang.

Bản song song (Parallel Seq/Index/Bitmap Scan) chia trang cho nhiều process. PostgreSQL 18 thêm skip scan cho B-tree nhiều cột và dòng Index Searches trong EXPLAIN.

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

Giá của một scan ≈ số trang phải đọc × giá mỗi trang (tuần tự hay nhảy) + số dòng phải xử lý. Mỗi kiểu scan là một cách khác nhau để chọn trang nào và đọc theo thứ tự nào:

Hệ quả: planner cần biết hai thứ — sẽ lấy bao nhiêu dòng (selectivity, từ thống kê — bài 1 mục 3.4) và các dòng đó nằm gần nhau hay rải rác (correlation trong pg_stats). Cột created_at của lab tăng theo thứ tự chèn nên correlation ≈ 1: lấy 1 ngày đơn chỉ đọc ~50 trang liền nhau. Cột user_id thì rải khắp bảng: 4 385 đơn của khách 1 nằm trên 4 138 trang khác nhau.

3. Internals và quyết định thiết kế — từng kiểu scan

3.1. Ví dụ 1 — Seq Scan và bản song song

Không có index nào cho total_cents. Bản tắt parallel (đã chạy 1 lần trước cho page cache của OS có sẵn bảng):

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE total_cents > 9500000;

Seq Scan on orders  (cost=0.00..62742.34 rows=55046 width=116) (actual time=0.039..282.159 rows=55501.00 loops=1)
  Filter: (total_cents > 9500000)
  Rows Removed by Filter: 1944499
  Buffers: shared hit=2372 read=35370
  I/O Timings: shared read=32.480
Planning:
  Buffers: shared hit=33
Planning Time: 0.939 ms
Execution Time: 284.917 ms

Đọc: Filter là điều kiện áp lên từng dòng đã đọc; Rows Removed by Filter: 1944499 — đọc 2 triệu dòng để giữ 55 nghìn. Đây là con số đầu tiên cần nhìn ở mọi scan: nó đo công sức bỏ đi. Để ý read=35370 dù vừa chạy xong một lần: Seq Scan trên bảng lớn hơn 1/4 shared_buffers dùng một vòng đệm nhỏ (ring buffer) để khỏi đẩy dữ liệu khác ra khỏi cache, nên lần nào cũng "read" — nhưng read từ page cache của OS (I/O chỉ 32 ms cho 35 nghìn trang).

Cấu hình mặc định — 2 worker:

Gather  (cost=1000.00..54663.41 rows=55046 width=116) (actual time=0.825..109.375 rows=55501.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=2466 read=35276
  I/O Timings: shared read=32.943
  ->  Parallel Seq Scan on orders  (cost=0.00..48158.81 rows=22936 width=116) (actual time=0.104..94.143 rows=18500.33 loops=3)
        Filter: (total_cents > 9500000)
        Rows Removed by Filter: 648166
        Buffers: shared hit=2466 read=35276
        I/O Timings: shared read=32.943
Planning Time: 0.090 ms
Execution Time: 112.074 ms

Ở node song song, mọi số là trung bình mỗi process (leader + 2 worker = loops=3): 18 500.33 × 3 = 55 501 dòng; 648 166 × 3 ≈ 1,94 triệu dòng bị lọc. 285 ms → 112 ms: nhanh hơn ~2,5 lần, không phải 3 lần — leader còn phải gom kết quả, và máy lab chỉ có 4 vCPU dùng chung. Khi không xin đủ worker (giả lập max_parallel_workers = 1):

Gather  (cost=1000.00..54663.41 rows=55046 width=116) (actual time=0.520..162.035 rows=55501.00 loops=1)
  Workers Planned: 2
  Workers Launched: 1
  Buffers: shared hit=2748 read=34994
  I/O Timings: shared read=48.373
  ->  Parallel Seq Scan on orders  (cost=0.00..48158.81 rows=22936 width=116) (actual time=0.844..141.174 rows=27750.50 loops=2)
        Filter: (total_cents > 9500000)
        Rows Removed by Filter: 972250
        Buffers: shared hit=2748 read=34994
        I/O Timings: shared read=48.373
Planning Time: 0.092 ms
Execution Time: 164.623 ms

Workers Launched nhỏ hơn Planned là dấu hiệu hệ thống đã hết slot worker (max_parallel_workers, max_worker_processes) — query vẫn chạy nhưng chậm hơn dự tính, và plan không đổi theo.

3.2. Ví dụ 2 — khoá ngoại không có index: mặc định của Laravel trên PostgreSQL

Migration Laravel kiểu chuẩn — DB::pretend cho thấy SQL thật (lab laravel/eloquent.php):

-- 13. Migration foreignId()->constrained() sinh ra SQL gì
create table "tmp_shipments" ("id" bigserial not null primary key, "order_id" bigint not null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null);
alter table "tmp_shipments" add constraint "tmp_shipments_order_id_foreign" foreign key ("order_id") references "orders" ("id");

Chỉ có add constraint ... foreign key, không có create index. PostgreSQL tự tạo index cho phía được tham chiếu (khoá chính), không bao giờ cho phía tham chiếu (cột khoá ngoại). Người quen MySQL hay bị bất ngờ vì InnoDB tự tạo index cho khoá ngoại. Project laravel-livewire thật đang ở đúng trạng thái này: migration gốc có khoá ngoại orders.user_id và order_items.order_id nhưng không có index cho hai cột đó. Giả lập bằng cách bỏ index trong transaction:

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

Gather  (cost=1000.00..49160.61 rows=18 width=116) (actual time=21.913..152.955 rows=37.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=1952 read=35790
  I/O Timings: shared read=67.467
  ->  Parallel Seq Scan on orders  (cost=0.00..48158.81 rows=8 width=116) (actual time=8.114..116.787 rows=12.33 loops=3)
        Filter: (user_id = 4242)
        Rows Removed by Filter: 666654
        Buffers: shared hit=1952 read=35790
        I/O Timings: shared read=67.467
Planning:
  Buffers: shared hit=114
Planning Time: 1.568 ms
Execution Time: 153.197 ms

$user->orders thành quét cả 2 triệu đơn để lấy 37 dòng: 153 ms thay vì 1 ms (bài 1, ví dụ 1). Nhưng phía DELETE mới đáng sợ — mỗi dòng cha bị xoá, PostgreSQL chạy một câu kiểm tra trên bảng con:

BEGIN;
BEGIN
DELETE FROM order_items WHERE order_id <= 20;
DELETE 50
EXPLAIN (ANALYZE) DELETE FROM orders WHERE id <= 20;

Delete on orders  (cost=0.43..8.78 rows=0 width=0) (actual time=0.170..0.170 rows=0.00 loops=1)
  Buffers: shared hit=49 dirtied=2
  ->  Index Scan using orders_pkey on orders  (cost=0.43..8.78 rows=20 width=6) (actual time=0.028..0.032 rows=20.00 loops=1)
        Index Cond: (id <= 20)
        Index Searches: 1
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=20
Planning Time: 0.406 ms
Trigger for constraint order_items_order_id_foreign: time=2.578 calls=20
Execution Time: 2.848 ms

BEGIN;
BEGIN
DELETE FROM order_items WHERE order_id <= 20;
DELETE 50
DROP INDEX order_items_order_id_index;
DROP INDEX
EXPLAIN (ANALYZE) DELETE FROM orders WHERE id <= 20;

Delete on orders  (cost=0.43..8.78 rows=0 width=0) (actual time=0.042..0.042 rows=0.00 loops=1)
  Buffers: shared hit=44
  ->  Index Scan using orders_pkey on orders  (cost=0.43..8.78 rows=20 width=6) (actual time=0.015..0.018 rows=20.00 loops=1)
        Index Cond: (id <= 20)
        Index Searches: 1
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=5
Planning Time: 0.139 ms
Trigger for constraint order_items_order_id_foreign: time=10048.178 calls=20
Execution Time: 10048.328 ms

Xoá 20 đơn: 2,8 ms có index, 10 048 ms không có index — mỗi lần kiểm tra là một lần quét 5 triệu dòng order_items. Và như bài 1 đã nói, cây plan trông hoàn toàn vô hại (node Delete 0,04 ms); toàn bộ thời gian nằm ở dòng Trigger for constraint. Mỗi lần kiểm tra mất ~500 ms — đúng cỡ một lượt quét tuần tự 5 triệu dòng không song song (câu kiểm tra khoá ngoại có khoá dòng kiểu FOR KEY SHARE theo source ri_triggers.c, mà câu có khoá dòng thì không chạy song song — giải thích theo source, lab chỉ thấy hệ quả).

3.3. Ví dụ 3 — Index Scan

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE id = 1234567;

Index Scan using orders_pkey on orders  (cost=0.43..8.45 rows=1 width=116) (actual time=0.247..0.249 rows=1.00 loops=1)
  Index Cond: (id = 1234567)
  Index Searches: 1
  Buffers: shared hit=5 read=2
  I/O Timings: shared read=0.066
Planning:
  Buffers: shared hit=140
Planning Time: 12.647 ms
Execution Time: 0.664 ms

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE created_at >= '2026-09-29' AND created_at < '2026-09-30';

Index Scan using orders_created_at_index on orders  (cost=0.43..111.17 rows=2137 width=116) (actual time=0.890..1.877 rows=2282.00 loops=1)
  Index Cond: ((created_at >= '2026-09-29 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-09-30 00:00:00+00'::timestamp with time zone))
  Index Searches: 1
  Buffers: shared hit=4 read=49
  I/O Timings: shared read=1.493
Planning:
  Buffers: shared hit=19 read=2
  I/O Timings: shared read=1.271
Planning Time: 1.786 ms
Execution Time: 2.002 ms

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

Limit  (cost=0.43..1.32 rows=20 width=116) (actual time=0.025..0.031 rows=20.00 loops=1)
  Buffers: shared hit=5
  ->  Index Scan Backward using orders_created_at_index on orders  (cost=0.43..89693.83 rows=2000027 width=116) (actual time=0.024..0.028 rows=20.00 loops=1)
        Index Searches: 1
        Buffers: shared hit=5
Planning:
  Buffers: shared hit=39
Planning Time: 0.437 ms
Execution Time: 0.067 ms

• Index Cond — điều kiện dùng để đi trong cây B-tree. Chỉ có điều kiện nằm ở đây mới giảm được số dòng phải đọc.
• Range 1 ngày: 2 282 đơn chỉ chạm 53 trang (4 hit + 49 read) — vì created_at tương quan với thứ tự vật lý, các đơn trong ngày nằm liền nhau. Cùng số dòng mà rải rác như user_id thì phải chạm ~2 000 trang.
• Index Scan Backward + Limit: ORDER BY created_at DESC LIMIT 20 (Laravel latest()->limit(20)) không cần Sort — đi ngược index, lấy 20 entry, dừng: 5 buffer, 0,067 ms.
• Planning Time: 12.647 ms ở câu đầu: câu đầu tiên của kết nối mới phải nạp catalog (bài 1, mục 5.4).

3.4. Ví dụ 4 — Index Only Scan và visibility map

Index (user_id, created_at) chứa đủ 2 cột câu cần → có thể khỏi đọc heap. Nhưng index không biết dòng nào còn hiệu lực (MVCC), nên với mỗi entry PostgreSQL hỏi visibility map: trang đó đã "mọi dòng đều ai cũng thấy" chưa? Rồi thì trả luôn; chưa thì phải đọc heap — mỗi lần như vậy cộng 1 vào Heap Fetches.

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

Index Only Scan Backward using orders_user_id_created_at_index on orders  (cost=0.43..4.74 rows=18 width=16) (actual time=0.508..0.517 rows=74.00 loops=1)
  Index Cond: (user_id = 1000)
  Heap Fetches: 0
  Index Searches: 1
  Buffers: shared hit=10 read=3
  I/O Timings: shared read=0.057
Planning:
  Buffers: shared hit=62
Planning Time: 0.690 ms
Execution Time: 0.582 ms

Một câu UPDATE không đổi giá trị nào vẫn tạo phiên bản dòng mới và xoá bit all-visible của các trang liên quan:

UPDATE orders SET updated_at = updated_at WHERE user_id = 1000;
UPDATE 74
Index Only Scan Backward using orders_user_id_created_at_index on orders  (cost=0.43..4.74 rows=18 width=16) (actual time=0.082..0.784 rows=74.00 loops=1)
  Index Cond: (user_id = 1000)
  Heap Fetches: 121
  Index Searches: 1
  Buffers: shared hit=194
Planning Time: 0.185 ms
Execution Time: 0.841 ms

121 lần đọc heap cho 74 dòng (entry trỏ tới cả bản cũ lẫn bản mới). Chạy VACUUM thường:

VACUUM (VERBOSE) orders;
INFO:  vacuuming "tmp_qplan.public.orders"
INFO:  launched 2 parallel vacuum workers for index cleanup (planned: 2)
INFO:  finished vacuuming "tmp_qplan.public.orders": index scans: 0
pages: 0 removed, 37743 remain, 96 scanned (0.25% of total), 0 eagerly scanned
tuples: 0 removed, 1999980 remain, 0 are dead but not yet removable
removable cutoff: 718881, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 50 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan bypassed: 2 pages from table (0.01% of total) have 47 dead item identifiers

Index Only Scan Backward using orders_user_id_created_at_index on orders  (cost=0.43..8.74 rows=18 width=16) (actual time=0.062..0.107 rows=74.00 loops=1)
  Index Cond: (user_id = 1000)
  Heap Fetches: 41
  Index Searches: 1
  Buffers: shared hit=89
Planning:
  Buffers: shared hit=5
Planning Time: 0.219 ms
Execution Time: 0.350 ms

Vẫn còn 41. Dòng VERBOSE giải thích: index scan bypassed: 2 pages ... have 47 dead item identifiers — từ PostgreSQL 14, khi số trang có dòng chết quá ít, VACUUM bỏ qua bước dọn index cho rẻ. Trang còn dòng chết chưa dọn thì không được đánh all-visible. Ép dọn index:

Index Only Scan Backward using orders_user_id_created_at_index on orders  (cost=0.43..4.74 rows=18 width=16) (actual time=0.073..0.083 rows=74.00 loops=1)
  Index Cond: (user_id = 1000)
  Heap Fetches: 0
  Index Searches: 1
  Buffers: shared hit=7
Planning:
  Buffers: shared hit=5
Planning Time: 0.499 ms
Execution Time: 0.122 ms

EXPLAIN (ANALYZE)
SELECT user_id, created_at, total_cents FROM orders WHERE user_id = 1000 ORDER BY created_at DESC;

Index Scan Backward using orders_user_id_created_at_index on orders  (cost=0.43..76.74 rows=18 width=20) (actual time=0.032..0.117 rows=74.00 loops=1)
  Index Cond: (user_id = 1000)
  Index Searches: 1
  Buffers: shared hit=54
Planning:
  Buffers: shared hit=3
Planning Time: 0.189 ms
Execution Time: 0.151 ms

Hai điều tài liệu không nói rõ mà plan cho thấy: (1) cost của Index Only Scan đổi theo visibility map — cost=0.43..8.74 khi bảng có trang chưa all-visible, 0.43..4.74 sau khi dọn sạch; planner đọc số trang all-visible (pg_class.relallvisible) để tính. (2) Thêm một cột không có trong index (total_cents) là quay về Index Scan thường, phải đọc heap cho mọi dòng.

3.5. Ví dụ 5 — Bitmap Index Scan + Bitmap Heap Scan

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

Bitmap Heap Scan on orders  (cost=47.81..10566.92 rows=3533 width=116) (actual time=1.746..11.462 rows=4385.00 loops=1)
  Recheck Cond: (user_id = 1)
  Heap Blocks: exact=4138
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..46.93 rows=3533 width=0) (actual time=1.153..1.154 rows=4385.00 loops=1)
        Index Cond: (user_id = 1)
        Index Searches: 1
Planning Time: 2.285 ms
Execution Time: 11.972 ms

4 385 đơn của khách 1 nằm trên 4 138 trang. Bước 1 (Bitmap Index Scan, node con) đi index, đánh dấu trang: 1,1 ms. Bước 2 (Bitmap Heap Scan) đọc 4 138 trang theo thứ tự vật lý. Recheck Cond là điều kiện được kiểm lại trên dòng thật — cần khi bitmap là lossy (ví dụ 6) và để chắc với các dòng có nhiều phiên bản; khi bitmap exact, recheck không loại dòng nào.

Ba thứ Bitmap làm được mà Index Scan không làm được:

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE user_id IN (1000, 4242, 50000);

Bitmap Heap Scan on orders  (cost=13.70..224.32 rows=54 width=116) (actual time=0.092..0.340 rows=123.00 loops=1)
  Recheck Cond: (user_id = ANY ('{1000,4242,50000}'::bigint[]))
  Heap Blocks: exact=99
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..13.69 rows=54 width=0) (actual time=0.073..0.074 rows=123.00 loops=1)
        Index Cond: (user_id = ANY ('{1000,4242,50000}'::bigint[]))
        Index Searches: 3
Planning Time: 0.624 ms
Execution Time: 0.389 ms

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE user_id = 4242 OR order_number = 'ORD-00123456';

Bitmap Heap Scan on orders  (cost=9.01..84.01 rows=19 width=116) (actual time=0.055..0.091 rows=38.00 loops=1)
  Recheck Cond: ((user_id = 4242) OR (order_number = 'ORD-00123456'::text))
  Heap Blocks: exact=38
  ->  BitmapOr  (cost=9.01..9.01 rows=19 width=0) (actual time=0.038..0.038 rows=0.00 loops=1)
        ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..4.56 rows=18 width=0) (actual time=0.025..0.025 rows=37.00 loops=1)
              Index Cond: (user_id = 4242)
              Index Searches: 1
        ->  Bitmap Index Scan on orders_order_number_unique  (cost=0.00..4.44 rows=1 width=0) (actual time=0.013..0.013 rows=1.00 loops=1)
              Index Cond: (order_number = 'ORD-00123456'::text)
              Index Searches: 1
Planning Time: 0.126 ms
Execution Time: 0.151 ms

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE user_id = 1 AND created_at >= '2026-09-01';

Bitmap Heap Scan on orders  (cost=1184.58..1604.65 rows=109 width=116) (actual time=3.533..3.744 rows=155.00 loops=1)
  Recheck Cond: ((user_id = 1) AND (created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone))
  Heap Blocks: exact=146
  ->  BitmapAnd  (cost=1184.58..1184.58 rows=109 width=0) (actual time=3.484..3.486 rows=0.00 loops=1)
        ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..46.93 rows=3533 width=0) (actual time=1.007..1.008 rows=4385.00 loops=1)
              Index Cond: (user_id = 1)
              Index Searches: 1
        ->  Bitmap Index Scan on orders_created_at_index  (cost=0.00..1137.36 rows=61457 width=0) (actual time=2.311..2.311 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.146 ms
Execution Time: 3.855 ms

• IN (...) thành = ANY('{...}'): một node, Index Searches: 3 (3 lần đi từ gốc cây xuống). Đây cũng là dạng SQL Laravel sinh ra khi eager load: where "order_items"."order_id" in (41305, 94801, 103957).
• OR giữa 2 cột có index riêng → BitmapOr hợp 2 bitmap. Không có bitmap thì OR trên 2 cột khác nhau gần như luôn thành Seq Scan.
• AND giữa 2 index riêng → BitmapAnd lấy giao. Nhưng để ý: nó phải đi index created_at lấy 65 990 vị trí (2,3 ms trong tổng 3,9 ms) chỉ để giao với 4 385 vị trí kia, ra 155 dòng. Một index ghép (user_id, created_at) làm việc đó trong 1 lần tra (ví dụ 8).

3.6. Ví dụ 6 — bitmap "lossy" khi work_mem nhỏ

Bitmap nằm trong work_mem. Không đủ chỗ thì PostgreSQL chuyển một phần bitmap từ "nhớ từng dòng" (exact) sang "chỉ nhớ trang" (lossy) — rồi phải kiểm lại mọi dòng trong các trang lossy.

SET work_mem = '64kB';
SET
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE user_id IN (1, 2, 10);

Index Scan using orders_user_id_index on orders  (cost=0.43..20991.14 rows=5600 width=116) (actual time=0.061..17.836 rows=6982.00 loops=1)
  Index Cond: (user_id = ANY ('{1,2,10}'::bigint[]))
  Index Searches: 2
Planning Time: 3.847 ms
Execution Time: 18.382 ms

SET enable_indexscan = off;
SET
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE user_id IN (1, 2, 10);

Gather  (cost=1080.69..27018.94 rows=5600 width=116) (actual time=3.367..43.454 rows=6982.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Bitmap Heap Scan on orders  (cost=80.69..25458.94 rows=2333 width=116) (actual time=0.897..27.331 rows=2327.33 loops=3)
        Recheck Cond: (user_id = ANY ('{1,2,10}'::bigint[]))
        Rows Removed by Index Recheck: 100311
        Heap Blocks: exact=276 lossy=2785
        Worker 0:  Heap Blocks: exact=110 lossy=1408
        Worker 1:  Heap Blocks: exact=176 lossy=1607
        ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..79.29 rows=5600 width=0) (actual time=1.813..1.814 rows=6982.00 loops=1)
              Index Cond: (user_id = ANY ('{1,2,10}'::bigint[]))
              Index Searches: 2
Planning Time: 0.136 ms
Execution Time: 44.146 ms

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE user_id IN (1, 2, 10);

Bitmap Heap Scan on orders  (cost=80.69..15199.90 rows=5600 width=116) (actual time=2.093..14.228 rows=6982.00 loops=1)
  Recheck Cond: (user_id = ANY ('{1,2,10}'::bigint[]))
  Heap Blocks: exact=6362
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..79.29 rows=5600 width=0) (actual time=1.322..1.322 rows=6982.00 loops=1)
        Index Cond: (user_id = ANY ('{1,2,10}'::bigint[]))
        Index Searches: 2
Planning Time: 0.128 ms
Execution Time: 14.719 ms

Với work_mem 64 kB, planner biết trước bitmap sẽ lossy và tự chọn Index Scan. Ép dùng bitmap thì thấy cái giá: lossy=2785 trang, Rows Removed by Index Recheck: 100311 mỗi process (~300 nghìn dòng kiểm lại vô ích), 44 ms. Đủ bộ nhớ: exact=6362, không recheck dòng nào, 14,7 ms. Thấy lossy= trong plan production → work_mem quá nhỏ cho câu đó.

3.7. Ví dụ 7 — cùng một câu, khác giá trị, khác kiểu scan

Tạo index trên status (trong transaction), chạy cùng câu với 3 giá trị có tần suất rất khác nhau:

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE status = 'pending';    -- ~0.5%

Index Scan using orders_status_index on orders  (cost=0.43..17873.04 rows=9133 width=116) (actual time=0.078..6.227 rows=9639.00 loops=1)
  Index Cond: (status = 'pending'::order_status)
  Index Searches: 1
Planning Time: 1.493 ms
Execution Time: 6.748 ms

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE status = 'refunded';   -- ~4%

Bitmap Heap Scan on orders  (cost=873.96..39593.62 rows=78133 width=116) (actual time=25.069..320.647 rows=78955.00 loops=1)
  Recheck Cond: (status = 'refunded'::order_status)
  Heap Blocks: exact=32905
  ->  Bitmap Index Scan on orders_status_index  (cost=0.00..854.42 rows=78133 width=0) (actual time=12.220..12.220 rows=78955.00 loops=1)
        Index Cond: (status = 'refunded'::order_status)
        Index Searches: 1
Planning Time: 0.099 ms
Execution Time: 325.493 ms

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE status = 'delivered';  -- ~85%

Seq Scan on orders  (cost=0.00..62742.75 rows=1697383 width=116) (actual time=0.015..517.960 rows=1691535.00 loops=1)
  Filter: (status = 'delivered'::order_status)
  Rows Removed by Filter: 308465
Planning Time: 0.160 ms
Execution Time: 597.742 ms

Muốn biết vì sao planner chọn vậy thì xem giá của phương án bị loại — cấm tạm loại node đang được chọn:

SET enable_seqscan = off;
SET
EXPLAIN SELECT * FROM orders WHERE status = 'refunded';

Bitmap Heap Scan on orders  (cost=873.96..39593.62 rows=78133 width=116)
  Recheck Cond: (status = 'refunded'::order_status)
  ->  Bitmap Index Scan on orders_status_index  (cost=0.00..854.42 rows=78133 width=0)
        Index Cond: (status = 'refunded'::order_status)

SET enable_bitmapscan = off;
SET
EXPLAIN SELECT * FROM orders WHERE status = 'refunded';

Index Scan using orders_status_index on orders  (cost=0.43..83841.17 rows=78133 width=116)
  Index Cond: (status = 'refunded'::order_status)

Với refunded: Bitmap 39 594 < Parallel Seq Scan 56 972 (bài 1, ví dụ 4a) < Index Scan 83 841. Nhưng nhìn kết quả thật: bitmap đọc exact=32905 trang — 87% bảng — theo kiểu nhảy cóc, chậm hơn một lượt Parallel Seq Scan cả bảng (~110 ms ở ví dụ 1). Ước lượng rows đúng, nhưng cost model không biết lúc đó phần lớn trang phải đọc từ đĩa (read=28068). Planner đúng về số dòng vẫn có thể sai về thời gian.

3.8. Ví dụ 8 — Index Cond khác Filter

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE user_id = 1 AND status = 'refunded';

Bitmap Heap Scan on orders  (cost=46.96..10574.90 rows=138 width=116) (actual time=5.395..26.413 rows=193.00 loops=1)
  Recheck Cond: (user_id = 1)
  Filter: (status = 'refunded'::order_status)
  Rows Removed by Filter: 4192
  Heap Blocks: exact=4138
  ->  Bitmap Index Scan on orders_user_id_index  (cost=0.00..46.93 rows=3533 width=0) (actual time=2.758..2.759 rows=4385.00 loops=1)
        Index Cond: (user_id = 1)
        Index Searches: 1
Planning Time: 12.195 ms
Execution Time: 26.647 ms

BEGIN;
BEGIN
CREATE INDEX orders_user_id_status_index ON orders (user_id, status);
CREATE INDEX
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE user_id = 1 AND status = 'refunded';

Bitmap Heap Scan on orders  (cost=5.84..534.88 rows=138 width=116) (actual time=0.109..0.468 rows=193.00 loops=1)
  Recheck Cond: ((user_id = 1) AND (status = 'refunded'::order_status))
  Heap Blocks: exact=193
  ->  Bitmap Index Scan on orders_user_id_status_index  (cost=0.00..5.81 rows=138 width=0) (actual time=0.081..0.081 rows=193.00 loops=1)
        Index Cond: ((user_id = 1) AND (status = 'refunded'::order_status))
        Index Searches: 1
Planning Time: 0.543 ms
Execution Time: 0.545 ms

Chỉ có index user_id: đọc 4 385 dòng, 4 138 trang, rồi lọc status bỏ 4 192 dòng — 26,6 ms. Index ghép (user_id, status): cả hai điều kiện vào Index Cond, chỉ đọc 193 trang — 0,5 ms. Đọc plan: điều kiện nằm ở Filter trên một node index là dấu hiệu index đang "thiếu một cột". Thứ tự cột trong index ghép: cột so sánh bằng (=) trước, cột khoảng hoặc cột sắp xếp sau.

3.9. Ví dụ 9 — skip scan (PostgreSQL 18)

BEGIN;
BEGIN
DROP INDEX orders_created_at_index;
DROP INDEX
CREATE INDEX orders_status_created_at_index ON orders (status, created_at);
CREATE INDEX
EXPLAIN (ANALYZE)
SELECT id, status, created_at FROM orders WHERE created_at >= '2026-09-29';

Index Scan using orders_status_created_at_index on orders  (cost=0.43..6224.30 rows=2120 width=20) (actual time=0.174..1.506 rows=2283.00 loops=1)
  Index Cond: (created_at >= '2026-09-29 00:00:00+00'::timestamp with time zone)
  Index Searches: 7
Planning Time: 1.783 ms
Execution Time: 1.733 ms

Câu không lọc status — cột đầu của index (status, created_at). PostgreSQL 18 vẫn dùng được index này: với mỗi giá trị của status (6 giá trị), nhảy thẳng tới đoạn created_at >= … — Index Searches: 7. Theo release notes, bản 17 trở xuống không làm vậy với điều kiện này (chưa verify trên 17). Skip scan chỉ đáng khi cột đầu có ít giá trị; với cột đầu nhiều giá trị (vd user_id), số lần nhảy tiến tới quét hết index.

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

• Không có SQL nào đảm bảo thứ tự nếu không có ORDER BY — kể cả khi lần nào chạy thử cũng "có vẻ đúng".
• Index không miễn phí: mỗi INSERT ghi vào mọi index; UPDATE không phải HOT cũng vậy. Partial index nhỏ hơn rất nhiều (ví dụ 12: 440 kB so với 43 MB).
• Parallel cần bảng lớn hơn min_parallel_table_scan_size (8 MB), slot worker còn trống, và trả về ít dòng — mỗi dòng chuyển từ worker về leader tốn parallel_tuple_cost, nên câu trả 1,7 triệu dòng (ví dụ 7, delivered) chạy Seq Scan thường.
• Index Only Scan chỉ tốt trên bảng ít ghi hoặc được VACUUM đều; bảng ghi liên tục thì Heap Fetches ≈ số dòng.
• Skip scan chỉ có từ PG 18 và chỉ hiệu quả khi cột đầu ít giá trị.

5. Failure modes — index có mà không dùng được

Phần này là các lỗi gặp hằng ngày khi viết query bằng ORM. Dấu hiệu chung trong plan: Seq Scan (hoặc quét hết một index) với Rows Removed by Filter rất lớn, dù cột trong WHERE có index.

5.1. Ví dụ 10 — bọc cột trong hàm hoặc ép kiểu

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE created_at::date = '2026-09-01';

Gather  (cost=1000.00..52242.88 rows=10000 width=116) (actual time=18.393..273.894 rows=2268.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..50242.88 rows=4167 width=116) (actual time=91.622..255.426 rows=756.00 loops=3)
        Filter: ((created_at)::date = '2026-09-01'::date)
        Rows Removed by Filter: 665911
Planning Time: 1.727 ms
Execution Time: 274.221 ms

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02';

Index Scan using orders_created_at_index on orders  (cost=0.43..110.99 rows=2128 width=116) (actual time=0.738..1.805 rows=2268.00 loops=1)
  Index Cond: ((created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-09-02 00:00:00+00'::timestamp with time zone))
  Index Searches: 1
Planning Time: 0.504 ms
Execution Time: 1.940 ms

whereDate('created_at', '2026-09-01') sinh ra "created_at"::date = '2026-09-01'. Index trên created_at không tìm được theo created_at::date → quét cả bảng, 274 ms. Viết lại thành khoảng nửa mở → Index Scan, 1,9 ms. Để ý thêm: ước lượng rows=10000 — biểu thức không có thống kê, planner đoán cứng 0,5% bảng (thật 2 268).

"Thế tạo index trên biểu thức là xong?" — với timestamptz thì không:

CREATE INDEX orders_created_date_index ON orders ((created_at::date));
ERROR:  functions in index expression must be marked IMMUTABLE

timestamptz::date phụ thuộc múi giờ của phiên (TimeZone) nên không phải IMMUTABLE — cùng một giá trị cho ra ngày khác nhau ở hai phiên khác múi giờ. Phải ghi rõ múi giờ, và câu query phải viết y hệt biểu thức của index:

CREATE INDEX orders_created_date_vn_index ON orders (((created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date));
CREATE INDEX
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE (created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date = '2026-09-01';

Bitmap Heap Scan on orders  (cost=113.93..22797.95 rows=10000 width=116) (actual time=0.116..0.374 rows=2268.00 loops=1)
  Recheck Cond: (((created_at AT TIME ZONE 'Asia/Ho_Chi_Minh'::text))::date = '2026-09-01'::date)
  Heap Blocks: exact=44
  ->  Bitmap Index Scan on orders_created_date_vn_index  (cost=0.00..111.43 rows=10000 width=0) (actual time=0.101..0.101 rows=2268.00 loops=1)
        Index Cond: (((created_at AT TIME ZONE 'Asia/Ho_Chi_Minh'::text))::date = '2026-09-01'::date)
        Index Searches: 1
Planning Time: 0.451 ms
Execution Time: 0.541 ms

Hai biến thể khác cùng họ:

EXPLAIN (ANALYZE)
SELECT count(*) FROM orders WHERE extract(year from created_at) = 2026 AND extract(month from created_at) = '09';

Finalize Aggregate  (cost=49615.09..49615.10 rows=1 width=8) (actual time=901.880..921.115 rows=1.00 loops=1)
  ->  Gather  (cost=49614.88..49615.09 rows=2 width=8) (actual time=880.241..921.099 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=48614.88..48614.89 rows=1 width=8) (actual time=876.361..876.362 rows=1.00 loops=3)
              ->  Parallel Index Only Scan using orders_created_at_index on orders  (cost=0.43..48614.83 rows=21 width=0) (actual time=748.980..873.860 rows=21996.67 loops=3)
                    Filter: ((EXTRACT(year FROM created_at) = '2026'::numeric) AND (EXTRACT(month FROM created_at) = '9'::numeric))
                    Rows Removed by Filter: 644670
                    Heap Fetches: 0
                    Index Searches: 1
Planning Time: 1.212 ms
Execution Time: 921.311 ms

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE id = 1234567.0;

Gather  (cost=1000.00..52242.88 rows=10000 width=116) (actual time=584.549..591.046 rows=1.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..50242.88 rows=4167 width=116) (actual time=480.687..572.077 rows=0.33 loops=3)
        Filter: ((id)::numeric = 1234567.0)
        Rows Removed by Filter: 666666
Planning Time: 0.107 ms
Execution Time: 591.089 ms

whereYear + whereMonth sinh extract(...): planner quét toàn bộ index created_at (Index Only Scan không có Index Cond) và lọc từng entry — 921 ms. Còn id = 1234567.0 (client gửi số kiểu thập phân, hay gặp với JavaScript/JSON): không có phép so sánh bigint = numeric nên PostgreSQL ép cột sang numeric ((id)::numeric) — mất cả khoá chính, 591 ms để tìm 1 dòng.

5.2. Ví dụ 11 — LIKE

SELECT datcollate FROM pg_database WHERE datname = current_database();
 datcollate
------------
 en_US.utf8
(1 row)

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE order_number LIKE 'ORD-0012345%';

Gather  (cost=1000.00..49179.56 rows=200 width=116) (actual time=128.087..136.188 rows=10.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..48159.56 rows=83 width=116) (actual time=87.974..119.988 rows=3.33 loops=3)
        Filter: (order_number ~~ 'ORD-0012345%'::text)
        Rows Removed by Filter: 666663
Planning Time: 9.152 ms
Execution Time: 136.585 ms

BEGIN;
BEGIN
CREATE INDEX orders_order_number_pattern_index ON orders (order_number text_pattern_ops);
CREATE INDEX
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE order_number LIKE 'ORD-0012345%';

Index Scan using orders_order_number_pattern_index on orders  (cost=0.43..8.45 rows=200 width=116) (actual time=0.110..0.131 rows=10.00 loops=1)
  Index Cond: ((order_number ~>=~ 'ORD-0012345'::text) AND (order_number ~<~ 'ORD-0012346'::text))
  Filter: (order_number ~~ 'ORD-0012345%'::text)
  Index Searches: 1
Planning Time: 5.845 ms
Execution Time: 0.186 ms

Database dùng collation en_US.utf8. Index B-tree thường sắp theo luật của collation, không dùng được cho LIKE 'abc%' (index unique có sẵn trên order_number bị bỏ qua, quét cả bảng). Index với operator class text_pattern_ops so từng byte: LIKE tiền tố biến thành khoảng ~>=~ ... ~<~ — 0,19 ms. Với LIKE '%...%' (Laravel thêm ::text vào cột, vô hại với cột text — plan không còn phép ép):

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE order_number::text LIKE '%0012345%';

Gather  (cost=1000.00..49179.56 rows=200 width=116) (actual time=21.293..181.046 rows=11.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..48159.56 rows=83 width=116) (actual time=67.920..166.275 rows=3.67 loops=3)
        Filter: (order_number ~~ '%0012345%'::text)
        Rows Removed by Filter: 666663
Planning Time: 0.237 ms
Execution Time: 181.087 ms

BEGIN;
BEGIN
CREATE INDEX orders_order_number_trgm_index ON orders USING gin (order_number gin_trgm_ops);
CREATE INDEX
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE order_number::text LIKE '%0012345%';

Bitmap Heap Scan on orders  (cost=130.46..889.29 rows=200 width=116) (actual time=16.220..16.228 rows=11.00 loops=1)
  Recheck Cond: (order_number ~~ '%0012345%'::text)
  Heap Blocks: exact=2
  ->  Bitmap Index Scan on orders_order_number_trgm_index  (cost=0.00..130.41 rows=200 width=0) (actual time=14.199..14.199 rows=11.00 loops=1)
        Index Cond: (order_number ~~ '%0012345%'::text)
        Index Searches: 1
Planning Time: 0.300 ms
Execution Time: 16.322 ms

Index GIN trigram: 181 ms → 16 ms. Đổi lại GIN to và ghi chậm hơn B-tree nhiều; chỉ đáng cho ô tìm kiếm thật sự cần.

5.3. Ví dụ 12 — partial index và giới hạn của nó

CREATE INDEX orders_open_created_at_index ON orders (created_at) WHERE status IN ('pending', 'paid');
CREATE INDEX
SELECT pg_size_pretty(pg_relation_size('orders_open_created_at_index')) AS partial_index,
       pg_size_pretty(pg_relation_size('orders_created_at_index'))      AS full_index;
 partial_index | full_index
---------------+------------
 440 kB        | 43 MB
(1 row)

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE status IN ('pending', 'paid') ORDER BY created_at LIMIT 50;

Limit  (cost=0.29..2.57 rows=50 width=116) (actual time=0.059..0.086 rows=50.00 loops=1)
  ->  Index Scan using orders_open_created_at_index on orders  (cost=0.29..834.28 rows=18266 width=116) (actual time=0.058..0.081 rows=50.00 loops=1)
        Index Searches: 1
Planning Time: 18.282 ms
Execution Time: 0.130 ms

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 50;

Limit  (cost=0.29..5.10 rows=50 width=116) (actual time=0.419..0.485 rows=50.00 loops=1)
  ->  Index Scan using orders_open_created_at_index on orders  (cost=0.29..879.94 rows=9133 width=116) (actual time=0.417..0.479 rows=50.00 loops=1)
        Filter: (status = 'pending'::order_status)
        Rows Removed by Filter: 63
        Index Searches: 1
Planning Time: 0.150 ms
Execution Time: 0.535 ms

Màn hình "đơn chờ xử lý" chỉ cần đơn pending/paid: index 440 kB thay vì 43 MB, 0,13 ms. Planner tự chứng minh được status = 'pending' nằm trong IN ('pending','paid'). Nhưng:

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE status = 'shipped' ORDER BY created_at LIMIT 50;

Limit  (cost=0.43..515.07 rows=50 width=116) (actual time=654.493..654.531 rows=50.00 loops=1)
  ->  Index Scan using orders_created_at_index on orders  (cost=0.43..94694.08 rows=9200 width=116) (actual time=654.491..654.524 rows=50.00 loops=1)
        Filter: (status = 'shipped'::order_status)
        Rows Removed by Filter: 1968199
        Index Searches: 1
Planning Time: 0.131 ms
Execution Time: 654.571 ms

PREPARE open_orders(order_status) AS
  SELECT * FROM orders WHERE status = $1 ORDER BY created_at LIMIT 50;
PREPARE
SET plan_cache_mode = force_generic_plan;
SET
EXPLAIN (ANALYZE) EXECUTE open_orders('pending');

Limit  (cost=0.43..14.63 rows=50 width=116) (actual time=465.020..465.073 rows=50.00 loops=1)
  ->  Index Scan using orders_created_at_index on orders  (cost=0.43..94694.08 rows=333330 width=116) (actual time=465.017..465.064 rows=50.00 loops=1)
        Filter: (status = $1)
        Rows Removed by Filter: 1968231
        Index Searches: 1
Planning Time: 0.165 ms
Execution Time: 465.154 ms

Hai bài học trong một ví dụ. (1) Giá trị không nằm trong điều kiện của index (shipped) → không dùng được, và planner rơi vào bẫy LIMIT: đi index created_at từ năm 2023, lọc từng dòng, bỏ 1 968 199 dòng vì đơn shipped chỉ có ở 2 tuần cuối — 654 ms (cơ chế ở bài 4). (2) Câu prepared chạy generic plan: $1 chưa biết là gì nên planner không chứng minh được $1 ∈ ('pending','paid') → không dùng được partial index, 465 ms so với 0,54 ms khi viết thẳng 'pending' (generic plan ở bài 5).

5.4. Ví dụ 13 — cột JSON

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

Gather  (cost=1000.00..52242.88 rows=10000 width=116) (actual time=0.898..247.999 rows=99664.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..50242.88 rows=4167 width=116) (actual time=0.452..211.258 rows=33221.33 loops=3)
        Filter: ((meta ->> 'source'::text) = 'pos'::text)
        Rows Removed by Filter: 633445
Planning Time: 12.997 ms
Execution Time: 253.828 ms

BEGIN;
BEGIN
CREATE INDEX orders_meta_source_index ON orders ((meta->>'source'));
CREATE INDEX
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE meta->>'source' = 'pos';

Bitmap Heap Scan on orders  (cost=113.93..22772.95 rows=10000 width=116) (actual time=21.987..314.490 rows=99664.00 loops=1)
  Recheck Cond: ((meta ->> 'source'::text) = 'pos'::text)
  Heap Blocks: exact=35319
  ->  Bitmap Index Scan on orders_meta_source_index  (cost=0.00..111.43 rows=10000 width=0) (actual time=14.692..14.693 rows=99664.00 loops=1)
        Index Cond: ((meta ->> 'source'::text) = 'pos'::text)
        Index Searches: 1
Planning Time: 0.500 ms
Execution Time: 320.919 ms

where('meta->source', 'pos') → ("meta"->>'source') = 'pos'. Có index trên biểu thức đó rồi mà câu lại chậm hơn (321 ms so với 254 ms): planner vẫn đoán 0,5% (10 000 dòng) vì chưa có thống kê cho biểu thức, nên chọn bitmap; thật ra là 99 664 dòng trên 35 319 trang — gần cả bảng, đọc kiểu nhảy cóc. Thống kê cho biểu thức chỉ có sau ANALYZE; bài 5 làm tiếp ví dụ này.

5.5. Bảng tra nhanh: triệu chứng → nguyên nhân

6. Vận hành trong production

Không cần EXPLAIN từng câu để biết bảng nào đang bị quét cả bảng, index nào không ai dùng, khoá ngoại nào thiếu index (02-14-van-hanh-index.sql; câu tìm khoá ngoại thiếu index chạy sau khi bỏ 2 index FK trong BEGIN … ROLLBACK để giả lập project thật):

SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables WHERE relname IN ('orders', 'order_items', 'users', 'products')
ORDER BY seq_tup_read DESC;
   relname   | seq_scan | seq_tup_read | idx_scan
-------------+----------+--------------+----------
 order_items |    45791 |   4073637820 |  1029520
 orders      |      174 |    167104812 |     2576
 users       |       19 |      2800000 |   600041
 products    |        8 |       350000 |      139
(4 rows)

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE relname IN ('orders', 'order_items')
ORDER BY idx_scan;
   relname   |         indexrelname         | idx_scan |  size
-------------+------------------------------+----------+--------
 order_items | order_items_pkey             |        0 | 107 MB
 orders      | orders_order_number_unique   |        2 | 93 MB
 order_items | order_items_product_id_index |       42 | 34 MB
 orders      | orders_created_at_index      |      152 | 57 MB
 orders      | orders_pkey                  |     1166 | 56 MB
 orders      | orders_user_id_index         |     1256 | 28 MB
 order_items | order_items_order_id_index   |  1029478 | 77 MB
(7 rows)

SELECT c.conrelid::regclass AS bang_con, a.attname AS cot_fk, c.conname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]
  )
ORDER BY 1, 2;
  bang_con   |  cot_fk  |           conname
-------------+----------+------------------------------
 orders      | user_id  | orders_user_id_foreign
 order_items | order_id | order_items_order_id_foreign
(2 rows)

• seq_tup_read của order_items: 4 tỷ dòng — cộng dồn từ các ví dụ cố ý thiếu index của lab (FK, NOT IN ở bài 3). Ở production, bảng lớn có seq_tup_read tăng nhanh là chỗ đầu tiên cần nhìn.
• idx_scan = 0 không có nghĩa là xoá được ngay: order_items_pkey (107 MB) chưa ai dùng để đọc nhưng là khoá chính; orders_order_number_unique giữ ràng buộc unique. Và số liệu này là của riêng từng node — trên replica:

 relname |        indexrelname        | idx_scan
---------+----------------------------+----------
 orders  | orders_created_at_index    |        0
 orders  | orders_order_number_unique |        0
 orders  | orders_pkey                |        0
 orders  | orders_user_id_index       |        1
(4 rows)

Primary ghi orders_user_id_index 1 256 lần dùng, replica ghi 1 (một câu ở bài 1). Index "không ai dùng" trên primary có thể đang phục vụ báo cáo chạy trên replica. Kiểm mọi node trước khi xoá.

• Thêm index trên bảng đang chạy: CREATE INDEX CONCURRENTLY (chi tiết và cái giá ở bài CREATE INDEX CONCURRENTLY). Laravel có ->online():

create index concurrently "orders_status_index" on "orders" ("status");
PostgresGrammar::supportsSchemaTransactions() = true

BEGIN;
BEGIN
CREATE INDEX CONCURRENTLY orders_status_index ON orders (status);
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Laravel sinh đúng create index concurrently, nhưng grammar PostgreSQL chạy migration trong transaction (supportsSchemaTransactions() = true) — mà CONCURRENTLY thì không chạy được trong transaction. Migration có ->online() phải khai báo public $withinTransaction = false; (thuộc tính có sẵn trong Illuminate\Database\Migrations\Migration, mặc định true).

• Visibility map: theo dõi relallvisible / relpages của bảng có Index Only Scan quan trọng; autovacuum phải chạy đủ đều.
• work_mem ảnh hưởng bitmap (lossy) — đặt riêng cho role chạy báo cáo thay vì tăng toàn cục (bài 5: tăng toàn cục trong Docker làm query chết).

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

Dùng index khi câu lấy ít dòng so với bảng, khi cần thứ tự cho LIMIT, hoặc để thực thi ràng buộc. Tránh thêm index chỉ vì thấy Seq Scan: với câu lấy phần lớn bảng, Seq Scan là đúng; và mỗi index thêm vào là thêm chi phí cho mọi INSERT/UPDATE.

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

• Luật cho migration: mọi foreignId()->constrained() đi kèm ->index(), trừ khi PR giải thích vì sao không. Kiểm tự động bằng câu SQL tìm FK thiếu index ở mục 6, chạy trong CI trên database đã migrate.
• Index là quyết định kiến trúc nhỏ: ghi vì sao có index đó (phục vụ màn hình/query nào). Không có lý do thì 2 năm sau không ai dám xoá — và nó vẫn làm chậm mọi lần ghi.
• Quy ước viết query trong team: cấm whereDate/whereYear trên bảng lớn (dùng khoảng); tham số khoá luôn là số nguyên; cột JSON hay lọc thì tách thành cột thật.
• Rủi ro dài hạn: index được thêm vội trong sự cố, không ai dọn. Đặt lịch rà pg_stat_user_indexes theo quý — trên mọi node.

Mẫu ADR ngắn: "Mọi cột khoá ngoại phải có index (B-tree, cột FK đứng đầu). CI chạy query kiểm FK thiếu index sau migrate, fail nếu có kết quả. Ngoại lệ phải ghi trong migration. Index mới trên bảng > 1 triệu dòng dùng ->online() + $withinTransaction = false. Lý do: lab cho thấy DELETE 20 dòng cha mất 10 giây khi thiếu index FK, và lỗi này không lộ ra trong cây plan."

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

1. Tin rằng khoá ngoại có sẵn index (đúng với MySQL InnoDB, sai với PostgreSQL).
2. Thấy Seq Scan là thêm index — kể cả cho câu lấy 85% bảng.
3. Mỗi cột một index rồi trông chờ BitmapAnd thay vì một index ghép đúng thứ tự (ví dụ 5: BitmapAnd phải lấy 65 990 vị trí từ index thứ hai để ra 155 dòng; ví dụ 8: index ghép tra thẳng).
4. whereDate/whereYear/lower(email) trong WHERE rồi thắc mắc vì sao không dùng index.
5. Tạo expression index trên timestamptz::date — bị từ chối; hoặc tạo với múi giờ rồi query viết khác biểu thức một chút.
6. Trông vào Index Only Scan trên bảng ghi liên tục mà không xem Heap Fetches.
7. Xoá index vì idx_scan = 0 trên primary mà không xem replica.
8. Partial index + prepared statement generic — index không bao giờ được dùng (ví dụ 12).

Nguồn công khai: Tom Lane trên mailing list pgsql-general (16/01/2024, message-id 1905752.1705443374@sss.pgh.pa.us) trả lời câu hỏi DELETE chậm: "99% of the time, the cause is lack of an index on the foreign key's referencing columns", và cách nhận ra: "if EXPLAIN ANALYZE shows a lot of time spent in the enforcement trigger for the FK" — đúng dòng Trigger for constraint ở ví dụ 2. Blog Render (Eric Fritz, 12/12/2024, "Fix a top cause of slow queries in PostgreSQL") dựng lại tình huống tương tự: xoá một user mất 19 giây, riêng trigger cascade của bảng comments 18,2 giây; thêm index FK còn 342 ms.

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

Khi nào PostgreSQL bỏ qua index dù có index?

Khi ước lượng lấy nhiều dòng (Seq/Bitmap rẻ hơn), khi cột bị bọc trong hàm hoặc ép kiểu, khi kiểu tham số không khớp (bigint = numeric), khi collation không hợp với LIKE, khi partial index không chứng minh được điều kiện (generic plan).

Bitmap Heap Scan khác Index Scan thế nào, sao lại có Recheck?

Bitmap gom vị trí rồi đọc trang theo thứ tự vật lý, mỗi trang một lần, mất thứ tự index. Recheck vì bitmap có thể lossy (chỉ nhớ trang) — khi đó phải kiểm lại điều kiện trên mọi dòng của trang.

Index Only Scan có đảm bảo không đọc bảng không?

Không. Phải hỏi visibility map; trang chưa all-visible thì đọc heap (Heap Fetches). Phụ thuộc VACUUM — và VACUUM có thể bỏ qua dọn index khi ít dòng chết (PG 14+).

Index ghép (a, b) dùng được cho WHERE b = ? không?

Từ PG 18 có thể, nhờ skip scan, nếu a ít giá trị (lab: Index Searches: 7). Bản cũ hơn thì gần như không. Thiết kế vẫn nên đặt cột hay lọc bằng = lên đầu.

Tại sao khoá ngoại thiếu index làm DELETE chậm mà EXPLAIN không thấy?

Kiểm tra FK chạy bằng trigger nội bộ, plan của nó không in ra; chỉ có dòng Trigger for constraint ... time=... ở cuối.

Muốn index cho whereDate trên timestamptz?

Không tạo được trên created_at::date (không IMMUTABLE). Viết lại thành khoảng; hoặc index trên (created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date và query viết đúng biểu thức đó.

Thêm index trên bảng production đang chạy thế nào?

CREATE INDEX CONCURRENTLY, ngoài transaction (Laravel: ->online() + $withinTransaction = false); theo dõi index INVALID nếu thất bại.

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

Môi trường: như bài 1 (PostgreSQL 18.4, database tmp_qplan, JIT tắt, cấu hình mặc định). Mọi index thử nghiệm tạo trong BEGIN … ROLLBACK; ví dụ 4 tạo và xoá index thật, có UPDATE + VACUUM.

cd labs/query-plan
./run.sh 02-*.sql                      # → out/02-*.txt
docker exec -i my-test-pgstandby psql -U postgres -d tmp_qplan \
  -c "SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'orders'"   # ví dụ 14, phía replica
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/migration-online.php        # ->online()

Chưa verify: hành vi skip scan và Index Searches trên PG 17 trở xuống (dựa vào release notes 18); ring buffer của Seq Scan (giải thích theo README buffer manager trong source PostgreSQL, lab chỉ thấy hệ quả read=); InnoDB tự tạo index cho FK (kiến thức chung, không dựng lab MySQL trong bài này).