Saturday, October 22, 2005

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 articleTurning 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, allAnalyze with pqa:C:\pqa-1.5>pqa -logtype pglog -normalize-format html -file pglog_sample.log > formatted_log.htmlYou’ll find the output in formatted_log.html, all provided parameters are self-explainingThe 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