Order of columns in table index matters (postgres)
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.
Written by Anton Katunin
Related protips
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Rails
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#