PostgreSQL query optimization with PQA

I was playing around with phpBB on PostgreSQL and looking at how it’s database schema is not optimized for PostgreSQL or other real databases (it does not implement foreign keys and other niceties …) but it’s really built with MySQL’s older versions in mind.
Beside modifying the schema, I was also looking at tuning queries, so I discovered an article (1) on Databasejournal’s site about “PostgreSQL SQL Analysis” which was exactly what I needed.
It explained how to turn on query logging and an handy tool to help analyzing those logs named PQA (2), unfortunately this tool is written in Ruby, but it comes in soource form only, which is bad for guys like me who don’t have a whole lot of interpreters/compilers on board.
Luckyly there is a great option for us, building an executable with Rubyscript2exe (3), so I built it and placed it on this site along with the original source used to build it, the MD5 hash of the executable and some usage notes that act as my personal update notes to the article
Turning on query logging on PostgreSQL 8.0.3:
Edit postgresql.conf, like the following:
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr'
….
# This is relevant when logging to stderr:
redirect_stderr = true # Enable capturing of stderr into log files.
….
log_statement = 'all' # none, mod, ddl, all
Analyze with pqa:
C:\pqa-1.5>pqa -logtype pglog -normalize
-format html -file pglog_sample.log > formatted_log.html
You’ll find the output in formatted_log.html, all provided parameters are self-explaining
The executable is made from PQA 1.5, with Ruby 1.8.2 and Rubyscript2Exe 0.3.5.
Pqa.exe is zipped into pqa.zip MD5: 3a28e5bf8edcae429b63f97a5cf846ad (made with winMD5Sum).
(1) DatabaseJournal’s article http://www.databasejournal.com/features/postgresql/article.php/3323561
(2) PostgreSQL Query Analyzer http://pgfoundry.org/projects/pqa/
(3) Rubyscript2Exe http://www.erikveen.dds.nl/rubyscript2exe/index.html