Subquery, CTE và hàng rào tối ưu

Trong nhiều năm, WITH của PostgreSQL có một đặc tính mà không chuẩn SQL nào đòi hỏi: nó là hàng rào tối ưu. Điều kiện lọc bên ngoài không được đẩy vào trong. PostgreSQL 12 đổi chuyện đó. Phần này dựng cả hai phiên bản với dữ liệu giống hệt nhau để đo chính xác khác biệt.

Bố trí

Hai container, PostgreSQL 11.22 và 16, mỗi bên một bảng 2.000.000 dòng đúng 100 MB, cùng chỉ mục, cùng đã ANALYZE:

create table dh2(id bigserial primary key, kh_id int, tien numeric(12,2), tt text);
create index dh2_kh on dh2(kh_id);

Truy vấn:

with t as (select * from dh2)
select count(*) from t where kh_id = 500;

Kết quả

Nhanh gấp 3.000 lần, đọc ít hơn 4.777 lần.

Trên PostgreSQL 11, CTE Scan nghĩa là toàn bộ 2 triệu dòng được vật chất hoá vào một bảng tạm trong bộ nhớ trước, rồi mới lọc kh_id = 500 trên kết quả đó. Chỉ mục dh2_kh nằm im — điều kiện lọc không có đường nào chui vào trong CTE.

Trên PostgreSQL 16, CTE được nội tuyến hoá: câu lệnh thực chất trở thành select count(*) from dh2 where kh_id = 500, và bộ tối ưu dùng chỉ mục như bình thường.

Điều đáng nói là cách chữa luôn có sẵn trên PostgreSQL 11:

select count(*) from (select * from dh2) t where kh_id = 500;
--  PostgreSQL 11: 0,10 ms

Truy vấn con không phải hàng rào. Chỉ cần đổi WITH thành truy vấn con là xuống 0,10 ms. Nhưng bạn phải biết điều đó, và không có gì trong thông báo lỗi hay kế hoạch nói cho bạn — chỉ có CTE Scan nếu bạn biết nó có nghĩa gì.

Đây là lý do lời khuyên "dùng CTE cho dễ đọc" từng là lời khuyên tệ trên PostgreSQL cũ, trong khi trên các CSDL khác nó luôn vô hại.

PostgreSQL 12 trở đi: bạn tự chọn

Từ phiên bản 12, mặc định là nội tuyến, và có hai từ khoá để ghi đè:

Chú ý số trang: 38.217 ở cả hai dòng. MATERIALIZED không phải gần giống hành vi cũ — nó là đúng hành vi cũ, cùng số trang tới từng đơn vị.

Từ khoá đặt sau AS, không nằm trong ngoặc. Tôi viết with t as (materialized select ...) ở lần thử đầu và nhận syntax error at or near "materialized" — một lỗi mất năm phút vì tôi tin trí nhớ thay vì tra tài liệu.

Nội tuyến không phải lúc nào cũng tốt hơn

Đây là phần dễ bị bỏ qua khi người ta nghe "PostgreSQL 12 sửa vấn đề CTE".

Nội tuyến hoá nghĩa là CTE được tính lại ở mỗi chỗ tham chiếu. Nếu bạn tham chiếu nó ba lần, nó chạy ba lần.

CTE gộp nhóm trên 2 triệu dòng, dùng ba lần trong cùng một truy vấn:

with tk as (select kh_id, count(*) n, sum(tien) s from dh2 group by kh_id)
select (select count(*) from tk where n > 15),
       (select count(*) from tk where n < 5),
       (select round(avg(s)) from tk);

Vật chất hoá nhanh hơn khoảng 20%. Khoảng cách nhỏ vì bản nội tuyến dùng được Index Only Scan, bù lại phần nào chi phí tính ba lần.

Làm CTE đắt hơn — thêm distinct và sắp xếp:

Chậm gấp 1,7 lần. CTE càng đắt thì việc tính lại càng đau.

Quy tắc rút ra được: CTE đắt và được dùng nhiều lần thì nên MATERIALIZED; CTE rẻ hoặc chỉ dùng một lần thì để mặc định.

