ActiveRecord - Indexes And Ordering

Today I was trying to solve a performance problem with one of the applications that I work with and postgres. I learned a little bit more about indexes and how to get postgres to work with the index.

I have a situation where I have to find out if a "code" is being used on another object, and that code is used for many of the fields on that object. This is also legacy code that while I do have some leeway to rewrite and make pretty, I don't always have the time to experiment and do things the "perfect" way.

The legacy code was taking a number of the fields on object "A" and then doing something like A.where(attr: id).first and then seeing if any of those returned a record.

When I looked at the code initially, for any code that needed to be deleted, it would take approximately 5 seconds to do all of the lookups. There are a lot of records that it needs to search.

My next thought was to combine them into a big long string of "a = 12 OR b = 12", but that only helped marginally.

While playing around with the query I saw that it was doing a "ORDER BY id ASC limit 1" on the end. I thought, "why does this need to be ordered?" We just cared if it retuns anything because that says that it is in use. when I took off the ".first" in Rails, it doesn't do the "order by id ASC" and the query for each of the attributes goes from being a 1000ms query to approximately a 22ms query. Very substantial change.

I'm sure that there are ways that I can make it even more efficient, but right now, this is good enough. I can reduce the load on the database even more and potentially even move to a smaller server. Real world gains. For the win.