postgresql - 加快通配 rune 本查找

Question

我在 Postgres 中有一个简单的表，其中有超过 800 万行。感兴趣的列包含短文本字符串，通常是一个或多个单词，总长度小于 100 个字符。它被设置为“字符变化(100)”。该列已编入索引。如下所示的简单查找需要 > 3000 毫秒。

SELECT a, b, c FROM t WHERE a LIKE '?%'

是的，现在，需要简单地找到“a”以输入的文本开头的行。我想将查找速度降低到 100 毫秒以下(瞬时外观)。建议？在我看来，全文搜索在这里无济于事，因为我的文本栏太短了，但如果值得的话，我很乐意尝试。

哦，顺便说一句，我还在 mongodb 和索引列“a”中加载了完全相同的数据。在 mongodb 中加载数据非常快 (mongodb++)。在进行精确查找时，mongodb 和 Postgres 几乎都是瞬时的。但是，Postgres 在进行上述尾随通配符搜索时实际上表现出色，始终只需要 mongodb 的 1/3 左右。如果我可以加快速度，我会很乐意追求 mongodb，因为这只是一个只读操作。

更新:首先，一些EXPLAIN ANALYZE输出

EXPLAIN ANALYZE SELECT a, b, c FROM t WHERE a LIKE 'abcd%'

"Seq Scan on t  (cost=0.00..282075.55 rows=802 width=40) 
    (actual time=1220.132..1220.132 rows=0 loops=1)"
"  Filter: ((a)::text ~~ 'abcd%'::text)"
"Total runtime: 1220.153 ms"

我实际上想将 Lower(a) 与总是至少 4 个字符长的搜索词进行比较，所以

EXPLAIN ANALYZE SELECT a, b, c FROM t WHERE Lower(a) LIKE 'abcd%'

"Seq Scan on t  (cost=0.00..302680.04 rows=40612 width=40) 
    (actual time=4.681..3321.387 rows=788 loops=1)"
"  Filter: (lower((a)::text) ~~ 'abcd%'::text)"
"Total runtime: 3321.504 ms"

所以我创建了一个索引

CREATE INDEX idx_t ON t USING btree (Lower(Substring(a, 1, 4) ));

"Seq Scan on t  (cost=0.00..302680.04 rows=40612 width=40) 
    (actual time=3243.841..3243.841 rows=0 loops=1)"
"  Filter: (lower((a)::text) = 'abcd%'::text)"
"Total runtime: 3243.860 ms"

似乎唯一一次使用索引是在我寻找完全匹配的时候

EXPLAIN ANALYZE SELECT a, b, c FROM t WHERE a = 'abcd'

"Index Scan using idx_t on geonames  (cost=0.00..57.89 rows=13 width=40) 
    (actual time=40.831..40.923 rows=17 loops=1)"
"  Index Cond: ((ascii_name)::text = 'Abcd'::text)"
"Total runtime: 40.940 ms"

通过使用 varchar_pattern_ops 和 am now looking for an even quicker lookups 实现索引找到了解决方案.

Answer 1

PostgreSQL 查询规划器很聪明，但不是人工智能。要使其在表达式上使用索引，请在查询中使用完全相同的表达式。

使用这样的索引:

CREATE INDEX t_a_lower_idx ON t (lower(substring(a, 1, 4)));

或在 PostgreSQL 9.1 中更简单:

CREATE INDEX t_a_lower_idx ON t (lower(left(a, 4)));

使用这个查询:

SELECT * FROM t WHERE lower(left(a, 4)) = 'abcd';

在功能上 100% 等同于:

SELECT * FROM t WHERE lower(a) LIKE 'abcd%'

或者:

SELECT * FROM t WHERE a ILIKE 'abcd%'

---

但不是:

SELECT * FROM t WHERE a LIKE 'abcd%'

这是一个功能不同的查询，您需要一个不同的索引:

CREATE INDEX t_a_idx ON t (substring(a, 1, 4));

或使用 PostgreSQL 9.1 更简单:

CREATE INDEX t_a_idx ON t (left(a, 4));

并使用这个查询:

SELECT * FROM t WHERE left(a, 4) = 'abcd';

---

可变长度的左锚定搜索词

不区分大小写。索引:

编辑:差点忘了:如果您使用默认“C”以外的任何其他语言环境运行您的数据库，则需要 specify the operator class explicitly - text_pattern_ops在我的例子中:

CREATE INDEX t_a_lower_idx
ON t (lower(left(a, <insert_max_length>)) text_pattern_ops);

查询:

SELECT * FROM t WHERE lower(left(a, <insert_max_length>)) ~~ 'abcdef%';

可以利用索引并且几乎与固定长度的变体一样快。

您可能对此感兴趣 post on dba.SE with more details about pattern matching ，尤其是关于运算符的最后一部分 ~>=~和 ~<~ .