Rails 7.1 allows passing options to ActiveRecord explain

railsNovember 29, 2023Dotby Alkesh Ghorpade

Active Record is an object-relational mapper (ORM) for the Ruby on Rails framework. It provides a convenient interface for working with databases, including creating, reading, updating, and deleting records. The explain method is a debugging tool that helps developers understand how Active Record generates SQL queries.

The explain method was first introduced in Rails 3.2.0. Initially, the method could only print the EXPLAIN statement for the current query. The explain method debugging tool that helps developers understand how Active Record generates SQL queries. It provides insight into the sequence of operations, indexes used, and estimated costs involved in executing a particular query. This information can be valuable for identifying and optimizing performance bottlenecks in Rails applications.

Here's an example of how to use the explain method:

Post.where(published: true).explain

SELECT "posts".* FROM "posts" WHERE "posts"."published" = $1  [["published", true]]

=> 
EXPLAIN for: SELECT "posts".* FROM "posts" WHERE "posts"."published" = $1 [["published", true]]
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on posts  (cost=0.00..15.40 rows=270 width=121)
   Filter: published
(2 rows)

This code will generate an EXPLAIN statement showing the retrieval of all published posts from the database. The output of the explain method can be verbose, but it can be parsed to extract critical information such as the type of joins used, the indexes used, and the estimated cost of the query.

Before Rails 7.1

In SQL queries, analyze and verbose options can be used with the EXPLAIN statement to provide additional information about the query plan.

Passing the analyze or the verbose option to the explain method will raise an ArgumentError.

Post.where(published: true).explain(:analyze)

`explain': wrong number of arguments (given 1, expected 0) (ArgumentError)

In Rails 7.1

Rails 7.1 allows passing options to ActiveRecord explain.

You can pass either analyze, verbose or both the options to the EXPLAIN method.

Analyze

When used with explain, the analyze option provides more accurate execution estimates by gathering statistical information about the tables involved in the query. To use explain with analyze in Rails, add the :analyze option to the explain method call. For example:

Post.where(published: true).explain(:analyze)

SELECT "posts".* FROM "posts" WHERE "posts"."published" = $1  [["published", true]]
=> 
EXPLAIN (ANALYZE) SELECT "posts".* FROM "posts" INNER JOIN "authors" ON "authors"."id" = "posts"."author_id" WHERE "posts"."published" = $1 [["published", true]]
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Hash Join  (cost=21.93..37.92 rows=265 width=125) (actual time=0.003..0.004 rows=0 loops=1)
   Hash Cond: (posts.author_id = authors.id)
   ->  Seq Scan on posts  (cost=0.00..15.30 rows=265 width=125) (actual time=0.003..0.003 rows=0 loops=1)
         Filter: published
   ->  Hash  (cost=15.30..15.30 rows=530 width=8) (never executed)
         ->  Seq Scan on authors  (cost=0.00..15.30 rows=530 width=8) (never executed)
 Planning Time: 0.070 ms
 Execution Time: 0.025 ms
(8 rows)

Verbose

When used with the explain method in Rails, the verbose option provides more detailed information about the query plan. This includes additional details about the access methods used for each table, the join algorithms used, and the estimated cost of each step in the plan. By providing more verbose output, the verbose option can help developers understand the intricacies of the query plan and identify potential areas for optimization. However, the verbose output can be lengthy and require some effort to parse and analyze.

To use verbose with explain in Rails, add the :verbose option to the explain method call. For example:

Post.where(published: true).explain(:verbose)

SELECT "posts".* FROM "posts" INNER JOIN "authors" ON "authors"."id" = "posts"."author_id" WHERE "posts"."published" = $1  [["published", true]]
=> 
EXPLAIN (VERBOSE) SELECT "posts".* FROM "posts" INNER JOIN "authors" ON "authors"."id" = "posts"."author_id" WHERE "posts"."published" = $1 [["published", true]]
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=21.93..37.92 rows=265 width=125)
   Output: posts.id, posts.title, posts.description, posts.published, posts.additional_options, posts.created_at, posts.updated_at, posts.author_id
   Inner Unique: true
   Hash Cond: (posts.author_id = authors.id)
   ->  Seq Scan on public.posts  (cost=0.00..15.30 rows=265 width=125)
         Output: posts.id, posts.title, posts.description, posts.published, posts.additional_options, posts.created_at, posts.updated_at, posts.author_id
         Filter: posts.published
   ->  Hash  (cost=15.30..15.30 rows=530 width=8)
         Output: authors.id
         ->  Seq Scan on public.authors  (cost=0.00..15.30 rows=530 width=8)
               Output: authors.id
(11 rows)

To know more about this feature, please refer to this PR.

Closing Remark

Could your team use some help with topics like this and others covered by ShakaCode's blog and open source? We specialize in optimizing Rails applications, especially those with advanced JavaScript frontends, like React. We can also help you optimize your CI processes with lower costs and faster, more reliable tests. Scraping web data and lowering infrastructure costs are two other areas of specialization. Feel free to reach out to ShakaCode's CEO, Justin Gordon, at [email protected] or schedule an appointment to discuss how ShakaCode can help your project!
Are you looking for a software development partner who can
develop modern, high-performance web apps and sites?
See what we've doneArrow right