Understanding The Output of EXPLAIN As a Rails Developer

Rails Active Record is a powerful ORM that simplifies database operations, but complex queries can sometimes lead to performance issues. We can use EXPLAIN to find out details about why a query is not performing well. In this post, we’ll explore the Active Record Explain feature and how to understand it’s output. We’ll cover the basics of using Explain, interpreting its output, and give you some hints on applying that knowledge to optimize your database queries. EXPLAIN is a database-level feature. We will be using PostgreSQL for this post. EXPLAIN provides insights into how the database executes your queries, helping you identify and resolve performance issues.

For relational databases in general, we will not be able to specify how we want to get the data. We can only specify what data we want. For example when using SELECT * FROM users WHERE name = 'haseeb' LIMIT 1; it means “Please select users with the name haseeb and fetch only one result” and it does not specify any details regarding how to fetch it. The database will plan how it wants to retrieve the data or do other operations. So, we give some helpers like proper indexes, etc. so that the query planner can use these to fetch the data faster and more efficiently. When a query is slow if there is a way to figure out why and where it is slow, we can add better optimizations. That’s where EXPLAIN comes in.

What is EXPLAIN 🔗

Explain is a tool to analyze an “Execution Plan”. I will explain about Execution Plan a.k.a Query Plan in the next section. At the database level, When you prefix a SQL query with EXPLAIN, instead of executing the query, the database returns a query plan that shows how it intends to run the query. example: EXPLAIN SELECT * FROM users; In PostgreSQL, the EXPLAIN command provides insights into:

1. The sequence of operations the database will perform
2. The estimated cost of each operation
3. The number of rows expected to be processed
4. The indexes that will be used

For Rails applications using Active Record, you can access this functionality through the explain method on relation objects. This method generates and displays the query plan for the corresponding SQL query.

The output of EXPLAIN helps you understand:

• Which tables are being scanned and in what order
• Whether indexes are being used effectively
• If there are any unexpected operations

It’s important to note that while EXPLAIN provides estimates, the actual execution may vary depending on factors like data distribution and system load. Nevertheless, it remains an invaluable tool for query optimization in both PostgreSQL and Rails applications.

Explain is available in Rails Active Record as #explain

 puts User.where(available: true).explain

Rails allows to pass all the options supported by the EXPLAIN command in a particular database. So, you can pass any options supported by PostgreSQL EXPLAIN

Example:

puts User.where(available: true).explain(:analyze, 'FORMAT XML')

I will tell you more about ANALYZE later. FORMAT XML simply means it returns output in XML instead of the regular format. To understand the output of the Rails explain we need to check how it is defined in PostgreSQL

In PostgreSQL, EXPLAIN is a widely used tool to detect slow queries. When a particular query is slow, You can ask PostgreSQL to explain the query by appending EXPLAIN in front of the query. Here we are checking the users with the super admin flag who have not yet deleted

EXPLAIN SELECT * FROM users WHERE deleted_at IS NULL AND super_admin = true;

 QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on users  (cost=0.00..12.30 rows=1 width=64)
 Filter: ((deleted_at IS NULL) AND super_admin)

Query plan 🔗

A query plan or an execution plan is the output of the PostgreSQL optimizer. The commands we give to our DB only indicates what needs to be done. For example SELECT * FROM users; means select all data from users table. However, it does not define how we should get the data from the users table. The query plan defines how we want to get the data. The PostgreSQL optimizer will always try to get the best possible query plan for a given query.

The sql EXPLAIN query output (query plan) above provides information about the execution plan for the query above that. Let me explain it:

1. The query plan shows a Sequential Scan (Seq Scan) operation on the “users” table.

