Đọc EXPLAIN ANALYZE trong PostgreSQL cho người mới

1. Tổng quan

EXPLAIN ANALYZE là công cụ quan trọng nhất để chẩn đoán query chậm trong PostgreSQL. Bài này giúp bạn đọc hiểu từng phần trong output: node type, cost, rows, actual time, và biết khi nào một query đang có vấn đề. Đọc xong, bạn sẽ tự tin đọc execution plan và biết nên thêm index hay sửa query ở đâu.

2. EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN chỉ hiển thị kế hoạch dự kiến (estimated plan) dựa trên statistics, không thực thi query. EXPLAIN ANALYZE thực sự chạy query và đo thời gian thực tế.

-- Chỉ xem plan dự kiến, không chạy query
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Chạy thật, đo thời gian thực tế
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Lưu ý: EXPLAIN ANALYZE chạy thật query, nên nếu đó là UPDATE hoặc DELETE, dữ liệu sẽ bị thay đổi. Dùng trong transaction và ROLLBACK nếu chỉ muốn test:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'SHIPPED' WHERE id = 1;
ROLLBACK;

3. Cấu trúc một node trong plan

Mỗi dòng trong output là một node — một bước xử lý dữ liệu. Ví dụ với bảng orders:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 99992
Planning Time: 0.120 ms
Execution Time: 12.465 ms

Giải thích từng phần:

• Seq Scan on orders: loại node, ở đây là quét tuần tự toàn bảng.
• cost=0.00..1834.00: chi phí ước lượng (startup cost .. total cost), đơn vị tương đối, không phải ms.
• rows=10: số dòng ước lượng planner nghĩ sẽ trả về.
• width=72: kích thước trung bình mỗi dòng (byte).
• actual time=0.015..12.430: thời gian thực tế (ms) — bắt đầu trả dòng đầu tiên .. hoàn thành toàn bộ node.
• rows=8 loops=1: số dòng thực tế trả về, và node này chạy bao nhiêu lần (loops).
• Rows Removed by Filter: số dòng bị quét nhưng không khớp filter — con số này lớn là dấu hiệu thiếu index.

4. So sánh Seq Scan và Index Scan

4.1. Khi chưa có index

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
Execution Time: 12.465 ms

PostgreSQL phải đọc toàn bộ bảng rồi lọc từng dòng. Với bảng lớn, đây là nguyên nhân chính gây chậm.

4.2. Sau khi thêm index

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
Execution Time: 0.045 ms

So sánh: cost giảm từ 1834 xuống 8.31, execution time giảm từ 12.465 ms xuống 0.045 ms. Đây là ví dụ kinh điển cho thấy index giúp PostgreSQL nhảy thẳng tới dòng cần, không cần quét toàn bảng.

Lưu ý: PostgreSQL không phải lúc nào cũng dùng index kể cả khi có sẵn. Nếu query trả về phần lớn bảng (ví dụ > 20-30% số dòng), planner thường chọn Seq Scan vì nó rẻ hơn so với việc nhảy ngẫu nhiên qua index rồi quay lại bảng (random I/O).

5. Đọc plan có JOIN

Khi query có join, plan sẽ có nhiều node lồng nhau. Node con thực thi trước, kết quả được đưa lên node cha.

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'VN';

Hash Join  (cost=45.50..520.75 rows=200 width=40)
           (actual time=1.200..8.900 rows=195 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o
        (cost=0.00..420.00 rows=10000 width=12)
        (actual time=0.010..3.500 rows=10000 loops=1)
  ->  Hash  (cost=40.00..40.00 rows=440 width=36)
            (actual time=1.100..1.100 rows=440 loops=1)
        ->  Seq Scan on customers c
              (cost=0.00..40.00 rows=440 width=36)
              (actual time=0.005..0.700 rows=440 loops=1)
              Filter: (country = 'VN'::text)
Execution Time: 9.050 ms

Cách đọc: Hash Join là node ngoài cùng (root), nó nhận dữ liệu từ hai node con (Seq Scan on orders và Hash chứa Seq Scan on customers). PostgreSQL build hash table từ bảng customers đã lọc, sau đó quét orders và match vào hash table. Thời gian actual time của node cha đã bao gồm thời gian của các node con bên trong.

6. Dấu hiệu cảnh báo cần chú ý

6.1. Estimate lệch xa thực tế

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=9500 loops=1)

