Một câu SQL chậm không tự giải thích nguyên nhân. Nó có thể đọc quá nhiều page, chọn sai kiểu join, spill xuống đĩa hoặc lặp một thao tác hàng nghìn lần. EXPLAIN của PostgreSQL phơi bày các quyết định ấy; EXPLAIN ANALYZE thực thi câu lệnh và đặt số đo cạnh ước lượng.
Plan là bằng chứng, không phải toa thuốc; sequential scan không mặc nhiên là lỗi. Kỹ năng cốt lõi là nối estimate error hoặc lần di chuyển dữ liệu tốn kém đầu tiên với schema, statistics, index hay SQL.
Bắt đầu từ một workload cụ thể
Xét một cơ sở dữ liệu thương mại điện tử multi-tenant. Báo cáo sau tìm 20 khách hàng có tổng doanh thu line item đã thanh toán cao nhất trong 30 ngày gần đây.
CREATE TABLE customers (
id bigint PRIMARY KEY,
tenant_id bigint NOT NULL,
name text NOT NULL,
UNIQUE (tenant_id, id)
);
CREATE TABLE orders (
id bigint PRIMARY KEY,
tenant_id bigint NOT NULL,
customer_id bigint NOT NULL,
status text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
created_at timestamptz NOT NULL,
FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id)
);
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES orders (id),
product_id bigint NOT NULL,
quantity integer NOT NULL CHECK (quantity > 0),
unit_price_cents integer NOT NULL CHECK (unit_price_cents >= 0),
PRIMARY KEY (order_id, product_id)
);
SELECT c.id,
c.name,
SUM(oi.quantity * oi.unit_price_cents) AS revenue_cents
FROM orders AS o
JOIN customers AS c
ON c.tenant_id = o.tenant_id
AND c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.tenant_id = 42
AND o.status = 'paid'
AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.id, c.name
ORDER BY revenue_cents DESC
LIMIT 20;
Hãy dùng EXPLAIN (ANALYZE, BUFFERS, SETTINGS) trong môi trường được kiểm soát. Trong cú pháp này, ANALYZE nghĩa là “thực thi và đo”, không phải “làm mới statistics của bảng”. Một kết quả đã rút gọn có thể như sau:
Limit (cost=18432.11..18432.16 rows=20 width=48)
(actual time=1841.802..1841.809 rows=20 loops=1)
Buffers: shared hit=94112 read=68744, temp read=1512 written=2634
-> Sort (cost=18432.11..18434.73 rows=1048 width=48)
(actual time=1841.800..1841.803 rows=20 loops=1)
Sort Key: (sum((oi.quantity * oi.unit_price_cents))) DESC
Sort Method: top-N heapsort Memory: 31kB
-> HashAggregate (cost=18393.95..18404.43 rows=1048 width=48)
(actual time=1782.401..1837.522 rows=18317 loops=1)
Batches: 5 Memory Usage: 8241kB Disk Usage: 12184kB
-> Hash Join (cost=7214.40..17798.31 rows=79419 width=24)
(actual time=113.051..1588.729 rows=642811 loops=1)
Hash Cond: (oi.order_id = o.id)
-> Seq Scan on order_items oi
(cost=0.00..8932.00 rows=1800000 width=20)
(actual time=0.018..512.346 rows=1800000 loops=1)
-> Hash
(cost=7188.15..7188.15 rows=2100 width=20)
(actual time=112.771..112.772 rows=48732 loops=1)
-> Seq Scan on orders o
(cost=0.00..7188.15 rows=2100 width=20)
(actual time=0.022..101.335 rows=48732 loops=1)
Filter: ((tenant_id = 42) AND (status = 'paid')
AND (created_at >= (CURRENT_DATE - '30 days'::interval)))
Rows Removed by Filter: 351268
Planning Time: 1.284 ms
Execution Time: 1844.126 ms
Customer join ít tốn kém đã được lược bỏ. PostgreSQL dự đoán 2.100 order nhưng gặp 48.732; sau đó dự đoán 79.419 line item nhưng tạo 642.811. Sai lệch làm méo các quyết định phía trên.
Warning
EXPLAIN ANALYZE thực sự chạy câu lệnh. Một SELECT vẫn có thể rất nặng hoặc giữ lock; một UPDATE, DELETE, hay function có side effect sẽ thay đổi dữ liệu. Đừng tùy tiện chạy nó trên production đang phục vụ traffic.
Đọc cây kế hoạch và các con số cho đúng
Plan là cây thụt lề. Đọc từ node sâu nhất đi lên vì node con tạo row cho node cha. Ví dụ scan hai bảng, join line item, aggregate theo customer, sort 20 group tốt nhất rồi Limit.
Mỗi metric trả lời một câu hỏi khác nhau:
| Trường | Ý nghĩa | Cách đọc sai thường gặp |
|---|---|---|
cost=a..b |
Startup cost và total cost ước lượng theo đơn vị planner | Xem cost là mili giây |
rows=n trước khi chạy |
Số row ước lượng được phát ra trong mỗi loop | Xem đó là số row đã scan hoặc loại bỏ |
actual time=a..b |
Thời gian đo đến row đầu và đến khi hoàn tất mỗi loop | Cộng thời gian bao hàm của cha và con |
rows=n loops=m |
Số row mỗi lần chạy và số lần node được chạy | Quên tổng output xấp xỉ |
Buffers |
Block shared/local/temp được hit, read, dirty hoặc write | Cho rằng cache hit không tốn gì |
Rows Removed by Filter |
Candidate bị loại tại node đó | Bỏ qua dấu hiệu filter diễn ra quá muộn |
Cost xếp hạng plan, không dự báo latency. Actual time của node cha bao gồm node con. Tìm nơi time, rows, buffers hoặc temp I/O tăng vọt thay vì cộng toàn cây.
Rows phải đi cùng loops. actual rows=13 loops=48732 nghĩa là khoảng 633.516 row tổng cộng. 0.020 ms mỗi loop vẫn đắt khi lặp hàng nghìn lần. Với parallel plan, xem chi tiết từng worker.
Tỷ lệ hữu ích nhất thường là độ chính xác cardinality:
Hệ số gần một là tốt. Hệ số 23 ở orders đáng chú ý vì xuất hiện thấp và lan lên trên. Với zero, hãy đánh giá theo quy mô dữ liệu thực tế.
Nhận diện scan, join, sort và aggregate
Tên node mô tả thuật toán, không phán quyết tốt xấu. Độ phù hợp tùy cardinality, thứ tự, selectivity, bộ nhớ và data locality.
| Nhóm node | Thường hiệu quả khi | Dấu hiệu cảnh báo |
|---|---|---|
| Sequential scan | Cần phần lớn bảng; row gọn | Nhiều row bị filter với predicate chọn lọc, lượng read cao |
| Index / index-only scan | Predicate chọn lọc hoặc thứ tự index bỏ được sort | Loop rất lớn, nhiều heap fetch, random access phần lớn bảng |
| Bitmap heap scan | Vài nghìn row nằm rải rác hoặc cần kết hợp index | Page lossy, nhiều recheck, kết quả chiếm gần cả bảng |
| Nested loop | Outer input nhỏ và inner lookup có index | Outer bị ước lượng thấp gây lặp inner quá nhiều |
| Hash join | Equality join và build side vừa bộ nhớ | Nhiều batch, temp I/O, build side lớn bất ngờ |
| Merge join | Hai input đã có thứ tự hoặc sort đủ rẻ | Phải sort lượng dữ liệu lớn trước khi join |
| Sort | Input vừa phải hoặc LIMIT cho phép top-N |
external merge, temp read/write lớn |
| Hash aggregate | Các group vừa bộ nhớ | Batches lớn hơn một và có disk usage |
| Group aggregate | Input đã có thứ tự group hữu ích | Sort đắt chỉ để tạo thứ tự ấy |
Index-only scan vẫn ghé heap nếu visibility map chưa đánh dấu page all-visible; update thường xuyên tạo nhiều Heap Fetches. Sequential scan cũng đúng khi một tenant chiếm nửa bảng, vì index scan sẽ gây nhiều heap access ngẫu nhiên.
Trong baseline, top-N heapsort chỉ giữ 20 ứng viên và dùng 31 kB nên không đáng lo. Aggregate có năm batch và 12 MB đĩa, tức đã spill. Full scan order_items cũng đắt vì chỉ cần item của 48.732 order.
Sửa giả định sai đầu tiên
Estimate sai có tác động đầu tiên nằm ở order đã filter. Tenant 42 vừa lớn vừa có tỷ lệ paid cao, correlation mà statistics đơn cột không mô tả hết. Sau khi kiểm tra autovacuum, hãy làm mới và mở rộng statistics:
ANALYZE orders;
CREATE STATISTICS orders_tenant_status_mcv (mcv, dependencies)
ON tenant_id, status
FROM orders;
ALTER TABLE orders ALTER COLUMN tenant_id SET STATISTICS 500;
ANALYZE orders;
Extended statistics mô hình hóa tổ hợp (42, 'paid'); target lớn hơn lấy mẫu skew tốt hơn. Statistics cải thiện quyết định nhưng không giảm giá truy cập, nên workload này cần index cho equality filter, range và join lookup:
CREATE INDEX CONCURRENTLY orders_paid_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (id, customer_id)
WHERE status = 'paid';
CREATE INDEX CONCURRENTLY order_items_order_id_cover_idx
ON order_items (order_id)
INCLUDE (quantity, unit_price_cents);
Equality tenant_id đứng trước range created_at; cột chỉ dùng sau tìm kiếm nằm trong INCLUDE. PostgreSQL dùng partial index khi chứng minh query suy ra status = 'paid'. Index thứ hai phục vụ item lookup và có thể index-only nếu visibility cho phép.
Sau khi tạo, phần trung tâm của plan mới có thể trông như sau:
HashAggregate (cost=36291.20..36462.01 rows=17081 width=48)
(actual time=381.410..407.885 rows=18317 loops=1)
Batches: 1 Memory Usage: 7185kB
-> Nested Loop (cost=0.85..31469.80 rows=642853 width=24)
(actual time=0.071..286.336 rows=642811 loops=1)
-> Index Only Scan using orders_paid_tenant_created_idx on orders o
(cost=0.43..2241.54 rows=47920 width=20)
(actual time=0.047..19.831 rows=48732 loops=1)
Index Cond: ((tenant_id = 42)
AND (created_at >= (CURRENT_DATE - '30 days'::interval)))
Heap Fetches: 126
-> Index Only Scan using order_items_order_id_cover_idx on order_items oi
(cost=0.42..0.48 rows=13 width=20)
(actual time=0.003..0.004 rows=13 loops=48732)
Index Cond: (order_id = o.id)
Heap Fetches: 811
Execution Time: 414.902 ms
Planner giờ dự đoán input khá sát. Nested loop hợp lý vì inner là index lookup hẹp. Runtime giảm từ 1,84 giây xuống 415 ms và aggregate hết spill. Kết quả này thuộc workload cụ thể, không chứng minh nested loop luôn tốt.
SQL cũng có thể loại việc thừa. Nếu join item chỉ để kiểm tra tồn tại rồi dùng DISTINCT xóa bản sao, hãy biểu đạt bằng semi-join:
-- Tránh: nhân row bằng join rồi lại loại bản sao.
SELECT DISTINCT o.id
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.tenant_id = $1 AND oi.product_id = $2;
-- Nên dùng khi chỉ cần biết có tồn tại hay không.
SELECT o.id
FROM orders AS o
WHERE o.tenant_id = $1
AND EXISTS (
SELECT 1
FROM order_items AS oi
WHERE oi.order_id = o.id AND oi.product_id = $2
);
Executor có thể dừng sau item khớp đầu tiên. Vẫn phải đo: SQL rõ nghĩa tạo cơ hội, không bảo đảm tăng tốc.
Tip
Hãy sửa cardinality error lớn xuất hiện sớm nhất và có khả năng đổi quyết định của plan. Tinh chỉnh sort ở node cha trong khi estimate input sai chỉ chữa triệu chứng; vấn đề ấy có thể tự biến mất khi estimate được sửa.
Tính đến statistics, spill và parameter
Cardinality khó ước lượng khi cột tương quan, giá trị skew, predicate dùng expression hoặc bảng đổi nhanh. Kiểm tra pg_stat_user_tables.last_analyze, row estimate và pg_stats. Extended statistics mô hình hóa dependency, distinct và common values; expression index có thể giúp lower(email) = $1.
Spill hiện qua external merge, hash có nhiều Batches, hoặc temp block trong BUFFERS. Đừng tăng work_mem toàn cục: nó nhân theo node, worker và query đồng thời. Ưu tiên giảm row, sửa estimate hoặc thêm thứ tự; nếu report vẫn cần RAM, dùng setting local và load test concurrency:
BEGIN;
SET LOCAL work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT /* report */ ...;
ROLLBACK;
Parameter tạo thêm khác biệt: tenant 7 có 40 order, tenant 42 có 48.732. PostgreSQL có thể đổi custom plan của prepared statement sang generic plan có average cost tốt nhưng chạy tệ cho outlier.
So EXECUTE report(7) với report(42) bằng EXPLAIN ANALYZE. force_custom_plan và force_generic_plan chỉ nên dùng để chẩn đoán. Giải pháp bền vững là statistics tốt, access path ổn trên phân phối, tách workload khác biệt hoặc cân nhắc cách application prepare statement.
Dùng vòng lặp tối ưu an toàn cho production
Tối ưu phải cải thiện workload, không chỉ một lần chạy đẹp. Quy trình an toàn gồm:
- Tìm query từ trace hoặc
pg_stat_statements; xếp hạng theo total time, tail latency, call count và ảnh hưởng. - Lưu SQL, phiên bản, setting, schema, row count và parameter đại diện; so warm/cold cache khi cần.
- Chạy
EXPLAINtrước. Chỉ dùngANALYZEtrên staging, replica phù hợp hoặc production có giới hạn và phê duyệt.TIMING OFFgiảm overhead với query rất nhanh. - Đọc từ dưới lên, đánh dấu estimate error, row explosion, buffer jump, loop hoặc spill đầu tiên rồi đặt một giả thuyết.
- Đổi một biến: statistics, một index hoặc SQL rewrite. Tạo production index bằng
CONCURRENTLYvà theo dõi I/O. - Chạy lại với parameter nhỏ, điển hình, xấu nhất; so time, buffers, temp I/O, planning, write overhead và plan stability.
- Deploy dần, quan sát p50/p95/p99 và load. Chỉ xóa index dư sau khi xét replica cùng job định kỳ.
Anti-pattern gồm ép tắt sequential scan, index mọi predicate, test một parameter, xem cost là latency, tăng work_mem toàn cục, bọc cột index trong function không có expression index, hoặc rewrite trước khi tìm node đắt. Planner toggle chỉ nên kiểm nghiệm giả thuyết, không đóng băng lựa chọn theo dữ liệu hôm nay.
Index tốn dung lượng, làm write và vacuum nặng hơn. Report 400 ms mỗi giờ chưa chắc đáng để làm chậm bảng cả ngày. Hãy tối ưu tổng workload trong giới hạn latency và correctness.
Note
Plan đổi sau upgrade, statistics refresh, tăng dữ liệu hoặc dịch parameter. Hãy lưu EXPLAIN (FORMAT JSON) đại diện, nhưng kiểm tra hành vi và latency budget thay vì so text từng node.
Những điều cần nhớ
- Đọc plan từ leaf đi lên và lần theo row khi mỗi node cha tiêu thụ output từ node con.
- Tách estimate khỏi measurement: cost không phải time, và
rowsphải được đọc cùngloops. - Tìm cardinality error, spill, row explosion hoặc repeated access có tác động đầu tiên thay vì kết luận mọi sequential scan đều xấu.
- Chọn index từ predicate, ordering, join và kinh tế workload; kiểm tra column order, partial predicate, visibility và write cost.
- Xem skew và parameter sensitivity là thuộc tính bình thường của production. Test giá trị nhỏ, điển hình và outlier.
- Tối ưu bằng vòng lặp kiểm soát được: quan sát, đặt giả thuyết, đổi một thứ, đo buffer và latency, rồi giám sát sau deploy.
Hãy đọc EXPLAIN như data-flow trace. Tìm nơi thông tin hoặc access path lệch thực tế, rồi cải thiện yếu tố nhỏ nhất có thể đổi kết quả.