带有日期时间的WHERE子句索引，以及更多

Question

我正在使用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秒的时间。我该如何添加索引以优化此查询的性能？

- AnApprentice

---

感谢您展示SQL、计划和Pg版本。如果更多的人这样做就好了！ - Craig Ringer

那么你有答案了吗？ - Erwin Brandstetter

Answer 1

仅仅像Jim建议的那样索引“所有内容”并不是一种非常高效的策略。索引有维护成本，合并许多单独的索引比一个定制化的索引更昂贵（无论是在维护还是查询时）。这总是取决于您的完整情况。

对于只读或很少写入的表来说，索引的成本很低，但对于具有大量写操作的易变表来说，成本很高。另一个缺点是索引禁止HOT-Updates（仅堆元组）更改涉及的列。请参见：

• 更新语句中的冗余数据

如果特定查询的性能很重要，则部分多列索引将是一种很好的策略。专业化，但比涉及所有列的单个索引便宜得多且更快。经验法则是...

• 将易变的条件（在查询之间变化）的列放入索引中。
• 使用稳定的条件（每个查询相同）在WHERE子句中缩小索引的分区。

从您的列名称来看（由于缺乏信息），accept_count = 0似乎是最具选择性（且稳定）的过滤器，而created_at和last_reminded_at可能会不断变化。因此，可以尝试以下内容：

CREATE INDEX invites_special_idx
ON     invites (created_at, last_reminded_at)
WHERE  accept_count = 0
AND    invite_method = 'email'
AND    reminded_count < 3;

将created_at和last_reminded_at按升序排序，以完全匹配查询 - 这恰好是默认设置。这样，系统可以从索引顶部的单个扫描中获取所有相关行。应该非常快。

正如我们在您之前的一个问题中讨论的那样，对于索引来说，将表聚集起来可能会有额外的帮助。一定要阅读关于CLUSTER的手册。
正如@Craig所提供的，您不能在部分索引上进行CLUSTER。由于CLUSTER是一次性操作（随后的写入操作会影响效果），因此您可以通过创建一个完整的索引，对表进行CLUSTER，然后再删除索引来规避此限制。就像这样：

CREATE INDEX invites_special_idx2 ON invites (created_at, last_reminded_at);
CLUSTER invites USING invites_special_idx2;
DROP INDEX invites_special_idx2;

CLUSTER 只在没有其他重要查询需要相反的数据分布要求时才有用。

PostgreSQL 9.2 具有一些新功能，可以使您的查询更快。特别是索引仅扫描（发布说明中的第一个项目）。考虑升级。

- Erwin Brandstetter

---

你不能在部分索引上使用 CLUSTER，因此需要一个全范围索引。 - Craig Ringer

@CraigRinger：好观点。CLUSTER不适用...但我提供了一种解决方法。 - Erwin Brandstetter

Answer 2

你应该为email、created_at、invite_method、accept_count、reminded_count和last_reminded添加索引。通常情况下，任何出现在WHERE语句左侧的内容都需要添加索引。

- Jim