Planner dự đoán rows=10 nhưng thực tế trả về 9500. Chênh lệch lớn như vậy thường do statistics cũ. Khắc phục bằng:

ANALYZE orders;

Lệnh này cập nhật lại thống kê phân phối dữ liệu để planner ước lượng chính xác hơn.

6.2. Loops lớn bất thường (Nested Loop)

Nested Loop  (cost=0.43..5000.00 rows=1 width=40)
             (actual time=0.020..450.000 rows=1 loops=1)
  ->  Seq Scan on customers c (actual time=0.010..0.015 rows=1 loops=1)
  ->  Index Scan on orders o
        (actual time=0.002..0.450 rows=1 loops=1000)

loops=1000 nghĩa là node Index Scan on orders được gọi lại 1000 lần (ứng với 1000 dòng từ node ngoài). Nếu mỗi lần gọi tốn thời gian đáng kể, tổng chi phí sẽ rất lớn dù actual time của node đó nhìn riêng lẻ có vẻ nhỏ. Hãy nhân actual time với loops để ước tính tổng thời gian thật của node.

6.3. Dùng BUFFERS để xem I/O

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=4
Execution Time: 0.045 ms

shared hit=4 nghĩa là 4 block được đọc từ cache (bộ nhớ), không chạm đĩa. Nếu thấy shared read lớn, nghĩa là dữ liệu phải đọc từ disk — dấu hiệu cache miss, có thể do shared_buffers quá nhỏ hoặc truy vấn quét quá nhiều dữ liệu lạnh.

7. Dùng EXPLAIN trong code Java/Spring

Khi debug performance trong ứng dụng Spring Boot, bạn có thể log plan trực tiếp qua JdbcTemplate:

@Service
public class QueryDiagnosticsService {

    private final JdbcTemplate jdbcTemplate;

    public QueryDiagnosticsService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<String> explainAnalyze(String sql) {
        String query = "EXPLAIN (ANALYZE, FORMAT TEXT) " + sql;
        return jdbcTemplate.queryForList(query, String.class);
    }
}

explainAnalyze nhận câu SQL gốc, bọc thêm EXPLAIN (ANALYZE, FORMAT TEXT) và trả về từng dòng plan dưới dạng String. Dùng trong môi trường dev/staging để log plan khi phát hiện query chậm.

@Test
void explainAnalyze_shouldReturnPlanWithExecutionTime() {
    QueryDiagnosticsService service = new QueryDiagnosticsService(jdbcTemplate);

    List<String> plan = service.explainAnalyze(
        "SELECT * FROM orders WHERE customer_id = 42"
    );

    assertThat(plan)
        .isNotEmpty()
        .anyMatch(line -> line.contains("Execution Time"));
}

Test này không assert con số cụ thể (vì thời gian thay đổi theo máy), chỉ xác nhận plan trả về có chứa dòng Execution Time — đủ để kiểm tra rằng câu lệnh chạy thành công và trả plan hợp lệ.

Lưu ý: Không nên chạy EXPLAIN ANALYZE trên production cho query có INSERT/UPDATE/DELETE mà không bọc transaction + ROLLBACK, vì nó sẽ thực thi thật.

8. Khi nào KHÔNG cần EXPLAIN ANALYZE

• Query đã nhanh và ổn định (dưới vài ms, ít dữ liệu) — không cần tối ưu thêm, tốn effort không đáng.
• Đang ở giai đoạn thiết kế schema sớm, chưa có đủ dữ liệu thật để statistics phản ánh đúng thực tế (ít dòng thì Seq Scan luôn thắng Index Scan).
• Query chạy trên bảng tạm (TEMP TABLE) nhỏ trong một lần duy nhất — overhead phân tích không đáng so với lợi ích.

9. Kết luận

EXPLAIN ANALYZE cho bạn thấy sự khác biệt giữa những gì PostgreSQL nghĩ sẽ xảy ra (cost, rows ước lượng) và những gì thực sự xảy ra (actual time, rows thật). Khi đọc plan, hãy chú ý thứ tự thực thi (từ trong ra ngoài), loại node (Seq Scan hay Index Scan), và đặc biệt là độ lệch giữa estimate và actual — đó là chìa khóa để biết query cần thêm index, cần ANALYZE lại bảng, hay cần viết lại logic.