Đã đăng vào khoảng 12 giờ trước 11 phút đọc

EXPLAIN ANALYZE: Bản đồ tối ưu câu lệnh SQL chậm

Khi một câu SQL chạy chậm, phản xạ phổ biến là thêm index. Nhưng index không phải lúc nào cũng xử lý được nguyên nhân gốc. Đọc hiểu EXPLAIN ANALYZE giúp chúng ta thấy database đã quét bao nhiêu dòng, chọn kiểu JOIN nào và tốn thời gian ở đâu. Từ đó, mỗi thay đổi đều dựa trên bằng chứng thay vì phỏng đoán.

Bài viết sử dụng PostgreSQL và một truy vấn phân trang sâu để minh họa.

`EXPLAIN ANALYZE` cho chúng ta biết điều gì?

EXPLAIN hiển thị kế hoạch PostgreSQL dự kiến sử dụng. EXPLAIN ANALYZE đi xa hơn: nó thực thi câu lệnh và trả về số liệu thực tế.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM order_items
WHERE created_at >= TIMESTAMP '2026-06-01 00:00:00';

Khi đọc kết quả, hãy tập trung vào một số thông tin chính:

cost: chi phí do PostgreSQL ước tính, không phải thời gian tính bằng mili giây.
actual time: thời gian thực tế của từng node.
rows và actual rows: số dòng ước tính và số dòng thực tế.
loops: số lần một node được thực thi.
Buffers: lượng dữ liệu được đọc từ bộ nhớ hoặc ổ đĩa.
Execution Time: tổng thời gian thực thi.

EXPLAIN ANALYZE thực sự chạy câu lệnh. Cần thận trọng với INSERT, UPDATE, DELETE và truy vấn nặng trên production.

Sai lầm thường gặp: Chỉ lấy 10 dòng thì chắc sẽ nhanh

Xét truy vấn sau:

SELECT *
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE oi.created_at >= TIMESTAMP '2026-06-01 00:00:00'
ORDER BY oi.id
LIMIT 10 OFFSET 3000000;

Đây là cách viết khá phổ biến với người mới bắt đầu: lọc dữ liệu, JOIN các bảng cần thiết rồi phân trang ở cuối. Truy vấn chỉ trả về 10 dòng nên thoạt nhìn có vẻ nhẹ.

Nhưng kế hoạch thực thi lại cho thấy một câu chuyện khác:

Limit  (cost=1349084.28..1349086.65 rows=10)
  -> Gather  (rows=3657460)
       -> Hash Join
            -> Parallel Hash Join
                 -> Parallel Seq Scan on orders
                 -> Parallel Bitmap Heap Scan on order_items
                      -> Bitmap Index Scan on idx_order_items_created_at
            -> Seq Scan on products

PostgreSQL dự kiến phải xử lý khoảng 3,65 triệu dòng, thực hiện các phép Hash Join, rồi mới bỏ qua 3 triệu dòng đầu tiên để lấy 10 dòng.

Đây là điểm dễ nhầm nhất: LIMIT 10 chỉ giới hạn kết quả trả về, không đảm bảo database chỉ xử lý 10 dòng.

Tối ưu lần đầu: Thêm index nhưng vẫn chưa đủ

Bước đầu tiên là tạo covering index cho các cột được dùng trong điều kiện lọc và phép JOIN. Đồng thời, phần lọc order_items được đặt trong truy vấn con:

SELECT *
FROM (
    SELECT order_id, product_id, created_at, id
    FROM order_items
    WHERE created_at >= TIMESTAMP '2026-06-01 00:00:00'
) oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
LIMIT 10 OFFSET 3000000;

Kế hoạch mới xuất hiện Index Only Scan:

Limit  (cost=852695.75..852697.19 rows=10)
  -> Gather  (rows=3657460)
       -> Hash Join
            -> Parallel Hash Join
                 -> Parallel Index Only Scan
                    using idx_order_items_created_at_id_include_order_product
                 -> Parallel Seq Scan on orders
            -> Seq Scan on products

Chi phí ước tính giảm từ khoảng 1,35 triệu xuống 852 nghìn. Database có thể lấy các cột cần thiết ngay trên index, nhờ đó giảm số lần phải truy cập bảng order_items.

