PostgreSQL chọn cách chạy câu SQL như thế nào?

Bảng vehicles có index trên cột province. Cần tìm các xe ở Lai Châu:

SELECT *
FROM vehicles
WHERE province = 'Lai Châu';

Để tìm xe ở Lai Châu, PostgreSQL có thể đọc từng dòng trong bảng để kiểm tra. Vì có index trên province, nó cũng có thể dùng index để tìm những dòng cần đọc.

Hai cách đều trả về đúng kết quả. Nhưng cách nào tốn ít công sức hơn? Trước khi chạy câu SQL, PostgreSQL cần đưa ra lựa chọn đó.

Cùng một yêu cầu có thể có nhiều cách đọc dữ liệu.

Có nhiều cách đọc dữ liệu, PostgreSQL chọn cách nào?

Planner là gì?

Trước khi chạy câu SQL, PostgreSQL xem xét những cách thực hiện có thể dùng. Với câu tìm xe ở Lai Châu, nó có thể cân nhắc quét bảng hoặc tìm qua index.

Bộ phận thực hiện việc lựa chọn này gọi là bộ lập kế hoạch (query planner), thường gọi ngắn là planner.

Kết quả planner chọn ra gọi là kế hoạch thực thi (execution plan): bản mô tả PostgreSQL sẽ đọc và xử lý dữ liệu như thế nào.

Sau đó, PostgreSQL thực hiện kế hoạch đã chọn để trả về kết quả.

Planner chọn cách thực hiện; PostgreSQL chạy kế hoạch đó để lấy kết quả.

Planner không chạy thử từng cách để bấm giờ. Nó chọn kế hoạch trước khi thực hiện truy vấn. Vậy làm sao nó biết cách nào có thể ít tốn công hơn?

Cost là gì?

Cost là điểm chi phí ước tính mà PostgreSQL dùng để so sánh các cách thực hiện câu SQL.

Để chạy câu SQL, PostgreSQL phải làm việc: đọc các page của bảng hoặc index, kiểm tra từng dòng, tính điều kiện trong WHERE. Planner ước lượng lượng công việc đó cho mỗi kế hoạch và biểu diễn bằng một con số gọi là cost.

Giả sử quét bảng được ước lượng cost 100, còn dùng index được ước lượng cost 30. Planner sẽ chọn dùng index vì cost thấp hơn:

Planner so sánh cost ước tính của các kế hoạch.

Cost 30 không có nghĩa là truy vấn chạy trong 30 mili giây. Đây là điểm ước tính theo mô hình của PostgreSQL, dùng để so sánh các kế hoạch với nhau.

Vì chỉ là ước lượng, kế hoạch có cost thấp hơn cũng không phải lúc nào chạy nhanh hơn trong thực tế.

Planner ước lượng cost từ những gì?

Để ước lượng cost, planner cần hình dung mỗi cách đọc sẽ phải làm bao nhiêu việc. Chẳng hạn:

• Bảng và index lớn đến đâu?
• Dự kiến có bao nhiêu dòng khớp điều kiện?
• Cần đọc khoảng bao nhiêu page?
• Cần kiểm tra và xử lý bao nhiêu dòng?

Các ước lượng này được kết hợp với những hệ số chi phí của PostgreSQL để tạo ra cost. Đọc lần lượt nhiều page liền nhau và nhảy tới các page nằm rải rác là hai kiểu công việc khác nhau, nên PostgreSQL có hệ số riêng cho từng kiểu.

Xem kế hoạch bằng EXPLAIN

Đặt EXPLAIN trước câu SQL để xem kế hoạch planner đã chọn:

EXPLAIN
SELECT *
FROM vehicles
WHERE province = 'Lai Châu';

Trong môi trường thực hành, kết quả là:

Bitmap Heap Scan on vehicles  (cost=279.60..69167.29 rows=24667 width=267)
  Recheck Cond: (province = 'Lai Châu'::text)
  ->  Bitmap Index Scan on vehicles_province_idx  (cost=0.00..273.44 rows=24667 width=0)
        Index Cond: (province = 'Lai Châu'::text)

Planner chọn Bitmap Heap Scan: tìm qua index trước, rồi đọc các page của bảng chứa dòng khớp. Ở đây chỉ cần để ý ba thứ:

• Bitmap Heap Scan: tên bước trong kế hoạch được chọn.
• cost=279.60..69167.29: cost khởi đầu và tổng cost ước tính.
• rows=24667: số dòng bước này được ước tính sẽ trả về.

279.60 là cost khởi đầu: công việc ước tính trước khi bước này trả dòng đầu tiên.

69167.29 là tổng cost ước tính nếu bước này chạy đến hết.

Trong kết quả trên, Bitmap Index Scan là bước nằm bên dưới Bitmap Heap Scan. Cost 69167.29 của bước trên đã tính cả công việc của bước bên dưới, nên không cộng thêm 273.44 lần nữa.

Khi chạy trên môi trường khác, số cost, số dòng ước tính và cả kế hoạch được chọn có thể khác.

EXPLAIN chỉ cho xem kế hoạch được chọn, không liệt kê mọi kế hoạch planner đã cân nhắc. Nó cũng không chạy câu SQL, nên không có thời gian đo. EXPLAIN ANALYZE thì chạy thật câu SQL và in thêm số liệu thực tế.

Muốn so sánh với cách quét bảng, ta có thể tạm thời yêu cầu planner tránh hai cách đọc qua index trong ví dụ này:

SET enable_bitmapscan = off;
SET enable_indexscan = off;

EXPLAIN
SELECT *
FROM vehicles
WHERE province = 'Lai Châu';

Trong môi trường thực hành, lần này planner chọn quét bảng song song, với tổng cost khoảng 221.816. Con số này cao hơn cost 69.167 của kế hoạch được chọn ban đầu.

Sau khi so sánh, trả các thiết lập về mặc định:

RESET enable_bitmapscan;
RESET enable_indexscan;

Planner so sánh cost ước tính của các cách thực hiện và chọn kế hoạch mà nó cho là phù hợp nhất.

Planner lấy số dòng ở đâu?

Để ước lượng cost, planner cần biết có thể có bao nhiêu dòng khớp, như con số rows=24667 ở trên. Nhưng trước khi chạy câu SQL, nó lấy con số đó ở đâu?

Trong môi trường thực hành, đổi 'Lai Châu' thành 'TP.HCM' rồi chạy lại EXPLAIN, planner chọn Seq Scan, dù index trên province vẫn còn nguyên.

Cùng một index, vì sao tìm tỉnh này dùng index còn tỉnh khác lại quét bảng?