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
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.