CODEWITHSUNDEEP
postgresqlexplain
Csuszmusz
Csuszmusz

Reputation: 335

Analyze stored procedures in PostgreSQL

So I have a complex, almost 200 lines long stored procedure in PostgreSQL and I would like to analyze it quickly, but unfortunately the PgAdmin's built in explain analyze function does not support nested loops and it does not let me look under the hood, so I updated my postgresql.conf file with the following:

auto_explain.log_analyze = true
auto_explain.log_timing = true
auto_explain.log_verbose = true
auto_explain.log_min_duration = '0ms'
auto_explain.log_nested_statements = true
auto_explain.log_buffers = true

So I can see the detailed logs in my pg_log folder, but it generates almost 300 lines long result log and its not easy to analyze.

Is there a better, more elegant way to do this? Maybe is there a UI tool for it on windows?

Upvotes: 1

Views: 6331

Answers (2)

Anthony Sotolongo
Anthony Sotolongo

Reputation: 1648

While explain.depesz.com is very useful, you can analyze your procedure with https://github.com/bigsql/plprofiler as well. You can combine both tools

Upvotes: 2

Csuszmusz
Csuszmusz

Reputation: 335

As @a_horse_with_no_name suggested it in the comments the explain.depesz.com site is very useful. Just have to copy paste your explain analyze plan, and see the output. You can click on column headers to let it know which parameter is the most important for you – exclusive node time, inclusive node time, or rowcount mis-estimate.

About explain.depesz.com

Upvotes: 1

Related Questions