Rails 7.1 enables detailed query plan analysis with options in ActiveRecord::Relation#explain

In Rails 7.1, an enhancement has been introduced to the ActiveRecord::Relation#explain method. This enhancement allows us to obtain detailed query plan analysis by specifying options for the explain output.

Understanding the EXPLAIN method

Before diving into the new options available in Rails 7.1, let's quickly recap the purpose and usage of the explain method in Active Record. The explain method is used to retrieve the execution plan of an SQL query chosen by the database optimizer. It provides insight into how the database intends to execute the query, including the sequence of operations, indexes used, and estimated costs.

By analyzing the query plan, we can identify potential performance bottlenecks, optimize database schema design, and fine-tune queries for better efficiency. However, prior to Rails 7.1, the level of detail available in the explain output was limited.

Detailed query plan analysis with options

In Rails 7.1, the explain method accepts options, enabling us to customize the output and obtain a more detailed query plan analysis. It's important to note that these options are the same ones that are already available in native SQL. Options may vary depending upon the database used. In this blog post, we will focus on examples using PostgreSQL. Let us take a look at some of the available options.

ANALYZE

The analyze option causes the statement to be actually executed, not only planned. Then actual run time statistics are added to the display, including the total elapsed time expended within each plan node and the total number of rows it actually returned.

Service.where('age > ?', 25).joins(:user).explain(:analyze)

EXPLAIN (ANALYZE) SELECT "services".* FROM "services" INNER JOIN "users" ON "users"."id" = "services"."user_id" WHERE (age > 25)
                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=15.50..29.42 rows=103 width=232) (actual time=0.015..0.017 rows=0 loops=1)
   Hash Cond: (services.user_id = users.id)
   ->  Seq Scan on services  (cost=0.00..13.10 rows=310 width=232) (actual time=0.012..0.012 rows=0 loops=1)
   ->  Hash  (cost=14.12..14.12 rows=110 width=8) (never executed)
         ->  Seq Scan on users  (cost=0.00..14.12 rows=110 width=8) (never executed)
               Filter: (age > 25)
 Planning Time: 0.915 ms
 Execution Time: 0.472 ms

VERBOSE

The verbose option provides a more detailed output by including additional information about each step in the execution plan. This includes statistics, cost estimates, and other relevant details.

Service.where('age > ?', 25).joins(:user).explain(:verbose)

EXPLAIN (VERBOSE) SELECT "services".* FROM "services" INNER JOIN "users" ON "users"."id" = "services"."user_id" WHERE (age > 25)
                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=15.50..29.42 rows=103 width=232)
   Output: services.id, services.user_id, services.provider, services.uid, services.access_token, services.access_token_secret, services.refresh_token, services.expires_at, services.auth, services.created_at, services.updated_at
   Inner Unique: true
   Hash Cond: (services.user_id = users.id)
   ->  Seq Scan on public.services  (cost=0.00..13.10 rows=310 width=232)
         Output: services.id, services.user_id, services.provider, services.uid, services.access_token, services.access_token_secret, services.refresh_token, services.expires_at, services.auth, services.created_at, services.updated_at
   ->  Hash  (cost=14.12..14.12 rows=110 width=8)
         Output: users.id
         ->  Seq Scan on public.users  (cost=0.00..14.12 rows=110 width=8)
               Output: users.id
               Filter: (users.age > 25)
(11 rows)

For more options available in PostgreSQL's EXPLAIN command, you can refer to the official documentation.

Please check out this pull request for more details.