How fast can an ActiveRecord SQL query run?

Your database monitoring (perhaps Amazon RDS performance insights, Skylight, or the Postgres pg_stat_activity view) is telling you that one particular query in your Rails app is causing heavy load. Unfortunately, it already seems pretty fast. What are the odds that you can get it to run any faster?

I had a case where the query generating the heaviest load in an application took 20 milliseconds. It was also the fastest query in the application; some queries in the same application take entire seconds to run! Surely the fastest query in the system had little room for optimisation?

It’s possible to get really good, detailed answers about duration and optimisation strategies for specific queries using a database query planner, but sometimes it’s nice to have a rule of thumb that can guide one to a quick answer like: “this query’s duration is [or is not] very close in speed to the fastest my query could possibly run and so I likely therefore can [or cannot] optimise it”.

sometimes it’s nice to have a rule of thumb, a quick answer like: “this query’s duration is [or is not] very close in speed to the fastest my query could possibly run and so I likely therefore can [or cannot] optimise it”

Here’s a trick you can run in the Rails console of your production environment to find out how fast the fastest possible queries can run:

> ActiveRecord::Base.logger = Logger.new(STDOUT) # assuming you have SQL logging turned off
> ActiveRecord::Base.connection.execute("SELECT 1;")
D, [2025-03-21T14:03:35.765188 #8173] DEBUG -- :    (0.7ms)  select 1;

In this snippet we’re turning on ActiveRecord SQL logging (for this Rails console only) as it is normally disabled in production. Then we’re running a query that does not touch any real data. The resulting logging shows that our query takes 0.7ms.

SELECT 1; is the minimum work that can be done in the database¹. The only work that happens is Rails assembling a SQL request, connecting to the database (which is normally on a different server), and returning an answer. Any “real” query must run slower than that because it has to search for and return real data.

It’s interesting to compare this number with the time it takes for a minimal network request to go from application server to database server.

If you have terminal acess to your application server, you could use the ping utility to get that number. If you’re in the cloud, you might need to rely on sites like cloudping.co (for Amazon) to tell you how long a network round trip takes. The 50th percentile cloudping time within my AWS region is reported to be 1.7ms, so this minimal query is faster than the average round trip time.

This was a real life example: I had previously thought that my 20ms query was pretty much as fast as things could get, but once I realised that a “fastest possible” SQL query could go much faster, I was emboldened to try optimise. I was able to drop the query time drastically: in fact, ActiveRecord now reports that the query takes 1 millisecond or less.

Improving my query time by 20x was a pretty good outcome for a case that started as “I’m not sure if it’s possible to do any better than what I have already”. Thank goodness for learning more about “possible”.