Tuy nhiên, kế hoạch vẫn có rows=3657460. Hàng triệu dòng vẫn đi qua các phép JOIN trước khi LIMIT/OFFSET được áp dụng.

Ngoài ra, việc chỉ đặt WHERE vào truy vấn con chưa chắc tạo ra một ranh giới thực thi mới. PostgreSQL có thể làm phẳng truy vấn con này. Phần cải thiện ở bước trên chủ yếu đến từ covering index.

Index đã có tác dụng, nhưng chưa chạm đến điểm nghẽn lớn nhất.

Điểm chết tiếp theo nằm ở `LIMIT/OFFSET`

Vấn đề không chỉ là cách đọc order_items, mà còn là thời điểm phân trang. Khi LIMIT/OFFSET nằm sau các phép JOIN, database có thể phải nối hàng triệu dòng dù kết quả cuối cùng chỉ cần 10 dòng.

Đã lọc dữ liệu rồi, tại sao vẫn chậm?

Kế hoạch thực thi ở bước trước cho thấy hai cách đọc dữ liệu khác nhau:

Gather  (rows=3657460)
  -> Hash Join
       -> Parallel Hash Join
            -> Parallel Index Only Scan
               using idx_order_items_created_at_id_include_order_product
            -> Parallel Seq Scan on orders
       -> Seq Scan on products

Truy vấn con sử dụng Index Only Scan vì bảng order_items có điều kiện cụ thể trên created_at, đồng thời covering index đã chứa các cột cần thiết. PostgreSQL có thể tìm các mục phù hợp ngay trên index mà không phải đọc toàn bộ dữ liệu từ heap.

Tuy nhiên, sau khi lọc vẫn còn khoảng 3,65 triệu dòng. Đây vẫn là một tập dữ liệu rất lớn. Mỗi dòng đó cần được ghép với orders và products vì câu lệnh đang yêu cầu SELECT * từ cả ba bảng.

Hai bảng được JOIN lại không có điều kiện lọc riêng:

Với orders, PostgreSQL dự kiến cần đối chiếu một lượng rất lớn đơn hàng. Quét tuần tự bảng một lần để tạo hoặc dò hash thường rẻ hơn thực hiện hàng triệu lần tra cứu ngẫu nhiên qua index.
Với products, bảng chỉ có khoảng 10.000 dòng. Đọc toàn bộ bảng rồi tạo hash là thao tác tương đối nhỏ, nên Seq Scan hợp lý hơn nhiều lần tra cứu index.
Index trên orders.id và products.id vẫn tồn tại, nhưng có index không đồng nghĩa PostgreSQL luôn phải dùng index. Planner chọn cách có tổng chi phí ước tính thấp nhất dựa trên số dòng cần xử lý.

Ngoài ra, truy vấn con chỉ chứa WHERE thường được PostgreSQL làm phẳng vào truy vấn chính. Nó không bắt buộc database phải hoàn tất truy vấn con rồi mới bắt đầu JOIN. Vì thế, đưa điều kiện lọc vào một cặp ngoặc chưa đủ để thay đổi thứ tự xử lý.

Nút thắt thật sự lúc này là: database vẫn phải JOIN hàng triệu dòng, trong khi cuối cùng chỉ trả về 10 dòng. Muốn thay đổi chiến lược từ Hash Join và Seq Scan sang các phép tra cứu index nhỏ, cần giảm đầu vào xuống 10 dòng trước khi thực hiện JOIN.

Giải pháp tiếp theo là phân trang order_items trước, sau đó mới JOIN với orders và products:

SELECT oi.*, o.*, p.*
FROM (
    SELECT order_id, product_id, created_at, id
    FROM order_items
    WHERE created_at >= TIMESTAMP '2026-06-01 00:00:00'
    ORDER BY id
    LIMIT 10 OFFSET 3000000
) oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
ORDER BY oi.id;

Phần quan trọng trong kế hoạch thực thi lúc này có dạng:

Nested Loop
  -> Nested Loop
       -> Limit
            -> Index Only Scan on order_items
       -> Index Only Scan using orders_pkey on orders
  -> Index Only Scan using products_pkey on products

