EXPLAIN là gì và cách dùng nó để phân tích query

Giả sử bạn đang phát triển một app, và một trong những query đột nhiên trở thành cái ngốn tài nguyên nhất trong database của bạn. Bạn bắt đầu thấy app bị lag, lỗi vặt. Đây là lúc EXPLAIN xuất hiện, giúp bạn hiểu được chỗ nào mọi thứ đi sai. Tối ưu hóa query dựa trên phân tích EXPLAIN giúp bạn tiết kiệm tài nguyên, tiết kiệm thời gian và làm user hài lòng hơn với app của bạn.

EXPLAIN — là cách để bạn nhìn vào bên trong PostgreSQL và xem database sẽ thực thi query như thế nào. Nó cho bạn biết có dùng index không hay sẽ quét toàn bộ bảng, optimizer sẽ làm những bước gì, thứ tự ra sao và kết quả trung gian sẽ lớn cỡ nào.

Nói cách khác, EXPLAIN giúp bạn biết trước điều gì sẽ xảy ra khi chạy query: nó "nặng" cỡ nào, dự kiến xử lý bao nhiêu dòng, và sẽ dùng tài nguyên gì. Đây là tool không thể thiếu khi query tự nhiên chạy chậm, và bạn cần biết — tại sao.

EXPLAIN giống như cái đèn pin trong bóng tối: nhờ nó bạn thấy được chuyện gì đang diễn ra bên trong, và chỗ nào mọi thứ bị tắc.

Cú pháp lệnh EXPLAIN

Cùng xem cú pháp cơ bản của lệnh EXPLAIN nhé:

EXPLAIN your_SQL_query;

Ví dụ query:

EXPLAIN SELECT * FROM students WHERE age > 20;

Khi bạn chạy lệnh này, PostgreSQL sẽ không thực thi query. Thay vào đó nó sẽ cho bạn xem execution plan. Kiểu như bản vẽ trước khi xây nhà — rất hữu ích để biết chuyện gì sẽ xảy ra trước khi "đập phá".

Ví dụ output:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Output này nhìn hơi đáng sợ, nhưng đừng lo — mình sẽ giải thích từng phần ngay sau đây.

Phân tích execution plan cơ bản

Cùng phân tích kết quả trên nhé:

1. Seq Scan on students — nghĩa là PostgreSQL sẽ quét toàn bộ bảng students (sequential scan). Không phải lúc nào cũng xấu, nhưng với bảng lớn thì Seq Scan sẽ khá chậm.

2. cost=0.00..35.00 — là ước lượng chi phí để thực hiện thao tác này:

   • Startup Cost: chi phí khởi động thao tác (ở đây là 0.00).
   • Total Cost: tổng chi phí để hoàn thành thao tác (ở đây là 35.00).

3. rows=7 — PostgreSQL dự đoán điều kiện age > 20 sẽ trả về 7 dòng. Đây gọi là "cardinality". Nếu bạn thấy số này kỳ lạ, có thể statistics của bảng đã cũ.

4. width=37 — là kích thước trung bình của một dòng (tính bằng byte).

5. Filter: (age > 20) — nghĩa là PostgreSQL sẽ áp dụng filter này cho từng dòng.

Vậy là output của EXPLAIN cho bạn cái nhìn về chiến lược và giả định của PostgreSQL. Bạn có thể dùng thông tin này để tối ưu hóa.

Tuỳ chọn của lệnh EXPLAIN

Dù output cơ bản của EXPLAIN đã hữu ích, bạn có thể chỉnh nó với các tuỳ chọn sau:

ANALYZE

Với tuỳ chọn này, PostgreSQL không chỉ cho bạn xem execution plan mà còn thực thi query, trả về dữ liệu thực tế. Ví dụ:

EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20;

Nhờ đó bạn có thể so sánh dự đoán của PostgreSQL với thực tế, xem chúng khớp nhau không.

VERBOSE

Hiện thêm chi tiết, hữu ích khi cần phân tích sâu. Ví dụ:

EXPLAIN VERBOSE SELECT * FROM students WHERE age > 20;

BUFFERS

Hiện thông tin về việc sử dụng buffer memory khi thực thi query. Dùng chung với ANALYZE:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM students WHERE age > 20;

COSTS

Nếu bạn muốn ẩn hoặc hiện thông tin về chi phí (cost), dùng tuỳ chọn này:

EXPLAIN (COSTS OFF) SELECT * FROM students WHERE age > 20;

FORMAT

Execution plan có thể xuất ra các định dạng khác như JSON hoặc XML. Ví dụ:

EXPLAIN (FORMAT JSON) SELECT * FROM students WHERE age > 20;

Ví dụ sử dụng EXPLAIN

Giả sử bạn có database university với bảng students. Bạn muốn tìm tất cả sinh viên trên 20 tuổi:

EXPLAIN SELECT * FROM students WHERE age > 20;

Output có thể như sau:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Như đã nói, đây là sequential scan Seq Scan, có thể không hiệu quả với bảng lớn.

Bây giờ tạo index cho cột age và xem plan có thay đổi không:

CREATE INDEX age_index ON students(age);

EXPLAIN SELECT * FROM students WHERE age > 20;

Output:

Index Scan using age_index on students  (cost=0.15..4.23 rows=7 width=37)
  Index Cond: (age > 20)

Bây giờ PostgreSQL dùng index scan (Index Scan), thường nhanh hơn nhiều, nhất là với bảng lớn.

Các câu hỏi và lỗi thường gặp

Tại sao query của mình vẫn chậm dù đã có index?

Có thể query trả về quá nhiều dòng, khiến index không còn lợi thế. Hoặc index chưa tốt hoặc chưa được cập nhật.

Nếu output EXPLAIN khó hiểu thì sao?

Bắt đầu với query đơn giản và tìm hiểu từng node trong execution plan.

Làm sao biết statistics của bảng đã cũ?

Chạy lệnh ANALYZE students

Khi nào dùng EXPLAIN mà không có ANALYZE?

Khi bạn chỉ muốn xem plan mà không thực thi thật (ví dụ với query thay đổi dữ liệu).