2. Cost estimation:

   • Start-up cost: 0.00
   • Total cost: 12.30

   The startup cost represents the estimated cost of the initial setup required to start executing the query plan.It includes the cost of activities such as initializing data structures, opening files, and performing any necessary preliminary operations before the actual data processing can begin.The startup cost is usually a small fraction of the total cost, but it can be significant for certain types of queries, such as those involving complex joins or subqueries.

   The total cost represents the estimated total cost of executing the entire query plan, including both the startup cost and the cost of processing the data.It provides an overall estimate of the resources (such as CPU, I/O, and memory) required to execute the query.The total cost is the primary metric used by the PostgreSQL optimizer to compare different query plans and choose the most efficient one.

   These are arbitrary units used by the query planner to estimate the relative cost of the operation.

3. Rows: The planner estimates this query will return 1 row.

4. Width: The estimated average width of each row is 64 bytes.

   It is an estimate of the average size of the data that will be returned for each row in the query result set. This information can be useful for understanding the memory and storage requirements of the query, as well as the potential network overhead when transferring the data.

5. Filter: The query includes two conditions:

   • deleted_at IS NULL
   • super_admin is true (assuming it’s a boolean column)

This plan indicates that the database will perform a full table scan (sequential scan) on the “users” table and apply the specified filters to find rows where the deleted_at column is NULL and the super_admin column is true. If there are multiple items The sequential scan suggests that there might not be an index that can be used to optimize this query.

Each line in the plan represents a node type. It is represented as a tree. Inner nodes are represented by an arrow (->) symbol. Execution is from the innermost node and will reach the outermost node. So the first line you see will be the last to execute.

See this better example,

puts Post.joins(:comments).where(comments: { email: }).select(:id).explain

EXPLAIN SELECT "posts"."id" FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE "comments"."email" = $1 [["email", "dione@gibson.test"]]
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Nested Loop  (cost=0.28..1040.13 rows=1 width=8)
   ->  Seq Scan on comments  (cost=0.00..1031.83 rows=1 width=8)
         Filter: ((email)::text = 'dione@gibson.test'::text)
   ->  Index Only Scan using posts_pkey on posts  (cost=0.28..8.29 rows=1 width=8)
         Index Cond: (id = comments.post_id)

Common node types are:

Seq Scan 🔗

Seq Scan means we have used a full table scan algorithm to access data. In a full table scan, we scan each row in the table and check if the filtering condition matches in all of the rows. When a database table is very large and contains many rows sequential scan consumes a lot of time and will be slow.

Index Scan 🔗

Postgresql uses an Index Scan only if needed. If one or more filtering conditions can be satisfied by an index in the table PostgreSQL can use the index to efficiently fetch the data.

Bitmap Heap Scan 🔗

Bitmap heap scan is a type of Index Scan in which PostgreSQL makes use of a Bitmap based algorithm to prevent the same row from being scanned by multiple reads on the same block when multiple indexes can be used. This is an index scan but the name indicates a more efficient algorithm is used because of multiple indexes. It will include operations like Bitmap Or and Bitmap And.

Index only Scan 🔗

If an index contains all the the columns used in the query, the database does not have to scan the row again. It can simply do the condition checking and selecting columns used for the query on the index itself. If PostgreSQL does this, it is called an index only scan.

Nested Loop Join 🔗

Since we are dealing with a relational database we can use joins to combine data from multiple tables for various use-cases. A nested loop is when a loop is contained in another loop. This can go any level deep. Here is an example of a nested loop in Ruby

# Outer loop
for i in 1..n do
  # Inner loop
  for j in 1..m do
    puts "i: #{i}, j: #{j}"
  end
end

Basically for each row i, It runs the j operation m times. As you can imagine this is not at all efficient. However the actual PostgreSQL algorithm involves many conditions and is more advanced. So it can be fine sometimes. If the outermost table has a large number of rows it can be inefficient. Sometimes the outermost table may be using an index scan. In that case, it can be very efficient.

Hash Join 🔗

