Today was trying to figure out why my query was running very and very slow.
SELECT "events".* FROM "events" WHERE "events"."calendar_id" = 24 AND (start >= '2012-01-01 00:00:00.000000') AND (start < '2013-01-01 00:00:00.000000') ORDER BY size desc LIMIT 200
So to break it down, we are sorting by one column (size) and filtering by two columns (calendar_id and start). And I already had index
add_index :events, [:calendar_id, :start]
Even though there is already index for filtering columns, it is not used because we are also sorting.
So I've changed my index to
add_index :events, [:calendar_id, :start, :size]
But it didn't make any difference!! The problem order of the columns in the index very important, as in the query it is always orders first, so the final result made a huge difference in perfomance:
add_index :events, [:size, :calendar_id, :start]
Lesson learnt, order of the columns in index is very important.