EXPLAIN là công cụ quan trọng nhất khi tối ưu truy vấn, và nó in ra một khối chữ mà phần lớn người dùng chỉ nhìn dòng đầu. Bài này mổ từng con số, trên bảng 1.000.000 đơn hàng và 100.000 khách hàng.

Mổ một dòng EXPLAIN ANALYZE

Một dòng, hai thế giới

Seq Scan on dh  (cost=0.00..17906.00 rows=199181 width=16)
                (actual time=0.113..32.068 rows=199800 loops=1)

Cụm ngoặc đầu là bộ tối ưu đoán trước khi chạy. Cụm sau là điều đã xảy ra. EXPLAIN trần chỉ cho cụm đầu; muốn cụm sau phải dùng EXPLAIN ANALYZE — và lưu ý nó thật sự chạy truy vấn, nên với UPDATE hay DELETE hãy bọc trong giao dịch rồi ROLLBACK.

Phần Nghĩa
cost=0.00..17906.00 chi phí ước lượng: trước dòng đầu tiên .. tới dòng cuối
rows=199181 số dòng dự đoán
width=16 bề rộng trung bình một dòng, tính bằng byte
actual time=0.113..32.068 thời gian thật, mili giây, mỗi vòng lặp
rows=199800 số dòng thật
loops=1 nút này chạy bao nhiêu lần

Ở đây dự đoán 199.181 so với thực tế 199.800 — lệch 0,3%, thống kê đang tốt.

Đơn vị của cost không phải mili giây. Nó là một con số quy ước, mặc định lấy "đọc tuần tự một trang" làm 1,0. Nó chỉ dùng để so sánh giữa các kế hoạch, không dùng để đoán thời gian.

Ba con số hay bị đọc sai

loops nhân lên mọi thứ

Nested Loop                        (actual time=0.021..0.194 rows=490 loops=1)
  ->  Index Scan on kh k           (actual time=0.011..0.013 rows=49  loops=1)
  ->  Index Scan on dh d           (actual time=0.001..0.003 rows=10  loops=49)

Nút trong cùng ghi rows=10 loops=49. Đó không phải 10 dòng — đó là 10 dòng mỗi lần, và nó chạy 49 lần. Tổng thật là 490, khớp với rows=490 của nút cha.

Thời gian cũng vậy: 0.003 ms là mỗi vòng, tổng khoảng 0.003 × 49 ≈ 0.15 ms.

Đây là chỗ người ta hay kết luận nhầm "nút này chỉ tốn 0,003 ms nên không phải vấn đề", trong khi loops có thể là hàng chục nghìn.

cost=X..Y — số đầu là chi phí trước dòng đầu tiên

EXPLAIN SELECT * FROM dh ORDER BY tong;
--  Sort  (cost=132154.34..134654.34 rows=1000000)

Chi phí khởi động 132.154 gần bằng tổng chi phí, vì Sort phải xếp xong toàn bộ mới trả được dòng nào.

Thêm LIMIT 10 và bộ tối ưu đổi hẳn kế hoạch:

Limit  (cost=19576.71..19577.88 rows=10)
  ->  Gather Merge  (cost=19576.71..116805.80 rows=833334)

Chi phí khởi động rơi từ 132.154 xuống 19.576. Đây là lý do một truy vấn "nhanh khi có LIMIT, chậm khi bỏ LIMIT" — không phải vì nó trả ít dòng hơn, mà vì kế hoạch khác hẳn.

Rows Removed by Filter là công sức bỏ đi

Parallel Seq Scan on dh  (actual rows=67 loops=3)
  Rows Removed by Filter: 333267

Để lấy 67 dòng, PostgreSQL đọc lên và vứt đi 333.267 dòng mỗi worker. Đây là dấu hiệu rõ nhất của việc thiếu chỉ mục: tỉ lệ giữ lại trên đọc vào càng nhỏ, chỉ mục càng đáng thêm.

Đọc cây theo thứ tự nào

Kế hoạch in ra dạng cây, và đọc từ trong ra ngoài, từ dưới lên:

