If you’d like to build a binary from the source code, run the following commands. Note that compiling requires Go version 1.13+.
git clone https://github.com/mgartner/pg_flame.git
Generate a query plan in JSON by prefixing a SQL query with EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON). Save the output to a file. Example query plan JSON can be found here.
psql lob_local -qAtc 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT id FROM users' > plan.json
Then generate the flamegraph by passing the JSON as standard input to pg_flame and direct standard output to a file.
cat plans.json | ./pg_flame > flamegraph.html
Open flamegraph.html in a browser of your choice.
Flamegraphs were invented by Brendan Gregg to visualize CPU consumption of profiled code-paths of software. They are useful visualization tools in many types of performance investigations. Luca Canali has previously shown the benefits of using flamegraph visualizations of Oracle database profiles for debugging slow database queries.
Pg_flame is in extension of that work for Postgres query plans. Instead of being used to graph CPU time of internal Postgres functions, it generates a visual hierarchy of query plans. This visualization identifies the relative time of each part of a query plan.