Tối ưu câu lệnh SQL trong PostgreSQL
1. VẤN ĐỀ
2. PHÂN TÍCH
1️⃣ Cost 0.14..88754.08 nghĩa là gì?
Trong PostgreSQL:
cost = estimated work units (không phải thời gian)
Format:
cost = start_cost .. total_cost
Với plan của anh
cost=0.14..88754.08
| Giá trị | Ý nghĩa |
|---|---|
| 0.14 | chi phí để lấy row đầu tiên |
| 88754.08 | chi phí ước lượng tổng để trả toàn bộ kết quả |
Điều quan trọng
Cost không phải ms
Cost chỉ là thước đo nội bộ của optimizer
Ví dụ:
| cost | thực tế |
|---|---|
| 100 | có thể 1ms |
| 10000 | có thể 100ms |
| 80000 | có thể 600ms |
Optimizer dùng cost để so sánh các plan với nhau.
2️⃣ Actual time 0.038..641.838 là gì?
Format:
actual time = first_row_time .. total_time
Trong plan của anh
actual time=0.038..641.838
| Giá trị | Ý nghĩa |
|---|---|
| 0.038 ms | thời gian lấy row đầu tiên |
| 641.838 ms | thời gian thực thi toàn bộ node |
Tức là:
query mất khoảng ~642 ms
3️⃣ Vì sao query của anh mất ~642 ms?
Nhìn vào plan:
Append
Subplans Removed: 45
→ table partition
Sau đó:
Seq Scan on isdn_retry_extend_202604
actual time=641.740..641.740
Rows Removed by Filter: 1636514
💥 Đây chính là nguyên nhân.
PostgreSQL phải:
scan 1,636,514 rows
để tìm:
rows=0
4️⃣ Vì sao PostgreSQL lại Seq Scan partition đó?
Partition 202604 không có index phù hợp
Trong plan:
Index Scan using ... on 202603
Index Scan using ... on 202605
Nhưng:
Seq Scan on 202604
→ partition 202604 không có index hoặc index không phù hợp
5️⃣ Cách đọc EXPLAIN PLAN nhanh (phương pháp DBA)
Chỉ cần nhìn 4 dòng sau:
Bước 1 — tìm node tốn thời gian nhất
Tìm dòng có:
actual time = lớn nhất
Trong plan của anh:
Seq Scan on 202604
actual time=641.740
→ điểm nghẽn
Bước 2 — xem Rows Removed by Filter
Rows Removed by Filter: 1636514
→ scan quá nhiều row
Bước 3 — xem type scan
| Scan type | Ý nghĩa |
|---|---|
| Index Scan | tốt |
| Bitmap Index Scan | ok |
| Seq Scan | nguy hiểm nếu table lớn |
Bước 4 — xem index condition
Index Cond: isdn='938630490'
Nếu không có Index Cond → index không được dùng.
6️⃣ Tối ưu query này thế nào?
Query:
SELECT *
FROM isdn_retry_extend
WHERE retry_datetime BETWEEN now() AND now() + interval '60 day'
AND isdn = '938630490'
AND status IN ('2','1')
Index hiện tại:
isdn_retry_extend_202603_idx1
Có vẻ index chỉ có:
(isdn)
Nhưng query có thêm:
retry_datetime
status
Index tối ưu hơn
Khuyến nghị:
CREATE INDEX idx_retry_extend_opt
ON isdn_retry_extend (isdn, retry_datetime, status);
Hoặc tốt hơn:
CREATE INDEX idx_retry_extend_opt
ON isdn_retry_extend (isdn, retry_datetime)
WHERE status IN ('1','2');
→ partial index
7️⃣ Sau khi index đúng, plan sẽ thành
Hiện tại:
Seq Scan
641 ms
Sau tối ưu:
Index Scan
<1 ms
8️⃣ Quy tắc vàng đọc Explain Plan PostgreSQL
Chỉ cần nhớ 5 bước này:
1️⃣ Tìm node có actual time lớn nhất
2️⃣ Kiểm tra scan type (Seq vs Index)
3️⃣ Xem Rows Removed by Filter
4️⃣ Kiểm tra Index Cond
5️⃣ So sánh cost vs actual
9️⃣ Một mẹo/bí kíp DBA cực quan trọng
Luôn chạy:
EXPLAIN (ANALYZE, BUFFERS)
Ví dụ:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
Nó sẽ cho thêm:
shared hit
shared read
→ biết query đọc bao nhiêu block disk.
Bí kíp đọc Explain Plan nhanh chóng và hiệu quả:
Để không bị "ngợp" giữa một rừng chữ, bạn hãy áp dụng quy tắc sau:
A. Đọc từ dưới lên trên, từ trong ra ngoài
Cấu trúc Explain Plan là một cái cây (Tree). Những dòng thụt lề vào sâu nhất là những việc được làm đầu tiên. Hãy tìm những chỗ có chi phí lớn nhất hoặc thời gian thực tế dài nhất.
B. Tập trung vào "Sự khác biệt" (The Gap)
Cost vs Actual Time: Đôi khi Cost rất thấp nhưng Actual Time lại cao (do tranh chấp tài nguyên, lock...).
Rows vs Actual Rows: Nếu PostgreSQL ước tính (rows) là 1 mà thực tế chạy ra 1,000,000 dòng, nghĩa là thống kê (statistics) của database đang bị cũ, dẫn đến nó chọn sai chiến thuật chạy (Plan).
C. Nhận diện các "Từ khóa nguy hiểm"
Seq Scan: Quét toàn bộ bảng. Nếu bảng lớn, đây là kẻ thù số 1.
Filter: Sau khi quét xong mới lọc lại. Rất tốn kém. Nên chuyển thành Index Cond (lọc bằng index).
Rows Removed by Filter: Nếu số này quá lớn (như trong hình của bạn là 1,636,514 dòng bị loại bỏ), nghĩa là bạn đang thiếu Index hoặc Index không hiệu quả.
D. Kiểm tra việc loại bỏ phân vùng (Partition Pruning)
Trong hình của bạn, hệ thống báo Subplans Removed: 45. Điều này tốt, nó đã loại bỏ được 45 bảng không liên quan. Tuy nhiên, nó vẫn phải quét 3 phân vùng (202603, 202604, 202605).
Giải pháp nhanh cho trường hợp của bạn:
Phân vùng isdn_retry_extend_202604 của bạn đang bị Seq Scan.
Lời khuyên: Hãy kiểm tra xem tại sao bảng này không dùng được Index giống như hai bảng còn lại. Có thể do Index trên bảng
202604bị hỏng, chưa được tạo, hoặc dữ liệu phân bổ khiến Postgres quyết định quét toàn bộ cho nhanh (nhưng thực tế lại chậm).
3. GIẢI PHÁP
Khuyến nghị:
1.Tạo index cho partition 202604:
CREATE INDEX isdn_retry_extend_202604_idx1 ON public.isdn_retry_extend_202604 USING btree (isdn, ((insert_date)::date) DESC NULLS LAST) WITH (fillfactor='100');
CREATE UNIQUE INDEX isdn_retry_extend_202604_pkey ON public.isdn_retry_extend_202604 USING btree (id, retry_datetime);
2.Có thể tạo index ở bảng cha khi đó sẽ tạo cho toàn bộ bảng con
CREATE INDEX idx_retry_extend_opt
ON isdn_retry_extend (isdn, retry_datetime, status);
Hoặc tốt hơn:
CREATE INDEX idx_retry_extend_opt
ON isdn_retry_extend (isdn, retry_datetime)
WHERE status IN ('1','2');Kết quả: partition 202604 đã có indexCheck lại:SELECT c.relname AS partition_name, i.relname AS index_name, pg_get_indexdef(i.oid) AS index_def FROM pg_class c JOIN pg_inherits inh ON inh.inhrelid = c.oid JOIN pg_class p ON p.oid = inh.inhparent LEFT JOIN pg_index ix ON ix.indrelid = c.oid LEFT JOIN pg_class i ON i.oid = ix.indexrelid WHERE p.relname = 'isdn_retry_extend' ORDER BY c.relname, i.relname;
TƯ VẤN: Click Here hoặc Hotline/Zalo 090.29.12.888
=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
