postgresql – 加速通配符文本查找

发布时间：2020-12-13 16:30:24 所属栏目：百科来源：网络整理

导读：我在Postgres有一个简单的表,有超过800万行.感兴趣的列包含短文本字符串,通常一个或多个单词的总长度小于100个字符.它被设置为’字符变化(100)’.该列已编制索引.如下所示的简单查找 3000毫秒. SELECT a,b,c FROM t WHERE a LIKE '?%' 是的,目前,需要简单地

我在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,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,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,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实现索引找到了解决方案.

PostgreSQL查询规划器很聪明,但不是AI.要使它在表达式上使用索引,请在查询中使用完全相同的表达式.

使用这样的索引：

CREATE INDEX t_a_lower_idx ON t (lower(substring(a,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,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感兴趣,特别是关于运算符的最后一部分?> =?和?<?.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!