Giải thích kế hoạch thực thi: đọc và phân tích các node (`Seq Scan`, `Index Scan`, `Hash Join`)

Hôm nay tụi mình sẽ tìm hiểu node trong execution plan của PostgreSQL là gì, cách đọc nó và quan trọng nhất là làm sao biết khi nào mọi thứ không ổn. Bạn sẽ hiểu tại sao database của bạn đôi khi lại thích dùng Seq Scan tốn tài nguyên, dù đã có index rồi, thậm chí có nhiều index luôn ấy.

Khi PostgreSQL xây dựng execution plan cho truy vấn, nó sẽ chia nhỏ thành các bước gọi là node. Mỗi node giống như một "bước" mà server database thực hiện để xử lý truy vấn của bạn. Các loại node chính gồm có:

Sequential Scan (Seq Scan)

Seq Scan hay quét tuần tự là cách đơn giản nhất để lấy dữ liệu từ bảng. PostgreSQL sẽ đọc từng dòng một trong bảng và kiểm tra xem nó có thỏa điều kiện truy vấn của bạn không.

Khi nào dùng Seq Scan?
Seq Scan được dùng nếu:

• Bảng không có index phù hợp để tăng tốc truy vấn.
• Điều kiện lọc quá chung chung, index không giúp ích gì (ví dụ: lấy hơn 50% dữ liệu).
• PostgreSQL nghĩ rằng đọc tuần tự bảng sẽ nhanh hơn dùng index (đôi khi đúng với bảng rất nhỏ).

EXPLAIN SELECT * FROM students WHERE age > 18;

Ví dụ kết quả:

Seq Scan on students  (cost=0.00..35.50 rows=10 width=50)
  Filter: (age > 18)

Chú ý Seq Scan on students — PostgreSQL báo là nó sẽ đọc toàn bộ bảng "students".

Vấn đề với Seq Scan: Nếu bảng to, quét tuần tự sẽ rất lâu luôn.

Index Scan

Index Scan là quét dữ liệu bằng index. Khi bạn tạo index trong PostgreSQL, nó giống như làm mục lục cho bảng vậy. Nếu truy vấn tận dụng được index, PostgreSQL sẽ chỉ đọc những phần cần thiết thay vì cả bảng.

Khi nào dùng Index Scan?

• Truy vấn có điều kiện lọc trên cột đã được index (ví dụ: WHERE).
• Dùng các phép so sánh như =, <, >, BETWEEN v.v.

CREATE INDEX idx_students_age ON students(age);

EXPLAIN SELECT * FROM students WHERE age = 18;

Ví dụ kết quả:

Index Scan using idx_students_age on students  (cost=0.15..8.27 rows=1 width=50)
  Index Cond: (age = 18)

Ở đây Index Scan using idx_students_age cho thấy PostgreSQL đang dùng index idx_students_age. Đọc từng dòng của bảng được thay bằng truy cập nhanh qua index.

Ưu điểm Index Scan:

• Tăng tốc truy vấn rõ rệt với bảng lớn.
• Giảm lượng dữ liệu phải đọc từ ổ cứng.

Vấn đề Index Scan:
Nếu truy vấn trả về quá nhiều dữ liệu (ví dụ: hơn nửa bảng), dùng index có khi còn chậm hơn Seq Scan nữa.

Hash Join

Hash Join dùng để join hai bảng dựa trên điều kiện (ví dụ: ON students.course_id = courses.id). PostgreSQL sẽ tạo một hash table cho một bảng (bảng nhỏ hơn) rồi dùng nó để tìm dòng khớp trong bảng còn lại.

Khi nào dùng Hash Join?

• Khi join bảng bằng INNER JOIN, LEFT JOIN v.v.
• Khi PostgreSQL thấy Hash Join hiệu quả hơn các kiểu join khác.

EXPLAIN
SELECT * 
FROM students 
JOIN courses ON students.course_id = courses.id;

Ví dụ kết quả:

Hash Join  (cost=25.00..50.00 rows=10 width=100)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..20.00 rows=10 width=50)
  -> Hash  (cost=15.00..15.00 rows=10 width=50)
       -> Seq Scan on courses  (cost=0.00..15.00 rows=10 width=50)

Ở đây Hash Join nối hai bảng. Chú ý là PostgreSQL sẽ chạy Seq Scan cho cả hai bảng trước, rồi mới tạo hash table (Hash).

Ưu điểm Hash Join:

• Xử lý nhanh với bảng vừa phải.
• Hiệu quả khi join bảng có nhiều dòng.

Vấn đề Hash Join:
Nếu hash table to quá, vượt quá bộ nhớ, PostgreSQL sẽ phải ghi ra ổ cứng, làm join chậm hẳn luôn.

Ví dụ phân tích execution plan

Cùng xem một ví dụ thực tế nhé.

Truy vấn:

EXPLAIN ANALYZE
SELECT *
FROM students
JOIN courses ON students.course_id = courses.id
WHERE students.age > 18;

Kết quả:

Hash Join  (cost=35.00..75.00 rows=5 width=100) (actual time=1.00..2.50 rows=5 loops=1)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..40.00 rows=10 width=50) (actual time=0.50..1.00 rows=7 loops=1)
        Filter: (age > 18)
        Rows Removed by Filter: 3
  -> Hash  (cost=25.00..25.00 rows=5 width=50) (actual time=0.30..0.30 rows=5 loops=1)
       -> Seq Scan on courses  (cost=0.00..20.00 rows=5 width=50) (actual time=0.20..0.25 rows=5 loops=1)
Planning Time: 0.50 ms
Execution Time: 3.00 ms

Giải thích:

1. Hash Join: Node chính. PostgreSQL nối hai bảng students và courses.
   • actual time: từ 1.00 đến 2.50 ms.
   • rows=5: truy vấn trả về 5 dòng.
2. Các node lồng bên trong:
   • Seq Scan on students: đọc tuần tự bảng students và áp dụng filter (age > 18).
   • Rows Removed by Filter = 3: 3 dòng không thỏa điều kiện.
   • Hash: PostgreSQL tạo hash table cho bảng courses.

So sánh và chọn node

Khi bạn phân tích execution plan, điều quan trọng là hiểu tại sao PostgreSQL lại chọn cách xử lý dữ liệu như vậy. Đôi khi bạn phải can thiệp, ví dụ: thêm index hoặc viết lại truy vấn. Một vài mẹo nhỏ:

• Nếu thấy Seq Scan trên bảng lớn, hãy nghĩ đến việc tạo index.
• Nếu Hash Join chậm, kiểm tra bộ nhớ cấp cho PostgreSQL.
• Dùng EXPLAIN ANALYZE để so sánh giá trị dự đoán và thực tế (rows, time).

Đến đây bạn đã có kiến thức cơ bản về cách đọc execution plan và giải thích các node rồi. Ở các bài sau tụi mình sẽ nói về các vấn đề tối ưu hóa thường gặp và cách xử lý chúng nhé.