Optimising presence in Rails with PostgreSQL

(blogarhythm ~ Can't Happen Here - Rainbow)
It is a pretty common pattern to branch depending on whether a query returns any data - for example to render a quite different view. In Rails we might do something like this:

query = User.where(deleted_at: nil).and_maybe_some_other_scopes
if results = query.presence
  results.each {|row| ... }
else
  # do something else
end

When this code executes, we raise at least 2 database requests: one to check presence, and another to retrieve the data. Running this at the Rails console, we can see the queries logged as they execute, for example:

(0.9ms)  SELECT COUNT(*) FROM "users" WHERE "users"."deleted_at" IS NULL
 User Load (15.2ms)  SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL

This is not surprising since under the covers, presence (or present?) end up calling count which must do the database query (unless you have already accessed/loaded the results set). And 0.9ms doesn't seem too high a price to pay to determine if you should even try to load the data, does it?

But when we are running on PostgreSQL in particular, we've learned to be leery of COUNT(*) due to it's well known performance problems. In fact I first started digging into this question when I started seeing expensive COUNT(*) queries show up in NewRelic slow transaction traces. How expensive COUNT(*) actually is depends on many factors including the complexity of the query, availability of indexes, size of the table, and size of the results set.

So can we improve things by avoiding the COUNT(*) query? Assuming we are going to use all the results anyway, and we haven't injected any calculated columns in the query, we could simply to_a the query before testing presence i.e.:

query = User.where(deleted_at: nil).and_maybe_some_other_scopes
if results = query.to_a.presence
  results.each {|row| ... }
else
  # do something else
end

I ran some benchmarks comparing the two approaches with different kinds of queries on a pretty well-tuned system and here are some of the results:

Query	Using present?	Using to_a	Faster By
10k indexed queries returning 1 / 1716 rows	17.511s	10.938s	38%
4k complex un-indexed queries returning 12 / 1716 rows	23.603s	15.221s	36%
4k indexed queries returning 1 / 1763218 rows	22.943s	20.924s	9%
10 complex un-indexed queries returning 15 / 1763218 rows	23.196s	14.072s	40%

Clearly, depending on the type of query we can gain up to 40% performance improvement by restructuring our code a little. While my aggregate results were fairly consistent over many runs, the performance of individual queries did vary quite widely.

I should note that the numbers were *not* consistent or proportional across development, staging, test and production environments (mainly due to differences in data volumes, latent activity and hardware) - so you can't benchmark on development and assume the same applies in production.

Things get murky with ActiveRecord add-ons

So far we've talked about the standard ActiveRecord situation. But there are various gems we might also be using to add features like pagination and search magic. MetaSearch is an example: a pretty awesome gem for building complex and flexible search features. But (at least with version 1.1.3) present? has a little surprise in store for you:

irb> User.where(id: '0').class
=> ActiveRecord::Relation
irb> User.where(id: 0).present?
   (0.8ms)  SELECT COUNT(*) FROM "users" WHERE "users"."id" = 0
=> false
irb> User.search(id_eq: 0).class
=> MetaSearch::Searches::User
irb> User.search(id_eq: 0).present?
=> true

Any Guidelines?

So, always to_a my query results? Well, no, it's not that simple. Here are some things to consider:

• First, don't assume that <my_scoped_query>.present? means what you think it might mean - test or play it safe
• If you are going to need all result rows anyway, consider calling to_a or similar before testing presence
• Avoid this kind of optimisation except at the point of use. One of the beauties of ActiveRecord::Relation is the chainability - something we'll kill as soon as we hydrate to a result set Array for example.
• While I got a nice 40% performance bonus in some cases with a minor code fiddle, mileage varies and much depends on the actual query. You probably want to benchmark in the actual environment that matters and not make any assumptions.