Postgres大文本行执行ILIKE %text%查询缓慢的优化方案咨询

优先级结论 #

优先做索引优化，表分区完全无法解决该场景的性能问题，不需要考虑。

表分区的优化前提是查询时可以通过分区键过滤掉大部分分区的数据，你当前的过滤条件是长文本的任意子串模糊匹配，无法和分区键做关联，查询时依然要扫描所有分区的全部长文本数据，总IO开销没有任何降低，反而会增加分区管理的额外成本。

具体优化步骤 #

1. 建专用文本索引消除全表扫描 #

你用的是PostgreSQL的前后模糊ILIKE匹配，普通B树索引无法生效，需要安装pg_trgm扩展后创建GIN索引：

-- 启用trgm扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 给长文本列建trgm GIN索引
CREATE INDEX idx_table1_longtext_trgm ON table_1 USING GIN (longtextcolumn gin_trgm_ops);

建完索引后你原有ILIKE ANY (ARRAY['%texttosearch1%', '%texttosearch2%'])的查询语句不需要做任何修改，就可以直接走索引，无需逐行扫描全表的长文本数据，查询耗时通常可以降到秒级。

如果你的搜索都是固定关键词匹配，也可以选择PostgreSQL内置的全文搜索索引，性能会比trgm索引更高，但不支持任意子串的模糊匹配。

2. 降低长文本拉取的IO开销 #

你当前SELECT *会把所有匹配行的长文本全部拉取到客户端填充DataTable，本身就会产生非常大的网络和IO开销，建议做如下优化：

• 先执行只查主键的过滤查询，拿到符合条件的主键ID集合，再根据ID批量查询需要的列，避免拉取无效行的长文本数据：

  -- 第一步：先拿符合条件的主键
  SELECT id FROM table_1 WHERE longtextcolumn ILIKE ANY (ARRAY['%texttosearch1%', '%texttosearch2%']);
  -- 第二步：根据拿到的ID集合查询需要的字段，避免全量拉取长文本
  SELECT * FROM table_1 WHERE id = ANY(ARRAY[...拿到的ID集合...]);
  

• 调整数据库连接的fetch size参数，比如设置为1000以上，减少客户端和数据库的网络交互次数，避免逐行拉取的额外开销。

3. 可选存储优化 #

如果做完上面两步之后性能依然不符合预期，可以调整长文本列的存储策略：

• 调整长文本列的TOAST存储压缩级别，降低磁盘IO开销：

  ALTER TABLE table_1 ALTER COLUMN longtextcolumn SET STORAGE EXTENDED;
  

• 可以把长文本列拆分到独立的关联表，平时查询其他列时完全不碰长文本的存储文件，需要查询长文本时再做关联，和你当前不带长文本查询速度快的逻辑一致。

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