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.

3 files changed, 137 insertions, 1 deletions

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