
PostgreSQL 17 mang đến những cải tiến đáng kể cho bộ tối ưu hóa truy vấn và công cụ thực thi, đòi hỏi một cách tiếp cận tinh tế hơn để điều chỉnh hiệu suất. Hướng dẫn này phân tích chi tiết cách giải mã execution plan, tận dụng các tính năng quản lý bộ nhớ mới và tối ưu hóa các mẫu truy vấn phức tạp để đạt được hiệu suất cao nhất.
Analyze queries for sargability, missing composite indexes & offsets
Analyze SQL queries for performance anti-patterns, wildcards, and slow offsets. Generates optimized queries and DDL composite indexes for PostgreSQL, MySQL, and SQLite.
EXPLAIN ANALYZENền tảng của việc tối ưu hóa truy vấn PostgreSQL nằm ở việc diễn giải đầu ra EXPLAIN ANALYZE. Lệnh này cung cấp kế hoạch thực thi ước tính của trình lập kế hoạch và số liệu thống kê thời gian chạy thực tế.
Mỗi nút trong cây EXPLAIN đại diện cho một thao tác. Các số liệu chính bao gồm:
cost: Chi phí ước tính của trình lập kế hoạch (khởi động..tổng cộng).rows: Số hàng ước tính của trình lập kế hoạch.actual time: Thời gian thực tế đã dành (khởi động..tổng cộng) tính bằng mili giây.actual rows: Số hàng thực tế được trả về.loops: Số lần nút được thực thi.Buffers: Chi tiết về quyền truy cập khối dùng chung, cục bộ và tạm thời (yêu cầu tùy chọn BUFFERS).Wal: Thống kê tạo bản ghi WAL (yêu cầu tùy chọn WAL).Settings: Các tham số GUC ảnh hưởng đến kế hoạch (yêu cầu tùy chọn SETTINGS).Xem xét một truy vấn đơn giản:
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL)
SELECT p.product_name, c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE p.price > 100 AND c.category_name = 'Electronics';
Một đầu ra điển hình có thể trông như thế này:
QUERY PLAN
--------------------------------------------------------------------------------------
Hash Join (cost=25.80..50.35 rows=10 width=64) (actual time=0.150..0.250 rows=5 loops=1)
Hash Cond: (p.category_id = c.category_id)
Buffers: shared hit=10 read=2
Wal: records=0, full_page_writes=0, bytes=0
-> Seq Scan on products p (cost=0.00..24.50 rows=20 width=36) (actual time=0.010..0.100 rows=15 loops=1)
Filter: (price > 100::numeric)
Rows Removed by Filter: 85
Buffers: shared hit=5 read=1
-> Hash (cost=25.75..25.75 rows=1 width=32) (actual time=0.030..0.030 rows=1 loops=1)
Buffers: shared hit=5 read=1
-> Seq Scan on categories c (cost=0.00..25.75 rows=1 width=32) (actual time=0.010..0.020 rows=1 loops=1)
Filter: (category_name = 'Electronics'::text)
Rows Removed by Filter: 99
Buffers: shared hit=5 read=1
Planning Time: 0.100 ms
Execution Time: 0.300 ms
Giải thích:
Hash Join: Thao tác cấp cao nhất. Mất 0.250 ms để hoàn thành và trả về 5 hàng. Trình lập kế hoạch ước tính 10 hàng.Seq Scan on products p: Đã quét bảng products. Đã lọc 85 hàng, giữ lại 15. Quét này đã truy cập 5 khối dùng chung và đọc 1 từ đĩa.Hash: Đã xây dựng một bảng băm từ quá trình quét bảng categories.Seq Scan on categories c: Đã quét categories, đã lọc 99 hàng, giữ lại 1. Quét này cũng đã truy cập 5 khối dùng chung và đọc 1 từ đĩa.Phần Buffers rất quan trọng. shared hit cho biết dữ liệu được tìm thấy trong bộ đệm dùng chung (bộ nhớ), read có nghĩa là dữ liệu phải được tìm nạp từ đĩa. Số lượng read cao thường chỉ ra các chỉ mục bị thiếu hoặc shared_buffers không đủ.
Sự khác biệt giữa rows (ước tính) và actual rows là những dấu hiệu đáng báo động. Sự không khớp đáng kể cho thấy trình lập kế hoạch đã đưa ra một quyết định kém, thường là do:
ANALYZE hoặc VACUUM ANALYZE thường xuyên.WHERE phức tạp, đặc biệt là liên quan đến các hàm hoặc biểu thức.ANALYZE với default_statistics_target tăng lên có thể giúp ích.CREATE STATISTICS có thể cung cấp thống kê phụ thuộc chức năng hoặc đa cột.Ví dụ về một lỗi:
Nếu actual rows cho Seq Scan on products p là 10000 nhưng rows là 20, trình lập kế hoạch có thể đã chọn Nested Loop thay vì Hash Join hoặc Merge Join, dẫn đến hiệu suất kém.
work_mem và maintenance_work_memPostgreSQL 17 tiếp tục tinh chỉnh quản lý bộ nhớ. Việc điều chỉnh đúng work_mem và maintenance_work_mem là rất quan trọng.
work_memTham số này xác định lượng bộ nhớ tối đa được sử dụng bởi một thao tác truy vấn (ví dụ: sắp xếp, bảng băm) trước khi ghi dữ liệu tạm thời vào đĩa. Mỗi thao tác như vậy có thể sử dụng lượng này.
spill hoặc disk trong đầu ra EXPLAIN ANALYZE (ví dụ: Sort Method: external merge Disk: 12345kB).Chiến lược điều chỉnh:
Sort Method: external merge Disk hoặc HashAggregate (actual time=... loops=1) Memory: 12345kB, Disk: 67890kB trong EXPLAIN ANALYZE.4MB hoặc 8MB). Nếu xảy ra tràn, hãy tăng nó. Một giá trị sản xuất phổ biến có thể là 64MB đến 256MB mỗi phiên, nhưng điều này phụ thuộc rất nhiều vào khối lượng công việc và RAM khả dụng.SET work_mem TO '256MB'; có thể được sử dụng.-- Example: Observe a sort spill
-- Assume 'large_table' has millions of rows and 'some_column' is not indexed
SET work_mem TO '64kB'; -- Artificially low for demonstration
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM large_table
ORDER BY some_column;
-- Output might show:
-- Sort (cost=... rows=... width=...) (actual time=... rows=... loops=1)
-- Sort Key: some_column
-- Sort Method: external merge Disk: 12345kB <-- This indicates a spill
-- Buffers: ...
-- Increase work_mem to prevent spill
SET work_mem TO '256MB'; -- Adjust based on actual spill size and available RAM
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM large_table
ORDER BY some_column;
-- Output should ideally show:
-- Sort (cost=... rows=... width=...) (actual time=... rows=... loops=1)
-- Sort Key: some_column
-- Sort Method: quicksort Memory: 12345kB <-- In-memory sort
-- Buffers: ...
maintenance_work_memĐược sử dụng cho các thao tác bảo trì như VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY. Các thao tác này thường ít thường xuyên hơn nhưng có thể rất tốn bộ nhớ.
Chiến lược điều chỉnh:
work_mem. Các giá trị phổ biến dao động từ 256MB đến 1GB trở lên, tùy thuộc vào kích thước của các bảng lớn nhất của bạn và RAM khả dụng.-- Example: Creating an index on a large table
-- A low maintenance_work_mem can make this very slow.
SET maintenance_work_mem TO '16MB'; -- Artificially low
CREATE INDEX idx_large_table_col ON large_table (another_column);
-- This might take a very long time and use temp files.
-- Increase for faster index creation
SET maintenance_work_mem TO '1GB'; -- Or higher, depending on table size
CREATE INDEX idx_large_table_col_new ON large_table (yet_another_column);
-- This should be significantly faster.
PostgreSQL 17 tăng cường khả năng của trình lập kế hoạch trong việc sử dụng sắp xếp tăng dần và cải thiện hiệu suất kết nối băm.
Khi một truy vấn yêu cầu sắp xếp trên một tập hợp con các cột đã được sắp xếp theo một chỉ mục, PostgreSQL có thể thực hiện "sắp xếp tăng dần". Điều này tránh một thao tác sắp xếp đầy đủ, giảm đáng kể chi phí.
-- Assume an index on (order_date, customer_id)
CREATE INDEX idx_orders_date_customer ON orders (order_date, customer_id);
-- Query that can benefit from incremental sort
EXPLAIN (ANALYZE)
SELECT *
FROM orders
WHERE order_date >= '2023-01-01'
ORDER BY order_date, customer_id, order_total DESC;
Đầu ra EXPLAIN có thể hiển thị:
QUERY PLAN
--------------------------------------------------------------------------------------
Sort (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Sort Key: order_total DESC
Presorted Key: order_date, customer_id <-- Indicates incremental sort
-> Index Scan using idx_orders_date_customer on orders (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Index Cond: (order_date >= '2023-01-01'::date)
Dòng Presorted Key là chỉ báo chính. Để tối ưu hóa:
ORDER BY của bạn.Sort Key còn lại sẽ được sắp xếp tăng dần.Kết nối băm hiệu quả để kết nối các tập dữ liệu lớn, không được sắp xếp. PostgreSQL 17 cải thiện việc sử dụng bộ nhớ và xử lý tràn của chúng.
work_mem đủ để chứa bảng băm của quan hệ nhỏ hơn trong bộ nhớ. Nếu nó tràn ra đĩa, hiệu suất sẽ giảm.enable_hashjoin: Đảm bảo GUC này là on (mặc định).-- Example: Hash Join
EXPLAIN (ANALYZE, BUFFERS)
SELECT a.id, b.value
FROM large_table_a a
JOIN medium_table_b b ON a.b_id = b.id;
Đầu ra:
QUERY PLAN
--------------------------------------------------------------------------------------
Hash Join (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Hash Cond: (a.b_id = b.id)
Buffers: ...
-> Seq Scan on large_table_a a (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Buffers: ...
-> Hash (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Memory: 12345kB <-- Hash table memory usage
Buffers: ...
-> Seq Scan on medium_table_b b (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Buffers: ...
Nếu Memory trong nút Hash được theo sau bởi Disk, điều đó có nghĩa là bảng băm đã tràn. Tăng work_mem.
PostgreSQL 17 tiếp tục tăng cường khả năng truy vấn song song, cho phép nhiều lõi CPU hoạt động trên các phần của một truy vấn duy nhất.
max_parallel_workers_per_gather: Kiểm soát số lượng worker song song tối đa có thể được khởi chạy cho một nút Gather hoặc Gather Merge duy nhất.max_parallel_workers: Tổng số worker song song được phép trên toàn hệ thống.min_parallel_table_scan_size: Kích thước bảng tối thiểu (tính bằng KB) để xem xét quét song song.parallel_setup_cost: Chi phí ước tính để khởi chạy các worker song song.parallel_tuple_cost: Chi phí ước tính để chuyển một tuple từ worker sang leader.Xác định các kế hoạch song song:
Tìm các nút Gather hoặc Gather Merge trong đầu ra EXPLAIN ANALYZE.
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM very_large_table
WHERE some_column > 100;
Đầu ra:
QUERY PLAN
--------------------------------------------------------------------------------------
Finalize Aggregate (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Buffers: ...
-> Gather (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: ...
-> Partial Aggregate (cost=... rows=... width=...) (actual time=... rows=... loops=2)
Buffers: ...
-> Parallel Seq Scan on very_large_table (cost=... rows=... width=...) (actual time=... rows=... loops=2)
Filter: (some_column > 100)
Buffers: ...
Tối ưu hóa:
max_parallel_workers_per_gather được đặt phù hợp (ví dụ: 4 đến 8 trên một máy chủ hiện đại).min_parallel_table_scan_size không quá cao.| Tính năng/Tham số | Mô tả | Mặc định (PG17) | Phạm vi khuyến nghị | Đánh đổi |
|---|---|---|---|---|
work_mem | Bộ nhớ cho các thao tác sắp xếp/băm trên mỗi truy vấn. | 4MB | 64MB - 256MB | Quá thấp: tràn đĩa, truy vấn chậm. Quá cao: OOM cho các truy vấn đồng thời. |
maintenance_work_mem | Bộ nhớ cho VACUUM, CREATE INDEX. | 64MB | 256MB - 1GB+ | Quá thấp: bảo trì chậm. Quá cao: ảnh hưởng đến các truy vấn đang hoạt động trong quá trình bảo trì. |
shared_buffers | Bộ đệm dữ liệu chính. | 128MB | 25% - 40% RAM | Quá thấp: I/O đĩa cao. Quá cao: dư thừa bộ đệm OS, OOM. |
effective_cache_size | Ước tính của trình lập kế hoạch về OS + shared_buffers. | 4GB | 50% - 75% RAM | Thông tin cho trình lập kế hoạch; không cấp phát bộ nhớ. Giá trị không chính xác dẫn đến các kế hoạch kém. |
max_parallel_workers_per_gather | Số worker song song tối đa trên mỗi truy vấn. | 2 | 4 - 8 | Quá thấp: không tận dụng hết CPU. Quá cao: chuyển đổi ngữ cảnh quá mức, tranh chấp tài nguyên. |
default_statistics_target | Mức độ chi tiết của thống kê. | 100 | 200 - 1000 | Quá thấp: ước tính hàng kém. Quá cao: tăng thời gian ANALYZE, bảng pg_statistic lớn hơn. |
Vấn đề: Hiệu suất giảm đột ngột sau VACUUM FULL hoặc REINDEX
Seq Scan thay thế Index Scan.VACUUM FULL và REINDEX ghi lại các bảng/chỉ mục, có khả năng dẫn đến trạng thái tạm thời mà thống kê bị cũ hoặc bộ đệm của trình lập kế hoạch bị vô hiệu hóa.ANALYZE VERBOSE; trên các bảng bị ảnh hưởng hoặc toàn bộ cơ sở dữ liệu sau các thao tác đó. Điều này buộc trình lập kế hoạch phải xây dựng lại thống kê chính xác. Đối với REINDEX, đảm bảo ANALYZE được chạy trên bảng sau khi chỉ mục được xây dựng lại.Vấn đề: work_mem tràn mặc dù cài đặt cao
EXPLAIN ANALYZE hiển thị Sort Method: external merge Disk hoặc HashAggregate ... Disk ngay cả khi work_mem được đặt ở giá trị dường như cao (ví dụ: 256MB).work_mem được cấp phát cho mỗi thao tác. Một truy vấn phức tạp duy nhất có thể có nhiều thao tác sắp xếp hoặc băm, mỗi thao tác tiêu thụ work_mem. Ngoài ra, nếu nhiều truy vấn đồng thời đang chạy, tổng mức tiêu thụ bộ nhớ có thể vượt quá RAM hệ thống.work_mem thêm: Nếu đó là một truy vấn đơn lẻ, quan trọng, hãy thử tăng work_mem cho phiên đó.ORDER BY hoặc GROUP BY. Tái cấu trúc truy vấn để giảm kích thước tập dữ liệu trước khi sắp xếp/băm.pg_stat_activity để xem temp_bytes cho các truy vấn đang hoạt động. Nếu tổng temp_bytes trên tất cả các truy vấn cao, bạn có thể đang đạt đến giới hạn hệ thống.Vấn đề: Trình lập kế hoạch chọn thứ tự kết nối không tối ưu
EXPLAIN ANALYZE hiển thị thứ tự kết nối xử lý một tập hợp kết quả trung gian lớn sớm.WHERE phức tạp mà trình lập kế hoạch không thể ước tính chính xác.ANALYZE: Đảm bảo tất cả các bảng liên quan có thống kê cập nhật.CREATE STATISTICS: Đối với các tương quan đa cột hoặc phụ thuộc chức năng, hãy tạo thống kê mở rộng.
CREATE STATISTICS my_correlation_stats ON (col1, col2) FROM my_table;
ANALYZE my_table;
WHERE: Nếu có thể, hãy chia nhỏ các vị ngữ phức tạp.SET join_collapse_limit / SET from_collapse_limit: Tạm thời giảm các GUC này để buộc trình lập kế hoạch xem xét nhiều thứ tự kết nối hơn, mặc dù điều này có thể làm tăng thời gian lập kế hoạch.SET enable_nestloop = off;: Tạm thời vô hiệu hóa các loại kết nối cụ thể để buộc trình lập kế hoạch xem xét các lựa chọn thay thế, hữu ích cho việc gỡ lỗi.Vấn đề: Planning Time cao trong EXPLAIN ANALYZE
Planning Time liên tục cao (ví dụ: hàng trăm mili giây đến vài giây), ngay cả đối với các truy vấn đơn giản.UNION.default_statistics_target cao: Mặc dù tốt cho độ chính xác, các giá trị cao hơn có nghĩa là nhiều dữ liệu hơn để xử lý trong quá trình lập kế hoạch.enable_seqscan = off: Buộc quét chỉ mục ở mọi nơi có thể khiến trình lập kế hoạch làm việc vất vả hơn để tìm một chỉ mục.default_statistics_target không quá cao đối với khối lượng công việc của bạn.Làm cách nào để biết work_mem của tôi được đặt đúng cách?
Kiểm tra đầu ra EXPLAIN ANALYZE để tìm Sort Method: external merge Disk hoặc HashAggregate ... Disk. Nếu chúng xuất hiện, work_mem quá thấp cho thao tác cụ thể đó. Ngoài ra, hãy giám sát pg_stat_statements để tìm temp_bytes và temp_files để xem tổng mức sử dụng tệp tạm thời.
Khi nào tôi nên sử dụng CREATE STATISTICS?
Sử dụng CREATE STATISTICS khi trình lập kế hoạch liên tục đưa ra các ước tính hàng kém cho các truy vấn liên quan đến nhiều cột trong các mệnh đề WHERE, điều kiện JOIN hoặc các mệnh đề GROUP BY, đặc biệt nếu các cột này có dữ liệu tương quan. Điều này giúp trình lập kế hoạch hiểu các mối quan hệ giữa các cột mà thống kê một cột bỏ qua.
Sự khác biệt giữa shared_buffers và effective_cache_size là gì?
shared_buffers là bộ nhớ thực tế mà PostgreSQL cấp phát cho bộ đệm dữ liệu của nó. effective_cache_size là một GUC cho trình lập kế hoạch truy vấn biết lượng bộ nhớ mà nó mong đợi có sẵn để lưu trữ dữ liệu, bao gồm shared_buffers và bộ đệm hệ thống tệp OS. Nó không cấp phát bộ nhớ mà ảnh hưởng đến ước tính chi phí I/O đĩa của trình lập kế hoạch.
Truy vấn của tôi đang sử dụng Seq Scan thay vì Index Scan ngay cả khi có chỉ mục. Tại sao?
Điều này thường xảy ra khi trình lập kế hoạch ước tính rằng quét tuần tự sẽ nhanh hơn quét chỉ mục. Các lý do phổ biến bao gồm:
ANALYZE.Seq Scan thường nhanh hơn do chi phí truy cập chỉ mục.random_page_cost: Nếu GUC này được đặt quá cao, nó làm cho I/O ngẫu nhiên (quét chỉ mục) có vẻ tốn kém hơn.REINDEX có thể giúp ích.Làm cách nào để buộc PostgreSQL sử dụng một kế hoạch cụ thể?
Mặc dù nói chung không được khuyến khích vì nó có thể dẫn đến các kế hoạch không tối ưu nếu dữ liệu thay đổi, bạn có thể ảnh hưởng đến trình lập kế hoạch bằng cách tạm thời vô hiệu hóa một số loại kế hoạch nhất định bằng cách sử dụng GUC (ví dụ: SET enable_seqscan = off;, SET enable_nestloop = off;). Để kiểm soát chi tiết hơn, các công cụ như pg_hint_plan (một tiện ích mở rộng) cho phép các gợi ý cấp SQL, nhưng điều này nên được sử dụng hết sức thận trọng và chỉ sau khi thử nghiệm kỹ lưỡng. Cách tiếp cận tốt nhất là khắc phục các vấn đề về thống kê hoặc lập chỉ mục cơ bản.
Một quản trị viên cơ sở dữ liệu nhận thấy rằng đầu ra `EXPLAIN ANALYZE` của một truy vấn quan trọng thường xuyên hiển thị số lượng `Buffers: shared read` cao cho một bảng cụ thể, mặc dù bảng đó tương đối nhỏ và được truy cập thường xuyên. Nguyên nhân trực tiếp có khả năng nhất của quan sát này là gì, và hành động trực tiếp nhất để điều tra thêm là gì?
Làm sạch log AI CLI, tạo biểu thức cron, giải mã JWT và tính quyền chmod offline.

Chẩn đoán và loại bỏ tình trạng phình (bloat) bảng và index trong PostgreSQL. Nắm vững các công thức tinh chỉnh autovacuum, nén dữ liệu không downtime với pg_repack, và cơ chế visibility map của MVCC.
Đọc tiếp
Hướng dẫn kiến trúc chuyên sâu về kết nối PostgreSQL: PgCat so với PgBouncer so với Supavisor dưới tải cao với các ví dụ thực tế đã được kiểm chứng.
Đọc tiếp
Hướng dẫn toàn diện về postgresql change data capture (cdc): debezium, kafka connect & transactional outbox với kiến trúc cấp độ production và ví dụ code.
Đọc tiếp