Query planner và hiệu năng truy vấn

Bài toán

Database quan hệ không chạy SQL theo đúng cách nó được viết. Query planner chọn một plan (dùng index nào, join kiểu gì: nested loop, merge, hash; dừng sớm khi có LIMIT hay không) dựa trên thống kê về dữ liệu: số dòng, tỉ lệ null, số giá trị khác nhau (n_distinct), giá trị phổ biến, histogram. Thống kê được tính bằng cách lấy mẫu và chỉ là ước lượng.

Hệ quả cho hệ đang lớn:

• Một query nhanh hôm qua có thể chậm cả nghìn lần hôm nay mà không đổi code, chỉ vì dữ liệu lớn lên hoặc thống kê được tính lại.
• Plan sai không chỉ làm chậm một query: nó quét hàng chục GB, đẩy dữ liệu nóng khỏi cache, kéo chậm cả DB.
• Có index chưa đủ: planner phải tin rằng index đó rẻ.
• Index không miễn phí: mọi insert trả tiền cho mọi index (một lần đi cây, một bản ghi WAL), còn chỉ những query dùng nó mới hưởng lợi.

Trang này nói về plan, thống kê và chi phí của index trong một DB, kể cả cách tìm query đang gây hại ở production. Chia dữ liệu ra nhiều DB ở Sharding và tách database; chọn loại DB ở Chọn database theo workload.

Diễn tiến theo thời gian

