有没有一种方法可以在postgres中建立索引以快速进行子字符串搜索 [英] Is there a way to index in postgres for fast substring searches

问题描述

我有一个数据库，希望能够在表中查找类似于以下内容的搜索：
select * from table where column as like abc％def％ghi
或
从表中的％def％ghi列中选择*
有没有一种方法可以对该列建立索引，以免它不太慢？

I have a database and want to be able to look up in a table a search that's something like: select * from table where column like "abc%def%ghi" or select * from table where column like "%def%ghi" Is there a way to index the column so that this isn't too slow?

编辑：
我还可以澄清一下数据库是只读的，不会经常更新。

Can I also clarify that the database is read only and won't be updated often.

推荐答案

选项用于文本搜索和索引编制的内容包括：

Options for text search and indexing include:

• 全文索引，基于字典的搜索，包括对前缀搜索的支持，例如 to_tsvector（mycol）@@ to_tsquery（'search ：*'）

• full-text indexing with dictionary based search, including support for prefix-search, eg to_tsvector(mycol) @@ to_tsquery('search:*')

text_pattern_ops 索引支持前缀字符串匹配，例如 LIKE'abc％'但 not 中缀搜索如％blah％;。 reverse（） d索引可用于后缀搜索。

text_pattern_ops indexes to support prefix string matches eg LIKE 'abc%' but not infix searches like %blah%;. A reverse()d index may be used for suffix searching.

pg_tgrm trigram索引在较新的版本最近在dba.stackexchange.com帖子中所展示的。

pg_tgrm trigram indexes on newer versions as demonstrated in this recent dba.stackexchange.com post.

外部搜索和索引工具，例如 Apache Solr 。

An external search and indexing tool like Apache Solr.

根据上面给出的最少信息，我想说的是只有三字母组合索引才能为您提供帮助，因为您正在对字符串进行中缀搜索，而不要查找字典单词。不幸的是，trigram索引巨大，效率很低；不要指望某种神奇的性能提升，并且要记住，数据库引擎的构建和更新需要大量工作。

From the minimal information given above, I'd say that only a trigram index will be able to help you, since you're doing infix searches on a string and not looking for dictionary words. Unfortunately, trigram indexes are huge and rather inefficient; don't expect some kind of magical performance boost, and keep in mind that they take a lot of work for the database engine to build and keep up to date.

这篇关于有没有一种方法可以在postgres中建立索引以快速进行子字符串搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！