Query Planning & Performance TuningQuery Planning & Performance Tuning

Thuộc bộ kiến thức PostgreSQL DBA Roadmap.

Tổng quan

Mỗi câu lệnh SQL bạn gửi cho PostgreSQL chỉ mô tả cái bạn muốn, chứ không nói làm thế nào để lấy được nó. Giữa văn bản SQL và các dòng dữ liệu trả về là query planner (còn gọi là optimizer): thành phần trong backend liệt kê các cách thực thi khả dĩ — dùng index nào (nếu có), join các bảng theo thứ tự nào, dùng thuật toán join nào cho mỗi cặp bảng, sắp xếp trước hay sau khi join — rồi chọn phương án mà nó ước tính là rẻ nhất. “Rẻ nhất” được đo bằng một đơn vị chi phí trừu tượng, tính từ statistics: kích thước bảng, phân bố giá trị của các cột, mức độ tương quan giữa thứ tự vật lý của dòng dữ liệu và giá trị cột, cùng các chi phí tương đối (có thể cấu hình) của sequential I/O, random I/O và CPU. Điều quan trọng là planner không bao giờ nhìn vào dữ liệu thật để ra quyết định — nó nhìn vào một bản tóm tắt của dữ liệu được chụp lại lần cuối ANALYZE chạy. Sự khác biệt này chính là nguồn gốc của gần như mọi sự cố kiểu “query này trước nhanh giờ chậm” mà một DBA phải điều tra.

Bài này nói về vòng lặp thực hành mà mọi PostgreSQL DBA lặp lại liên tục: đọc một plan, hiểu vì sao planner chọn nó, xác minh ước tính so với thực tế, rồi sửa nguyên nhân gốc rễ — có thể là thiếu index, statistics cũ, một tham số bộ nhớ cấu hình sai kích thước, hoặc một dạng query khiến planner “bó tay”. EXPLAIN và EXPLAIN ANALYZE là hai lệnh giúp nhìn thấy plan; pg_stat_statements là công cụ cho biết query nào đáng để dành sự chú ý này ngay từ đầu. Kết hợp lại, chúng biến việc tuning hiệu năng từ đoán mò thành một quy trình chẩn đoán có thể lặp lại.

Kiến thức nền tảng

Planner thực sự làm gì

Với một query cho trước, planner của PostgreSQL:

1. Parse và viết lại query (mở rộng view, áp dụng rule) thành một cây query.
2. Sinh ra một tập các plan ứng viên — các tổ hợp của phương thức scan (sequential scan, index scan, index-only scan, bitmap scan), phương thức join (nested loop, hash join, merge join), và thứ tự join.
3. Ước tính cost của từng ứng viên bằng hai con số: startup cost (công việc phải làm trước khi trả về dòng đầu tiên) và total cost (công việc để trả về toàn bộ dòng), cả hai đều tính theo đơn vị tùy ý, được hiệu chỉnh gần đúng theo “một lần đọc trang tuần tự.”
4. Chọn plan có total cost thấp nhất (ở mức query cấp cao nhất) và thực thi nó.

Với các query có nhiều hơn một vài bảng, việc thử hết mọi thứ tự join là tốn kém theo cấp số tổ hợp, nên vượt quá join_collapse_limit (mặc định 8) bảng, planner chuyển sang Genetic Query Optimizer (GEQO), tìm kiếm theo kiểu heuristic thay vì duyệt cạn. Điều này đáng biết khi plan của một query join rất rộng dường như thay đổi giữa các lần chạy — GEQO có yếu tố ngẫu nhiên.

Đầu vào của mô hình ước tính chi phí

Mô hình cost dựa trên:

• Kích thước bảng — pg_class.relpages và reltuples, được cập nhật bởi ANALYZE/VACUUM.
• Statistics của cột — pg_stats (xây trên pg_statistic): các giá trị phổ biến nhất cùng tần suất, histogram cho phần phân bố còn lại, số lượng giá trị khác nhau (n_distinct), tỷ lệ null, và correlation vật lý/logic.
• Hằng số chi phí của planner — seq_page_cost (mặc định 1.0), random_page_cost (mặc định 4.0 — nên giảm xuống gần 1.1 trên storage SSD/NVMe), cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost.
• Bối cảnh bộ nhớ — work_mem và effective_cache_size, quyết định liệu một sort/hash có vừa trong bộ nhớ hay không, và liệu một index có khả năng nằm sẵn trong cache hay không.

Mọi thứ về sau — chọn index-vs-seqscan, thuật toán join, thứ tự join — đều là phép tính số học trên các đầu vào này. Khi một plan có vẻ sai, cách sửa hầu như luôn là chỉnh lại một trong các đầu vào này (làm mới statistics, thêm index, tăng work_mem) thay vì cố “đấu” với planner bằng hint, vì PostgreSQL cố tình không có cú pháp query hint.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN hiển thị plan được chọn cùng chi phí ước tính mà KHÔNG chạy query:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                              QUERY PLAN
------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..104166.00 rows=25 width=97)
   Filter: (customer_id = 42)