May là PostgreSQL đã tự làm phần lớn việc này: quy tắc mặc định của nó là nội tuyến hoá chỉ khi CTE được tham chiếu đúng một lần, không có tác dụng phụ, và không phải WITH RECURSIVE. Nghĩa là ví dụ ba-tham-chiếu ở trên đã tự được vật chất hoá mà không cần từ khoá — tôi phải viết not materialized tường minh mới ép được nó tính lại.

Ba trường hợp luôn vật chất hoá

Tôi kiểm chứng hai trường hợp bắt buộc:

with recursive n(i) as (select 1 union all select i+1 from n where i<1000)
select count(*) from n;
--  kế hoạch: CTE Scan + WorkTable Scan  ->  luôn vật chất hoá

with x as (insert into dh2(kh_id,tien,tt) values (1,1,'moi') returning id)
select count(*) from x;
--  kế hoạch: CTE Scan  ->  luôn vật chất hoá

Hai trường hợp này là bắt buộc về ngữ nghĩa, không phải về hiệu năng: một CTE có INSERT bên trong phải chạy đúng một lần. Nếu nó được nội tuyến vào ba chỗ tham chiếu, bạn sẽ chèn ba dòng. PostgreSQL không cho phép chuyện đó, và NOT MATERIALIZED cũng không ép được.

Trường hợp thứ ba — CTE được tham chiếu từ hai chỗ trở lên — là quy tắc mặc định chứ không bắt buộc, nên NOT MATERIALIZED ghi đè được.

Truy vấn con thì sao

Truy vấn con chưa bao giờ là hàng rào, ở mọi phiên bản. Trên PostgreSQL 11 nó cho 0,10 ms trong khi CTE cho 226 ms.

Nhưng truy vấn con có giới hạn riêng: không tái sử dụng được. Muốn dùng cùng một kết quả ở ba chỗ thì phải viết lại ba lần, và bộ tối ưu sẽ tính ba lần — đúng như NOT MATERIALIZED.

Từ PostgreSQL 12, CTE mặc định không còn nhược điểm nào so với truy vấn con. Lời khuyên "dùng CTE cho dễ đọc" giờ đúng.

Điều cần kiểm khi nâng cấp

Nếu bạn nâng từ PostgreSQL 11 hoặc cũ hơn lên 12+, hành vi của mọi CTE trong mã của bạn đổi. Phần lớn sẽ nhanh lên, nhưng không phải tất cả:

Mã cố ý lợi dụng hàng rào sẽ chậm đi. Có một mẹo cũ: bọc một phần truy vấn trong CTE để ép bộ tối ưu tính nó trước, khi bạn biết rõ hơn nó. Sau khi nâng cấp, mẹo đó ngừng hoạt động — và cách sửa là thêm MATERIALIZED, không phải viết lại truy vấn.

CTE đắt dùng nhiều lần thì vẫn an toàn, vì quy tắc mặc định giữ nguyên vật chất hoá cho chúng.

Cách tìm CTE trong mã đang chạy:

select query, calls, round(mean_exec_time::numeric, 2) as tb_ms
from pg_stat_statements
where query ilike '%with %'
order by mean_exec_time * calls desc
limit 20;

Cần bật extension pg_stat_statements. Chạy trước và sau khi nâng cấp rồi so hai danh sách là cách chắc chắn nhất tìm ra truy vấn nào đổi hành vi.

Thử ba mươi giây

Lấy một truy vấn có WITH mà bạn thấy chậm:

explain (analyze, buffers) <truy vấn của bạn>;

Tìm chữ CTE Scan trong kế hoạch. Nếu có, CTE đó đang được vật chất hoá. Hỏi tiếp hai câu:

1. Nó có được tham chiếu nhiều hơn một lần không? Nếu không, thử not materialized — bạn có thể đang trả giá vô ích.
2. Có điều kiện lọc nào ở ngoài đáng lẽ nên đẩy vào trong không? Nếu có và số trang đọc lớn bất thường, đó chính là hàng rào đang chặn.

Trên PostgreSQL 11 trở xuống, câu trả lời cho cả hai luôn là "đổi sang truy vấn con".

Phần sau đo window function: cách chúng chạy, chi phí so với GROUP BY, và vì sao một câu ORDER BY trong OVER có thể đắt hơn cả phần còn lại của truy vấn.