Foreign Key Indexes

One of our PostgreSQL databases started slowing down considerably over three years of operation. To debug what was going on, we edited the /etc/postgresql/8.4/main/postgresql.conf file and set the parameter "log_min_duration_statement = 200". Save the file then run "service postgresql reload".

This will start logging all queries that take longer than 200ms to execute. You can view the output in the /var/log/postgresql directory. You may need to tweak your number up or down to filter the most important queries.

Once you find some queries that take a long time to execute, copy them from the log, run "psql" and connect to your database. Then run "EXPLAIN ANALYZE the_slow_query;". This will show exactly what postgres does to run the query, and how long each step takes.

In our case, there were a number of slow SELECT queries with many nested JOIN statements.

One possible cause for this is that by default, postgresql creates indexes for primary keys, but NOT for foreign keys, which may affect performance of join statements.

We ran a script to create indexes for all foreign keys, then ran a "reindex", an "analyze" and a "vacuum". A report that took 20 minutes or longer then started running in under 3 minutes. Likewise, certain other reports dropped from 30 seconds to 5 or 6 seconds. Seems to have done the trick...

Note that we also ran "reindex", "vacuum", "analyze" and "vacuum full" before creating the new foreign-key indexes and this did not improve performance, so I assume the new indexes definitely contribute to the performance gain.


Popular posts from this blog

Wkhtmltopdf font and sizing issues

Import Google Contacts to Nokia PC Suite

Can't delete last blank page from Word