EXPLAIN ANALYZE thực sự thực thi câu lệnh (được bọc lại sao cho tác dụng phụ của INSERT/UPDATE/DELETE vẫn xảy ra — hãy bọc trong transaction rồi rollback nếu không muốn vậy) và báo cáo thời gian, số dòng thực tế bên cạnh cùng các ước tính chi phí:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

                                              QUERY PLAN
--------------------------------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=25 width=97)
                                                     (actual time=0.031..0.048 rows=23 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.112 ms
 Execution Time: 0.071 ms

(Khi có index, planner đã chuyển từ Seq Scan sang Index Scan — xem phần “Đọc một plan thực tế” bên dưới để hiểu vì sao.)

Đọc dòng cost (cost=0.43..8.45 rows=25 width=97):

Trường	Ý nghĩa
0.43 (startup cost)	Chi phí ước tính trước khi dòng đầu tiên được trả về — với index scan, chủ yếu là chi phí đi xuống B-tree
8.45 (total cost)	Chi phí ước tính để trả về toàn bộ số dòng mà node này sinh ra
rows=25	Số dòng ước tính node này sẽ trả ra
width=97	Độ rộng trung bình ước tính của một dòng, tính bằng byte

Và trong dòng actual (actual time=0.031..0.048 rows=23 loops=1):

Trường	Ý nghĩa
0.031	Số mili-giây thực tế cho tới khi dòng đầu tiên sẵn sàng
0.048	Số mili-giây thực tế cho tới khi toàn bộ dòng được trả về
rows=23	Số dòng thực tế được sinh ra
loops=1	Số lần node này được thực thi (>1 với vế trong của một nested loop)

Thói quen quan trọng nhất khi đọc output EXPLAIN ANALYZE là so sánh rows ước tính với rows thực tế trên cùng một node. rows=25 ước tính so với rows=23 thực tế (ở trên) là không đáng lo — đủ gần để cho thấy lựa chọn của planner là hợp lý. Một node hiện rows=50 ước tính nhưng rows=125000 thực tế thì không phải sai số làm tròn; nó nghĩa là planner đã có một bức tranh sai lệch cơ bản về dữ liệu khi chọn plan này, và đây là dấu hiệu phổ biến nhất của vấn đề statistics — cũ, quá thô, hoặc (với các cột tương quan nhau) đơn giản là không thể mô hình hóa được nếu thiếu extended statistics. Hãy truy tìm sự lệch pha này trước khi động vào bất kỳ tham số nào khác.

Khái niệm chính

Đọc một plan thực tế: các loại node scan

Xét một bảng orders với 5.000.000 dòng, có status text, customer_id int, created_at timestamptz, total_cents int, và có index trên customer_id và status.

Seq Scan — đọc mọi trang của bảng/heap và lọc trong bộ nhớ. Đúng đắn khi phần lớn dòng đều thỏa điều kiện, hoặc bảng đủ nhỏ để chi phí random I/O của index không đáng:

EXPLAIN ANALYZE SELECT * FROM orders WHERE total_cents > 0;

 Seq Scan on orders  (cost=0.00..104166.00 rows=4998211 width=97) (actual time=0.011..612.334 rows=5000000 loops=1)
   Filter: (total_cents > 0)
 Planning Time: 0.065 ms
 Execution Time: 845.221 ms

Index Scan — đi xuống B-tree để tìm các TID khớp, rồi lấy từng dòng khớp từ heap. Tốt nhất khi ít dòng thỏa điều kiện:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=25 width=97) (actual time=0.031..0.048 rows=23 loops=1)
   Index Cond: (customer_id = 42)

Index Only Scan — giống Index Scan, nhưng mọi cột được yêu cầu đều đã có sẵn trong chính index, nên không cần truy cập heap chút nào miễn là visibility map đánh dấu các trang đó “all visible” (các phiên bản dòng ở đó được đảm bảo nhìn thấy được với mọi transaction, tức không bị update/delete gần đây):

