Prepared statement (PREPARE/EXECUTE) là cách chuẩn để tái dùng một truy vấn tham số hóa — vừa chống SQL injection, vừa bỏ qua công đoạn phân tích cú pháp và lập kế hoạch mỗi lần chạy. Nhưng phần "bỏ qua lập kế hoạch" ẩn một cái bẫy tinh vi: PostgreSQL có thể chuyển sang generic plan — một kế hoạch chung dùng cho mọi giá trị tham số — và trên dữ liệu lệch, kế hoạch chung đó có thể sai nghiêm trọng. Bài này đo thật cả lợi ích lẫn cái bẫy.

Prepared statement làm gì

Khi bạn PREPARE một truy vấn rồi EXECUTE nhiều lần, PostgreSQL không phải phân tích cú pháp và lập kế hoạch lại mỗi lần — nó tái dùng kế hoạch đã cache:

PREPARE q(int) AS SELECT ... WHERE grp=$1;
EXECUTE q(100);   -- lần sau chỉ EXECUTE, không parse/plan lại

Phần tiết kiệm là Planning Time (thời gian lập kế hoạch). Với truy vấn cực đơn giản (tra khóa chính một dòng), phần này vốn đã rất nhỏ nên tái dùng gần như không đo được lợi — đo thật pgbench -M simple (239k TPS) so với -M prepared (234k) gần như bằng nhau vì query của pgbench quá đơn giản. Nhưng với truy vấn phức tạp nhiều JOIN, Planning Time có thể lên vài mili-giây, và tái dùng plan lúc đó rất đáng giá.

Ảnh chụp đoạn mã SQL nền tối minh hoạ prepared statement và plan cache tái dùng kế hoạch và cái bẫy generic plan, prepared statement bỏ qua parse cộng plan khi chạy lại PREPARE q int AS SELECT WHERE grp bằng 1 EXECUTE q 100 lần sau chỉ EXECUTE không parse plan lại tiết kiệm phân tích cú pháp cộng lập kế hoạch Planning Time với truy vấn đơn giản tiết kiệm nhỏ truy vấn phức tạp nhiều JOIN Planning Time có thể vài ms tái dùng plan đáng giá, custom plan vs generic plan custom lập kế hoạch theo giá trị thật của tham số mỗi lần chính xác cho skew generic lập một kế hoạch tham số là placeholder dùng độ chọn lọc trung bình quy tắc auto mặc định 5 lần đầu dùng custom rồi so chi phí nếu generic không đắt hơn custom trung bình thì chuyển sang generic SET plan_cache_mode force_custom_plan ép custom an toàn cho skew SET plan_cache_mode force_generic_plan ép generic, cái bẫy generic plan trên dữ liệu lệch skew bảng skew 99 phần trăm dòng có grp bằng 0 các grp khác chỉ 1 dòng grp bằng 0 phổ biến nên Seq Scan grp hiếm nên Index Scan generic plan dùng ước lượng trung bình một con số duy nhất cho mọi giá trị chọn sai kế hoạch cho ít nhất một nhóm, đo tác động parse plan bằng pgbench pgbench -S -M simple parse plan mỗi lần pgbench -S -M prepared tái dùng plan protocol level với truy vấn cực đơn giản tra khóa chính 1 dòng chênh lệch gần như bằng 0 vì parse plan vốn đã rất rẻ lợi ích lớn ở truy vấn phức tạp

Hình 1: Prepared statement bỏ qua parse+plan khi chạy lại. PostgreSQL chọn giữa custom plan (lập theo giá trị thật mỗi lần) và generic plan (một kế hoạch dùng ước lượng trung bình). Cơ chế auto: 5 lần đầu custom, rồi có thể chuyển generic.

Đo thật: generic plan ước lượng sai 313 lần

Để lộ cái bẫy, tôi dựng bảng skew 2 triệu dòng cực lệch: 99% số dòng có grp=0, còn các giá trị grp khác mỗi cái chỉ 1 dòng. Với truy vấn WHERE grp=$1:

  • grp=0 (phổ biến, 1,98 triệu dòng) → kế hoạch đúng là Seq Scan (index vô dụng khi lấy gần hết bảng).
  • grp hiếm (1 dòng) → kế hoạch đúng là Index Scan.

Ảnh chụp bảng kết quả đo thật nền tối generic plan ước lượng sai 313 lần trên dữ liệu lệch bảng skew 2 triệu dòng 99 phần trăm grp bằng 0 PREPARE WHERE grp bằng 1 plan_cache_mode PostgreSQL 16, custom plan lập kế hoạch theo giá trị thật đúng mỗi lần EXECUTE q 0 grp bằng 0 99 phần trăm 1.98M dòng Seq Scan rows khoảng 1.979.200 đúng EXECUTE q 100 grp bằng 100 hiếm 1 dòng Index Scan rows khoảng 66 đúng, generic plan một kế hoạch ước lượng trung bình cho mọi giá trị EXECUTE q 0 Index Only Scan rows 6329 sai thật ra 1.980.000 dòng EXECUTE q 100 Index Only Scan rows 6329 tình cờ ổn cho giá trị hiếm generic dùng độ chọn lọc trung bình 2M chia số grp phân biệt khoảng 6329 ước lượng lệch 313 lần so với thực tế 1,98 triệu cho grp bằng 0, bảng grp bằng 0 SELECT sum id buộc chạm heap custom plan Seq Scan 80,3 ms buffer 8.896 generic plan Index Scan 108,8 ms buffer 10.519, nhận xét trung thực ở đây generic chậm hơn khoảng 35 phần trăm 108 so với 80ms không phải thảm hoạ vì dữ liệu đã cache trên đĩa thật Index Scan chạm heap 1,98 triệu lần đọc ngẫu nhiên sẽ tệ hơn nhiều so với Seq Scan tuần tự cái nguy hiểm cốt lõi là ước lượng sai 313 lần hệ quả tuỳ plan và storage, bảo vệ mặc định auto 5 lần đầu custom chỉ chuyển generic nếu không đắt hơn PostgreSQL 12 cộng thường tự tránh generic tệ ép chắc chắn SET plan_cache_mode force_custom_plan cho truy vấn tham số trên cột lệch

