Rails 7.1 allows passing options to ActiveRecord explain
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.