This means a hash-based algorithm is used for the join. Postgresql uses this algorithm when all the data in consideration can be fit into the memory. Either the table is small enough or the relation used in the query is. On an advanced level the hash table can be also stored using a hybrid table for joins that can not fit into the memory. So, it can be sometimes used for tables that does not fit into the memory. The basic algorithm works in two phases. In the build phase, PostgreSQL scans the smaller table (called the inner table) and creates an in-memory hash table on the join key. In the probe phase, it checks the hash of each row of the larger table and finds matches.

Merge Join 🔗

it uses a sort merge algorithm. This can be used when both relations in the join are sorted. When there are indexes on both sides, data can be retrieved in the sort order.

Memoize 🔗

Just like in the regular programming paradigm, It is a technique used by PostgreSQL to cache results.

EXPLAIN ANALYZE - Get stats from execution 🔗

When we add ANALYZE to the EXPLAIN command the query will be actually executed and we get information based on that. This data also includes actual performance figures in ms, detailed timing information for each node in the query plan, and an actual number of rows.

Key Differences 🔗

1. EXPLAIN only generates a plan, while EXPLAIN ANALYZE executes the query.
2. EXPLAIN provides estimates, whereas EXPLAIN ANALYZE gives actual results.
3. EXPLAIN is lightweight, but EXPLAIN ANALYZE can impact system performance, especially for complex queries.
4. For operations involving modifications, EXPLAIN is safe to use in production, but EXPLAIN ANALYZE will actually modify the data.

In Rails,

User.where(deleted_at: nil, super_admin: true).explain(:analyze)

In SQL,

EXPLAIN (ANALYZE) SELECT * FROM users WHERE deleted_at IS NULL AND super_admin = true;

 QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..12.30 rows=1 width=313) (actual time=0.006..0.008 rows=4 loops=1)
 Filter: ((deleted_at IS NULL) AND super_admin)
 Rows Removed by Filter: 5
 Planning Time: 0.042 ms
 Execution Time: 0.016 ms

What to look for 🔗

1. Large discrepancies between estimated and actual rows:

   The query planner uses these estimates to choose the best execution plan. If estimates are off, it might choose a suboptimal plan. This can lead to unexpected performance issues, as the chosen plan may not be efficient for the actual data volume. You might need to update table statistics (ANALYZE command) or adjust the planner’s assumptions.

2. Operations with high costs or long execution times:

   These are your bottlenecks, slowing down the overall query execution. They directly affect query performance and resource usage. Focus optimization efforts on these operations first, as they’ll likely yield the biggest improvements.

3. Sequential scans on large tables where an index might help:

   Sequential scans read the entire table, which is inefficient for large tables when only a few rows are needed. This can significantly slow down queries and increase I/O load. Consider adding appropriate indexes or restructuring the query to use existing indexes.

4. Nested loops with many iterations:

   Nested loops can be inefficient for large datasets, especially if the inner loop is executed many times. This can lead to poor performance as the data size grows.

Data should match with the production 🔗

This is something to keep in mind when running EXPLAIN or EXPLAIN ANALYZE locally. We can copy production data from the tables used in the query and use it locally or use Rails related tooling like Faker Gem to generate the data. PostgreSQL Query planning is beyond just indexes. It uses various statistics like table size, index size, etc. So, the data should be similar to production.

The amount of data significantly impacts query execution plans and performance. A query that runs quickly on a small local dataset may behave very differently with production-scale data. The distribution of values in columns affects how the query planner chooses execution strategies. Having representative data helps predict real-world performance. PostgreSQL relies on statistics about tables and columns to make optimization decisions. These should reflect production data. Also, Production data often has edge cases or patterns that affect query behavior. Testing with realistic data exposes these scenarios.

Other factors like hardware resources and concurrency can also affect the query planner.

What’s next 🔗

If you enjoyed so far, I recommend reading part 2 of this blog post: How to Add PostgreSQL Index based on Query Plan as a Rails Developer

Wrapping up 🔗

SQL is hard for Rails developers due to ORM. It is very common in Rails applications to have performance issues. I hope this helped you to have a basic understanding of the results generated by the EXPLAIN query.