如何提升COUNT(*)查询效率？批量更新表的性能优化求助

我有一张约90万条记录的table1（每条记录的joins字段存储table2的列名组合），以及一张约50万行、26列的table2。需要计算table2中每个joins对应的列唯一组合总数，更新到table1的result字段。现有循环执行动态SQL的方案性能极差：仅处理10行就耗时近2分钟，尝试Excel处理小批量更快但大数据量仍受限。

现有方案的问题 #

当前的PL/pgSQL循环方案存在两个核心低效点：

1. 重复全表扫描：每个joins组合都要单独扫描一次table2，90万条记录意味着90万次全表扫描，完全无法承受。
2. 逐行更新：每次循环仅更新一行，事务开销极大。

现有查询示例 #

• query #1（10行耗时1分47秒）：

do $$ 
declare 
  rec record;
  current_joins text;
  current_result int;
begin for rec in (select joins from table1  where line<=10) loop
     select rec.joins into current_joins;
     execute format('select count(*) from (select 1 from table2 group by %1$s) as some_alias;', current_joins) into current_result;
     update table1  set result = current_result where joins=current_joins;
  end loop;
end $$;

• query #2（10行耗时1分48秒）：

do $$ 
declare 
  rec record;
  current_joins text;
  current_result int;
begin for rec in (select joins from table1  where line<=10) loop
     select rec.joins into current_joins;
     execute format('select count(*) from (select 1 from table2 group by %1$s having count(*)>=1) as some_alias;', current_joins) into current_result;
     update table1 set result = current_result where joins=current_joins;
  end loop;
end $$;

• query #3（10行耗时1分52秒）：

do $$ 
declare 
  rec record;
  current_joins text;
  current_result int;
begin for rec in (select joins from table1 where line<=10) loop
     select rec.joins into current_joins;
     execute format('select count(distinct (%1$s)) from table2', current_joins) into current_result;
     update table1 set result = current_result where joins=current_joins;
  end loop;
end $$;

• query #4（10行耗时1分55秒）：

do $$ 
declare 
  rec record;
  current_joins text;
  current_result int;
begin for rec in (select joins from table1 where line<=10) loop
     select rec.joins into current_joins;
     execute format('select count(*) from (select distinct (%1$s) from table2) as temp', current_joins) into current_result;
     update table1 set result = current_result where joins=current_joins;
  end loop;
end $$;

Excel方案（小批量<10秒，大数据量受限） #

=LET(A,Data!$C$2:$AB$12,B,ROWS(A),C,FILTERXML("<A><B>"&SUBSTITUTE(C2,",","</B><B>")&"</B></A>","//B"),ROWS(UNIQUE(INDEX(A,SEQUENCE(B),TRANSPOSE(C)))))

核心优化方案 #

1. 去重列组合+批量计算计数 #

先提取table1中所有唯一的joins值，仅对每个唯一组合计算一次计数，避免重复扫描table2。然后批量更新table1。

实现代码

-- 步骤1：创建临时表存储唯一组合的计数
CREATE TEMP TABLE join_counts AS
WITH unique_joins AS (
    SELECT DISTINCT joins FROM table1
)
SELECT
    u.joins,
    (EXECUTE format('SELECT count(*) FROM (SELECT 1 FROM table2 GROUP BY %s) AS t', u.joins))::int AS result
FROM unique_joins u;

-- 步骤2：批量更新table1
UPDATE table1 t1
SET result = jc.result
FROM join_counts jc
WHERE t1.joins = jc.joins;

-- 可选：删除临时表
DROP TABLE join_counts;

2. 用PL/pgSQL批量处理唯一组合（更高效的内存存储） #

DO $$
DECLARE
    rec record;
    count_map jsonb := '{}'::jsonb;
BEGIN
    -- 遍历所有唯一的joins组合，计算计数并存入jsonb映射
    FOR rec IN SELECT DISTINCT joins FROM table1 LOOP
        EXECUTE format('SELECT count(*) FROM (SELECT 1 FROM table2 GROUP BY %s) AS t', rec.joins) INTO count_map := count_map || jsonb_build_object(rec.joins, (SELECT count(*) FROM (SELECT 1 FROM table2 GROUP BY rec.joins) AS t));
    END LOOP;

    -- 一次性批量更新所有记录
    UPDATE table1 t1
    SET result = (count_map ->> t1.joins)::int
    WHERE t1.joins IS NOT NULL;
END $$;

3. 针对高频组合创建复合索引 #

如果table1中存在大量重复的高频列组合，可为这些组合创建复合索引，大幅加速分组计数：

-- 示例：针对"col1,col2"组合创建索引
CREATE INDEX idx_table2_col1_col2 ON table2(col1, col2);

4. 强制哈希聚合加速分组 #

PostgreSQL默认的排序聚合在处理大数据量时较慢，可临时禁用排序，强制使用哈希聚合（适合无序数据）：

-- 临时设置，仅当前会话有效
SET enable_sort = off;

-- 执行计数查询
SELECT count(*) FROM (SELECT 1 FROM table2 GROUP BY col1, col2) AS t;

-- 恢复默认设置
SET enable_sort = on;

额外性能优化建议 #

• 给table1的joins字段建索引：CREATE INDEX idx_table1_joins ON table1(joins);，加快批量更新时的关联速度。
• 关闭自动提交：执行大更新前运行BEGIN;，更新完成后COMMIT;，减少事务提交的开销。
• 调整并行查询参数：根据服务器CPU核心数，设置max_parallel_workers_per_gather = 8;（默认4），利用多核加速扫描和聚合。

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