implements AR::Relation#explain

This is a first implementation, EXPLAIN is highly
dependent on the database and I have made some
compromises.

On one hand, the method allows you to run the most
common EXPLAIN and that's it. If you want EXPLAIN
ANALYZE in PostgreSQL you need to do it by hand.

On the other hand, I've tried to construct a string
as close as possible to the ones built by the
respective shells. The rationale is that IMO the
user should feel at home with the output and
recognize it at first sight. Per database.

I don't know whether this implementation is going
to work well. Let's see whether people like it.

8 files changed, 226 insertions, 1 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 6b1cf4c841..a79f4df570 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,5 +1,7 @@
 ## Rails 3.2.0 (unreleased) ##
 
+*   Implemented ActiveRecord::Relation#explain. *fxn*
+
 *   Add ActiveRecord::Relation#uniq for generating unique queries.
 
     Before:
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
index 306b185c5e..1146323147 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -228,6 +228,80 @@ module ActiveRecord
 
       # DATABASE STATEMENTS ======================================
 
+      def explain(arel)
+        sql     = "EXPLAIN #{to_sql(arel)}"
+        start   = Time.now
+        result  = exec_query(sql, 'EXPLAIN')
+        elapsed = Time.now - start
+
+        ExplainPrettyPrinter.new.pp(result, elapsed)
+      end
+
+      class ExplainPrettyPrinter # :nodoc:
+        # Pretty prints the result of a EXPLAIN in a way that resembles the output of the
+        # MySQL shell:
+        #
+        #   +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
+        #   | id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+        #   +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
+        #   |  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 4       | const |    1 |             |
+        #   |  1 | SIMPLE      | posts | ALL   | NULL          | NULL    | NULL    | NULL  |    1 | Using where |
+        #   +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
+        #   2 rows in set (0.00 sec)
+        #
+        # This is an exercise in Ruby hyperrealism :).
+        def pp(result, elapsed)
+          widths    = compute_column_widths(result)
+          separator = build_separator(widths)
+
+          pp = []
+
+          pp << separator
+          pp << build_cells(result.columns, widths)
+          pp << separator
+
+          result.rows.each do |row|
+            pp << build_cells(row, widths)
+          end
+
+          pp << separator
+          pp << build_footer(result.rows.length, elapsed)
+
+          pp.join("\n") + "\n"
+        end
+
+        private
+
+        def compute_column_widths(result)
+          [].tap do |widths|
+            result.columns.each_with_index do |column, i|
+              cells_in_column = [column] + result.rows.map {|r| r[i].nil? ? 'NULL' : r[i].to_s}
+              widths << cells_in_column.map(&:length).max
+            end
+          end
+        end
+
+        def build_separator(widths)
+          # Each cell has one char of padding at both sides, that's why we add 2.
+          '+' + widths.map {|w| '-' * (w + 2)}.join('+')
+        end
+
+        def build_cells(items, widths)
+          cells = []
+          items.each_with_index do |item, i|
+            item = 'NULL' if item.nil?
+            justifier = item.is_a?(Numeric) ? 'rjust' : 'ljust'
+            cells << item.to_s.send(justifier, widths[i])
+          end
+          '| ' + cells.join(' | ') + ' |'
+        end
+
+        def build_footer(nrows, elapsed)
+          rows_label = nrows == 1 ? 'row' : 'rows'
+          "#{nrows} #{rows_label} in set (%.2f sec)" % elapsed
+        end
+      end
+
       # Executes the SQL statement in the context of this connection.
       def execute(sql, name = nil)
         if name == :skip_logging
@@ -290,6 +364,7 @@ module ActiveRecord
       # these, we must use a subquery. However, MySQL is too stupid to create a
       # temporary table for this automatically, so we have to give it some prompting
       # in the form of a subsubquery. Ugh!
+
       def join_to_update(update, select) #:nodoc:
         if select.limit || select.offset || select.orders.any?
           subsubselect = select.clone
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index 15e329a1c8..44c2fa439c 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -517,6 +517,48 @@ module ActiveRecord
 
       # DATABASE STATEMENTS ======================================
 
+      def explain(arel)
+        sql = "EXPLAIN #{to_sql(arel)}"
+        ExplainPrettyPrinter.new.pp(exec_query(sql))
+      end
+
+      class ExplainPrettyPrinter # :nodoc:
+        # Pretty prints the result of a EXPLAIN in a way that resembles the output of the
+        # PostgreSQL shell:
+        #
+        #                                     QUERY PLAN
+        #   ------------------------------------------------------------------------------
+        #    Nested Loop Left Join  (cost=0.00..37.24 rows=8 width=0)
+        #      Join Filter: (posts.user_id = users.id)
+        #      ->  Index Scan using users_pkey on users  (cost=0.00..8.27 rows=1 width=4)
+        #            Index Cond: (id = 1)
+        #      ->  Seq Scan on posts  (cost=0.00..28.88 rows=8 width=4)
+        #            Filter: (posts.user_id = 1)
+        #   (6 rows)
+        #
+        def pp(result)
+          header = result.columns.first
+          lines  = result.rows.map(&:first)
+
+          # We add 2 because there's one char of padding at both sides, note
+          # the extra hyphens in the example above.
+          width = [header, *lines].map(&:length).max + 2
+
+          pp = []
+
+          pp << header.center(width).rstrip
+          pp << '-' * width
+
+          pp += lines.map {|line| " #{line}"}
+
+          nrows = result.rows.length
+          rows_label = nrows == 1 ? 'row' : 'rows'
+          pp << "(#{nrows} #{rows_label})"
+
+          pp.join("\n") + "\n"
+        end
+      end
+
       # Executes a SELECT query and returns an array of rows. Each row is an
       # array of field values.
       def select_rows(sql, name = nil)
