Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

I have the following query:

c = Invite.where(:invite_method => 'email', :email => email, :created_at => Time.zone.now.beginning_of_day..Time.zone.now.end_of_day).count

This query is taking some time as the table has 1m+ records. Here is the query output:

> invites_sent_today = Invite.where(:invite_method => 'email', :email => email, :created_at => Time.zone.now.beginning_of_day..Time.zone.now.end_of_day).exp 

Invite Load (62.3ms)  SELECT "invites".* FROM "invites" WHERE "invites"."invite_method" = 'email' AND "invites"."email" = '[email protected]' AND ("invites"."created_at" BETWEEN '2013-01-20 00:00:00.000000' AND '2013-01-20 23:59:59.999999')

EXPLAIN (2.2ms)  EXPLAIN SELECT "invites".* FROM "invites" WHERE "invites"."invite_method" = 'email' AND "invites"."email" = '[email protected]' AND ("invites"."created_at" BETWEEN '2013-01-20 00:00:00.000000' AND '2013-01-20 23:59:59.999999')

=> EXPLAIN for:

SELECT \"invites\".*
  FROM \"invites\"
 WHERE \"invites\".\"invite_method\" = 'email' 
   AND \"invites\".\"email\" = '[email protected]'
   AND (\"invites\".\"created_at\"
    BETWEEN '2013-01-2000:00:00.000000'                                                             
       AND'2013-01-20 23:59:59.999999'
      ;

QUERY PLAN\n------------------------------------------------------------------------------------------------------------------------------------------------------------------\n 
Index Scan using index_invites_on_created_at on invites  (cost=0.00..17998.11 rows=2 width=129)\n   
Index Cond: ((created_at >= '2013-01-20 00:00:00'::timestamp without time zone) AND (created_at <= '2013-01-20 23:59:59.999999'::timestamp without time zone))\n   
Filter: (((invite_method)::text = 'email'::text) AND ((email)::text = '[email protected]'::text))\n(3 rows)\n"

Any suggestions on how to possibly improve this query performance? Thanks

share|improve this question
1  
Composite or partial index can speed up this particular query. –  Igor Romanchenko Jan 20 at 20:31
1  
Example: CREATE INDEX zzzzz ON invites (email) WHERE invite_method = 'email' BTW: the way to check the queryplan is to run EXPLAIN ANALYZE query statement, and look at the differences between observed and expected. –  wildplasser Jan 20 at 20:36
2  
Do you always query for last 24h ? Yes -> create extra table and truncate it at the end of the day, queries will be extra fast. If cardinality on email is better create index on email –  iddqd Jan 20 at 20:37
1  
@iddqd Maybe a partial index for the last day would be better? And recreate the index at the end of the day. –  Igor Romanchenko Jan 20 at 20:40
2  
@AnApprentice If you want a partial index on data for the last day (or 3 days or ...) you must recreate the index, because the the day that is "last day" changes constantly. If you want a partial index on something like "invite_method" = 'email' - you do not need to recreate it. –  Igor Romanchenko Jan 20 at 20:45
show 7 more comments

1 Answer

up vote 0 down vote accepted

As suggested by Igor, try a composite index :

CREATE INDEX index_invites_email_created_at on invites(email,created_at);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.