Tối ưu Slow Query PostgreSQL: Khi một dòng code cứu cả hệ thống

Bối cảnh

Trong một dự án e-commerce gần đây, tôi gặp một bài toán rất quen thuộc:
trên trang chi tiết đơn hàng, cần có nút:

• “Đơn tiếp theo” (Next)
• “Đơn trước đó” (Previous)

Yêu cầu tưởng đơn giản, nhưng có một constraint quan trọng:
không được load toàn bộ danh sách lên RAM.

Giải pháp hiển nhiên là dùng Keyset Pagination (cursor-based), thay vì offset pagination.

Cách triển khai ban đầu

Dữ liệu được sort theo:

• paid_at (thời điểm thanh toán)
• id (để break tie)

Code Laravel Eloquent ban đầu của tôi cho “Next Order” trông như sau:

->where(function (Builder $q) use ($order) {
    $q->where('paid_at', '>', $order->paid_at)
        ->orWhere(function (Builder $q1) use ($order) {
            $q1->where('paid_at', $order->paid_at)
                ->where('id', '>', $order->id);
        });
})
->orderBy('paid_at')
->orderBy('id')

Nếu đọc bằng “tiếng người”, logic này hoàn toàn đúng:

Lấy các đơn có paid_at lớn hơn,
hoặc nếu cùng thời điểm thì id phải lớn hơn.

Vấn đề thực tế xảy ra

Khi dữ liệu tăng lên vài triệu bản ghi, hệ thống bắt đầu:

• xuất hiện slow query
• query plan chuyển sang:
  • Bitmap Heap Scan
  • hoặc tệ hơn là Seq Scan

Trong khi đó, database đã có composite index:

(paid_at, id)

=> Về lý thuyết phải chạy rất nhanh.

Nguyên nhân cốt lõi

Thủ phạm nằm ở mệnh đề OR.

PostgreSQL Query Planner khi gặp OR thường:

• tách điều kiện thành nhiều nhánh
• không tận dụng được composite index một cách hiệu quả
• dẫn tới scan nhiều hơn cần thiết

Nói đơn giản:
Index có, nhưng không được dùng đúng cách.

Cách giải quyết (và là điểm “aha moment”)

Thay vì viết điều kiện dạng boolean phức tạp,
ta sử dụng Tuple Comparison (Row Value Syntax) của PostgreSQL.

Thay đổi duy nhất:

->whereRaw('(paid_at, id) > (?, ?)', [$order->paid_at, $order->id])

Với “Previous”:

->whereRaw('(paid_at, id) < (?, ?)', [$order->paid_at, $order->id])

Vì sao cách này hiệu quả?

Composite index (paid_at, id) trong PostgreSQL được sắp xếp theo kiểu:

từ điển (lexicographical order)

Tức là:

1. So sánh paid_at
2. Nếu bằng nhau → so sánh id

Khi bạn viết:

(paid_at, id) > (x, y)

PostgreSQL có thể:

• map trực tiếp vào B-Tree index
• nhảy đúng vị trí (x, y)
• thực hiện Index Scan tuyến tính

=> Không cần phân nhánh logic như OR nữa.

Kết quả

Sau khi thay đổi:

• Query Plan chuyển thành Index Scan
• Slow query biến mất
• Thời gian response ổn định lại

Ngoài ra còn một lợi ích “không ngờ”:

Code gọn hơn rất nhiều

Từ:

• nhiều closure lồng nhau
• logic khó đọc

=> còn:

->whereRaw('(paid_at, id) > (?, ?)', [...])

Bài học rút ra

1. Tránh OR khi làm keyset pagination

Đặc biệt khi:

• sort theo nhiều cột
• đã có composite index

2. Hiểu cách database tổ chức index quan trọng hơn ORM

ORM giúp code “đẹp”, nhưng:

không đảm bảo query tối ưu

Đôi khi, quay về RAW SQL đúng chỗ lại là lựa chọn tốt hơn.

3. Tận dụng Tuple Comparison trong PostgreSQL

Đây là một feature rất mạnh nhưng ít được dùng:

(col1, col2) > (val1, val2)

Rất phù hợp cho:

• keyset pagination
• multi-column sorting

Kết luận

Một thay đổi nhỏ:

• bỏ OR
• dùng tuple comparison

=> có thể:

• giảm load database
• tránh slow query
• đơn giản hoá code

Đây là một ví dụ điển hình cho việc:

tối ưu performance không phải lúc nào cũng cần rewrite lớn — đôi khi chỉ cần hiểu đúng cách database hoạt động.