如何创建索引以加快对表达式的查询之类的聚合？

Question

我可能问错题目了。以下是事实：

我的客户服务人员一直抱怨在我们基于Django的站点的管理界面上进行客户查找时响应时间很慢。

我们用的是Postgres 8.4.6。我开始记录缓慢的查询，并发现了这个罪魁祸首：

SELECT COUNT(*) FROM "auth_user" WHERE UPPER("auth_user"."email"::text) LIKE UPPER(E'%deyk%')

此查询运行时间超过32秒。以下是解释提供的查询计划：

QUERY PLAN
Aggregate  (cost=205171.71..205171.72 rows=1 width=0)
  ->  Seq Scan on auth_user  (cost=0.00..205166.46 rows=2096 width=0)
        Filter: (upper((email)::text) ~~ '%DEYK%'::text)

因为这是Django ORM从Django Admin应用程序生成的Django QuerySet生成的查询，所以我对查询本身没有任何控制。索引似乎是合乎逻辑的解决方案。我试着创建一个索引来加快速度，但是它并没有起到什么作用：

CREATE INDEX auth_user_email_upper ON auth_user USING btree (upper(email::text))

我做错了什么？如何加快查询速度？

Answer 1

在LIKE 8.4中没有对ILIKE / PostgreSQL的索引支持--除了左锚式搜索项。

自PostgreSQL 9.1以来，附加模块pg_trgm为GIN和GiST三元索引提供了操作符类，支持LIKE / ILIKE或正则表达式(操作符、~和朋友)。每个数据库安装一次：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

示例GIN索引：

CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops);

相关信息：

• 如何实现？
• 在PostgreSQL中使用类似、类似或正则表达式的模式匹配

Answer 2

由于匹配开始时的“%”，该索引不会有帮助-- BTREE索引只能匹配前缀，查询开始时的通配符意味着没有固定的前缀可供查找。

这就是为什么它要进行表扫描，并依次将每条记录与查询字符串匹配。

您可能需要考虑使用完整的文本索引和文本匹配运算符，而不是像现在这样进行子字符串搜索。您可以在文档中找到关于全文搜索的更多信息：

http://www.postgresql.org/docs/8.4/static/textsearch-intro.html

事实上，我从那个页面中注意到，似乎从不使用索引，这在我看来很奇怪，因为它应该能够使用BTREE索引解析非通配符前缀。一些快速测试表明，文档可能是正确的，在这种情况下，当您使用LIKE解析查询时，任何索引都不会有帮助。