Những khái niệm cơ bản về kế hoạch thực thi truy vấn: cost, rows, width

Khi bạn viết một truy vấn SQL, PostgreSQL không thực thi ngay đâu. Đầu tiên nó sẽ bật “bộ não” của mình — trình tối ưu hóa truy vấn, cái này sẽ tạo ra kế hoạch thực thi. Kế hoạch này giống như một lộ trình trên bản đồ: PostgreSQL tính toán xem cần làm gì và theo thứ tự nào để lấy được dữ liệu thành công.

Optimizer sẽ đánh giá mọi con đường có thể để thực thi truy vấn của bạn: quét tuần tự bảng, dùng index, lọc, sắp xếp v.v. Nó sẽ cố gắng tìm cách thực thi “rẻ” nhất (về tài nguyên). Tức là nó tìm sự cân bằng giữa thời gian chạy và tài nguyên server.

Các tham số chính của kế hoạch thực thi

Rồi, giờ đến phần “căng” nhất — phân tích các tham số mà PostgreSQL show ra khi bạn chạy lệnh EXPLAIN. Đầu tiên, lấy ví dụ đơn giản nhé:

EXPLAIN
SELECT * FROM students WHERE age > 20;

Bạn sẽ nhận được kết quả kiểu như này:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=72)
  Filter: (age > 20)

Giờ mình sẽ giải thích mấy từ và con số bí ẩn này.

1. cost (chi phí thực thi)

cost — là ước lượng về lượng tài nguyên cần để thực thi truy vấn. Tham số này gồm hai phần:

• Startup Cost: chi phí để bắt đầu thực hiện thao tác (ví dụ chuẩn bị index).
• Total Cost: tổng chi phí để hoàn thành toàn bộ thao tác.

Ví dụ:

cost=0.00..35.00

• 0.00 — là Startup Cost.
• 35.00 — là Total Cost.

Giá trị cost càng thấp thì kế hoạch càng được PostgreSQL ưu tiên. Nhưng nhớ là cost chỉ là giá trị tương đối thôi nhé. Nó không phải là giây hay mili giây, mà là đánh giá nội bộ của PostgreSQL.

2. rows (số dòng dự kiến)

rows cho biết PostgreSQL dự đoán sẽ trả về hoặc xử lý bao nhiêu dòng ở bước này của truy vấn. Trong ví dụ của mình:

rows=7

Có nghĩa là PostgreSQL nghĩ rằng filter age > 20 sẽ trả về 7 dòng. Dữ liệu này lấy từ thống kê mà PostgreSQL thu thập về bảng. Nếu thống kê cũ, dự đoán có thể sai, dẫn đến kế hoạch không tối ưu.

3. width (độ rộng dòng tính bằng byte)

width — là kích thước trung bình của mỗi dòng trả về ở bước này, tính bằng byte. Trong ví dụ:

width=72

Có nghĩa là mỗi dòng trả về trung bình chiếm 72 byte. width tính cả kích thước dữ liệu trong cột và các chi phí phụ như id dòng hoặc thông tin hệ thống.

Nó giống như khi bạn tải app về máy. Nếu app “nặng” (giống width lớn), bạn sẽ mất nhiều thời gian tải hơn, dù mạng có nhanh (giống cost thấp).

Ví dụ phân tích kế hoạch thực thi

Xem thử ví dụ thực tế nhé. Giả sử bạn có bảng students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    major VARCHAR(50)
);

Và bạn chạy truy vấn sau:

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Kết quả có thể như này:

Seq Scan on students  (cost=0.00..42.50 rows=3 width=164)
  Filter: ((age > 20) AND (major = 'CS'))

• Seq Scan: PostgreSQL thực hiện quét tuần tự bảng students. Tức là nó duyệt từng dòng một.
• cost=0.00..42.50: Chi phí thực thi thao tác. Startup Cost là 0.00, tổng chi phí là 42.50.
• rows=3: PostgreSQL dự đoán filter age > 20 AND major = 'CS' trả về 3 dòng.
• width=164: Mỗi dòng trung bình chiếm 164 byte.

Giờ bạn đã hiểu cách PostgreSQL quyết định, và có thể phát hiện điểm yếu trong truy vấn. Ví dụ, nếu thấy cost cao, có thể truy vấn quá nặng. Hoặc nếu rows lớn, nên xem lại filter của mình.

cost hoạt động thực tế như nào?

Thử thêm index cho cột age nhé:

CREATE INDEX idx_age ON students(age);

Giờ chạy lại truy vấn:

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Kết quả có thể thay đổi:

Bitmap Heap Scan on students  (cost=4.37..20.50 rows=3 width=164)
  Recheck Cond: (age > 20)
  Filter: (major = 'CS')
  ->  Bitmap Index Scan on idx_age  (cost=0.00..4.37 rows=20 width=0)
        Index Cond: (age > 20)

Có gì khác?

• Thay vì Seq Scan giờ là Bitmap Heap Scan: PostgreSQL sẽ tìm dòng phù hợp trong index idx_age trước, rồi lấy dữ liệu từ bảng.
• cost giảm đáng kể: Startup Cost là 4.37, Total Cost là 20.50.
• Nhờ index mà thao tác hiệu quả hơn.

Hình dung: sự khác biệt giữa Seq Scan và Index Scan

Bảng so sánh nhỏ cho dễ hình dung nhé:

Bẫy thường gặp và lỗi phổ biến

Khi dùng các tham số kế hoạch thực thi, hãy chuẩn bị cho vài bất ngờ nhé. Ví dụ, cost thấp chưa chắc đã chạy nhanh nhất đâu. Nếu thống kê database cũ (ví dụ sau khi update bảng hàng loạt), kế hoạch có thể không chuẩn. Hãy cập nhật thống kê bằng lệnh ANALYZE. Chi tiết về lệnh này sẽ có ở bài sau.

Nhớ dùng index đúng chỗ. Nhưng đừng lạm dụng index: chúng chiếm dung lượng và làm chậm thao tác ghi.