Ghép cặp: chọn loại index cho từng query Trung cấp

Thời gian đọc ước tính: 1 phút

“Thêm index” là câu trả lời quá chung. Câu hỏi đúng là loại nào, và đôi khi câu trả lời là không cần index nào. Kéo loại index vào từng query.

Mỗi đáp án đã chạy thật trên PostgreSQL 17 với bảng cỡ như đề bài, nên phần giải thích có số đo.
Cặp hay nhầm nhất: B-tree một cột với partial, composite với covering, và B-tree với GIN cho LIKE.

Mỗi tình huống là một query thật trên PostgreSQL 17. Kéo loại index phù hợp nhất vào từng tình huống. Một loại có thể dùng cho nhiều tình huống, và có loại không dùng tới.

Mẹo: nhìn toán tử trong WHERE và hình dạng dữ liệu trước, rồi mới nghĩ tới index.

Chạm Chọn đáp án ở từng tình huống. Trên máy tính có thể kéo thẻ bên dưới thả vào tình huống.

Đã giải 0/8 · sai 0 lần

1Đăng nhập không phân biệt hoa thường trên bảng users 1 triệu dòng. Đã có index trên email, nhưng plan vẫn là Parallel Seq Scan, 42 ms.

SELECT id FROM users WHERE lower(email) = lower($1);

2Worker lấy việc chưa làm từ bảng jobs 2 triệu dòng. Chỉ 0,1% là pending, phần còn lại là done và không bao giờ bị hỏi lại.

SELECT id FROM jobs WHERE status = 'pending'
ORDER BY created_at LIMIT 50;

3Màn hình "Đơn của tôi" trên bảng orders 30 triệu dòng. Mỗi khách khoảng 30 đơn, một khách B2B có 200.000 đơn.

SELECT id, status, total, created_at FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC LIMIT 20;

4Lọc sản phẩm theo thuộc tính lưu trong cột attrs jsonb (1 triệu dòng). Mỗi ngành hàng có bộ thuộc tính khác nhau. Hiện Parallel Seq Scan, 46 ms.

SELECT id FROM products
WHERE attrs @> '{"brand": "b42", "color": "black"}';

5Bảng events chỉ insert, khoảng 2 tỷ dòng, ghi theo thời gian. Mọi query đều lọc theo khoảng created_at vài giờ. pg_stats.correlation của created_at là 1.

SELECT count(*) FROM events
WHERE created_at >= $1 AND created_at < $1 + interval '1 hour';

6Biểu đồ doanh thu 30 ngày của một khách B2B (200.000 đơn), dashboard gọi liên tục. Đã có index (customer_id, created_at), nhưng mỗi query đọc 1.292 buffer cho 1.281 dòng vì các đơn nằm rải rác trong bảng.

SELECT created_at, total FROM orders
WHERE customer_id = $1
  AND created_at >= now() - interval '30 days';

7Bảng app_config 200 dòng, nằm gọn trong 2 page. Ai đó đề xuất thêm index trên category vì "query nào cũng nên có index".

SELECT key, value FROM app_config
WHERE category = 'payment';

8Ô tìm kiếm sản phẩm theo một đoạn tên bất kỳ, trên 1 triệu dòng. Đã có index B-tree trên name, plan vẫn là Parallel Seq Scan.

SELECT id FROM products
WHERE name LIKE '%' || $1 || '%';

Muốn hiểu vì sao toán tử quyết định loại index, đọc GIN, GiST và BRIN.

#playground #postgresql #index #gin #brin