EXPLAIN ANALYZE SELECT customer_id FROM orders WHERE customer_id = 42;

 Index Only Scan using idx_orders_customer_id on orders  (cost=0.43..4.45 rows=25 width=4) (actual time=0.020..0.028 rows=23 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
 Planning Time: 0.078 ms
 Execution Time: 0.045 ms

Heap Fetches: 0 nghĩa là visibility map đã làm tốt nhiệm vụ. Nếu Heap Fetches cao trên một bảng có nhiều thao tác ghi gần đây, thường là do autovacuum chưa kịp bắt kịp — chạy VACUUM để xây lại visibility map.

Bitmap Heap Scan / Bitmap Index Scan — một giải pháp trung gian cho độ chọn lọc vừa phải: xây một bitmap trong bộ nhớ đánh dấu các trang heap khớp từ index (Bitmap Index Scan), rồi truy cập các trang đó một lần theo thứ tự vật lý (Bitmap Heap Scan), tránh cả full table scan lẫn random I/O theo từng dòng:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';

 Bitmap Heap Scan on orders  (cost=4523.28..98219.10 rows=249876 width=97) (actual time=18.223..612.884 rows=248112 loops=1)
   Recheck Cond: (status = 'shipped'::text)
   Heap Blocks: exact=45213
   ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..4460.79 rows=249876 width=0) (actual time=17.902..17.902 rows=248112 loops=1)
         Index Cond: (status = 'shipped'::text)
 Planning Time: 0.089 ms
 Execution Time: 625.451 ms

Nếu bitmap cần theo dõi nhiều trang hơn mức work_mem cho phép, PostgreSQL sẽ giảm cấp một cách “mềm” sang bitmap lossy (ở mức trang thay vì mức dòng), buộc phải Recheck Cond với mọi dòng trên trang đó — thể hiện qua Heap Blocks: exact=N lossy=M với M > 0, một triệu chứng khác cũng bắt nguồn từ work_mem.

Thuật toán join

Nested Loop — với mỗi dòng của vế ngoài, quét lại (hoặc dò lại index trên) vế trong. Rẻ khi vế ngoài nhỏ, vì chi phí mỗi dòng của vế trong chỉ phải trả một số ít lần:

EXPLAIN ANALYZE
SELECT c.name, o.total_cents
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 42;

 Nested Loop  (cost=0.86..12.94 rows=25 width=40) (actual time=0.045..0.089 rows=23 loops=1)
   ->  Index Scan using customers_pkey on customers c  (cost=0.43..2.45 rows=1 width=20) (actual time=0.020..0.021 rows=1 loops=1)
         Index Cond: (id = 42)
   ->  Index Scan using idx_orders_customer_id on orders o  (cost=0.43..8.45 rows=25 width=24) (actual time=0.021..0.056 rows=23 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.145 ms
 Execution Time: 0.118 ms

Nested loop trở nên thảm họa khi ước tính số dòng của vế ngoài sai lệch nhiều bậc độ lớn — một ước tính loops=1 mà thực tế thành loops=48000 nghĩa là vế trong (nhìn có vẻ rẻ) giờ phải chạy tới 48.000 lần.

Hash Join — xây một hash table trong bộ nhớ trên vế (thường nhỏ hơn), rồi dò một lần cho mỗi dòng của vế còn lại. Tốt cho các equi-join lớn khi không có sẵn thứ tự sắp xếp nào hữu ích:

EXPLAIN ANALYZE
SELECT c.region, sum(o.total_cents)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.region;

 HashAggregate  (cost=195432.50..195432.62 rows=8 width=40) (actual time=1820.442..1820.448 rows=8 loops=1)
   Group Key: c.region
   Batches: 1  Memory Usage: 24kB
   ->  Hash Join  (cost=3211.00..170432.50 rows=5000000 width=12) (actual time=32.110..1654.223 rows=5000000 loops=1)
         Hash Cond: (o.customer_id = c.id)
         ->  Seq Scan on orders o  (cost=0.00..104166.00 rows=5000000 width=8) (actual time=0.010..421.556 rows=5000000 loops=1)
         ->  Hash  (cost=1929.00..1929.00 rows=100000 width=12) (actual time=31.980..31.981 rows=100000 loops=1)
               Buckets: 131072  Batches: 1  Memory Usage: 5905kB
               ->  Seq Scan on customers c  (cost=0.00..1929.00 rows=100000 width=12) (actual time=0.008..14.223 rows=100000 loops=1)
 Planning Time: 0.210 ms
 Execution Time: 1820.601 ms

Chú ý Batches trong một node Hash: Batches: 1 nghĩa là hash table vừa hoàn toàn trong work_mem. Batches: 4 (hoặc nhiều hơn) nghĩa là không vừa và PostgreSQL đã chia hash thành các partition tràn ra đĩa — chậm hơn, và là một tín hiệu mạnh để tăng work_mem cho workload này.

Merge Join — cả hai vế đã (hoặc được làm cho) sắp xếp theo khóa join, và phép join duyệt song song hai bên như khóa kéo. Tốt khi cả hai vế đã sẵn sàng sắp xếp (ví dụ nhờ một index cung cấp thứ tự đó miễn phí), tránh phải thêm bước sort riêng:

EXPLAIN ANALYZE
SELECT c.name, o.total_cents
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.id;

 Merge Join  (cost=1.13..215432.88 rows=5000000 width=40) (actual time=0.055..1980.221 rows=5000000 loops=1)
   Merge Cond: (c.id = o.customer_id)
   ->  Index Scan using customers_pkey on customers c  (cost=0.29..3859.29 rows=100000 width=20) (actual time=0.020..18.556 rows=100000 loops=1)
   ->  Index Scan using idx_orders_customer_id on orders o  (cost=0.43..185432.50 rows=5000000 width=24) (actual time=0.018..1502.331 rows=5000000 loops=1)
 Planning Time: 0.180 ms
 Execution Time: 2105.442 ms

Nếu không vế nào tự nhiên đã sắp xếp, planner sẽ chèn thêm node Sort tường minh bên dưới Merge Join — lúc đó nó đang cạnh tranh về chi phí với Hash Join, và planner chọn phương án nào thực sự rẻ hơn với khối lượng dữ liệu đó.

Trực quan hóa plan

Output văn bản thô của EXPLAIN ANALYZE rất khó đọc khi plan có hơn năm sáu node lồng nhau nhiều tầng. Hai công cụ cộng đồng giúp việc này dễ dàng hơn hẳn, và việc dán output vào một trong hai công cụ này nên là phản xạ trong bất kỳ buổi tuning thực tế nào:

• explain.depesz.com — dán output EXPLAIN ANALYZE dạng văn bản thuần; nó hiển thị một bảng liệt kê mọi node với các cột thời gian độc quyền (exclusive), thời gian bao gồm (inclusive) và số dòng, tô màu các node chậm nhất để điểm nghẽn hiện ra ngay lập tức.
• explain.dalibo.com (pev2) — chấp nhận cả văn bản thuần lẫn (tốt hơn) output EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), và vẽ một sơ đồ cây tương tác với kích thước tỷ lệ theo cost/time, kèm số liệu buffer (cache hit/read) cho từng node nếu có.

