pgsi is the Postgres System Impact report, a script that analyzes Postgres log files and reports on which queries have the highest overall impact on the server. The impact is computed by looking at not only how long each query takes to complete, but how many times it is run, and the time period between subsequent runs. The report show the queries with the highest system impact, grouped by the type of query (SELECT, UPDATE, etc.)
The latest version of pgsi, 1.7.1, can be downloaded at:
To use pgsi, you must run it against a Postgres logfile that has full logging enabled. Then run:
perl pgsi.pl --file=logfile > pgsi.html
Make sure the following options are set in your postgresql.conf, or otherwise enabled for the traffic you want pgsi to analyze:
log_duration = on
` log_statement = ‘all’`
If you’re using the stderr log log destination (the default) you’ll also need something like the following line prefix at minimum:
log_line_prefix = '%t %p '
However pgsi will support additional information following that on the line. For example remote host (%h), user name (%u), and database name (%d) are often useful for filtering the log before analysis, and can be helpful if you need to manually read or track activity through the log itself.
Bugs should be reported through the Github Issues page. Feature requests are welcome there as well, or send us an email.
Everyone is encouraged to look over and make improvements to the code. The latest development version can be obtained from GitHub by running:
git clone firstname.lastname@example.org:bucardo/pgsi.git
End Point Dev offers professional support for pgsi, as well as specializing in developing, designing, and marketing effective websites. Since 1995, our diverse team of developers has shown that End Point can handle your organization’s greatest web and database challenges.