在PostgreSQL中使用LIKE，SIMILAR TO或正则表达式进行模式匹配

#1 楼

您的查询几乎是最佳的。语法不会变短，查询也不会变快：

SELECT name
FROM   spelers
WHERE  name LIKE 'B%' OR name LIKE 'D%'
ORDER  BY 1;

如果您真的想缩短语法，请使用带有分支的正则表达式：

...
WHERE  name ~ '^(B|D).*'

或稍快一点，使用一个字符类：
对我而言，没有索引的快速测试在任何情况下都比SIMILAR TO产生更快的结果。
在适当的B树索引中，LIKE在数量级上赢得这场比赛。
阅读手册中有关模式匹配的基础知识。
卓越性能的索引
如果您关注性能，请为更大的表创建这样的索引：

...
WHERE  name ~ '^[BD].*'

使这种查询的速度提高了几个数量级。特殊注意事项适用于特定于语言环境的排序顺序。在手册中阅读更多关于操作员类别的信息。如果使用标准的“ C”语言环境（大多数人不使用），则将使用普通索引（具有默认的运算符类）。
这种索引仅适用于左锚模式（从一开始就匹配）
SIMILAR TO或带有基本左锚表达式的正则表达式也可以使用此索引。但是分支(B|D)或字符类[BD]（至少在我对PostgreSQL 9.0的测试中）不可用。
三元组匹配或文本搜索使用特殊的GIN或GiST索引。 br />
LIKE（~~）简单，快速，但功能有限。 ILIKE（~~*）不区分大小写的变体。
pg_trgm扩展了两者的索引支持。而不是基本表达式。


~毫无意义。 SIMILAR TO和正则表达式的特殊混合体。我从不使用它。参见下文。


％是附加模块LIKE提供的“相似”运算符。参见下文。


pg_trgm是文本搜索运算符。参见下文。


pg_trgm-三元组匹配
从PostgreSQL 9.1开始，您可以方便扩展@@来为任何pg_trgm / LIKE模式（以及带有ILIKE的简单正则表达式模式）提供索引支持），使用GIN或GiST索引。
详细信息，示例和链接：

LIKE如何实现？

~还提供以下运算符：


pg_trgm-“相似性”运算符

%（换向器：<%）-Postgres 9.6或更高版本中的“ word_similarity”运算符

%>（换向器：<<%）-Postgres 11或更高版本中的“ strict_word_similarity”运算符

文本搜索
是一种特殊的模式匹配类型，具有单独的基础结构和索引类型。它使用字典和词干，是在文档中查找单词的好工具，尤其是对于自然语言。
还支持前缀匹配：

从GIN索引的TSVECTOR列中获得部分匹配

以及自Postgres 9.6起的短语搜索：

如何在PostgreSQL全文搜索中搜索带连字符的单词？

请考虑手册中的介绍和概述
用于模糊字符串匹配的附加工具
附加模块Fuzzystrmatch提供了更多选项，但性能通常不如上述所有。
尤其是， %>>函数可能会起作用。
为什么正则表达式（levenshtein()）总是比~快？
答案很简单。 SIMILAR TO表达式在内部被重写为正则表达式。因此，对于每个SIMILAR TO表达式，至少有一个更快的正则表达式（这节省了重写表达式的开销）。使用SIMILAR TO永远不会提高性能。
而且无论如何，使用SIMILAR TO（LIKE）可以完成的简单表达式仍然更快。
~~仅在PostgreSQL中受支持，因为它最终出现在SQL标准的早期草案中。他们仍然没有摆脱它。但是有计划删除它，而改为包含正则表达式匹配项-或我听说。
LIKE揭示了它。

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

显示：

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO 'B%';

SIMILAR TO已用正则表达式（EXPLAIN ANALYZE）重写。
此特定情况的最佳性能
但SIMILAR TO揭示了更多内容。尝试使用上述索引：