Luôn thu thập plan bằng EXPLAIN (ANALYZE, BUFFERS) (thay vì EXPLAIN ANALYZE trần) trước khi dán vào một trong hai công cụ trên — tùy chọn BUFFERS thêm Buffers: shared hit=… read=… cho mỗi node, cho biết một node chậm là do công việc CPU, do đọc đĩa (read), hay thực ra chạy nhanh và được phục vụ hoàn toàn từ shared_buffers (hit). Đây thường là ranh giới giữa “node này chậm vì cần index” và “node này chậm vì đang đọc dữ liệu nguội từ đĩa,” hai tình huống đòi hỏi cách sửa hoàn toàn khác nhau.

Statistics và ANALYZE

Mọi quyết định lập kế hoạch ở trên cuối cùng đều dựa vào pg_stats:

SELECT attname, n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

 attname |      n_distinct       |              most_common_vals               |         most_common_freqs          | correlation
---------+------------------------+----------------------------------------------+-------------------------------------+-------------
 status  |                      6 | {shipped,delivered,pending,cancelled,refunded,returned} | {0.42,0.31,0.14,0.08,0.03,0.02} |        0.11

Những con số này chỉ là một ảnh chụp, được làm mới bởi ANALYZE (một lượt quét nhẹ, dựa trên lấy mẫu — rẻ hơn nhiều so với VACUUM) hoặc bởi tính năng auto-analyze tự động của autovacuum khi đủ số dòng đã thay đổi (điều khiển bởi autovacuum_analyze_scale_factor/autovacuum_analyze_threshold). default_statistics_target (mặc định 100, có thể đặt toàn server hoặc theo từng cột qua ALTER TABLE ... ALTER COLUMN ... SET STATISTICS n) quyết định ANALYZE lấy mẫu bao nhiêu dòng và danh sách most-common-values/histogram lớn tới đâu — tăng giá trị này (ví dụ lên 500 hoặc 1000) trên một vài cột được biết là có phân bố lệch là một cách tuning phổ biến, ít rủi ro; tăng giá trị này toàn cục khiến mọi ANALYZE chậm hơn và mọi plan tốn thêm chút chi phí để tạo ra.

Kịch bản thất bại kinh điển: một đợt bulk load hoặc một UPDATE/DELETE lớn làm thay đổi hình dạng dữ liệu (một giá trị status mới giờ chiếm áp đảo, hoặc một bảng từ 10 nghìn dòng nhảy lên 10 triệu dòng) nhưng statistics vẫn phản ánh hình dạng cũ cho tới khi ngưỡng auto-analyze bị vượt qua — trên một bảng khổng lồ, điều này có thể xảy ra rất lâu sau khi đợt load kết thúc. Trong lúc đó, planner vẫn đang chọn plan cho dữ liệu không còn tồn tại nữa:

