Postgresql EXPLAIN ANALYZE 활용과 시각화
2023. 7. 12. 14:14ㆍDB/Postgresql
PostgreSQLEXPLAIN ANALYZE를 활용하여 실행 계획을 확인하고 실행 계획을 시각화 하는 방법을 정리했습니다.
Query 실행 계획 확인 방법
1. 바로 확인
EXPLAIN ANALYZE
SELECT "id"
FROM "student"
WHERE "student"."status" = 1;2. JSON으로 추출
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT "id"
FROM "student"
WHERE "student"."status" = 1;
실행계획 Visualizer
1. JSON으로 추출
[
{
"Plan": {
"Node Type": "Gather Merge",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 3959329.41,
"Total Cost": 3959586.79,
"Plan Rows": 2206,
"Plan Width": 4,
"Actual Startup Time": 2719.318,
"Actual Total Time": 2758.205,
"Actual Rows": 1784,
"Actual Loops": 1,
"Output": [
"id"
],
"Workers Planned": 2,
"Workers Launched": 2,
"Shared Hit Blocks": 1606733,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 3958329.38,
"Total Cost": 3958332.14,
"Plan Rows": 1103,
"Plan Width": 4,
"Actual Startup Time": 2714.759,
"Actual Total Time": 2714.819,
"Actual Rows": 595,
"Actual Loops": 3,
"Output": [
"id"
],
"Sort Key": [
"issue_issue.id DESC"
],
"Sort Method": "quicksort",
"Sort Space Used": 54,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 1606733,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0,
"Workers": [
{
"Worker Number": 0,
"Actual Startup Time": 2712.133,
"Actual Total Time": 2712.183,
"Actual Rows": 541,
"Actual Loops": 1,
"Sort Method": "quicksort",
"Sort Space Used": 50,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 518293,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0
},
{
"Worker Number": 1,
"Actual Startup Time": 2713.18,
"Actual Total Time": 2713.241,
"Actual Rows": 619,
"Actual Loops": 1,
"Sort Method": "quicksort",
"Sort Space Used": 54,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 518287,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0
}
],
"Plans": [
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Async Capable": false,
"Relation Name": "issue",
"Schema": "public",
"Alias": "issue",
"Startup Cost": 331240.82,
"Total Cost": 3958273.64,
"Plan Rows": 1103,
"Plan Width": 4,
"Actual Startup Time": 667.179,
"Actual Total Time": 2714.169,
"Actual Rows": 595,
"Actual Loops": 3,
"Output": [
"id"
],
"Recheck Cond": "(issue.type_id = 1009)",
"Rows Removed by Index Recheck": 1568367,
"Filter": "(((issue.status_id <> 1) OR (issue.status_id IS NULL)) AND ((issue.status_id <> 2) OR (issue.status_id IS NULL)) AND (issue_issue.created_date >= '2023-07-04 00:00:00+09'::timestamp with time zone) AND (issue_issue.created_date <= '2023-07-04 23:59:59.999999+09'::timestamp with time zone))",
"Rows Removed by Filter": 753493,
"Exact Heap Blocks": 101116,
"Lossy Heap Blocks": 415726,
"Shared Hit Blocks": 1606719,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0,
"Workers": [
{
"Worker Number": 0,
"Actual Startup Time": 677.653,
"Actual Total Time": 2711.579,
"Actual Rows": 541,
"Actual Loops": 1,
"Shared Hit Blocks": 518286,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0
},
{
"Worker Number": 1,
"Actual Startup Time": 667.324,
"Actual Total Time": 2712.583,
"Actual Rows": 619,
"Actual Loops": 1,
"Shared Hit Blocks": 518280,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0
}
],
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Index Name": "issue_test",
"Startup Cost": 0,
"Total Cost": 331240.16,
"Plan Rows": 2306613,
"Plan Width": 0,
"Actual Startup Time": 551.372,
"Actual Total Time": 551.373,
"Actual Rows": 2288132,
"Actual Loops": 1,
"Index Cond": "(issue.type_id = 1009)",
"Shared Hit Blocks": 53290,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0,
"Workers": []
}
]
}
]
}
]
},
"Query Identifier": 72742028201614000,
"Planning": {
"Shared Hit Blocks": 6,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0
},
"Planning Time": 0.19,
"Triggers": [],
"Execution Time": 2758.358
}
]
2. Explain Visualizer Website 접속
(https://tatiyants.com/pev/#/plans/new)
3. 시각화 결과
