PostgreSQL FTS与GiST索引使用：不同阈值下触发索引方案

全局参数pg_trgm.similarity_threshold当前取值为0.9：

SHOW pg_trgm.similarity_threshold;

pg_trgm.similarity_threshold|
----------------------------+
0.9                         |

现有两张示例表：

表t1 #

表t2 #

t1的description列和t2的name列均为text类型，且各自创建了GiST索引：

CREATE INDEX gist_index_t1 ON t1 USING gist (description gist_trgm_ops);
CREATE INDEX gist_index_t2 ON t2 USING gist (name gist_trgm_ops);

当查询的相似度阈值与全局阈值（0.9）一致时，索引可正常使用。但对t2使用不同阈值查询时：

查询1（可利用索引） #

SELECT *
FROM t1
WHERE strict_word_similarity('a text', description) > 0.9

查询2（无法利用索引） #

SELECT *
FROM t2
WHERE strict_word_similarity('a text', name) > 0.6

由于pg_trgm.similarity_threshold是全局参数，仅查询1能触发GiST索引。如何让两个查询都能使用对应的GiST索引？

以下是几种可行的实现方式：

1. 临时修改会话参数 #

如果只是临时执行查询2，可在当前会话内修改参数，执行完成后恢复原值：

-- 将当前会话的阈值临时改为0.6
SET pg_trgm.similarity_threshold = 0.6;

-- 执行查询2，此时会匹配GiST索引
SELECT *
FROM t2
WHERE strict_word_similarity('a text', name) > 0.6;

-- 恢复全局阈值
SET pg_trgm.similarity_threshold = 0.9;

该方式仅影响当前会话，不会干扰其他业务操作。

2. 事务内局部设置参数 #

如果需要更严格的隔离性，可在事务中使用SET LOCAL修改参数，仅对当前事务生效：

BEGIN;
-- 仅在当前事务内将阈值设为0.6
SET LOCAL pg_trgm.similarity_threshold = 0.6;
SELECT * FROM t2 WHERE strict_word_similarity('a text', name) > 0.6;
COMMIT;

事务结束后参数自动恢复，无需手动重置。

3. 创建特定阈值的表达式索引 #

如果查询2是高频固定阈值的查询，可针对该阈值创建带条件的GiST索引：

CREATE INDEX gist_index_t2_strict_06 ON t2 USING gist (name gist_trgm_ops)
WHERE strict_word_similarity(name, 'a text') > 0.6;

注意：这种索引仅适配固定的目标文本和阈值，若查询的目标文本经常变化，该方式通用性较差。

4. 改用%操作符结合参数设置 #

pg_trgm的%操作符会直接使用pg_trgm.similarity_threshold参数，结合临时参数设置可实现索引利用：

BEGIN;
SET LOCAL pg_trgm.similarity_threshold = 0.6;
-- 使用%操作符替代strict_word_similarity函数
SELECT * FROM t2 WHERE name % 'a text';
COMMIT;

需注意：%操作符基于普通相似度算法，与strict_word_similarity的逻辑存在差异，需确认业务逻辑是否允许替换。

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