PostgreSQL表索引优化：百万行大表条件查询性能问题解决

嘿，针对你这个PostgreSQL大表的查询性能问题，我整理了几个专门适配upper(request_no) LIKE ...场景的索引优化方案，你可以根据实际业务场景来选择：

1. 直接创建函数索引 #

这是最贴合你当前查询写法的方案，因为你的查询依赖upper(request_no)的计算结果，直接给这个函数结果建索引就能让PostgreSQL跳过全表扫描：

CREATE INDEX idx_main_transaction_upper_request_no ON main_transaction (upper(request_no));

• 优点：完全匹配你的现有查询语句，不需要修改任何业务代码就能享受到性能提升。
• 注意点：如果request_no字段频繁更新，这个索引会增加写操作的额外开销；另外如果你的LIKE查询是前缀匹配（比如'ABC%'），索引效率会更高，要是是'%ABC'或者'%ABC%'这种模糊匹配，索引的利用率会打折扣。

2. 前缀匹配专用的表达式索引 #

如果你的查询都是前缀匹配（比如upper(request_no) LIKE 'XYZ%'），可以用text_pattern_ops操作符类来优化索引，进一步提升查询速度：

CREATE INDEX idx_main_transaction_upper_request_no_pattern ON main_transaction (upper(request_no) text_pattern_ops);

• 优点：专门针对文本前缀匹配场景做了优化，在百万级数据量下，比普通函数索引的性能表现更好。
• 限制：只对前缀匹配的LIKE查询有效，后缀或中间匹配的场景用不了。

3. 预存转换后的值（持久化计算字段） #

如果大小写转换是request_no的固定查询需求，且你能接受少量写操作开销，可以新增一个持久化的计算字段，再给它建普通索引：

-- 添加自动计算的持久化字段
ALTER TABLE main_transaction ADD COLUMN request_no_upper character varying(18) GENERATED ALWAYS AS (upper(request_no)) STORED;

-- 在新字段上创建普通索引
CREATE INDEX idx_main_transaction_request_no_upper ON main_transaction (request_no_upper);

之后把查询语句改成直接用这个新字段：

SELECT * FROM main_transaction WHERE request_no_upper LIKE 'XXX%';

• 优点：索引维护的开销比函数索引更低，查询时不需要实时计算upper(request_no)，性能更稳定。
• 注意点：需要修改业务查询语句；request_no更新时，数据库会自动同步request_no_upper的值，会增加一点点写操作的耗时。

4. 全模糊匹配的特殊优化方案 #

如果你的查询是'%ABC%'这种任意位置的模糊匹配，上面的索引都没法高效发挥作用，这时候可以用pg_trgm扩展来创建GIN/GIST索引：

-- 先安装pg_trgm扩展（如果没装过）
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建支持全模糊匹配的GIN索引
CREATE INDEX idx_main_transaction_request_no_trgm ON main_transaction USING GIN (upper(request_no) gin_trgm_ops);

这种索引能高效处理任意位置的LIKE匹配，即使是百万级数据也能快速定位结果。

• 记得定期更新表的统计信息，让PostgreSQL的查询优化器能准确判断该用哪个索引：

  ANALYZE main_transaction;
  

• 如果查询只需要部分字段，别用SELECT *，只查需要的字段，能减少数据传输和内存开销，进一步提升性能。

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