如何优化含GROUP BY与聚合的PostgreSQL全文搜索查询？

问题背景 #

现有一条PostgreSQL查询，先执行全文搜索再做分组聚合。全文搜索耗时小于1秒，但分组聚合导致整体查询耗时约9秒，目标是将总耗时优化至1-3秒内。

原始查询语句：

SELECT imv.ref_chain_name,
       array_agg(imv.item_name) AS matching_ingredients,
       count(imv.item_name)
FROM ingredients_mv2 imv
WHERE imv.full_text_tsv @@ websearch_to_tsquery('english', 'coca cola')
GROUP BY imv.ref_chain_name

关键细节 #

• full_text_tsv列存储ref_chain_name的全文内容（包含所有item_name、描述及修饰符）。
• 用户实时输入搜索词，无法预计算特定食材的结果。
• 全文搜索后需按ref_chain_name分组，展示匹配的item_name列表及数量。
• 表ingredients_mv2规模约6-8百万行。
• 已为full_text_tsv创建GIN索引，WHERE子句执行速度快，瓶颈集中在GROUP BY和聚合操作。

执行计划（EXPLAIN (ANALYZE, BUFFERS)） #

HashAggregate  (cost=2306.79..2366.69 rows=2077 width=55) (actual time=10116.826..10172.958 rows=15404 loops=1)
  Group Key: ref_chain_name
  Planned Partitions: 8  Batches: 9  Memory Usage: 1145kB  Disk Usage: 7288kB
  Buffers: shared hit=1689 read=83353, temp read=606 written=1429
  ->  Bitmap Heap Scan on ingredients_mv2 imv  (cost=22.98..2213.13 rows=2172 width=39) (actual time=64.030..9863.038 rows=100113 loops=1)
        Recheck Cond: (full_text_tsv @@ '''coca'' & ''cola'''::tsquery)
        Rows Removed by Index Recheck: 1500010
        Heap Blocks: exact=21211 lossy=61698
        Buffers: shared hit=1689 read=83353
        ->  Bitmap Index Scan on idx_full_text_tsv  (cost=0.00..22.43 rows=2172 width=0) (actual time=58.337..58.338 rows=100113 loops=1)
              Index Cond: (full_text_tsv @@ '''coca'' & ''cola'''::tsquery)
              Buffers: shared hit=35 read=58
Planning:
  Buffers: shared hit=164 read=20
Planning Time: 8.429 ms
Execution Time: 10176.310 ms

生产环境优化策略 #

1. 解决Bitmap Heap Scan的Lossy块问题 #

从执行计划可见，Heap Blocks存在大量lossy块（61698个），这是因为work_mem不足，PostgreSQL无法在内存中存储所有符合条件的行指针，只能按块存储，导致后续需要重新检查块内所有行，大幅增加耗时。

• 临时调整：执行查询前设置更高的work_mem，比如：

  SET work_mem = '64MB';
  

  具体数值可根据服务器内存调整（如16G内存服务器可设为128MB）。
• 永久调整：针对用户或数据库设置默认值：

  ALTER ROLE your_user SET work_mem = '64MB';
  

2. 创建覆盖索引，减少磁盘IO #

当前查询需要ref_chain_name、item_name和full_text_tsv三列，创建包含这三列的GIN覆盖索引，让索引直接提供查询所需数据，避免回表扫描：

CREATE INDEX idx_full_text_covering ON ingredients_mv2 USING GIN (full_text_tsv) INCLUDE (ref_chain_name, item_name);

3. 替换HashAggregate为Sort + GroupAggregate #

HashAggregate在内存不足时会写入磁盘（执行计划显示Disk Usage:7288kB），若调整work_mem后仍无法完全在内存处理，可强制使用Sort + GroupAggregate：

• 临时生效：

  SET enable_hashagg = off;
  

• 单查询生效（用查询提示）：

  SELECT /*+ NO_HASH_AGGREGATE */ imv.ref_chain_name,
         array_agg(imv.item_name) AS matching_ingredients,
         count(imv.item_name)
  FROM ingredients_mv2 imv
  WHERE imv.full_text_tsv @@ websearch_to_tsquery('english', 'coca cola')
  GROUP BY imv.ref_chain_name;
  

4. 优化array_agg的内存占用 #

若item_name存在大量重复，可先去重再聚合，减少聚合时的数据量：

SELECT imv.ref_chain_name,
       array_agg(DISTINCT imv.item_name) AS matching_ingredients,
       count(DISTINCT imv.item_name)
FROM ingredients_mv2 imv
WHERE imv.full_text_tsv @@ websearch_to_tsquery('english', 'coca cola')
GROUP BY imv.ref_chain_name;

5. 分区表优化 #

若表数据持续增长，可按ref_chain_name做哈希分区，查询时仅扫描匹配的分区，减少聚合的数据范围。

6. 热门词物化视图折中方案 #

虽然无法预计算所有食材结果，但可定期预计算搜索量TOP的热门食材聚合结果，生成物化视图。用户搜索热门词时直接从物化视图查询，其他词走原查询。

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