Query |A|n|a|l|y|z|e|r| for Rails

The Query Analyzer plugin will expand the usability of your log files and/or console by providing your dbms`s query analysis.

Each SQL select query ‘EXPLAIN’ is and added right below the original query.

Using this plugin and a good understanding of the results, you will be able to analyze and optimize the queries your application is making.

Refer to your DBMS manual for more information on understanding the results.

Install

  gem install nofxx-query_analyzer

config/enviroments/development.rb

  config.gem "nofxx-query_analyzer", :lib => "query_analyzer"

Example Use (MySQL)

Here is a real life usage of the plugin that detected the omission of indexes on a table. In this case, it was a join table and the keys didn’t have indexes (silly me!). Names have been changed to protect the innocent (and make it fit 80 columns)

# development.log

P Load (0.008669)

 => SELECT p.* FROM p INNER JOIN d ON p.id = d.p_id WHERE (d.p_id = 2 AND ((d.type = 'P')))

Analyzing P Load

select_type | key_len | type | Extra | id | possible_keys | rows | table | ref | key

SIMPLE | | ALL | Using where | 1 | | 74 | d | | SIMPLE | 4 | eq_ref | Using where | 1 | PRIMARY | 1 | p | d.p_id | PRIMARY

Analyze the results

Looking at the results of the execution plan, we can see that the lookup in the d table is missing an index (possible_keys=null) and performed a full table scan (type=ALL) to satisfy the WHERE condition. In this case, there was only one row that matched the condition in the table, but MySQL still had to search all 74 rows in the table to find it, a key indicator of a missing or malformed index(es). Once it has pulled all the records to satisfy the WHERE, it then starts the p table join. This time, it was able to match d.p_id to p.id using the PRIMARY key on the p table. The type=eq_ref indicates a 1 to 1 match against a primary or unique column.

Lets add some indexes to the join table and see if we can cut that full table scan down in the number of rows it needs to search.

  script/generate Migration AddIndexesToD

# file: 005_add_indexes_to_d

  class AddIndexesToD < ActiveRecord::Migration
    def self.up
      add_index :d, [ :p_id, :type ]
      add_index :d, :type
    end

    def self.remove
      remove_index :d, [ :p_id, :type ]
      remove_index :d, :type
    end
  end

Now that we have an index on the foreign_key column and type, lets re-run the query and see if we got rid of that full table scan.

# development.log

P Load (0.009011)

 => SELECT p.* FROM p INNER JOIN d ON p.id = d.p_id WHERE (d.p_id = 2 AND ((d.type = 'P')))

Analyzing P Load

select_type | key_len | type | Extra | id | possible_keys | rows | table | ref | key

SIMPLE | 255 | ref | Using where | 1 | d_p_id_type_index,d_type_index | 1 | d | const | d_p_id_type_index SIMPLE | 4 | eq_ref | Using where | 1 | PRIMARY | 1 | p | d.p_id | PRIMARY

Okay. Now MySQL is using an index satisfy the WHERE condition. Using the index, it was able to find the single row that matched, preventing the full table scan.

PostgreSQL

Output example:

  Analyzing Car Load Execution Time:

   Hash Join  (cost=8.01..21.90 rows=6 width=2699)
   Hash Cond: (cars.group_id = group.id)
   ->  Seq Scan on cars  (cost=0.00..12.80 rows=280 width=2699)
   ->  Hash  (cost=8.00..8.00 rows=1 width=8)
       ->  Hash Join  (cost=3.90..8.00 rows=1 width=8)
             Hash Cond: (groups.id = contracts.group_id)
             ->  Seq Scan on groups  (cost=0.00..3.79 rows=79 width=4)
             ->  Hash  (cost=3.89..3.89 rows=1 width=4)
                   ->  Seq Scan on contracts  (cost=0.00..3.89 rows=1 width=4)
                         Filter: (user_id = 8)

Some options:

  ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.explain_analyze = true

If true then uses the ANALYZE option which (from postgresql manual): Carry out the command and show the actual run times.

  ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.explain_verbose = true

If true then uses the VERBOSE option which (from postgresql manual): Shows the full internal representation of the plan tree, rather than a summary.

Credits:

MODIFIED by John Eberly originally take from svn.nfectio.us

PG/ORACLE by Luca Mearelli

Cooked and gemified by Marcos Piccinini

The extension of the Array class for printing the columnized records was originally written by Peter Cooper who adapted it from Courtenay from #caboose.

www.rubyinside.com/columnized-text-datasets-in-rails-71.html habtm.com/articles/2006/06/10/pretty-tables-for-ruby-objects

PostgreSQL and Oracle Adapter from Luca Mearelli spazidigitali.com

Released under the MIT license