-- Ngay sau khi load 4.9 triệu đơn 'refunded', trước khi ANALYZE chạy
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'refunded';

 Seq Scan on orders  (cost=0.00..104166.00 rows=50 width=97) (actual time=0.015..523.223 rows=125000 loops=1)
   Filter: (status = 'refunded'::text)
   Rows Removed by Filter: 4875000
 Planning Time: 0.095 ms
 Execution Time: 545.667 ms

Ước tính rows=50, thực tế rows=125000 — sai lệch 2.500 lần, đúng là kiểu lệch pha ước-tính-so-với-thực-tế được nhắc ở trên như dấu hiệu đặc trưng của vấn đề statistics. Cách sửa mang tính quy trình, không phải cấu trúc: chạy ANALYZE orders; (hoặc ANALYZE; cho cả database) ngay sau bất kỳ đợt bulk load, update/delete hàng loạt, hay attach/detach partition nào, thay vì chờ autovacuum tự nhận ra. Việc này chỉ tốn vài giây tới vài phút và có thể biến một query mất nhiều giây trở lại thành query dưới một mili-giây, không cần thay đổi schema.

pg_stat_statements: tìm ra cái gì cần tuning

Đọc từng plan riêng lẻ chỉ hữu ích khi bạn đã biết query nào cần nhìn. pg_stat_statements theo dõi số liệu thực thi cho mọi query khác biệt đã được chuẩn hóa (hằng số được thay bằng placeholder như $1) trên toàn bộ server, và đây là nơi đầu tiên một DBA có kinh nghiệm nhìn vào — không phải phương án cuối cùng sau khi đoán mò.

Bật extension này (cần khởi động lại server vì nó cần shared memory):

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;

Tìm các query tiêu tốn tổng thời gian nhiều nhất trên server — thường là điểm bắt đầu có đòn bẩy cao nhất, vì nó tính đến cả chi phí mỗi lần gọi lẫn tần suất gọi:

SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

                          query                              |  calls  | total_ms  | mean_ms | pct_total
---------------------------------------------------------------+---------+-----------+---------+-----------
 SELECT * FROM orders WHERE customer_id = $1                   |  812340 | 214532.10 |    0.26 |     18.20
 UPDATE orders SET status = $1 WHERE id = $2                    |   45210 | 189213.55 |    4.19 |     16.04
 SELECT c.*, o.* FROM customers c JOIN orders o ON ...          |    3211 | 165442.80 |   51.53 |     14.02
 SELECT * FROM orders WHERE status = $1                         |    1876 |  98221.44 |   52.35 |      8.33
 DELETE FROM sessions WHERE expires_at < $1                     |     512 |  87023.11 |  170.00 |      7.38

Hai query rất khác nhau có thể cùng chiếm ưu thế trong total_ms vì hai lý do trái ngược: dòng đầu tiên ở đây được gọi 812.340 lần với trung bình chỉ 0,26 ms — bản thân nó hoàn toàn ổn, nhưng đáng để xác nhận nó thực sự đang dùng idx_orders_customer_id. Dòng cuối chỉ được gọi 512 lần nhưng trung bình 170 ms — dòng đó đáng được EXPLAIN ANALYZE ngay lập tức. Sắp xếp theo mean_exec_time DESC thay vì total_exec_time sẽ đưa lên các query tệ nhất theo mỗi lần gọi, hữu ích để bắt được một query báo cáo cực chậm nhưng không được gọi đủ nhiều để chiếm ưu thế trong bảng xếp hạng tổng thời gian. pg_stat_statements_reset() xóa các bộ đếm, hữu ích trước một bài load test hoặc sau khi sửa lỗi để đo “trước/sau” một cách sạch sẽ.

Anti-pattern phổ biến và cách sửa

Anti-pattern	Triệu chứng trong EXPLAIN	Cách sửa
Thiếu index	Seq Scan với Filter có độ chọn lọc cao trên bảng lớn	Thêm index B-tree (hoặc loại phù hợp) — xem Indexing Strategies
SELECT *	width= lớn dù chỉ 2 cột được dùng ở tầng sau; chặn Index Only Scan	Chỉ select các cột cần thiết; cho phép index hẹp hơn “cover” được query
Ép kiểu ngầm định (implicit cast)	Filter hiện (customer_phone)::text = '12345'::text hoặc cast bọc quanh cột thay vì literal	Khớp kiểu dữ liệu giữa cột và literal một cách tường minh (WHERE customer_phone = '12345', không so sánh text với integer) để cast không làm mất tác dụng của index
Hàm áp lên cột có index	Filter: (lower(email) = 'foo@bar.com'::text) cùng Seq Scan dù có index trên email	Tạo expression index: CREATE INDEX ON customers (lower(email));
Mẫu N+1 từ code ứng dụng	pg_stat_statements cho thấy một query đơn dòng tham số hóa (WHERE id = $1) với calls cực lớn	Gom nhóm bằng WHERE id = ANY($1::int[]), một JOIN, hoặc eager-loading trong ORM

