sql - 如何在 postgresql 中为 regexp_matches 创建索引？

Question

我有一张 table 产品

product_id | desciption                                     
============================================================
322919     | text {add}185{/add} text                       
322920     | text {add}184{/add} text {add}185{/add} text   
322921     | text {add}185{/add} text {add}187{/add} text

使用 的 sql 查询喜欢 很慢

SELECT product_id, desciption 
FROM product 
WHERE LOWER(desciption) like '%{add}185{/add}%'
> Time: 340,159s

我只需要一个索引来搜索 {add}185{/add} 表达式。
即需要为此表创建索引

SELECT product_id, regexp_matches (desciption, '(\{add\}\d+\{\/add\})', 'g') 
FROM product 

返回:

product_id | regexp_matches 
================================================================================
322919     | {"{add}185{/add}"}
322920     | {"{add}184{/add}"}
322920     | {"{add}185{/add}"}
322921     | {"{add}185{/add}"}
322921     | {"{add}187{/add}"}

哪个更好地为数据采样创建索引？

哪个更好地使用“WHERE”中的表达？

Answer 1

最简单的解决方案就是构建一个 pg_trgm index .

 create extension pg_trgm;
 create index on product using gin (description gin_trgm_ops);

然后您可以使用相同的查询，只需删除 LOWER 并将 LIKE 更改为 ILIKE。

那应该足够好了，但如果不是，您可以制作更有针对性的索引。您将需要创建一个辅助函数来进行聚合，因为您不能将聚合直接放入功能索引中。

create function extract_tokens(text) returns text[] immutable language sql as $$ 
   select array_agg(regexp_matches[1]) from 
      regexp_matches ($1, '\{add\}(\d+)\{\/add\}+','g') 
$$;

请注意，我将捕获括号移了进去，因此它们只获取数字而不是周围的标签，这看起来像是噪音。有一场比赛的事实证明他们在那里，我们不需要看到他们。

create index on product using gin (extract_tokens(description))

select * from product where extract_tokens(description) @> ARRAY['185'];