我正在使用Postgres 9.1并且查询速度非常慢。
查询语句:
Explain Analyze SELECT COUNT(DISTINCT email) FROM "invites" WHERE (
created_at < '2012-10-10 21:08:05.259200'
AND invite_method = 'email'
AND accept_count = 0
AND reminded_count < 3
AND (last_reminded_at IS NULL OR last_reminded_at < '2012-10-10 21:08:05.261483'))
结果:
Aggregate (cost=19828.24..19828.25 rows=1 width=21) (actual time=11395.903..11395.903 rows=1 loops=1)
-> Seq Scan on invites (cost=0.00..18970.57 rows=343068 width=21) (actual time=0.036..353.121 rows=337143 loops=1)
Filter: ((created_at < '2012-10-10 21:08:05.2592'::timestamp without time zone) AND (reminded_count < 3) AND ((last_reminded_at IS NULL) OR (last_reminded_at < '2012-10-10 21:08:05.261483'::timestamp without time zone)) AND ((invite_method)::text = 'email'::text) AND (accept_count = 0))
Total runtime: 11395.970 ms
您可以看到,这需要大约11秒的时间。我该如何添加索引以优化此查询的性能?