Anti-pattern ép kiểu ngầm định dễ bị bỏ qua vì query vẫn “chạy được” — nó chỉ âm thầm ngừng dùng index đáng lẽ phải dùng. So sánh:

EXPLAIN SELECT * FROM orders WHERE customer_id = '42';   -- literal text, cột int

 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=25 width=97)
   Index Cond: (customer_id = 42)

PostgreSQL thường đủ thông minh để gấp (fold) cast của literal ngay tại thời điểm lập plan (như trên), nhưng điều đó không được đảm bảo với cast áp lên chính cột, hay với so sánh khác kiểu mà planner không thể rút gọn thành hằng số — những trường hợp đó thường xuất hiện dưới dạng điều kiện Filter: (được đánh giá từng dòng sau khi scan) thay vì Index Cond: (dùng để dẫn dắt scan), đây chính là dấu hiệu cần chú ý khi đọc output EXPLAIN.

Cấu hình bộ nhớ và cache

Ba tham số ảnh hưởng đến việc một plan có thể thực thi hiệu quả hay không, kể cả khi bản thân lựa chọn plan đã đúng:

• work_mem — bộ nhớ khả dụng cho mỗi thao tác sort hoặc hash (không phải mỗi query hay mỗi connection — một query có nhiều sort/hash, chạy bởi nhiều connection đồng thời, có thể nhân con số này lên nhiều lần, nên hãy tăng thận trọng trên các server có độ đồng thời cao). Nếu quá thấp, một Sort hoặc Hash sẽ tràn ra đĩa:

EXPLAIN ANALYZE SELECT * FROM orders ORDER BY total_cents;

 Sort  (cost=712345.12..724845.12 rows=5000000 width=97) (actual time=4521.223..5890.114 rows=5000000 loops=1)
   Sort Key: total_cents
   Sort Method: external merge  Disk: 512000kB
   ->  Seq Scan on orders  (cost=0.00..104166.00 rows=5000000 width=97) (actual time=0.012..430.221 rows=5000000 loops=1)
 Planning Time: 0.088 ms
 Execution Time: 6102.556 ms

Sort Method: external merge Disk: 512000kB chính là bằng chứng rõ ràng — thao tác sort không vừa trong work_mem và đã tràn khoảng 500 MB ra file tạm trên đĩa. Tăng work_mem cho session (hoặc toàn cục, một cách thận trọng) sẽ khắc phục:

SET work_mem = '256MB';

 Sort  (cost=712345.12..724845.12 rows=5000000 width=97) (actual time=2210.556..2890.221 rows=5000000 loops=1)
   Sort Key: total_cents
   Sort Method: quicksort  Memory: 665432kB
   ->  Seq Scan on orders  (cost=0.00..104166.00 rows=5000000 width=97) (actual time=0.011..415.223 rows=5000000 loops=1)
 Planning Time: 0.075 ms
 Execution Time: 3102.556 ms

Ý tưởng tương tự áp dụng cho Batches: N (N > 1) trên một node Hash đã thấy ở trên — đó là hash table tràn ra đĩa vì cùng nguyên nhân gốc rễ.

• shared_buffers — page cache riêng của PostgreSQL, thường đặt bằng 25% RAM hệ thống làm điểm khởi đầu. Nếu quá nhỏ, cùng những trang dữ liệu sẽ bị đọc lại nhiều lần từ OS cache (hoặc đĩa); số liệu shared hit so với read trong EXPLAIN (ANALYZE, BUFFERS) cho biết đây có thực sự là vấn đề với workload của bạn trước khi đụng vào tham số này.
• effective_cache_size — không phải một khoản cấp phát bộ nhớ; nó là một gợi ý báo cho planner biết ước chừng bao nhiêu bộ nhớ tổng cộng (shared_buffers + OS page cache) thực sự khả dụng cho việc cache, thường đặt bằng 50–75% RAM hệ thống. Một giá trị thấp khiến index scan trông có vẻ đắt hơn thực tế (planner giả định các trang index có khả năng không nằm trong cache và mô hình hóa nhiều random I/O hơn), khiến planner thiên về sequential scan; tăng giá trị này cho khớp với thực tế thường “lật” các plan biên giới từ Seq Scan trở lại Index Scan mà không cần đụng tới bất kỳ index nào.

Best Practices

