Check the PostgreSQL plan for visualization
Today we will see how to generate plans in graphical form freely in PostgreSQL.
Try to generate a plan in json format by using the below prefix before any query you want to check plan explain (format json)
new_tbs_olap=# explain (format json) SELECT name, count(*)
new_tbs_olap-# FROM t_gender AS a, t_person AS b
new_tbs_olap-# WHERE a.id = b.gender
new_tbs_olap-# GROUP BY 1;
QUERY PLAN
[ +
{ +
"Plan": { +
"Node Type": "Aggregate", +
"Strategy": "Sorted", +
"Partial Mode": "Finalize", +
"Parallel Aware": false, +
"Async Capable": false, +
"Startup Cost": 607798.95, +
"Total Cost": 607849.62, +
"Plan Rows": 200, +
"Plan Width": 40, +
"Group Key": ["a.name"], +
"Plans": [ +
{ +
"Node Type": "Gather Merge", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Async Capable": false, +
"Startup Cost": 607798.95, +
"Total Cost": 607845.62, +
"Plan Rows": 400, +
"Plan Width": 40, +
"Workers Planned": 2, +
"Plans": [ +
{ +
"Node Type": "Sort", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Async Capable": false, +
"Startup Cost": 606798.93, +
"Total Cost": 606799.43, +
"Plan Rows": 200, +
"Plan Width": 40, +
"Sort Key": ["a.name"], +
"Plans": [ +
{ +
"Node Type": "Aggregate", +
"Strategy": "Hashed", +
"Partial Mode": "Partial", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Async Capable": false, +
"Startup Cost": 606789.28, +
"Total Cost": 606791.28, +
"Plan Rows": 200, +
"Plan Width": 40, +
"Group Key": ["a.name"], +
"Planned Partitions": 0, +
"Plans": [ +
{ +
"Node Type": "Hash Join", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Async Capable": false, +
"Join Type": "Inner", +
"Startup Cost": 38.58, +
"Total Cost": 540644.01, +
"Plan Rows": 13229055, +
"Plan Width": 32, +
"Inner Unique": false, +
"Hash Cond": "(b.gender = a.id)", +
"Plans": [ +
{ +
"Node Type": "Seq Scan", +
"Parent Relationship": "Outer", +
"Parallel Aware": true, +
"Async Capable": false, +
"Relation Name": "t_person", +
"Alias": "b", +
"Startup Cost": 0.00, +
"Total Cost": 72380.16, +
"Plan Rows": 2083316, +
"Plan Width": 4 +
}, +
{ +
"Node Type": "Hash", +
"Parent Relationship": "Inner", +
"Parallel Aware": false, +
"Async Capable": false, +
"Startup Cost": 22.70, +
"Total Cost": 22.70, +
"Plan Rows": 1270, +
"Plan Width": 36, +
"Plans": [ +
{ +
"Node Type": "Seq Scan", +
"Parent Relationship": "Outer",+
"Parallel Aware": false, +
"Async Capable": false, +
"Relation Name": "t_gender", +
"Alias": "a", +
"Startup Cost": 0.00, +
"Total Cost": 22.70, +
"Plan Rows": 1270, +
"Plan Width": 36 +
} +
] +
} +
] +
} +
] +
} +
] +
} +
] +
} +
] +
} +
} +
]
(1 row)
Go to this website https://tatiyants.com/pev/ and put the plan as instructed, click on new plan
put all the required details as per the below image and click on submit
once you submit , you can see the plan
You can see the details of each stesp by clicking view details in each leaf block of explain tree
239 total views, 1 views today