Postgresql
如何在 PostgreSQL 中閱讀 EXPLAIN ANALYZE
我已經閱讀了網際網路上的大量資源和 PostgreSQL 的官方文件，但我仍然不知道如何解釋 EXPLAIN ANALYZE 的結果。我使用的是 PostgreSQL 12.8，執行 EXPLAIN ANALYZE 命令時得到以下結果：
用文字編輯
QUERY PLAN ------------------------------------------------ Nested Loop (cost=138604.33.. 149260.39 rows=1 width=238) (actual time=448.894.. 162293.701 rows=372 loops=1) Join Filter: (s.id = setting_aud.id) Rows Removed by Join Filter: 69006 Buffers: shared hit=38723745 -> Gather (cost=1000.00.. 10052.82 rows=1 width=234) (actual time=0.264..0.799 rows=372 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=7845 -> Parallel Seq Scan on settings (cost=0.00..9052.72 rows=1 width=234) (actual time=5.367..15.796 rows=124 loops=3) Filter: (active AND ((type)::text = 'AgePlateLever'::text)) Rows Removed by Filter: 76875 Buffers: shared hit=7845 -> Finalize GroupAggregate (cost=137604.33. . 139076.80 rows=5812 width=12) (actual time=436.029..436.206 rows=186 loops=372) Group Key: setting_aud.id Buffers: shared hit=13063859 -> Gather Merge (cost=137604.33..138960.56 rows=11624 width=12) (actual time=436.022..436.121 rows=554 loops=372) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=13063859 -> Sort (cost=136604.31..136618.84 rows=5812 width=12) (actual time=431.252..431.278 rows=480 loops=1116) Sort Key: setting_aud.id Sort Method: quicksort Memory: 59kB Worker 0: Sort Method: quicksort Memory: 59kB Worker 1: Sort Method: quicksort Memory: 59kB Buffers: shared hit=38715900 -> Partial HashAggregate (cost=136182.79..136240.91 rows=5812 width=12) (actual time=430.954..431.065 rows=741 loops=1116) Group Key: setting_aud.id Buffers: shared hit=38709948 -> Parallel Seq Scan on setting_aud (cost=0.00.. 129655.88 rows=1305384 width=12) (actual time=0.017..282.585 rows=1040198 loops=1116) Filter: ((revision_type <> 2) AND ((type)::text = 'AgeplateLever'::text)) Rows Removed by Filter: 324937 Buffers: shared hit=38709948 Planning Time: 0.332 ms Execution Time: 162294.083 ms我怎麼知道在哪裡放置索引來查看這些結果？我怎麼知道在
Nested Loop連接中執行了多少次迭代？編輯 2
我添加了以下索引：
CREATE INDEX setting_type_active_id_idx ON setting (type, active, id); CREATE INDEX setting_aud_type_revision_type_id_revision_id_idx ON setting_aud (type, revision_type, id, revision_id);並且還使用 VERBOSE 選項執行 EXPLAIN 以查看 Laurenz Albe 建議的更多資訊，現在結果更好：
QUERY PLAN | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Merge Join (cost=139152.19..140705.34 rows=1 width=238) (actual time=468.645..470.827 rows=372 loops=1) | Output: s.id, s.setting_key, s.type, s.cap_id, s.current_value, s.source, s.rationale, s.created_date, s.last_modified_date, s.model_year, s.batch_adjustment_id, s.active, s.last_activated_at, s.setting_metadata_id, (max(setting_aud.revision_id))| Inner Unique: true | Merge Cond: (s.id = setting_aud.id) | Buffers: shared hit=105643 | -> Index Scan using setting_type_active_id_idx on db_schema.setting s (cost=0.42..8.44 rows=1 width=234) (actual time=0.022..0.385 rows=372 loops=1) | Output: s.id, s.setting_key, s.type, s.cap_id, s.current_value, s.source, s.rationale, s.created_date, s.last_modified_date, s.model_year, s.batch_adjustment_id, s.active, s.last_activated_at, s.setting_metadata_id | Index Cond: (((s.type)::text = 'AgePlateLever'::text) AND (s.active = true)) | Buffers: shared hit=385 | -> Finalize GroupAggregate (cost=139151.77..140624.24 rows=5812 width=12) (actual time=468.620..470.347 rows=372 loops=1) | Output: setting_aud.id, max(setting_aud.revision_id) | Group Key: setting_aud.id | Buffers: shared hit=105258 | -> Gather Merge (cost=139151.77..140508.00 rows=11624 width=12) (actual time=468.610..470.165 rows=1109 loops=1) | Output: setting_aud.id, (PARTIAL max(setting_aud.revision_id)) | Workers Planned: 2 | Workers Launched: 2 | Buffers: shared hit=105258 | -> Sort (cost=138151.75..138166.28 rows=5812 width=12) (actual time=465.746..465.780 rows=618 loops=3) | Output: setting_aud.id, (PARTIAL max(setting_aud.revision_id)) | Sort Key: setting_aud.id | Sort Method: quicksort Memory: 59kB | Worker 0: Sort Method: quicksort Memory: 59kB | Worker 1: Sort Method: quicksort Memory: 59kB | Buffers: shared hit=105258 | Worker 0: actual time=463.883..463.923 rows=740 loops=1 | Buffers: shared hit=34816 | Worker 1: actual time=465.030..465.070 rows=740 loops=1 | Buffers: shared hit=37348 | -> Partial HashAggregate (cost=137730.24..137788.36 rows=5812 width=12) (actual time=465.461..465.568 rows=741 loops=3) | Output: setting_aud.id, PARTIAL max(setting_aud.revision_id) | Group Key: setting_aud.id | Buffers: shared hit=105242 | Worker 0: actual time=463.576..463.685 rows=740 loops=1 | Buffers: shared hit=34808 | Worker 1: actual time=464.728..464.834 rows=740 loops=1 | Buffers: shared hit=37340 | -> Parallel Seq Scan on db_schema.setting_aud (cost=0.00..131129.27 rows=1320193 width=12) (actual time=0.017..304.923 rows=1052528 loops=3) | Output: setting_aud.id, setting_aud.revision_id | Filter: ((setting_aud.revision_type <> 2) AND ((setting_aud.type)::text = 'AgePlateLever'::text)) | Rows Removed by Filter: 328028 | Buffers: shared hit=105242 | Worker 0: actual time=0.006..303.967 rows=1044836 loops=1 | Buffers: shared hit=34808 | Worker 1: actual time=0.013..304.850 rows=1117851 loops=1 | Buffers: shared hit=37340 | Planning Time: 0.234 ms | Execution Time: 470.919 ms |現在正在對
setting錶使用索引掃描，但為什麼仍在對 setting_aud 進行並行 Seq 掃描而不是索引掃描？為什麼循環減少了？
索引可以幫助的唯一潛在地方是
-> Parallel Seq Scan on setting_aud (cost=0.00.. 129655.88 rows=1305384 width=12) (actual time=0.017..282.585 rows=1040198 loops=1116) Filter: ((revision_type <> 2) AND ((type)::text = 'AgeplateLever'::text)) Rows Removed by Filter: 324937 Buffers: shared hit=38709948但是索引在這裡並沒有太大幫助，因為只有幾行被過濾掉了。可能有幫助的是將所有必需的列添加到索引中，以便您可以獲得僅索引掃描，但要確定哪些列是您需要
EXPLAIN (VERBOSE).但是，主要問題可能是對
setting（124 而不是 1）上的掃描估計錯誤，這導致優化器選擇嵌套循環連接，因此setting_aud掃描了 1116 次。要解決這個問題，請嘗試改進估計：
- 或許
ANALYZE setting已經足夠了。- 如果沒有，請改進統計資訊：
ALTER TABLE setting ALTER type SET STATISTICS 1000; ANALYZE setting;
- 如果這還不夠，請嘗試擴展統計資訊：
CREATE STATISTICS some_name (dependencies) ON type, active FROM setting; ANALYZE setting;然後希望選擇散列或合併連接，並且性能會提高。