PostgreSQL中%操作符与similarity函数为何查询计划不同？

这本质是PostgreSQL索引对操作符和函数表达式的支持差异导致的：

• 对于column % 'some string s'写法：
  %是pg_trgm扩展提供的索引感知型操作符，专门和gin_trgm_ops/gist_trgm_ops索引绑定。优化器明确知道这个操作符可以通过预建的trgm索引快速筛选符合条件的行，所以会选择索引扫描。

• 对于similarity(column, 'some string s') >= 0.9写法：
  similarity()是普通函数，虽然逻辑上和前者等价，但优化器没法直接把这个函数表达式和trgm索引关联起来。因为trgm索引存储的是字符串的trigram统计信息，是为%这类操作符量身设计的，没办法直接用来计算并筛选函数返回值≥0.9的行，只能执行全表扫描，逐行计算函数值再做比较。

如果想让函数写法也能用上索引，可以创建函数索引，比如：

CREATE INDEX idx_column_similarity ON your_table USING gin (similarity(column, 'some string s') gin_trgm_ops);

不过这种索引只能针对固定的目标字符串，通用性不强，所以多数场景下还是推荐用%操作符配合pg_trgm.similarity_threshold的写法来复用现有索引。

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