Trong phép thử với biến thể count(*), kế hoạch bạn cung cấp có chi phí ước tính giảm còn khoảng 68 nghìn. Thay vì JOIN hàng triệu dòng, PostgreSQL lấy 10 dòng từ order_items, sau đó tra cứu các bản ghi tương ứng bằng khóa chính của orders và products.

Biến thể count(*) trong phép thử đã bỏ ORDER BY và không trả về cùng cấu trúc dữ liệu với truy vấn ban đầu. Nó cho thấy lợi ích của việc giảm dữ liệu trước khi JOIN, nhưng không phải phép so sánh hoàn toàn tương đương. Khi áp dụng thực tế, cần giữ ORDER BY, giữ các cột đầu ra cần thiết và chạy lại EXPLAIN (ANALYZE, BUFFERS).

Đây mới là thay đổi tác động trực tiếp vào điểm nghẽn mà execution plan đã chỉ ra.

Cách đẩy phân trang vào truy vấn con chỉ giữ nguyên ý nghĩa khi các phép JOIN không loại bỏ thêm dữ liệu. Chẳng hạn, khóa ngoại phải hợp lệ và không có điều kiện lọc bổ sung trên orders hoặc products.

Vậy truy vấn đã tối ưu hoàn toàn chưa?

Chưa hẳn. PostgreSQL vẫn phải đi qua 3 triệu mục trước khi trả về 10 dòng, vì OFFSET 3000000 yêu cầu database bỏ qua toàn bộ các dòng đứng trước nó.

Đây là lúc keyset pagination thường được nhắc đến. Thay vì yêu cầu “bỏ qua 3 triệu dòng”, keyset pagination bắt đầu từ khóa cuối cùng của trang trước:

WHERE oi.id > :last_id
ORDER BY oi.id
LIMIT 10;

Cách này có thể nhanh hơn đáng kể với dữ liệu lớn. Nhưng nó cũng tạo ra một sự đánh đổi:

LIMIT/OFFSET cho phép nhảy trực tiếp đến một trang bất kỳ.
Keyset pagination phù hợp với luồng trang kế tiếp, nhưng cần cursor của trang trước.
Keyset pagination không tự cho biết tổng số trang.
Muốn nhảy từ trang đầu đến trang cuối, hệ thống cần thêm chiến lược lưu mốc hoặc truy vấn hỗ trợ.

Vậy khi nào nên giữ LIMIT/OFFSET, khi nào nên dùng keyset pagination, và liệu có thể kết hợp cả hai? Đây là câu chuyện đủ lớn cho một bài viết riêng: “LIMIT/OFFSET và keyset pagination: Đổi khả năng nhảy trang lấy hiệu năng?”

Quy trình đọc `EXPLAIN ANALYZE` cho câu SQL chậm

Khi gặp một truy vấn chậm, có thể bắt đầu bằng năm bước:

Tìm node tiêu tốn nhiều thời gian hoặc xử lý nhiều dòng nhất.
So sánh rows với actual rows để phát hiện ước tính sai.
Kiểm tra số dòng đi qua scan, filter và join.
Thay đổi từng yếu tố như index, thứ tự xử lý hoặc cách phân trang.
Chạy lại EXPLAIN (ANALYZE, BUFFERS) để đo kết quả trước và sau.

Trong ví dụ này, execution plan đã dẫn chúng ta qua ba lớp vấn đề:

Truy cập dữ liệu tốn kém: dùng covering index.
JOIN quá nhiều dòng không cần thiết: phân trang trước khi JOIN.
OFFSET quá sâu: cân nhắc keyset pagination và sự đánh đổi về trải nghiệm chuyển trang.

Kết luận

EXPLAIN ANALYZE không tự sửa câu SQL, nhưng nó cho biết chính xác nên nhìn vào đâu. Một index có thể làm truy vấn tốt hơn, song execution plan sẽ giúp chúng ta nhận ra liệu điểm nghẽn thật sự đã được giải quyết hay chỉ được giảm nhẹ. Trước khi thêm index tiếp theo, hãy nhìn vào số dòng đang được quét, số dòng đi qua JOIN và vị trí của LIMIT/OFFSET. Đó thường là nơi câu SQL chậm tiết lộ nguyên nhân thật sự.

Postgres

EXPLAIN ANALYZE cho chúng ta biết điều gì?