Ở bài 3 ta gặp những con số cost=... và mình nói đó là "đơn vị trừu tượng để so sánh". Giờ mở hộp đen ra: con số đó không ngẫu nhiên — nó là kết quả của một công thức đơn giản mà bạn có thể tự tính lại. Hiểu cost model giúp bạn đoán được vì sao planner chọn kế hoạch này thay vì kế hoạch kia, và vì sao chỉnh vài tham số cấu hình có thể lật ngược quyết định đó.

Năm tham số, quy mọi thứ về một thang

Planner không đo mili-giây (nó chưa chạy gì cả). Thay vào đó nó ước lượng khối lượng công việc theo một thang tương đối, với seq_page_cost = 1 làm gốc:

Ảnh chụp mã nền tối liệt kê tham số cost model và công thức. Các tham số chi phí thật đơn vị tương đối gốc quy về seq_page_cost bằng 1. seq_page_cost bằng 1.0 đọc 1 trang tuần tự. random_page_cost bằng 4.0 đọc 1 trang ngẫu nhiên đắt gấp 4. cpu_tuple_cost bằng 0.01 xử lý 1 dòng. cpu_index_tuple_cost bằng 0.005 xử lý 1 mục index. cpu_operator_cost bằng 0.0025 một phép toán hoặc so sánh. Công thức chi phí một Seq Scan quét cả bảng bằng số trang nhân seq_page_cost cộng số dòng nhân cpu_tuple_cost. Với pgbench_accounts số trang bằng 82932 từ pg_relation_size chia 8192, số dòng bằng 5058803 là reltuples rescale theo kích thước hiện tại, cost bằng 82932 nhân 1 cộng 5058803 nhân 0.01 bằng 133520.03

Hình 1: Năm tham số chi phí (giá trị mặc định thật). Điểm cốt: đọc trang ngẫu nhiên đắt gấp 4 lần đọc tuần tự (random_page_cost 4 vs seq_page_cost 1) — đây là lý do planner ngại index scan trên nhiều dòng. Công thức chi phí một Seq Scan quét cả bảng rất gọn: số_trang × seq_page_cost + số_dòng × cpu_tuple_cost.

Tự tay tính lại — và khớp đến từng số lẻ

Lý thuyết là vậy, giờ kiểm chứng. Lấy pgbench_accounts (5 triệu dòng):

Ảnh chụp kết quả nền tối kiểm công thức và planner chọn cost thấp. Phần một kiểm công thức trên Seq Scan cả bảng. EXPLAIN SELECT sao FROM pgbench_accounts cho Seq Scan on pgbench_accounts cost 0.00 tới 133520.03 rows 5058803. Tính tay 82932 nhân 1 cộng 5058803 nhân 0.01 bằng 133520.03 màu xanh khớp chính xác. Phần hai cùng truy vấn aid nhỏ hơn bằng 500000 khoảng 10 phần trăm planner so 2 lựa chọn. SELECT sao FROM pgbench_accounts WHERE aid nhỏ hơn bằng 500000. Index Scan using pkey cost 0.43 tới 21920.58 màu xanh planner chọn vì rẻ. Seq Scan ép chạy thử cost 0.00 tới 146167.04 màu đỏ đắt gấp khoảng 6,7 lần. Chú thích planner tính cost cho mọi cách rồi chọn thấp nhất, đổi tham số cost ví dụ random_page_cost đổi con số có thể đổi luôn cách nó chọn

Hình 2: Thật, và khớp chính xác. EXPLAIN cho Seq Scan cost 133520.03. Tính tay: bảng có 82.932 trang (pg_relation_size/8192) và planner ước 5.058.803 dòng (rescale reltuples theo kích thước hiện tại). 82932 × 1 + 5058803 × 0.01 = 133520.03 — đúng đến hai số lẻ. Con số không phải phép thuật; nó là số học.

Planner luôn chọn cost thấp nhất

Đây là toàn bộ triết lý của planner: với mỗi truy vấn, nó liệt kê các cách thực thi, tính cost cho từng cách, và chọn cái thấp nhất. Nhìn phần ② của Hình 2 — cùng truy vấn aid <= 500000 (~10% bảng):

  • Index Scan: cost 21920.58 — planner chọn cái này.
  • Seq Scan (mình ép chạy thử bằng enable_indexscan=off): cost 146167.04 — đắt gấp ~6,7 lần.

Với 10% dữ liệu và index trên khóa chính (dòng nằm gần nhau vật lý), index scan rẻ hơn nhiều nên thắng. Nhưng nếu truy vấn lấy 60% bảng, chi phí đọc ngẫu nhiên qua index sẽ vượt chi phí quét tuần tự, và cán cân lật sang Seq Scan.

Vì sao điều này hữu ích khi tối ưu

  • Đoán được quyết định của planner. Biết công thức, bạn hiểu vì sao một truy vấn lấy nhiều dòng lại nên dùng Seq Scan (đọc tuần tự rẻ), còn lấy ít dòng thì Index Scan thắng.
  • Giải thích "vì sao không dùng index của tôi". Thường không phải index hỏng, mà là planner tính ra Seq Scan rẻ hơn cho truy vấn đó — và nó thường đúng.
  • Chỉnh cost cho đúng phần cứng. random_page_cost = 4 là giá trị cho đĩa quay (HDD), nơi seek ngẫu nhiên đắt thật. Trên SSD, seek ngẫu nhiên gần như rẻ ngang tuần tự — nên hạ random_page_cost xuống ~1.1 để planner mạnh dạn dùng index hơn. Đây là một trong những chỉnh config đáng giá nhất, ta sẽ đo kỹ ở bài về cấu hình I/O.

Vài lưu ý

  • cost chỉ để so sánh, không phải thời gian. Cost thấp hơn không đảm bảo nhanh hơn tuyệt đối — chỉ nghĩa là planner tin nó rẻ hơn. Khi ước lượng dòng sai (bài 4), cost cũng sai theo.
  • Startup cost quan trọng với LIMIT. cost=startup..total; với LIMIT nhỏ, planner ưu tiên kế hoạch có startup thấp (trả dòng đầu nhanh) dù total cao hơn.
  • Đừng chỉnh cost params một cách bừa bãi. Chúng ảnh hưởng mọi truy vấn. random_page_cost cho SSD là chỉnh hợp lý; vặn lung tung các tham số khác thì dễ làm hại nhiều hơn lợi.

Ba ý mang về

  1. Cost là số học, không phải phép thuật. Seq Scan = số_trang × seq_page_cost + số_dòng × cpu_tuple_cost; đã tính tay ra đúng 133520.03 như EXPLAIN.
  2. Planner tính cost cho mọi phương án và chọn thấp nhất — đã thấy Index Scan (21.920) thắng Seq Scan (146.167) cho truy vấn 10% dữ liệu.
  3. random_page_cost = 4 là cho HDD; SSD nên hạ xuống ~1.1 để planner dùng index nhiều hơn — một chỉnh config đáng giá, nhưng cost chỉ để so sánh và phụ thuộc ước lượng dòng có đúng không.

Ta đã biết cách planner chọn và cách đọc một truy vấn. Nhưng trong hàng nghìn truy vấn của một hệ thống thật, tối ưu cái nào trước? Phần sau giới thiệu pg_stat_statements — extension xếp hạng mọi truy vấn theo tổng thời gian, để bạn đổ công sức vào đúng truy vấn thực sự tốn kém, không phải cái trông có vẻ chậm.