代码之家 › 专栏 › 技术社区 › Naor Tedgi

如何在Postgres中优化子字符串搜索查询?

query-optimization postgresql

Naor Tedgi · 技术社区 · 7 年前

我有两张桌子,都是同一张表格

content | text

表B: SubContent | text

表B包含表A中文本的子字符串

例如:

表a:

比以前更糟。
特克南·阿尔瓦·伊布 . . 埃达·特克南·阿尔瓦·伊布。

表B:

打造南非

我想要实现的是:

making south africa | zupta . . for **making south africa** worse than ever.
terkenang arwah ibu | zur eda **terkenang arwah ibu** . . eda terkenang arwah ibu . .

我使用的查询非常简单

select subcontent, content from A join B on  content SIMILAR TO '%'||subcontent||'%'

Gather  (cost=1000.00..6611032.19 rows=1678309 width=180)
  Workers Planned: 2
  ->  Nested Loop  (cost=0.00..6442201.29 rows=699295 width=180)
"        Join Filter: (A.content ~ similar_escape((('%'::text || B.subcontent) || '%'::text), NULL::text))"
        ->  Parallel Seq Scan on A  (cost=0.00..8675.79 rows=186479 width=91)
        ->  Seq Scan on B  (cost=0.00..19.50 rows=750 width=89)

我试着用下面是这个博客(安德鲁·凯恩做得很好!)

https://medium.com/@ankane/large-text-indexes-in-postgres-5d7f1677f89f

那没用,执行需要40分钟,有什么想法吗?

使用Laurenz Albe答案后进行解释

Nested Loop  (cost=27.79..100352.93 rows=1678309 width=180)
  ->  Seq Scan on q2_distinct  (cost=0.00..19.50 rows=750 width=89)
  ->  Bitmap Heap Scan on clean_distinct  (cost=27.79..111.40 rows=2238 width=91)
"        Recheck Cond: (A.content ~ similar_escape((('%'::text || q2_distinct.part) || '%'::text), NULL::text))"
        ->  Bitmap Index Scan on "clean_distinct_Post content_idx"  (cost=0.00..27.23 rows=2238 width=0)
"              Index Cond: (A.content ~ similar_escape((('%'::text || B.content) || '%'::text), NULL::text))"

2 回复 | 直到 7 年前

Laurenz Albe 7 年前

没有带相等运算符的联接条件的任何联接( = )只能使用嵌套循环联接。

这里唯一可能有帮助的索引是 A

CREATE EXTENSION pg_trgm;
CREATE INDEX ON "A" USING gin (content gin_trgm_ops);

但是您不应该期望通过这样的查询获得良好的执行时间。

Dunes 7 年前

博士后内置 textsearch functions 我可以帮忙。但您需要在表a中添加第二列来帮助索引它。您还需要知道表A中每个字符串的语言。

示例查询。

postgres=# select to_tsvector('english', 'Big cats eat fish on Mondays.') 
               @@ phraseto_tsquery('english', 'eat fish on monday') as query;
 query
-------
 t

请注意,公司的多元化和资本化 Mondays 没有阻止比赛。

使用 to_tsvector 函数创建内容的解析形式,可用于GIN索引。

postgres=# select to_tsvector('english', 'Big cats eat fish on Mondays.');
               to_tsvector
---------------------------------------------
 'big':1 'cat':2 'eat':3 'fish':4 'monday':6

注意如何 图 LIKE tsvector 金酒指数。

所以你的查询现在看起来像,

SELECT subcontent, content 
FROM A 
JOIN B ON (
    A.tsv_content @@ phraseto_tsquery(B.lang, B.subcontent) 
    AND content LIKE '%' || subcontent || '%'
);

注意。 phraseto_tsquery 需要9.6+