Hình 2: Custom plan chọn đúng (Seq Scan cho grp=0, Index Scan cho grp hiếm). Generic plan dùng một ước lượng trung bình 6329 dòng cho mọi giá trị — sai 313 lần so với thực tế 1,98 triệu của grp=0. Đo thời gian: generic (Index Scan) 108,8 ms so với custom (Seq Scan) 80,3 ms.

  • Custom plan: EXECUTE q(0) → Seq Scan (ước lượng ~1.979.200 dòng, đúng); EXECUTE q(100) → Index Scan (ước lượng ~66, đúng). Mỗi lần lập kế hoạch theo giá trị thật nên chọn đúng.
  • Generic plan: cả q(0) lẫn q(100) đều dùng Index Only Scan với ước lượng 6329 dòng — con số "trung bình" (2 triệu chia cho số giá trị grp phân biệt). Với grp=0, ước lượng này lệch 313 lần so với thực tế 1,98 triệu dòng.

Đo thời gian thật cho grp=0 với SELECT sum(id) (buộc chạm heap): custom plan (Seq Scan) mất 80,3 ms, generic plan (Index Scan) mất 108,8 ms — chậm hơn ~35% và chạm nhiều buffer hơn.

Nhận xét trung thực và cách bảo vệ

Ở lab này, generic plan chỉ chậm hơn ~35%, không phải thảm họa — vì dữ liệu đã nằm trong cache. Trên đĩa thật với cache nguội, Index Scan phải chạm heap 1,98 triệu lần bằng đọc ngẫu nhiên, sẽ tệ hơn nhiều so với Seq Scan tuần tự. Cái nguy hiểm cốt lõi là ước lượng sai 313 lần; hệ quả cụ thể tệ đến đâu tùy vào plan được chọn và loại storage.

Tin tốt: PostgreSQL bảo vệ bạn khá tốt theo mặc định. Với plan_cache_mode='auto' (mặc định), PostgreSQL dùng custom plan cho 5 lần EXECUTE đầu, rồi mới so sánh chi phí generic với chi phí custom trung bình — và chỉ chuyển sang generic nếu generic không đắt hơn. Nhờ vậy PostgreSQL 12+ thường tự tránh generic plan tệ. Khi bạn muốn chắc chắn (truy vấn tham số trên cột lệch quan trọng):

SET plan_cache_mode = 'force_custom_plan';   -- luôn lập kế hoạch theo giá trị thật

Đánh đổi cần cân nhắc

Generic plan không xấu — nó tiết kiệm lập kế hoạch cho dữ liệu phân bố đều. Với cột phân bố đồng đều (không lệch), generic plan cho kết quả gần như custom mà bỏ được chi phí lập kế hoạch lặp lại. force_custom_plan đánh đổi bằng việc lập kế hoạch lại mỗi lần — tốn CPU hơn cho truy vấn phức tạp. Chỉ ép custom khi dữ liệu thật sự lệch.

Prepared statement tương tác với connection pooling. Như bài PgBouncer đã nhắc: prepared statement kiểu server-side gắn với một backend cụ thể. Trong transaction pooling, mỗi giao dịch có thể rơi vào backend khác nên prepared statement "biến mất". PgBouncer 1.21+ hỗ trợ protocol-level prepared statement trong transaction mode, nhưng phải kiểm driver. Nếu dùng pool, cân nhắc tắt prepared ở tầng driver hoặc dùng session pooling.

Đây là nguồn gốc của "truy vấn nhanh rồi bỗng chậm". Một triệu chứng kinh điển: truy vấn tham số chạy nhanh vài lần đầu (custom plan) rồi đột ngột chậm ở lần thứ 6+ (chuyển sang generic plan tệ). Nếu gặp, kiểm plan_cache_mode và cân nhắc force_custom_plan.

Ba ý mang về

  1. Prepared statement bỏ qua parse+plan khi chạy lại — tiết kiệm Planning Time (nhỏ với truy vấn đơn giản, đáng kể với truy vấn phức tạp nhiều JOIN); đo pgbench simple vs prepared gần bằng nhau vì query pgbench quá đơn giản.
  2. Generic plan dùng ước lượng trung bình, nguy hiểm trên dữ liệu lệch: đo thật, generic plan ước lượng 6329 dòng trong khi grp=0 thực tế có 1,98 triệu — sai 313 lần, chọn Index Scan 108ms thay vì Seq Scan 80ms của custom plan.
  3. PostgreSQL 12+ tự bảo vệ qua cơ chế auto (5 lần đầu custom, chỉ chuyển generic nếu không đắt hơn) — nhưng với cột lệch quan trọng, ép plan_cache_mode='force_custom_plan', và nhớ prepared statement tương tác với transaction pooling.

Phần sau ta chuyển sang cơ chế đồng thời cốt lõi: Phần sau mổ xẻ khóa hàng, khóa bảng và advisory lock — các mức khóa PostgreSQL dùng, khi nào chúng chặn nhau, và cách quan sát bằng pg_locks.