Optimize SQL Queries Using EXPLAIN in PostgreSQL

By Ardine Hinium

An essential tool to inspect slow SQL queries in Ruby on Rails is the Postgres EXPLAIN command. Perhaps you've seen this command in your own web server logs. ActiveRecord in Ruby on Rails will automatically re-run a slow query with EXPLAIN in the development environment. Using this command, you can gain insights into what Postgres is doing behind the scenes.

This article will take a look at one of several quick strategies to make use of EXPLAIN to find missing indexes. Suppose you see a line show up that looks like:

-> Seq Scan in vehicles (cost=0. 00.. 102401. 77 rows=49 width=943)

This indicates the Postgres server has identified it needs to iterate sequentially through result sets. If you see a 'Seq Scan' within your records, it is usually a superb indicator of a probable missing SQL index.

Take a look at your query that is running through EXPLAIN. Postgres tells us the 'Seq Scan' is certainly going through the vehicle results list, and I believed I needed an index within the company_id because that's inside the WHERE clause of my query.

If you continue to dig through your logs, you may also see a Filter line. This is a good place to find a part of your query that indicates what column is present in a WHERE clause, and thus needs an index.

The solution is to create a Rails migration to add the index using the create_index command.

Soon after creating this migration, I'm able to say this specific query has sped up substantially and I no longer see the EXPLAIN in my server logs.

Note that when your app is running in production, ActiveRecord will not attempt to re-run slow queries using EXPLAIN due to performance issues. Running EXPLAIN requires re-running a slow query a second time (after it's been determined to be slow), in order to output the complete query plan as generated by the database.

Always run your application thoroughly in development mode before you attempt to turn it on in production in order to take advantage of ActiveRecord's comprehensive slow query analyzer. In addition, if you're using a tool like New Relic, you may get alerts to slow queries, which you can then try running again in development mode.

There are many additional ways to speed up your slow SQL queries, and EXPLAIN is only one option. But it's often a quick way to spot and fix performance problems. Hope this is helpful to you too!

I highly recommend reading further about optimizing slow queries in Ruby on Rails using the Postgres EXPLAIN command at the ElegantRuby.com blog.

Article Source: http://EzineArticles.com/?expert=Ardine_Hinium