Execution Plan trong Postgresql

Execution plan

• Execution Plan (chiến lược thực thi) câu lệnh là giải thuật của dbms để thực thi một câu lệnh SQL. Nó cho ta biết là dbms thực thi câu lệnh SQL đó bằng cách nào.

• Thông thường, các dbms đều có câu lệnh để xem chi tiết chiến lược thực thi của 1 câu SQL. Đối với Postgresql thì là câu lệnh Explain.

• Khi câu query chậm, điều đầu tiên là hãy xem Execution Plan của nó.

Explain

• Câu lệnh giải thích từng bước mà postgresql thực thi câu lệnh với các thông số cost, rows, width

• để sử dụng câu lệnh, ta bắt đầu đặt Explain trước khi thực hiện:

  EXPLAIN SELECT * FROM ENGINEER;

  

• Giải thích:

  • sử dụng sequence scan (quét full bảng) trên bảng engineer

  • cost: giá trị ước tính để thực thi câu lệnh, đơn vị này là quy ước do postgresql đặt. ở ví dụ trên có thể đấy cost: 0.00..1996.00. giá trị đầu tiên là cost cần để lấy row đầu tiên

  • rows: số lượng row mà Postgres cần scan để thực thi query

  • width: kích cỡ trung bình của 1 row tại stage đó (tính theo byte)

Cost

• là chi phí khi thực thi câu lệnh. cost không phải tính theo mili giây, mà nó tính theo đơn vị trừu tượng do postgres tự quy ước.

• cost càng thấp, tốc độ câu query càng cao.

• cost được tính dựa trên các tham số cấu hình, ví dụ 1 số đơn vị cost base:

  • seq_page_cost (Sequential Page Cost): chi phí đọc tuần tự theo từng page (page là đơn vị lưu trữ vật lý nhỏ nhất của database), mặc định mỗi page đọc hết 1.0 đơn vị. db đọc tuần tự 5 page thì tốn 5.0 đơn vị.

  • random_page_cost (Random Page Cost): chi phí đọc ngẫu nhiên một page bất kì (như là đọc từ index). Vì đọc ngẫu nhiên sẽ chậm hơn đọc tuần tự, nên giá trị mặc định đọc 1 page ngẫu nhiên là 4.0 đơn vị.

  • cpu_tuple_cost (CPU Tuple Cost): chi phí xử lí 1 row bằng cpu ở trên RAM. giá trị mặc định hết 0.01

• cost thường được thể hiện trong trong 1 khoảng từ a đến b, với a là cost để fetch row đầu tiên và b là cost để fetch toàn bộ data trong query.

• ví dụ cách tính cost:

  

  • cost từ 0.00 đến 3372572.84

• để tính toán, ta cần biết bảng có bao nhiêu page, bao nhiêu rows, rồi tính dựa trên các tham số cấu hình của postgres

  

Một số tham số dùng với Explain

Analyze

• Analyze thực thi luôn câu lệnh rồi trả về kết quả thực tế của câu lệnh chứ không chỉ ước tính.

• lưu ý: vì analyze sẽ thực thi câu lệnh nên với câu lệnh DML như create, update, delete thì cần phải để vào cụm BEGIN...ROLLBACK

  

• Một số giá trị trả về:

  • actual time: giống như cost, nó thể hiện thời gian cần để trả về row đầu tiên và toàn bộ row.

  • loops: số vòng lặp

  • planning time: thời gian lên kế hoạch cho query, bao gồm parsing, estimate execution plan.

  • execution time: thời tgian thực thi toàn bộ câu lệnh. execution time

Buffer

• cho ta biết được khối lượng công việc liên quan đến IO khi thực hiện 1 câu lệnh query

• data không thực sự đọc ghi thẳng từ db mà nó thông qua buffer (trên RAM), điều này giúp tăng tốc câu truy vấn, giảm stress tới db.

  

• Các thông số có trong buffer:

  • shared hit: số page đọc được từ buffer mà không cần đọc từ disk. shared hit càng cao chứng tỏ câu query càng nhanh vì nó hạn chế physical read (đọc từ disk)

  • shared read: số page cần đọc từ disk rồi load vào buffer. giá trị càng cao, tức là đọc từ disk nhiều, càng chậm.

  • shared dirtied: số page bị thay đổi bởi các câu lệnh DML, database ko ghi trực tiếp xuống db mà nó ghi từ buffer trước, sau đó mới sync xuống db.

  • shared written: số page dirtied trước đó phải ghi xuống disk do buffer hiện tại đã đầy nên cần loại bỏ để sử dụng cho câu query hiện tại