Last Updated: February 25, 2016
·
16.42K
· krainboltgreene

Advanced Rails Indexing: WHERE clause (postgresql)

I don't know much about databases, especially compared to my peers. What I do know I've picked up by reading other people's experiences right after I run into a problem. This time I was having a lookup time performance issues. Consider this Rails ActiveRecord query:

Status.bad.pluck(:text)

That produces this SQL statement:

SELECT "statuses"."text" FROM "statuses" WHERE "statuses"."liked" = 'f'

This query, on my development database, took 107.6ms. I have to do this operation about a 100,000 times! I knew that I could improve the performance with indexes, but I didn't know quite how. My general solution to indexing has been to add one to any field that is: a short string, a boolean field, an integer, or a float. I know this isn't the best, but it's better than no indexes. I stumbled upon this article by Heroku.

My next step was to look for how to do where clauses on indexes in migrations, but I found nothing. This is likely due to the where clause being specific to postgresql. So I went ahead and used execute. Much to my surprise the db/schema.rb had my index and used a where: option! This meant ActiveRecord supported it and I could use it freely, so I generated this index:

class AddDislikedIndexToStatuses < ActiveRecord::Migration
  def change
    add_index :statuses, :liked, name: "index_statuses_on_disliked", where: "(liked IS FALSE)"
  end
end

And now the query ran in 48.6ms!

2 Responses
Add your response

Wow did you really delete my comment? I was just trying to be helpful.

over 1 year ago ·

The Instagram Engineering blog has an interesting post on how they used partial indexes in PG (and other interesting things): http://instagram-engineering.tumblr.com/post/40781627982/handling-growth-with-postgres-5-tips-from-instagram

over 1 year ago ·