PostgreSQL 不区分大小写文本字段LEFT JOIN性能优化问题

你之前的两个方案性能差的核心原因是关联时对两个表的字段实时做大小写转换，没有可用索引，触发了全表扫描与逐行计算，以下是可落地的高效方案：

方案1：创建函数表达式索引（改造成本最低，无需修改表结构与业务逻辑） #

直接对关联字段的UPPER()结果建索引，查询优化器会自动匹配索引跳过实时计算，性能基本和原生区分大小写关联持平：

1. 创建索引（左连接场景右表b的索引优先级更高）

   -- 右表b的UPPER(text)索引
   CREATE INDEX idx_b_text_upper ON b (UPPER(text));
   -- 若a表也经常需要用text字段做不区分大小写查询/关联，可同步创建
   CREATE INDEX idx_a_text_upper ON a (UPPER(text));
   

2. 直接使用你原有的方案2的查询语句即可，性能可接近176ms的原生水平：

   SELECT * FROM a LEFT JOIN b ON UPPER(b.text) = UPPER(a.text);
   

方案2：使用citext大小写不敏感类型（长期业务场景最优，代码更易维护） #

如果你的业务大部分场景下对text字段的查询、关联都要求不区分大小写，直接修改字段类型为PostgreSQL内置的citext类型最省心：

1. 先启用citext扩展

   CREATE EXTENSION IF NOT EXISTS citext;
   

2. 修改两个表的text字段类型

   ALTER TABLE a ALTER COLUMN text TYPE citext;
   ALTER TABLE b ALTER COLUMN text TYPE citext;
   

3. 直接使用原生区分大小写的关联语句即可，自动实现不区分大小写匹配，性能和原生text类型几乎一致：

   SELECT * FROM a LEFT JOIN b ON b.text = a.text;
   

方案3：冗余预计算字段（超大数据量极致性能场景适用） #

如果数据量特别大对性能要求极高，可以新增冗余字段存储预计算的大写结果，用存储生成列自动维护，关联时直接匹配冗余字段：

1. 新增字段与索引

   -- 给两个表加预计算的大写存储生成列，数据库自动维护无需手动更新
   ALTER TABLE a ADD COLUMN text_upper TEXT GENERATED ALWAYS AS (UPPER(text)) STORED;
   ALTER TABLE b ADD COLUMN text_upper TEXT GENERATED ALWAYS AS (UPPER(text)) STORED;
   -- 给冗余字段建索引
   CREATE INDEX idx_a_text_upper ON a (text_upper);
   CREATE INDEX idx_b_text_upper ON b (text_upper);
   

2. 关联时直接用冗余字段匹配，性能最高：

   SELECT * FROM a LEFT JOIN b ON b.text_upper = a.text_upper;
   

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