Limit
  ->  Sort
        Sort Key: (count(*)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Finalize GroupAggregate
              ->  Gather Merge
                    Workers Planned: 2
                    Workers Launched: 2
                    ->  Sort
                          ->  Partial HashAggregate

Nút sâu nhất chạy trước. Dữ liệu chảy từ dưới lên, mỗi tầng biến đổi rồi đưa lên tầng trên.

Vài dòng phụ đáng đọc:

  • Sort Method: top-N heapsort Memory: 25kB — sắp xếp trong bộ nhớ. Nếu thấy external merge Disk: ... thì work_mem không đủ và nó đang tràn ra đĩa.
  • Workers Planned: 2 / Workers Launched: 2 — hai con số này lệch nhau nghĩa là hệ thống hết worker rảnh, và truy vấn chạy chậm hơn kế hoạch.

BUFFERS cho biết dữ liệu đến từ đâu

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

Buffers: shared hit=1 read=19
Buffers: shared hit=99

hit là trang lấy từ bộ nhớ đệm, read là trang phải đọc từ hệ thống tệp. Câu thứ hai chạy toàn bộ trong bộ nhớ đệm.

Đây là con số đáng tin hơn thời gian khi so hai truy vấn: thời gian phụ thuộc bộ đệm đang nóng hay lạnh, còn số trang phải đụng tới thì ổn định. Tôi khuyên luôn bật BUFFERS — từ PostgreSQL 18 nó bật sẵn cùng ANALYZE.

Khi ước lượng sai, mọi thứ sai theo

Một bảng mà hai cột luôn bằng nhau:

CREATE TABLE lech AS SELECT i, (i%100) a, (i%100) b FROM generate_series(1,500000) i;
SELECT count(*) FROM lech WHERE a = 7 AND b = 7;
Dự đoán Thực tế Lệch
Mặc định 30 dòng 2.500 83 lần

Bộ tối ưu tính xác suất a=7 là 1/100, b=7 là 1/100, rồi nhân chúng thành 1/10.000 — vì nó mặc định coi các cột độc lập. Ở đây chúng không độc lập chút nào.

Ước lượng lệch 83 lần dẫn tới chọn sai kế hoạch: nó tưởng chỉ có 30 dòng nên có thể chọn Nested Loop, trong khi 2.500 dòng thì Hash Join hợp hơn.

Cách sửa là khai cho PostgreSQL biết hai cột có liên quan:

CREATE STATISTICS st_lech (dependencies, ndistinct) ON a, b FROM lech;
ANALYZE lech;
Dự đoán Thực tế Lệch
Sau CREATE STATISTICS 2.882 dòng 2.500 15%

Từ 83 lần xuống 15%. Đây là công cụ ít được dùng nhưng rất hiệu quả cho các cột có quan hệ tự nhiên — tỉnh và mã vùng, danh mục và danh mục con, quốc gia và tiền tệ.

Quy trình đọc một kế hoạch chậm

  1. So rows dự đoán với rows thật. Lệch trên một bậc là vấn đề thống kê, không phải vấn đề chỉ mục.
  2. Tìm nút có actual time lớn nhất, nhớ nhân với loops.
  3. Xem Rows Removed by Filter. Đọc nhiều bỏ nhiều là thiếu chỉ mục.
  4. Xem Sort Method. Có chữ Disk là thiếu work_mem.
  5. Xem Buffers. read cao nghĩa là dữ liệu không nằm trong bộ đệm.

Thử ba mươi giây

Lấy kế hoạch đầy đủ của truy vấn chậm nhất trong hệ thống bạn:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) <truy van cua ban>;

SETTINGS in ra những tham số đã bị đổi khác mặc định — rất hữu ích khi kế hoạch trên máy chủ khác trên máy bạn.

Và tìm nhanh nút có ước lượng tệ nhất:

EXPLAIN (ANALYZE, FORMAT JSON) <truy van>;

Rồi so Plan Rows với Actual Rows ở từng nút. Nút đầu tiên lệch trên mười lần thường là nguyên nhân của mọi thứ bên trên nó.

Phần sau đo EXPLAIN ANALYZE sâu hơn: chỗ ước lượng sai kéo theo chọn nhầm thuật toán join.