Luôn dùng pg_stat_statements trước khi mở riêng lẻ một query — nó cho biết một cách khách quan query nào đáng để dành thời gian, thay vì tuning bất kỳ query nào bạn tình cờ đang nhìn vào. Khi đã có ứng viên, chạy EXPLAIN (ANALYZE, BUFFERS) — không phải EXPLAIN trần — vì chi phí ước tính đơn thuần không cho biết plan có thực sự chậm hay không, hay chậm vì disk I/O, CPU, hay vì hình dạng plan sai; dán output vào explain.dalibo.com hoặc explain.depesz.com ngay khi plan có nhiều hơn vài node, thay vì cố nhìn thụt lề bằng mắt. So sánh số dòng ước tính với thực tế trên mọi node trước khi kết luận bất cứ điều gì về thứ tự join hay lựa chọn scan — một plan “nhìn có vẻ sai” thường lại là một plan đúng đắn với statistics mà planner có trong tay, và cách sửa thực sự là ANALYZE, không phải viết lại query.

Đưa ANALYZE vào runbook vận hành, không phải một việc làm thêm khi rảnh: chạy tường minh sau các đợt bulk load, các batch UPDATE/DELETE lớn, khôi phục một bản dump, và attach/detach partition — đừng dựa vào lịch trình của autovacuum cho các sự kiện đơn lẻ này trên bảng lớn, vì độ trễ trước khi nó kích hoạt có thể khiến planner làm việc với statistics cũ hàng giờ liền. Coi việc tuning work_mem là chuyện đặc thù theo workload và thận trọng trên các server OLTP có độ đồng thời cao (tham số này nhân lên theo mỗi sort/hash đồng thời, và một lần tăng với thiện chí có thể đẩy server vào tình trạng swap); thường an toàn hơn khi tăng theo session hoặc theo role cho các query báo cáo nặng đã biết trước (ALTER ROLE reporting_user SET work_mem = '256MB';) thay vì tăng toàn cục. Đừng bao giờ cố “ép” hình dạng plan bằng query hint — PostgreSQL cố tình không hỗ trợ chúng, chính xác để giữ áp lực vào việc sửa đầu vào thực sự (statistics, index, schema) thay vì che giấu triệu chứng. Cuối cùng, coi các anti-pattern như ép kiểu ngầm định và hàm áp lên cột có index là hạng mục code review, không chỉ là việc dọn dẹp của DBA: bắt được WHERE lower(email) = ... chạy trên một index thường, hay một so sánh khác kiểu text/int, trước khi lên production rẻ hơn rất nhiều so với chẩn đoán nó nhiều tháng sau trong production.

Tài liệu tham khảo

• PostgreSQL Docs — Using EXPLAIN
• PostgreSQL Docs — Planner Statistics
• PostgreSQL Docs — Planner Cost Constants
• PostgreSQL Docs — pg_stat_statements
• PostgreSQL Docs — Resource Consumption (work_mem, shared_buffers, effective_cache_size)
• explain.depesz.com
• explain.dalibo.com (pev2)
• Liên quan: Indexing Strategies · Transactions & Concurrency Control · Monitoring, Logging & Diagnostics

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                              QUERY PLAN
------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..104166.00 rows=25 width=97)
   Filter: (customer_id = 42)

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

                                              QUERY PLAN
--------------------------------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=25 width=97)
                                                     (actual time=0.031..0.048 rows=23 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.112 ms
 Execution Time: 0.071 ms

EXPLAIN ANALYZE SELECT * FROM orders WHERE total_cents > 0;

 Seq Scan on orders  (cost=0.00..104166.00 rows=4998211 width=97) (actual time=0.011..612.334 rows=5000000 loops=1)
   Filter: (total_cents > 0)
 Planning Time: 0.065 ms
 Execution Time: 845.221 ms

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=25 width=97) (actual time=0.031..0.048 rows=23 loops=1)
   Index Cond: (customer_id = 42)

EXPLAIN ANALYZE SELECT customer_id FROM orders WHERE customer_id = 42;

 Index Only Scan using idx_orders_customer_id on orders  (cost=0.43..4.45 rows=25 width=4) (actual time=0.020..0.028 rows=23 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
 Planning Time: 0.078 ms
 Execution Time: 0.045 ms

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';

 Bitmap Heap Scan on orders  (cost=4523.28..98219.10 rows=249876 width=97) (actual time=18.223..612.884 rows=248112 loops=1)
   Recheck Cond: (status = 'shipped'::text)
   Heap Blocks: exact=45213
   ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..4460.79 rows=249876 width=0) (actual time=17.902..17.902 rows=248112 loops=1)
         Index Cond: (status = 'shipped'::text)
 Planning Time: 0.089 ms
 Execution Time: 625.451 ms

