Order of columns in table index matters (postgres)

Posted by Anton Katunin on 30 June 2013
Tags: rails, activerecord, postgres, index, order by, slow query, sorted index, code

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.

Read next:

gem install mysql2 - fails with MySQL 5.6.12