优化PostgreSQL聚合查询中的COUNT(*)性能

1. 消除冗余子查询，简化查询结构 #

原查询通过IN子查询重复扫描同一张表，完全可以将过滤条件合并到外层WHERE中，减少一次表扫描开销：

SELECT
  product_name,
  product_color,
  (array_agg("product_distributor"))[1] AS "product_distributor",
  (array_agg("product_release"))[1] AS "product_release",
  COUNT(*) AS "count"
FROM
  product
WHERE
  product_type = 1
  AND (product_name ILIKE '%red%' OR product_color ILIKE '%red%')
GROUP BY
  product_name, product_color
LIMIT 1000 OFFSET 0

2. 替换低效的array_agg取数方式 #

如果同一product_name+product_color分组下的product_distributor和product_release值唯一，用MAX/MIN替代array_agg会更高效，无需构建数组再取元素：

SELECT
  product_name,
  product_color,
  MAX(product_distributor) AS product_distributor,
  MAX(product_release) AS product_release,
  COUNT(*) AS "count"
FROM
  product
WHERE
  product_type = 1
  AND (product_name ILIKE '%red%' OR product_color ILIKE '%red%')
GROUP BY
  product_name, product_color
LIMIT 1000 OFFSET 0

如果分组内存在多值但只需任意一个，可用DISTINCT ON提前筛选每组数据，再用窗口函数计算计数，减少聚合运算的数据量：

SELECT
  product_name,
  product_color,
  product_distributor,
  product_release,
  COUNT(*) OVER (PARTITION BY product_name, product_color) AS "count"
FROM (
  SELECT DISTINCT ON (product_name, product_color)
    product_name,
    product_color,
    product_distributor,
    product_release
  FROM product
  WHERE
    product_type = 1
    AND (product_name ILIKE '%red%' OR product_color ILIKE '%red%')
) AS sub
LIMIT 1000 OFFSET 0

3. 为模糊查询创建trigram索引 #

原查询中的ILIKE '%red%'是前缀模糊匹配，普通BTREE索引无法生效，需创建trigram索引加速匹配：
首先确保安装pg_trgm扩展（PostgreSQL 9.6+默认可能已安装）：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

然后创建单列trigram索引：

CREATE INDEX product_name_trgm_idx ON product USING gin (product_name gin_trgm_ops);
CREATE INDEX product_color_trgm_idx ON product USING gin (product_color gin_trgm_ops);

结合product_type过滤条件，可创建组合索引进一步优化：

CREATE INDEX product_type_name_trgm_idx ON product USING gin (product_type, product_name gin_trgm_ops);
CREATE INDEX product_type_color_trgm_idx ON product USING gin (product_type, product_color gin_trgm_ops);

4. 利用现有唯一索引优化分组计数 #

现有唯一索引product_product_type_name_color包含product_type, product_name, product_color，可先通过索引获取符合条件的分组，再关联获取其他字段和计数：

SELECT
  p.product_name,
  p.product_color,
  p.product_distributor,
  p.product_release,
  cnt.group_count AS "count"
FROM (
  SELECT product_name, product_color, COUNT(*) AS group_count
  FROM product
  WHERE product_type=1 AND (product_name ILIKE '%red%' OR product_color ILIKE '%red%')
  GROUP BY product_name, product_color
  LIMIT 1000 OFFSET 0
) AS cnt
JOIN product p ON p.product_type=1 AND p.product_name=cnt.product_name AND p.product_color=cnt.product_color
LIMIT 1000 OFFSET 0

这种写法先分组获取计数，再关联取其他字段，避免聚合时处理不必要的列。

5. 拆分OR条件提升索引利用率 #

OR条件可能导致优化器无法有效使用索引，可将查询拆分为两个独立查询，用UNION ALL合并（注意排除重复分组）：

SELECT product_name, product_color, MAX(product_distributor), MAX(product_release), COUNT(*)
FROM product
WHERE product_type=1 AND product_name ILIKE '%red%'
GROUP BY product_name, product_color
UNION ALL
SELECT product_name, product_color, MAX(product_distributor), MAX(product_release), COUNT(*)
FROM product
WHERE product_type=1 AND product_color ILIKE '%red%' AND product_name NOT ILIKE '%red%'
GROUP BY product_name, product_color
LIMIT 1000 OFFSET 0

每个子查询可单独使用对应的trigram索引，提升过滤效率。

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