PostgreSQL含通配符变位词查询：索引创建与替代数据库咨询

1. PostgreSQL中能否为这类查询创建索引？ #

可以，根据查询场景选择对应的索引方案：

• B-tree索引（结合长度过滤）：默认B-tree索引对前缀匹配的LIKE查询有效，但对中间带下划线的匹配无法直接高效利用。如果你的目标sorted_word长度固定（比如示例中是4位），可以在查询中先加LENGTH(sorted_word) = 4过滤，再配合sorted_word的B-tree索引，数据库会先筛选出符合长度的记录，再执行LIKE匹配，能显著提升效率。
• pg_trgm扩展索引：PostgreSQL的pg_trgm扩展支持基于三元组的模糊匹配索引。先安装扩展：

  CREATE EXTENSION pg_trgm;
  

  然后创建GIN或GIST索引：

  CREATE INDEX idx_words_sorted_word_trgm ON words USING GIN (sorted_word gin_trgm_ops);
  

  这种索引能高效处理任意位置带通配符的LIKE查询，非常适合你这类变位词加空白tile的场景。

2. 如果SQL不合适，哪种数据库更合适？ #

如果PostgreSQL配合扩展仍无法满足性能需求，可以考虑以下类型的数据库：

• 全文搜索引擎：比如Elasticsearch，它针对文本检索做了深度优化，支持通配符查询、n-gram分析等功能，能快速处理大规模数据下的变位词模糊匹配，配置好字段分析器后，查询效率会远高于传统SQL数据库。
• 专业文本检索数据库：比如Apache Solr，和Elasticsearch类似，专注于文本检索场景，自带丰富的模糊匹配、分词功能，适合这类拼字游戏式的查询需求。

通常情况下，PostgreSQL结合pg_trgm扩展已经能很好解决这类问题，无需更换数据库。

内容的提问来源于stack exchange，提问作者r34