基于t1结果统计t2数据：优化低效查询及CTE使用问题

我有两张表t1和t2，建表语句如下：

select setseed(.42);

create table t1(a,b,c,d)as
select (random()*9)::int
     , (random()*9)::int
     , (random()*9)::int
     , (random()*9)::int
from generate_series(1,100);

create table t2(a,b,c,d)as
select (random()*9)::int
     , (random()*9)::int
     , (random()*9)::int
     , (random()*9)::int
from generate_series(1,200);

我需要基于t1的筛选结果，对t2做多层条件的统计。下面的写法能得到正确结果，但效率极低（耗时半小时）：

SELECT *,
  (SELECT COUNT (*) FROM t2 WHERE t2.A = T1.A) AS cnt1,
  (SELECT COUNT (*) FROM t2 WHERE t2.A = T1.A AND t2.B = T1.B) AS cnt2,
  (SELECT COUNT (*) FROM t2 WHERE t2.A = T1.A AND t2.B = T1.B AND t2.C = T1.C) AS cnt3,
  (SELECT COUNT (*) FROM t2 WHERE t2.A = T1.A AND t2.B = T1.B AND t2.C = T1.C AND t2.D = T1.D) AS cnt4,
   ... 以此类推 ...

FROM t1 WHERE A>B AND 1.5>C AND D-E>A

我尝试用CTE优化，但当前写法只能返回单个统计值（比如cnt1），不知道怎么同时返回多个统计值（比如cnt1、cnt2等），甚至考虑过用数组形式返回，但不知道具体实现：

SELECT *,
  (WITH CTE1 AS (SELECT COUNT (*) FROM t2 WHERE t2.A = T1.A),
   CTE2 AS (SELECT COUNT (*) FROM CTE1 WHERE t2.B = T1.B),
   CTE3 AS (SELECT COUNT (*) FROM CTE2 WHERE t2.C = T1.C),
   CTE4 AS (SELECT COUNT (*) FROM CTE3 WHERE t2.D = T1.D)
     ARRAY (SELECT 
       (SELECT COUNT (*) FROM CTE1) ,
     SELECT 
       (SELECT COUNT (*) FROM CTE2),
     SELECT 
       (SELECT COUNT (*) FROM CTE3),
     SELECT 
       (SELECT COUNT (*) FROM CTE4))
   ) AS CNT[]

FROM t1 WHERE A>B AND 1.5>C AND D-E>A

编辑：以下是简化的表结构及预期结果示例，建表语句如下：

create table t1(id,a,b,c) as 
 values(1,2,1,1),
       (2,3,2,2),
       (3,1,3,3),
       (4,2,4,4),
       (5,3,5,5),
       (6,1,2,6),
       (7,2,2,7),
       (8,3,2,8),
       (9,4,2,9),
       (10,5,4,1);

create table t2(id,a,b,c) as 
 values(1,2,1,1),
       (2,3,2,2),
       (3,1,3,1),
       (4,2,4,4),
       (5,3,5,5),
       (6,1,2,6),
       (7,2,2,7),
       (8,3,2,8),
       (9,4,3,9),
       (10,5,4,1),
       (11,3,2,2),
       (12,4,4,4),
       (13,5,6,5),
       (14,3,2,3),
       (15,2,2,7),
       (16,1,2,8),
       (17,4,1,9),
       (18,5,2,5),
       (19,6,3,6),
       (20,7,4,7);

对应的问题查询改写如下：

SELECT *,   
 (WITH CTE1 AS (SELECT COUNT (*) FROM t2 WHERE t2.A = T1.A),    
  CTE2 AS (SELECT COUNT (*) FROM CTE1 WHERE t2.B = T1.B),   
  CTE3 AS (SELECT COUNT (*) FROM CTE2 WHERE t2.C = T1.C)
     SELECT 
       (SELECT COUNT (*) FROM CTE1) AS cnt1,
     SELECT 
       (SELECT COUNT (*) FROM CTE2) AS cnt2,
     SELECT 
       (SELECT COUNT (*) FROM CTE3) AS cnt3    
   ) 
 FROM t1 WHERE A>B AND C>1.5 

优化方案：预统计t2多维度组合，再关联t1 #

原写法的核心问题是对t1的每一行都重复扫描t2多次，导致IO开销爆炸。我们可以先预统计t2中所有需要的维度组合的计数，再和筛选后的t1关联，只需要扫描t21-3次，效率会大幅提升。

方案一：用窗口函数预统计

以简化后的示例为例，优化后的查询如下：

WITH t2_stats AS (
    SELECT
        a,
        b,
        c,
        -- 按a分组统计总数
        COUNT(*) OVER (PARTITION BY a) AS cnt1,
        -- 按a,b分组统计总数
        COUNT(*) OVER (PARTITION BY a, b) AS cnt2,
        -- 按a,b,c分组统计总数
        COUNT(*) OVER (PARTITION BY a, b, c) AS cnt3
    FROM t2
)
SELECT
    t1.*,
    -- 取任意匹配行的统计值（同一维度组合的统计值一致）
    MAX(s.cnt1) AS cnt1,
    MAX(s.cnt2) AS cnt2,
    MAX(s.cnt3) AS cnt3
FROM t1
LEFT JOIN t2_stats s 
    ON s.a = t1.a 
    AND (s.b = t1.b OR s.b IS NULL) -- 确保无匹配b时也能拿到cnt1
    AND (s.c = t1.c OR s.c IS NULL) -- 确保无匹配c时也能拿到cnt1、cnt2
WHERE t1.A > t1.B AND t1.C > 1.5
GROUP BY t1.id, t1.a, t1.b, t1.c;

方案二：预统计各维度唯一组合

如果t2数据量极大，窗口函数可能占用较多内存，可以改用分组统计各维度的唯一组合：

WITH t2_stats AS (
    -- 统计仅匹配a的计数
    SELECT a, NULL::int AS b, NULL::int AS c, COUNT(*) AS cnt1, 0 AS cnt2, 0 AS cnt3 FROM t2 GROUP BY a
    UNION ALL
    -- 统计匹配a,b的计数
    SELECT a, b, NULL::int AS c, 0 AS cnt1, COUNT(*) AS cnt2, 0 AS cnt3 FROM t2 GROUP BY a, b
    UNION ALL
    -- 统计匹配a,b,c的计数
    SELECT a, b, c, 0 AS cnt1, 0 AS cnt2, COUNT(*) AS cnt3 FROM t2 GROUP BY a, b, c
)
SELECT
    t1.*,
    COALESCE(SUM(s.cnt1), 0) AS cnt1,
    COALESCE(SUM(s.cnt2), 0) AS cnt2,
    COALESCE(SUM(s.cnt3), 0) AS cnt3
FROM t1
LEFT JOIN t2_stats s 
    ON s.a = t1.a 
    AND (s.b IS NULL OR s.b = t1.b)
    AND (s.c IS NULL OR s.c = t1.c)
WHERE t1.A > t1.B AND t1.C > 1.5
GROUP BY t1.id, t1.a, t1.b, t1.c;

额外优化：添加索引 #

为了进一步加速t2的统计查询，可以创建以下复合索引：

CREATE INDEX idx_t2_a ON t2(a);
CREATE INDEX idx_t2_ab ON t2(a,b);
CREATE INDEX idx_t2_abc ON t2(a,b,c);

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