与PostgreSQL中的LIKE,SIMILAR TO或正则表达式匹配的模式

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

您的查询几乎是最佳的.语法不会变得更短,查询不会更快：

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

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

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

或稍快一点,使用字符类：

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

对于我来说,没有索引的快速测试产生比SIMILAR TO更快的结果.
有了适当的B-Tree索引,LIKE以数量级赢得这场比赛.

阅读有关pattern matching in the manual的基础知识.

卓越绩效指数

如果您关心性能,请为更大的表创建这样的索引：

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

使这种查询的速度提高了几个数量级.特殊注意事项适用于特定于语言环境的排序顺序.阅读有关operator classes in the manual的更多信息.如果您使用标准的“C”语言环境(大多数人没有),那么普通索引(使用默认的运算符类)就可以了.

这样的索引仅适用于左锚定模式(从字符串的开头匹配).

SIMILAR TO或具有基本左锚定表达式的正则表达式也可以使用此索引.但不是分支(B | D)或字符类[BD](至少在我对PostgreSQL 9.0的测试中).

Trigram匹配或文本搜索使用特殊的GIN或GiST索引.

模式匹配运算符概述

> LIKE(~~)简单快速但功能有限.
ILIKE(~~ *)不区分大小写的变体.
pg_trgm扩展了对两者的索引支持.
> ~(正则表达式匹配)功能强大但更复杂,对于基本表达式以外的任何内容都可能很慢.
> SIMILAR TO毫无意义.一个特殊的LIKE和正则表达式.我从不使用它.说明如下.
> %是“相似性”运算符,由附加模块pg_trgm提供.
> @@是文本搜索运算符.见下文.

pg_trgm – 三元组匹配

从PostgreSQL 9.1开始,您可以使用GIN或GiST索引促进扩展pg_trgm为任何LIKE / ILIKE模式(以及带有?的简单正则表达式模式)提供索引支持.

详细信息,示例和链接：

> How is LIKE implemented?

pg_trgm还提供“相似度”运算符％

文字搜索

是一种特殊类型的模式,与单独的基础结构和索引类型匹配.它使用词典和词干,是一种很好的工具,可以在文档中查找单词,尤其是自然语言.

还支持前缀匹配：

> Get partial match from GIN indexed TSVECTOR column

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

> How to search hyphenated words in PostgreSQL full text search?

考虑introduction in the manual和overview of operators and functions.

模糊字符串匹配的附加工具

附加模块fuzzystrmatch提供了一些更多选项,但性能通常低于上述所有选项.

特别地,levenshtein()函数的各种实现可以是有用的.

为什么正则表达式(?)总是比SIMILAR TO快？

答案很简单. SIMILAR TO表达式在内部重写为正则表达式.因此,对于每个SIMILAR TO表达式,至少有一个更快的正则表达式(这节省了重写表达式的开销).使用SIMILAR TO没有性能提升.

无论如何,使用LIKE可以用LIKE(~~)完成的简单表达式更快.

SIMILAR TO仅在PostgreSQL中受支持,因为它最终出现在SQL标准的早期草稿中.他们仍然没有摆脱它.但是有计划删除它并包括regexp匹配 – 或者我听说过.

EXPLAIN ANALYZE揭示了它.亲自尝试任何桌子！

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

揭示了：

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

SIMILAR TO已经用正则表达式(?)重写.

这种特殊情况的最终表现

但是EXPLAIN ANALYZE揭示了更多.尝试使用上述索引：

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

揭示了：

...
 ->  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))

在内部,使用不支持语言环境的索引(text_pattern_ops或使用语言环境C),使用这些文本模式运算符重写简单的左锚定表达式：?> =?,?< =?,?>?,?&lt ;?.这是?,~~或类似的情况.

对于varchar_pattern_ops或带有bpchar_pattern_ops的char的varchar类型的索引也是如此.

因此,应用于原始问题,这是最快的方式：

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

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

WHERE  name ~>=~ 'B' AND name ~<~ 'D'   -- strings starting with B or C

普通使用?或~~的收益很小.如果性能不是您的首要要求,您应该坚持使用标准运算符 – 达到您在问题中已有的内容.