Rails removes much of the pain from coding database interactions, but this does not mean that developers can ignore the implications of unoptimized queries, which under normal circumstances manifest themselves as a sluggish experience for users. Slow page loads are one of the top reasons for users to give up on a web site, so it is important for developers to keep things speedy.
One of the tools available to us to view what is happening under the hood of the application is a PostgreSQL query called EXPLAIN ANALYZE. Other databases such as MySQL have similar tools available.
EXPLAIN is a command that allows us to view the query plan for a specific statement, and ANALYZE is the command that tells PostgreSQL to actually execute our query (as opposed to just coming up with estimates). Using the output of this command, we can isolate things like unindexed columns and expensive joins that could be causing performance headaches.
As a basic example, I’ve created a Rails app designed to manage Widgets. At the moment, the widgets table contains 1,000,000 records, each with a name and a price.
We’ll start by asking PostgreSQL to show us what happens when we look for a record with an ID of 56,777.
According to PostgreSQL, it took 0.071ms to plan the best way to run this query and only 0.039 to actually execute it. Because the id column is properly indexed, it can look up that specific record with very low overhead. However, let’s see what happens when we try to find a list of all widgets that cost between 60 and 65 dollars:
This query took significantly longer - almost a quarter second. While that may seem low, in a production environment it could be quite significant - particularly when coupled with the overhead of view rendering, etc. EXPLAIN is telling us that this uses a sequence scan, which means it needs to search the entire table row by row to find rows that match the given criteria. Indexing the column we are searching on (price) will result in speedier query execution, thereby speeding up our app. So, we can create the index like this:
Which results in a query execution like this:
Much better! Ensuring that your tables have the proper indices can ensure a snappy experience for your users. Little tweaks like this have very low overhead to implement, and depending on the size of your dataset can make a ton of difference.