...  
Seq Scan on spelers  (cost= ...  
  Filter: (name ~ '^(?:B.*)$'::text)

显示：

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ '^B.*;

内部，使用不支持区域设置的索引（~或使用区域设置EXPLAIN ANALYZE），简单使用以下文本模式运算符重写左锚表达式：text_pattern_ops，C，~>=~，~<=~。对于~>~，~<~或~都是这种情况。
对于带有~~的SIMILAR TO类型或带有varchar的varchar_pattern_ops类型的索引也是如此。
因此，将其应用于原始问题，这是最快的方法：

...
 ->  Bitmap Heap Scan on spelers  (cost= ...
       Filter: (name ~ '^B.*'::text)
        ->  Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ...
              Index Cond: ((prod ~>=~ 'B'::text) AND (prod ~<~ 'C'::text))

当然，如果您碰巧要搜索相邻的首字母，则可以进一步简化：

SELECT name
FROM   spelers  
WHERE  name ~>=~ 'B' AND name ~<~ 'C'
    OR name ~>=~ 'D' AND name ~<~ 'E'
ORDER  BY 1;

与普通使用char或bpchar_pattern_ops相比，收益很小。如果性能不是您的首要要求，那么您应该坚持使用标准运算符-得出问题中已经存在的内容。

#2 楼

如何在表格中添加一列。根据您的实际要求，

person_name_start_with_B_or_D (Boolean)

person_name_start_with_char CHAR(1)

person_name_start_with VARCHAR(30)

PostgreSQL在SQL Server中不支持基表中的计算列，但是可以通过触发器维护新列。显然，此新列将被索引。

或者，对表达式的索引也可以使您便宜。例如：

CREATE INDEX spelers_name_initial_idx ON spelers (left(name, 1)); 

在其条件下与表达式匹配的查询可以利用此索引。

这样，当数据已创建或修改，因此可能仅适用于低活动性环境（即写入次数少于读取次数）。

#3 楼

您可以尝试

SELECT s.name
FROM   spelers s
WHERE  s.name SIMILAR TO '(B|D)%' 
ORDER  BY s.name

我不知道上面的还是您的原始表达式在Postgres中是否都是可修饰的。您创建建议的索引也将有兴趣了解它与其他选项的比较。

SELECT name
FROM   spelers
WHERE  name >= 'B' AND name < 'C'
UNION ALL
SELECT name
FROM   spelers
WHERE  name >= 'D' AND name < 'E'
ORDER  BY name

#4 楼

面对类似的性能问题，我过去所做的就是增加最后一个字母的ASCII字符，然后执行BETWEEN。然后，对于LIKE功能的一部分，您将获得最佳性能。当然，它仅在某些情况下有效，但是对于例如您正在搜索名称的超大型数据集，它会使性能从糟糕到可接受。

#5 楼

一个非常老的问题，但是我找到了解决这个问题的另一种快速解决方案：

SELECT s.name 
FROM spelers s 
WHERE ascii(s.name) in (ascii('B'),ascii('D'))
ORDER BY 1

因为函数ascii（）只看字符串的第一个字符。

#6 楼

为了检查首字母缩写，我经常使用强制转换为"char"（带双引号）。它不是便携式的，但速度很快。在内部，它简单地删除文本并返回第一个字符，并且“字符”比较操作非常快，因为类型为1字节固定长度：

SELECT s.name 
FROM spelers s 
WHERE s.name::"char" =ANY( ARRAY[ "char" 'B', 'D' ] )
ORDER BY 1

注意强制转换为"char"的速度比@ Sole021的ascii()解决方案要快，但是它与UTF8不兼容（或与此相关的任何其他编码），仅返回第一个字节，因此仅在与普通旧式进行比较的情况下使用7位ASCII字符。

#7 楼

有两种尚未提及的用于处理此类情况的方法：




局部索引（或分区-如果手动创建用于全范围索引）-仅在以下情况下有用数据是必需的（例如，在某些维护期间或临时进行某些报告）：特别值得在PostgreSQL 10+（减轻痛苦的分区）和11+（在查询执行过程中进行分区修剪）中考虑。超过第一个字符）。

#8 楼

进行单个字符比较可能更快：

SUBSTR(s.name,1,1)='B' OR SUBSTR(s.name,1,1)='D'