• 2018 · DoorDash: Query nặng nhất trên staging theo Performance Insights cần instance lớn gấp 1.000 lần; trên Aurora, I/O bất ngờ có thể làm replica tự khởi động lại. statement_timeout thấp chặn query xấu trước khi bão hoà CPU/I/O. DoorDash 2018
• 2019 · GoCardless: DB Postgres khoảng 2 TB, bảng payment_transitions gần nửa tỉ dòng. Query WHERE payout_id = … ORDER BY id LIMIT 1 đột ngột chậm vì n_distinct của payout_id bị ước 25.650 thay vì khoảng 3,5 triệu; planner tưởng mỗi payout có khoảng 2.600 dòng nên chọn quét primary key "để dừng sớm", thực tế quét khoảng 17,5 triệu dòng. Nguyên nhân gốc: ANALYZE lấy mẫu theo page, mà các dòng cùng payout nằm cạnh nhau trên đĩa, nên mẫu bị lệch; phải tăng statistics target của cột lên 5.000 mới ổn định. GoCardless 2019
• 2020 · LinkedIn: LIquid: planner tĩnh theo thống kê cấp bảng thua self-join trên dữ liệu skew; planner động thoả ràng buộc rẻ nhất trước, dựa trên kích thước tập đọc được trong 1 L3 miss; tránh tích Descartes giả bằng trả subgraph. LinkedIn 2020
• 2021 · Zerodha: chỉnh work_mem (khoảng 600 MB vì sort/JOIN lớn) và shared_buffers dựa trên EXPLAIN ANALYZE; theo Zerodha, phần lớn vấn đề của RDBMS là do index và vacuum kém, chưa phải lúc chia shard. Zerodha 2021
• 2021 · GitHub: khi tách bảng MySQL ra cụm riêng phải gỡ JOIN; join ở phía app đôi khi lại nhanh hơn vì planner của MySQL có lúc chọn plan tệ. GitHub 2021
• 2024 · Render: query join hai bảng (100 nghìn và 90 triệu dòng) theo data ->> 'serviceId' trong cột JSON chạy hàng giờ ở staging. Merge join phải đi qua 87% index trước khi gặp dòng join được; bỏ LIMIT thì planner đổi sang hash join + quét toàn bảng. Nguyên nhân: thống kê được tính trên cả object JSON, không trên biểu thức. CREATE STATISTICS trên biểu thức làm query gốc chạy dưới 1 ms bằng nested loop. Render 2024
• 2024 · Render (phần 1): trước khi biết vì sao query chậm, phải tìm ra nó: deploy staging tắc vì migration CREATE OR REPLACE VIEW chờ khoá sau một query ad-hoc chạy hơn 4 giờ từ psql. Công cụ: biểu đồ tuổi transaction mở lâu nhất, pg_stat_activity (query active quá 5 phút, wait event cho thấy query đang đọc đĩa chứ không bị chặn), pg_blocking_pids(), cột application_name/usename để biết ai chạy. Render 2024
• 2026 · Neon: trong đợt sửa độ tin cậy sau hai sự cố, DB Postgres của control plane phải sửa các lần query plan bị thoái hoá, chặn tái diễn, và tắt parallel query vì gây lỗi plan. Neon 2026
• 2026 · Tiger Data: mặt ghi của index (theo nhà cung cấp, Postgres 16 mặc định): bảng 3 index sinh 4 bản ghi WAL mỗi dòng, khoảng 345 byte WAL cho dòng 40 byte; bỏ index thì tỉ lệ từ 8,7 xuống 2,4 lần, tức khoảng 70% WAL là bảo trì index. Full page image sau checkpoint làm WAL vọt lên. Cách giảm theo rủi ro: COPY/gom lô, bỏ index idx_scan gần 0, wal_compression, giãn checkpoint. Tiger Data 2026
• 2026 · Tiger Data: quyết định có giữ một composite index hay không: đo lợi ích bằng số buffer trong EXPLAIN (ANALYZE, BUFFERS) (254.781 → 506), đo chi phí theo cột đầu: (tag_id, ts) với 10.000 tag rải chèn khắp cây, dựng bằng insert chỉ đầy 57,6% (236 MB so với 150 MB nếu dựng một lần), thêm WAL; pgbench trên bảng 40 triệu dòng giảm trần ingest 7,8% (bảng nhỏ chỉ thấy 4,7% vì index vừa cache). So với đỉnh ingest, không phải trung bình. Index không khớp cột đầu vẫn có thể được planner chọn mà gần như không giúp. Tiger Data 2026
• 2026 · ClickHouse: (nhà cung cấp) primary key (ServiceName, Timestamp) chặn tối ưu read_in_order (dừng sớm Top N); bucket 1 phút phân mảnh dữ liệu làm chậm search dài; chọn toStartOfFiveMinutes(Timestamp) đứng đầu. Text index cho direct_read nhưng làm chậm tra một dòng log, bù bằng _block_number/_block_offset. Mỗi thay đổi đo ingest trước rồi mới đo query, luôn chạy cả workload để bắt hồi quy. ClickHouse 2026
• 2026 · Snowflake (nhà cung cấp): Optima Planning băm từng plan fragment/operator trên kết quả của optimizer để truy vấn mới mượn số dòng thật từ truy vấn khác có cùng mảnh plan; có chặn khi ngữ cảnh khác. Truy vấn 19 join có predicate tương quan từ gần hai giờ xuống 30 giây ngay lần chạy đầu (theo Snowflake). Snowflake 2026
• 2026 · Tiger Data (nhà cung cấp): sizing bảng IIoT trước khi deploy: 20 byte dữ liệu thành 52,2 byte heap và 95,6 byte với một index được lấp bởi ingest (page split, lá đầy khoảng 65%); đo trần ghi bằng pgbench trên bảng đã nạp (8.719/giây so với 10.000 cần). Tiger Data 2026
• 2026 · Elastic, Qdrant (nhà cung cấp): query vector có filter cũng là bài toán planner. Elastic ước tính độ chọn lọc từ cost() của filter, chọn lọc sau khi ≥ 0,7, lấy dư theo mô hình nhị thức, lùi về lọc trước nếu thiếu. Qdrant ước tính số điểm qua filter để chọn giữa đồ thị, ACORN và payload index. Elastic 2026 Qdrant 2026
• 2026 · Uber: cắt file bằng min/max chỉ hiệu quả khi bố cục vật lý gom khoá (sắp xếp theo cột predicate); thống kê trong footer Parquet không đủ vì đọc footer cũng là đụng file; secondary index không giúp vì bản ghi của một người rải trên hầu hết file, và nặng hơn thống kê cột. Uber 2026

Các lựa chọn và đánh đổi

Quy tắc rút ra

