我有以下查询:
c = Invite.where(:invite_method => 'email', :email => email, :created_at => Time.zone.now.beginning_of_day..Time.zone.now.end_of_day).count
这个查询需要一些时间,因为表格中有超过一百万条记录。以下是查询结果:
> 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" = 'santa@site.com' 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" = 'santa@site.com' 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\" = 'santa@site.com'
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 = 'santa@site.com'::text))\n(3 rows)\n"
有什么建议可以提高这个查询的性能吗?谢谢。
CREATE INDEX zzzzz ON invites (email) WHERE invite_method = 'email'
顺便说一句:检查查询计划的方法是运行EXPLAIN ANALYZE query statement
,并观察观察结果和预期之间的差异。 - wildplasser