EXPLAIN ANALYZE
SELECT c.name, o.total_cents
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 42;

 Nested Loop  (cost=0.86..12.94 rows=25 width=40) (actual time=0.045..0.089 rows=23 loops=1)
   ->  Index Scan using customers_pkey on customers c  (cost=0.43..2.45 rows=1 width=20) (actual time=0.020..0.021 rows=1 loops=1)
         Index Cond: (id = 42)
   ->  Index Scan using idx_orders_customer_id on orders o  (cost=0.43..8.45 rows=25 width=24) (actual time=0.021..0.056 rows=23 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.145 ms
 Execution Time: 0.118 ms

EXPLAIN ANALYZE
SELECT c.region, sum(o.total_cents)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.region;

 HashAggregate  (cost=195432.50..195432.62 rows=8 width=40) (actual time=1820.442..1820.448 rows=8 loops=1)
   Group Key: c.region
   Batches: 1  Memory Usage: 24kB
   ->  Hash Join  (cost=3211.00..170432.50 rows=5000000 width=12) (actual time=32.110..1654.223 rows=5000000 loops=1)
         Hash Cond: (o.customer_id = c.id)
         ->  Seq Scan on orders o  (cost=0.00..104166.00 rows=5000000 width=8) (actual time=0.010..421.556 rows=5000000 loops=1)
         ->  Hash  (cost=1929.00..1929.00 rows=100000 width=12) (actual time=31.980..31.981 rows=100000 loops=1)
               Buckets: 131072  Batches: 1  Memory Usage: 5905kB
               ->  Seq Scan on customers c  (cost=0.00..1929.00 rows=100000 width=12) (actual time=0.008..14.223 rows=100000 loops=1)
 Planning Time: 0.210 ms
 Execution Time: 1820.601 ms

EXPLAIN ANALYZE
SELECT c.name, o.total_cents
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.id;

 Merge Join  (cost=1.13..215432.88 rows=5000000 width=40) (actual time=0.055..1980.221 rows=5000000 loops=1)
   Merge Cond: (c.id = o.customer_id)
   ->  Index Scan using customers_pkey on customers c  (cost=0.29..3859.29 rows=100000 width=20) (actual time=0.020..18.556 rows=100000 loops=1)
   ->  Index Scan using idx_orders_customer_id on orders o  (cost=0.43..185432.50 rows=5000000 width=24) (actual time=0.018..1502.331 rows=5000000 loops=1)
 Planning Time: 0.180 ms
 Execution Time: 2105.442 ms

SELECT attname, n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

 attname |      n_distinct       |              most_common_vals               |         most_common_freqs          | correlation
---------+------------------------+----------------------------------------------+-------------------------------------+-------------
 status  |                      6 | {shipped,delivered,pending,cancelled,refunded,returned} | {0.42,0.31,0.14,0.08,0.03,0.02} |        0.11

-- Right after loading 4.9M 'refunded' orders, before ANALYZE runs
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'refunded';

 Seq Scan on orders  (cost=0.00..104166.00 rows=50 width=97) (actual time=0.015..523.223 rows=125000 loops=1)
   Filter: (status = 'refunded'::text)
   Rows Removed by Filter: 4875000
 Planning Time: 0.095 ms
 Execution Time: 545.667 ms

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;

SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

                          query                              |  calls  | total_ms  | mean_ms | pct_total
---------------------------------------------------------------+---------+-----------+---------+-----------
 SELECT * FROM orders WHERE customer_id = $1                   |  812340 | 214532.10 |    0.26 |     18.20
 UPDATE orders SET status = $1 WHERE id = $2                    |   45210 | 189213.55 |    4.19 |     16.04
 SELECT c.*, o.* FROM customers c JOIN orders o ON ...          |    3211 | 165442.80 |   51.53 |     14.02
 SELECT * FROM orders WHERE status = $1                         |    1876 |  98221.44 |   52.35 |      8.33
 DELETE FROM sessions WHERE expires_at < $1                     |     512 |  87023.11 |  170.00 |      7.38

EXPLAIN SELECT * FROM orders WHERE customer_id = '42';   -- text literal, int column

 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=25 width=97)
   Index Cond: (customer_id = 42)

EXPLAIN ANALYZE SELECT * FROM orders ORDER BY total_cents;

 Sort  (cost=712345.12..724845.12 rows=5000000 width=97) (actual time=4521.223..5890.114 rows=5000000 loops=1)
   Sort Key: total_cents
   Sort Method: external merge  Disk: 512000kB
   ->  Seq Scan on orders  (cost=0.00..104166.00 rows=5000000 width=97) (actual time=0.012..430.221 rows=5000000 loops=1)
 Planning Time: 0.088 ms
 Execution Time: 6102.556 ms

SET work_mem = '256MB';

 Sort  (cost=712345.12..724845.12 rows=5000000 width=97) (actual time=2210.556..2890.221 rows=5000000 loops=1)
   Sort Key: total_cents
   Sort Method: quicksort  Memory: 665432kB
   ->  Seq Scan on orders  (cost=0.00..104166.00 rows=5000000 width=97) (actual time=0.011..415.223 rows=5000000 loops=1)
 Planning Time: 0.075 ms
 Execution Time: 3102.556 ms