Rails 7.1 Allows ActiveRecord::Relation#explain To Accept Options

We can run ActiveRecord::Relation#explain on relation. It runs EXPLAIN on the query or queries triggered by the relation and returns the result as a string. The string is formatted imitating the ones printed by the database shell.

It used to analyze and display the SQL query execution plan generated by ActiveRecord for a particular query.

This method helps us to understand how the database will execute the query and identify potential performance bottlenecks.

To use the explain method, we can append it to an ActiveRecord query chain.

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)

The output will vary based on the database being used (e.g., MySQL, PostgreSQL, SQLite) since each database might have a different format for displaying query plans.

With Rails 7.1

Rails 7.1 introduces analyze and verbose as ActiveRecord::Relation#explain options to give us deeper insight into query performance.

These options give us deeper insight into query performance and enable us to customize the output and obtain a more detailed query plan analysis.

Analyze

It provides detailed information about how the database executed the query, including actual runtime statistics. This includes details such as the number of rows processed, execution time, and resource utilization.

User.where(id: 1).explain(:analyze)

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

=> 
EXPLAIN (ANALYZE) 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) (actual time=0.047..0.050 rows=1 loops=1)
   Index Cond: (id = '1'::bigint)
 Planning Time: 0.205 ms
 Execution Time: 0.112 ms
(4 rows)

Verbose

It provides a more detailed and verbose output compared to the regular “EXPLAIN” command. It includes extra information about each step in the query plan, statistics, cost estimates, making it especially useful for in-depth analysis and understanding of query execution.

User.where(id: 1).explain(:verbose)

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

=> 
EXPLAIN (VERBOSE) SELECT "users".* FROM "users" WHERE "users"."id" = $1 [["id", 1]]
                                                                                                                                                                                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using users_pkey on public.users  (cost=0.14..8.16 rows=1 width=600)
   Output: id, first_name, last_name, email, encrypted_password, reset_password_token, reset_password_sent_at, remember_created_at, sign_in_count, current_sign_in_at, last_sign_in_at, current_sign_in_ip, last_sign_in_ip, confirmation_token, confirmed_at, confirmation_sent_at, unconfirmed_email, created_at, updated_at, current_workspace_id, discarded_at, personal_email_id, date_of_birth, social_accounts, phone, token, calendar_enabled, calendar_connected
   Index Cond: (users.id = '1'::bigint)
(3 rows)

Summary

These analyze and verbose options serve as crucial tools for us, offering various levels of insight into query execution, aiding in performance optimizations, and enabling informed decisions for improving database efficiency and speed.