Rails 7.2 Added Support For Explain Method To ActiveRecord::Relation.

When optimizing database queries in Rails, it’s essential to understand how the database plans to execute a query. Rails provides a built-in method, ActiveRecord::Relation#explain, to analyze and display a query’s execution plan. The output mimics the format of a database shell, offering valuable insights into query performance and potential bottlenecks.

Background

Before Rails 7.1, the explain method provided basic query execution plans. Rails 7.1 introduced options like analyze and verbose to offer deeper insights into query performance. You can learn more about this in our blog post.

With Rails 7.2, the feature has been further enhanced. The explain method now supports pluck, count, first, and other methods directly on an ActiveRecord::Relation, making it even more powerful and user-friendly.

What Is ActiveRecord::Relation#explain?

The explain method runs the database’s EXPLAIN command on the query triggered by the relation and returns the result. This allows us to:

• See how the database will execute the query.
• Understand query efficiency.
• Identify and address performance issues.

Before:

Prior to Rails 7.2, we could use explain like this:

User.where(id: 1).explain

SELECT "users".* FROM "users" WHERE "users"."id" = $1  [["id", 1]]

=>                                    
EXPLAIN for: SELECT "users".* FROM "users" WHERE "users"."id" = $1 [["id", 1]]
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using users_pkey on users  (cost=0.14..8.16 rows=1 width=600)
   Index Cond: (id = '1'::bigint)     
(2 rows)                              

While explain worked well for basic queries, it failed when used with relation methods like count, pluck, or last. For example:

> User.where(id: 1).explain.count
  User Load (0.9ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1  [["id", 1]]
(irb):16:in `count': wrong number of arguments (given 0, expected 1+) (ArgumentError)

After

Rails 7.2 adds explain support to ActiveRecord::Relation.

Now we can use explain method on ActiveRecord::Relation. The object returned by explain now responds to pluck, first, last, average, count, maximum, minimum, and sum.

> User.where(id: 1).explain.count
  User Count (0.6ms)  SELECT COUNT(*) FROM "users" WHERE "users"."id" = ?  [["id", 1]]
=> 
EXPLAIN for: SELECT COUNT(*) FROM "users" WHERE "users"."id" = ? [["id", 1]]
3|0|0|SEARCH users USING INTEGER PRIMARY KEY (rowid=?)