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模式(以及带有〜的简单正则表达式模式)提供索引支持.
详细信息,示例和链接：
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),使用这些文本模式运算符重写简单的左锚定表达式：〜> =〜,〜< =〜,〜>〜,〜< ;〜.这是〜,~~或类似的情况.
对于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
