PostgreSQL Stories: From slow query to fast, via stats

Render · 2024 · 2. Tăng trưởng · Độ khó: Trung bình — bài gốc

Phần 2 của một câu chuyện; phần 1 (tìm ra query làm treo pipeline deploy staging) chưa có trong wiki. Đây là DB nội bộ của Render, không phải sản phẩm Postgres cho khách.

Bối cảnh

• Pipeline deploy staging của Render bị treo vì một query đơn giản chạy hàng giờ: join bảng postgres_dbs (khoảng 100 nghìn dòng) với bảng events (khoảng 90 triệu dòng) theo serviceId nằm trong cột JSON data, có LIMIT 1.
• Cả hai bảng đều có index phù hợp (index nhiều cột trên biểu thức data ->> 'serviceId' và timestamp). Chỉ khoảng 10 nghìn event (0,01%) join được: bài toán mò kim đáy bể mà DB có index lẽ ra làm tốt.

Thay đổi chính

• Đọc plan: EXPLAIN (không dùng được EXPLAIN ANALYZE vì nó chạy thật query hàng giờ) cho thấy merge join trên hai index, dừng ở dòng đầu tiên.
• Vẽ dữ liệu ra mới thấy: serviceId có tiền tố theo loại (crn- cron, dpg- database, srv- server). Index sắp theo serviceId, nên merge join phải đi qua 79 triệu event (87%) đứng trước dpg- theo thứ tự chữ cái (phần lớn là event cron rất dày) trước khi gặp dòng có thể join.
• Vá bằng điều kiện WHERE để nhảy thẳng tới đoạn dpg-: còn dưới 2 ms. Nhưng bỏ LIMIT thì planner đổi sang hash join + sequential scan toàn bảng events, mất 2,5 phút. Thêm cận trên cho khoảng dpg- thì còn khoảng 70 ms.
• Tìm nguyên nhân gốc: xem pg_stats của cột data thì thấy thống kê tính trên cả object JSON, không phải trên serviceId; planner không dùng được nên đoán theo giả định mặc định.
• Sửa gốc: CREATE STATISTICS trên biểu thức data ->> 'serviceId' (Postgres cho tạo thống kê mở rộng trên biểu thức, trên cột tương quan, phụ thuộc hàm). Sau đó pg_stats_ext có null_frac, n_distinct và giá trị phổ biến theo đúng serviceId.
• Kết quả: query gốc (không cần WHERE vá) chạy dưới 1 ms bằng nested loop join (với mỗi database, tra index tìm event); bỏ LIMIT thì lấy khoảng 10 nghìn dòng trong khoảng 650 ms.

Vì sao

• Theo Render, planner của Postgres lẽ ra chọn được plan tốt cho query kiểu này; khi nó chọn sai, nghi đầu tiên là nó "biết" ít về dữ liệu hơn ta tưởng. Vá query bằng WHERE chạy được nhưng không bền: mỗi query mới lại phải vá.

Bài học cho SWE

• Plan đúng phụ thuộc vào thống kê đúng. Index trên biểu thức (JSON, hàm) không tự kéo theo thống kê trên biểu thức đó; phải tạo riêng (Query planner và hiệu năng truy vấn).
• Phân bố dữ liệu lệch theo thứ tự index (tiền tố, ID tăng dần) làm hỏng giả định "trải đều" của planner, đặc biệt với LIMIT.
• Cùng một query, có và không có LIMIT có thể ra hai plan khác hẳn; phải thử cả hai.
• Quy trình hữu ích: đếm để biết hình dạng dữ liệu → EXPLAIN → vẽ tay cách join chạy → vá tạm → tìm vì sao planner sai → sửa thống kê. Công cụ PEV2 giúp đọc plan.
• Staging có dữ liệu "nhỏ" vẫn đủ để lộ lỗi plan; đừng coi chậm ở staging là vấn đề của staging.

Liên quan

• GoCardless 2019 (cùng lớp lỗi: thống kê sai làm planner chọn plan tệ khi có LIMIT)
• Render 2024 · Query planner và hiệu năng truy vấn