Bọn mình vừa tìm ra một lỗi chậm trong chính site này: khoá ngoại thiếu index

Trước khi mở site, bọn mình chạy pg_stat_statements và một truy vấn đơn giản trên pg_stat_user_tables để xem bảng nào hay bị quét toàn bộ. Kết quả chỉ ra bảng notifications: bị quét toàn bộ 60 lần, trong khi dùng index chỉ 33 lần.

Vì sao

Mỗi thông báo trỏ tới bài, bình luận và người gây ra thông báo bằng khoá ngoại ON DELETE CASCADE. Khi một bài bị xoá, Postgres phải tìm mọi thông báo trỏ tới bài đó. Postgres không tự tạo index cho cột khoá ngoại (nó chỉ tự tạo index cho khoá chính và unique), nên mỗi dòng cha bị xoá là một lần quét toàn bộ bảng con.

Job dọn dẹp của site xoá hẳn các bài và bình luận đã tự xoá quá 30 ngày, tức là xoá hàng loạt. Bảng thông báo lại là bảng lớn nhanh nhất.

Đo trước và sau

Trên dữ liệu thử (khoảng 2.500 thông báo), xoá một bài có 412 bình luận:

• Trước: 75 ms. Hai trigger khoá ngoại tốn 23 ms và 36 ms vì quét bảng.
• Sau khi thêm index: 30 ms, mỗi trigger còn khoảng 4 ms.

Con số tuyệt đối nhỏ vì bảng còn nhỏ, nhưng không có index thì chi phí đó tăng theo kích thước bảng.

Kiểm tra database của bạn

Truy vấn này liệt kê các khoá ngoại mà cột đầu tiên chưa có index nào đứng đầu:

select c.conrelid::regclass as bang, a.attname as cot, c.confrelid::regclass as tro_toi
from pg_constraint c
join pg_attribute a on a.attrelid = c.conrelid and a.attnum = c.conkey[1]
where c.contype = 'f'
  and not exists (
    select 1 from pg_index i
    where i.indrelid = c.conrelid and i.indkey[0] = c.conkey[1]
  );

Không phải khoá ngoại nào cũng cần index: bảng nhỏ, hiếm khi xoá dòng cha thì thêm index chỉ tốn chỗ và làm chậm thao tác ghi. Ưu tiên bảng lớn có ON DELETE CASCADE hoặc hay được join theo cột đó. Với bảng đã lớn, dùng CREATE INDEX CONCURRENTLY để không khoá bảng.

Bạn đã từng gặp lỗi chậm nào "tưởng nhỏ" mà tốn cả tuần mới tìm ra chưa?

Nguồn: PostgreSQL docs — Foreign keys