Sử dụng pg_stat_statements để phân tích việc sử dụng index và filter

Index giống như mấy cái bookmark trong sách ấy. Nó giúp tụi mình tìm dữ liệu nhanh hơn. Nhưng nếu tụi mình gắn quá trời bookmark mà chẳng ai xài, hoặc tệ hơn là bookmark dở khiến phải lật từng trang từ đầu đến cuối thì sao? Đó là lúc cần phải phân tích xem index có thực sự hữu ích không.

Query viết dở có thể bỏ qua index, dẫn đến việc phải quét tuần tự (Seq Scan). Điều này làm query chạy chậm và tăng tải cho server. Mục tiêu của tụi mình là hiểu query nào không xài index và tại sao lại như vậy.

Làm sao biết index có được dùng không?

Cùng xem hai vấn đề chính:

1. Index mà tụi mình tạo ra có được dùng không?
2. Nếu có dùng thì nó có hiệu quả không?

Để kiểm tra, tụi mình có thể phân tích thống kê query trong pg_stat_statements, chú ý mấy cột sau:

• rows: số dòng được query xử lý.
• shared_blks_hit: số page đọc từ RAM (không phải từ disk).
• shared_blks_read: số page thực sự đọc từ disk.

Càng ít dòng được xử lý và tỷ lệ shared_blks_hit càng cao so với tổng số page thì index càng ngon.

Ví dụ phân tích index

Giả sử tụi mình có bảng sinh viên:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- Thêm index cho grade_level
CREATE INDEX idx_grade_level ON students(grade_level);

Bây giờ insert dữ liệu để test thử:

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    'SinhVien ' || generate_series(1, 100000),
    'Ho',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

Chạy query để tìm sinh viên có level nhất định:

SELECT *
FROM students
WHERE grade_level = 10;

Kiểm tra trong pg_stat_statements

Sau khi chạy query vài lần, tụi mình có thể kiểm tra thống kê:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

Cách đọc kết quả:

• rows: Nếu query trả về quá nhiều dòng, index có còn ý nghĩa không? Có thể không cần index cho điều kiện ít chọn lọc.
• shared_blks_hit và shared_blks_read: Nếu nhiều page phải đọc từ disk (shared_blks_read), có thể index không hoạt động hoặc dữ liệu nằm ngoài buffer pool.

Tối ưu hóa index

Tạo index mới chỉ là một nửa câu chuyện. Quan trọng là PostgreSQL có thực sự dùng nó không. Đôi khi, dù tụi mình đã cố gắng, database vẫn chọn quét tuần tự thay vì index. Tại sao lại vậy? Cùng tìm hiểu nhé.

Đầu tiên, xem thử vì sao index có thể bị bỏ qua dù nhìn có vẻ rất hợp lý. Sau đó, xem mấy mẹo để "nhắc nhở" database nhớ là mình có index và nên dùng nó.

Nếu index không được dùng thì sao?

Đôi khi PostgreSQL bỏ qua index và chạy quét tuần tự (Seq Scan). Có thể do mấy lý do sau:

1. Điều kiện chọn lọc thấp. Nếu query trả về hơn nửa số dòng của bảng, quét tuần tự có thể nhanh hơn.
2. Kiểu dữ liệu hoặc function. Nếu trong query tụi mình dùng function trên cột đã index, index có thể bị bỏ qua. Ví dụ:

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- Index không được dùng

Trong trường hợp này, có thể viết lại query:

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- Dùng index

3. Kiểu index không phù hợp. Ví dụ, tìm kiếm full-text thì nên dùng index GIN hoặc GiST chứ không phải B-TREE.

4. Thống kê bị lỗi thời. Nếu thống kê cũ, optimizer có thể quyết định sai. Dùng ANALYZE để cập nhật:

   ANALYZE students;
   

Cải thiện query

Quay lại ví dụ của tụi mình. Nếu index không hoạt động, thử mấy cách sau:

1. Đảm bảo query dùng filter có thể tận dụng index: đừng dùng function, ép kiểu lung tung, v.v.
2. Nếu filter trả về nhiều giá trị, cân nhắc xem có cần index không. Nếu query này chạy thường xuyên, thử thay đổi cấu trúc bảng hoặc thêm materialized view.
3. Nếu bị Seq Scan do dữ liệu quá nhiều, thử chia bảng thành partition (PARTITION BY).

Kiểm tra hiệu quả index

Sau khi tối ưu, chạy lại query và kiểm tra thống kê:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

So sánh chỉ số trước và sau. Bạn sẽ thấy số lần đọc từ disk (shared_blks_read) giảm và số lần hit tăng (shared_blks_hit).

Case thực tế

1. Dùng index sai cách

Tụi mình có bảng sản phẩm với trường text description:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- Index cho tìm kiếm full-text
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

Nếu chạy query này:

SELECT *
FROM products
WHERE description ILIKE '%smartphone%';

Index sẽ không được dùng! Lý do là ILIKE không tương thích với GIN. Muốn dùng index thì phải viết lại query như sau:

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('smartphone');

2. Thiếu index chỗ cần thiết

Giả sử query này:

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

chạy quét tuần tự (Seq Scan). Có thể do chưa có index cho birth_date. Tạo index như sau:

CREATE INDEX idx_birth_date ON students(birth_date);

và cập nhật thống kê (ANALYZE students), bạn sẽ thấy query chạy nhanh hơn hẳn.