Postgres 9.5未使用表达式索引：正则查询优化需求咨询
嘿，我来帮你梳理下PostgreSQL 9.5中text列正则匹配查询的优化思路，结合你的场景给出具体方案：
先明确你的核心场景：你在用PostgreSQL 9.5，有一个character varying类型的text列，需要优化固定正则匹配的查询，偶尔还要在关联查询中使用反向匹配，且不想直接给该列建全列索引（你提到直接建索引会有相关问题，虽未详述，我先基于常见痛点给出替代方案）。另外你怀疑问题可能和PostgreSQL 9.6中count(*)不使用索引的变更有关，但暂无法确定，我也会针对这点做说明。
1. 用部分索引缩小索引体积
如果你的正则匹配是固定规则（比如匹配以特定前缀开头、包含特定子串的记录），可以创建部分索引，只对符合正则规则的记录建索引，避免全列索引带来的存储和维护开销：
CREATE INDEX idx_text_partial ON your_table (text) WHERE text ~ '你的固定正则表达式';
当你的查询条件和索引的WHERE子句完全匹配时，PostgreSQL会自动调用这个索引，不管是正向匹配还是关联查询中的反向匹配，只要过滤条件符合就能生效。
2. 用表达式索引适配固定正则模式
如果你的正则匹配有固定模式（比如频繁用text ~ '^prefix.*'前缀匹配、text ~ '.*suffix$'后缀匹配），可以基于正则转换后的表达式建索引：
- 前缀匹配场景，可提取固定长度前缀建索引：
CREATE INDEX idx_text_prefix ON your_table (substring(text FROM 1 FOR 10));
查询时对应调整为WHERE substring(text FROM 1 FOR 10) = '目标前缀'，即可命中索引。
- 固定正则匹配场景，直接基于正则布尔结果建索引：
CREATE INDEX idx_text_regex ON your_table ((text ~ '你的固定正则'));
后续用WHERE text ~ '你的固定正则'查询时，尤其是做count(*)统计，索引会大幅提升效率。
3. 关于PostgreSQL 9.6 count(*)变更的说明
你提到的9.6版本count(*)不使用索引的变更，本质是优化器策略调整：9.6之前优化器更倾向于用索引扫描做count(*)，9.6之后若表中大部分数据需被扫描，优化器会选择全表扫描（因为全表扫描IO效率更高）。但你用的是9.5，这个变更暂时不会影响你的场景。如果你的count(*)是和正则匹配结合的，依然可以靠上面的部分索引或表达式索引来优化。
4. 关联查询中反向匹配的优化
如果是关联查询里的动态反向匹配（比如table_a.text ~ table_b.pattern），普通索引很难生效，可尝试：
- 若
table_b的pattern是固定几个值，提前为每个pattern创建对应的部分索引； - 若
pattern是动态的，用pg_trgm扩展的trigram索引，它对模糊匹配、正则匹配的加速效果很好，反向关联匹配也能受益：
先启用扩展：
CREATE EXTENSION pg_trgm;
再创建trigram索引：
CREATE INDEX idx_text_trgm ON your_table USING gin (text gin_trgm_ops);
如果能补充你提到的“为text列创建索引会导致……”的具体问题，我还能给出更精准的调整方案哦！
内容的提问来源于stack exchange，提问作者Logan
