2014-11-19.md

Pitfalls with implicit ordering and indexing

(Kashyap)

Loan model has the columns: id, created_at and user_id User model has the columns: id

Scenario:

• There's one user with two loans. The first loan is rejected and the second is in still under application process. That is, LoanA with state rejected and LoanB with state incomplete_loan_info.

There a line in the code that uses the following call:

resource.loans.first.state != 'rejected'

The intention of the author was to check if there's any previous loan that is rejected. This depends on implicit ordering of the column data. The implicit ordering in this scenario is the .first method call will return the earliest loan created (that is, created_at is sorted in asc order and the head of the array is returned). In this case, the conditional returns false.

Now, without changing any code, and just by adding an index on created_at column, same call returned the latest loan (created_at sorted in ascending order). That is, after adding an index, the above conditional returned true since the .first call now returns LoanB.

In the first case, there was no index on created_at and so the DB does a sequential scan and returns the first found item. In the second case, however, since there's an index, the DB chooses to use it and performs and index based lookup.

This is a gotcha that one needs to keep in mind while writing code that uses implicit ordering.

Note that this problem is in Rails 3.0. Rails 4 adds an ordering to all .first calls.