@@ -836,7 +878,7 @@ module ActiveRecord
 
       # Returns the active schema search path.
       def schema_search_path
-        @schema_search_path ||= query('SHOW search_path')[0][0]
+        @schema_search_path ||= query('SHOW search_path', 'SCHEMA')[0][0]
       end
 
       # Returns the current client message level.
diff --git a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb
index caecbc9b3a..35df0a1542 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb
@@ -222,6 +222,25 @@ module ActiveRecord
 
       # DATABASE STATEMENTS ======================================
 
+      def explain(arel)
+        sql = "EXPLAIN QUERY PLAN #{to_sql(arel)}"
+        ExplainPrettyPrinter.new.pp(exec_query(sql, 'EXPLAIN'))
+      end
+
+      class ExplainPrettyPrinter
+        # Pretty prints the result of a EXPLAIN QUERY PLAN in a way that resembles
+        # the output of the SQLite shell:
+        #
+        #   0|0|0|SEARCH TABLE users USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
+        #   0|1|1|SCAN TABLE posts (~100000 rows)
+        #
+        def pp(result) # :nodoc:
+          result.rows.map do |row|
+            row.join('|')
+          end.join("\n") + "\n"
+        end
+      end
+
       def exec_query(sql, name = nil, binds = [])
         log(sql, name, binds) do
 
diff --git a/activerecord/lib/active_record/relation.rb b/activerecord/lib/active_record/relation.rb
index 3baf9b3f49..f0891440a6 100644
--- a/activerecord/lib/active_record/relation.rb
+++ b/activerecord/lib/active_record/relation.rb
@@ -143,6 +143,22 @@ module ActiveRecord
         super
     end
 
+    def explain
+      queries = []
+      callback = lambda do |*args|
+        payload = args.last
+        queries << payload[:sql] unless payload[:exception] || %w(SCHEMA EXPLAIN).include?(payload[:name])
+      end
+
+      ActiveSupport::Notifications.subscribed(callback, "sql.active_record") do
+        to_a
+      end
+
+      queries.map do |sql|
+        @klass.connection.explain(sql)
+      end.join
+    end
+
     def to_a
       return @records if loaded?
 
diff --git a/activerecord/test/cases/adapters/mysql2/explain_test.rb b/activerecord/test/cases/adapters/mysql2/explain_test.rb
new file mode 100644
index 0000000000..8ea777b72b
--- /dev/null
+++ b/activerecord/test/cases/adapters/mysql2/explain_test.rb
@@ -0,0 +1,23 @@
+require "cases/helper"
+require 'models/developer'
+
+module ActiveRecord
+  module ConnectionAdapters
+    class Mysql2Adapter
+      class ExplainTest < ActiveRecord::TestCase
+        fixtures :developers
+
+        def test_explain_for_one_query
+          explain = Developer.where(:id => 1).explain
+          assert_match %(developers | const), explain
+        end
+
+        def test_explain_with_eager_loading
+          explain = Developer.where(:id => 1).includes(:audit_logs).explain
+          assert_match %(developers | const), explain
+          assert_match %(audit_logs | ALL), explain
+        end
+      end
+    end
+  end
+end
diff --git a/activerecord/test/cases/adapters/postgresql/explain_test.rb b/activerecord/test/cases/adapters/postgresql/explain_test.rb
new file mode 100644
index 0000000000..0d599ed37f
--- /dev/null
+++ b/activerecord/test/cases/adapters/postgresql/explain_test.rb
@@ -0,0 +1,25 @@
+require "cases/helper"
+require 'models/developer'
+
+module ActiveRecord
+  module ConnectionAdapters
+    class PostgreSQLAdapter
+      class ExplainTest < ActiveRecord::TestCase
+        fixtures :developers
+
+        def test_explain_for_one_query
+          explain = Developer.where(:id => 1).explain
+          assert_match %(QUERY PLAN), explain
+          assert_match %(Index Scan using developers_pkey on developers), explain
+        end
+
+        def test_explain_with_eager_loading
+          explain = Developer.where(:id => 1).includes(:audit_logs).explain
+          assert_match %(QUERY PLAN), explain
+          assert_match %(Index Scan using developers_pkey on developers), explain
+          assert_match %(Seq Scan on audit_logs), explain
+        end
+      end
+    end
+  end
+end
diff --git a/activerecord/test/cases/adapters/sqlite3/explain_test.rb b/activerecord/test/cases/adapters/sqlite3/explain_test.rb
new file mode 100644
index 0000000000..97be9f14e9
--- /dev/null
+++ b/activerecord/test/cases/adapters/sqlite3/explain_test.rb
@@ -0,0 +1,23 @@
+require "cases/helper"
+require 'models/developer'
+
+module ActiveRecord
+  module ConnectionAdapters
+    class SQLite3Adapter
+      class ExplainTest < ActiveRecord::TestCase
+        fixtures :developers
+
+        def test_explain_for_one_query
+          explain = Developer.where(:id => 1).explain
+          assert_match %(SEARCH TABLE developers USING INTEGER PRIMARY KEY), explain
+        end
+
+        def test_explain_with_eager_loading
+          explain = Developer.where(:id => 1).includes(:audit_logs).explain
+          assert_match %(SEARCH TABLE developers USING INTEGER PRIMARY KEY), explain
+          assert_match %(SCAN TABLE audit_logs), explain
+        end
+      end
+    end
+  end
+end