CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordrails-activerecordpostgresql-9.3
overallduka
overallduka

Reputation: 1549

Create rails index to multi-column query with daterange

I am having some performance problems with this two queries:

any_impression = Impression.exists?(user_id: user_id, created_at: range)
any_visit      = Visit.exists?(user_id: user_id, created_at: range)

They have about 500k of records for each user and are taking more than 15s to run.

Based on this I would like to create two indexes, one for each search.

My question is, the indexes I should create are:

add_index :visits, [:user_id, :created_at]
add_index :impressions, [:user_id, :created_at]

Or need more some specific information to queries above use the indexes created ?

Thanks much.

Upvotes: 1

Views: 550

Answers (1)

Paul A Jungwirth
Paul A Jungwirth

Reputation: 24551

Those indexes should be fine. In Postgres an index doesn't always know how to use a given operator---it depends on the index type. This page from the manual explains the details.

Your proposed indexes would be btree indexes. In my experiments, telling ActiveRecord to query a timestamp column based on a range produces BETWEEN ... AND ... SQL:

User.where(created_at: (Date.parse('2015-01-01') ..
                        Date.parse('2016-01-01'))).to_sql

gives:

SELECT "users".*
FROM   "users"
WHERE  ("users"."created_at" BETWEEN '2015-01-01' AND '2016-01-01')

Is that what you're seeing also? Then Postgres should use your index, because BETWEEN is just <= and >=.

You could also run the query by hand with EXPLAIN or EXPLAIN ANALYZE to see if the index is used as you expect.

Upvotes: 1

Related Questions