TIL: Blazing-fast correlated exists queries

Today I took some time at the end of the first work week of the year to review some metrics and touch up some performance issues.

In one spot we're querying for, using Twitter-clone parlance "Posts with at least one :suggestion Comment", or more generally "gater records from table X that have at least one of association Y matching some conditions".

Usually I solve this with a join or a subquery like so

Post.joins(:comments).merge(Comment.where(type: :suggestion))

But this can get inefficient if there are few Posts and a huge amount of Comments, we're basically redundantly checking all the comments belonging to the same post.

The key idea is to realize that detecting just one matching Comment for a post is sufficient, we don't have to check/join all of them. Think clearly about whether this situation applies in your systems.

We can achieve the same lookup result with a clever use of EXISTS like so:

Post.where(
  Comment
    .where("comments.post_id = posts.id") # IMPORTANT correlation clause
    .where(type: :suggestion)
    .arel.exists
)

For simple datasets Postgres may be smart enough to optimize both approaches to a semi-join, so don't be surprised if there's no improvement in some cases, this is still a useful technique to know.

See also a previous post of mine regarding the power of EXISTS subqueries.