主页 / dba / 问题 / 4521
Accepted
David Eyk
Asked: 2011-08-10 15:04:10 +0800 CST

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

  • 772

我可能在标题中问了错误的问题。以下是事实：

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

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

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

此查询运行时间超过 32 秒。EXPLAIN 提供的查询计划如下：

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))

我究竟做错了什么？我怎样才能加快这个查询？

postgresql performance

2 个回答

  1. Best Answer

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

    从PostgreSQL 9.1 开始，附加模块为支持/或正则表达式（运算符和朋友）pg_trgm的 GIN 和 GiST trigram 索引提供运算符类。每个数据库安装一次：LIKEILIKE~

    CREATE EXTENSION IF NOT EXISTS pg_trgm;

    示例 GIN 索引：

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

    有关的：

    • 34

  2. 由于匹配开始时的“%”，该索引无济于事 - BTREE 索引只能匹配前缀，而查询开头的通配符意味着没有固定的前缀可供查找。

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

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

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

    事实上，我从那个页面注意到 LIKE 显然从不使用索引，这对我来说似乎很奇怪，因为它应该能够使用 BTREE 索引来解析非通配符前缀。一些快速测试表明文档可能是正确的，但是在这种情况下，当您使用 LIKE 来解析查询时，再多的索引也无济于事。

    • 13

相关问题