1. Query chậm mà cấu trúc đơn giản, có index, có LIMIT: nghi thống kê trước, không nghi index. Render 2024 GoCardless 2019
2. ORDER BY … LIMIT là chỗ planner dễ đánh cược sai nhất: plan "dừng sớm" được giảm chi phí theo số dòng ước tính; ước sai một bậc là chọn quét cả bảng. Thử cả phiên bản có và không có LIMIT. GoCardless 2019 Render 2024
3. Giả định "phân bố đều" của planner gãy khi dữ liệu tương quan với thứ tự vật lý hoặc thứ tự index (ID tăng dần, ghi theo lô, tiền tố theo loại). GoCardless 2019 Render 2024
4. Index trên biểu thức (JSON, hàm) không tự kéo theo thống kê trên biểu thức đó; phải tạo riêng. Render 2024
5. Biết hình dạng dữ liệu bằng con số (đếm, phân bố) trước khi đọc plan; vẽ tay cách join chạy. Render 2024 GoCardless 2019
6. Vá query cho chạy là bước tạm; tìm vì sao planner sai là bước sửa. Render 2024
7. Thống kê là trạng thái vận hành: nâng major version Postgres xoá thống kê, nên thời gian ANALYZE nằm trong thời gian downtime của upgrade. GoCardless 2019
8. Tinh chỉnh dựa trên EXPLAIN ANALYZE và vệ sinh index/vacuum đi trước chuyện chia shard. Zerodha 2021
9. Với DB làm nền cho hệ khác (control plane), plan thoái hoá là một loại sự cố; cần chặn tái diễn, không chỉ sửa lần đó. Neon 2026
10. Tìm query đang gây hại trước khi tối ưu: tuổi transaction mở lâu nhất trên dashboard, pg_stat_activity + wait event (đang làm việc hay đang chờ), pg_blocking_pids() (ai chặn ai), application_name (ai chạy). Migration treo không có lỗi thường là chờ khoá. Render 2024
11. Index là thuế ghi trên mọi dòng: đo bằng WAL (pg_stat_wal, pg_current_wal_lsn()), không đoán. Bảng nhiều index có thể có phần lớn WAL là bảo trì index. Tiger Data 2026
12. Câu hỏi về index là "nó có rải ghi ra không": cột đầu cardinality cao làm page split nhiều, cây trống một nửa, nhiều full page image. Benchmark ghi trên bảng nhỏ nói dối vì index nằm gọn trong cache. Tiger Data 2026
13. Quyết định index theo trần ingest so với đỉnh tải, không theo mili giây tiết kiệm; với index đang có, idx_scan gần 0 qua đủ một chu kỳ là ứng viên xoá. Tiger Data 2026
14. Feedback thực thi khoá theo cấu trúc plan (không theo chữ SQL) dùng lại được giữa các truy vấn khác nhau, giúp cả lần chạy đầu; cần cửa chặn khi ngữ cảnh khác (theo Snowflake 2026). Snowflake 2026
15. Sizing trước khi deploy: đo byte mỗi dòng và trần ghi trên bảng đã nạp và có đủ index sẽ ship; index lấp bởi ingest phân tán lớn hơn index dựng một lượt (theo Tiger Data 2026). Tiger Data 2026
16. Ước tính thô vẫn dùng được nếu kế hoạch dựa trên nó có đường lùi đúng đắn: ước tính sai chỉ tốn latency, không làm sai kết quả (Elastic 2026 lùi về pre-filter); và ước tính toàn cục có thể sai cục bộ khi điều kiện tương quan với vùng dữ liệu được truy vấn. Elastic 2026 Qdrant 2026

Câu hỏi mở

• Chưa có nguồn kể cách phát hiện sớm plan thoái hoá ở production (theo dõi plan theo thời gian, auto_explain, pg_stat_statements, so sánh plan sau ANALYZE) hay cách ghim plan (plan hint, pg_hint_plan).
• Render 2024 cho rằng Ctrl+C trong psql chỉ dừng phía client còn backend vẫn chạy; nhận định này cần kiểm chứng (psql có cơ chế gửi yêu cầu huỷ, có thể bị chặn bởi proxy hay pooler ở giữa). Chưa có nguồn thứ hai nói về chuyện này.
• Hai bài Tiger Data 2026 là của nhà cung cấp, đo trên một máy 7 GB; chưa có nguồn production kể chi phí ghi của index ở quy mô lớn (replica lag, backup) trước/sau khi dọn index.
• Các nguồn đều là Postgres (MySQL chỉ có một câu ở GitHub 2021); chưa có nguồn đối chiếu planner của MySQL, hay của DB phân tán (CockroachDB, Vitess) khi phải chọn plan xuyên shard.
• GoCardless 2019 gợi ý partition để planner ít chọn sai nhưng không kể đã làm hay chưa.
• Liên hệ: Vector search và index ANN (planner cho query vector có filter), Sharding và tách database (tách DB như lựa chọn sau cùng), Migration không downtime (upgrade major và thống kê), Sự cố và postmortem (plan thoái hoá là nguyên nhân sự cố), Chọn database theo workload, Failover, replica và dự phòng dữ liệu (lượng WAL do index sinh ra quyết định replica lag).