postgresSql - select group by 和 子查询select 明细外层group by 的性能对比

sql 1 : select group by

select a.* . b.id 
from a,b 
group by xxx

执行计划：

Limit (cost=31546.83..31550.37 rows=100 width=1124)
CTE tmp
-> HashAggregate (cost=28714.63..29581.63 rows=57800 width=92)
Group Key: a.dt_str, a.org_id, a.tenant_id, a.type, CASE WHEN ((a.pig_type)::text = ANY ('{XXX,XXX}'::text[])) THEN 'EEE'::text WHEN ((a.pig_type)::text = ANY ('{EEE,WWW}'::text[])) THEN 'WWW'::text ELSE 'EEE'::text END, b.org_name
-> Hash Left Join (cost=4969.01..18978.95 rows=556325 width=88)
Hash Cond: (a.org_id = b.org_id)
-> Bitmap Heap Scan on table_name a (cost=147.86..5742.84 rows=289 width=43)
Recheck Cond: (dt_str = '2018-06-01'::date)
Filter: (org_id = 'SSS'::bigint)
-> Bitmap Index Scan on idx_table_name_dt_str (cost=0.00..147.79 rows=1515 width=0)
Index Cond: (dt_str = '2018-06-01'::date)
-> Hash (cost=4797.09..4797.09 rows=1925 width=33)
-> Subquery Scan on b (cost=4758.59..4797.09 rows=1925 width=33)
-> HashAggregate (cost=4758.59..4777.84 rows=1925 width=33)
Group Key: table_name1.org_id, table_name1.org_name
-> Bitmap Heap Scan on table_name1 (cost=524.96..4745.33 rows=2652 width=33)
Recheck Cond: (org_id = 'SSS'::bigint)
-> Bitmap Index Scan on idx_horg_org_id (cost=0.00..524.30 rows=2652 width=0)
Index Cond: (org_id = 'SSS'::bigint)
-> Hash Left Join (cost=1965.20..4009.88 rows=57800 width=1124)

sql2 ： 子查询select 明细外层group by

select *
from (
select a.* . b.id 
from a,b 
)
group by xxx

Limit (cost=30156.02..30159.56 rows=100 width=608)
CTE tmp
-> HashAggregate (cost=27323.82..28190.82 rows=57800 width=85)
Group Key: a.org_id, b.org_name, a.tenant_id, CASE WHEN ((a.pig_type)::text = ANY ('{XXX,XXX}'::text[])) THEN 'EEE'::text WHEN ((a.pig_type)::text = ANY ('{EEE,WWW}'::text[])) THEN 'WWW'::text ELSE 'EEE'::text END, a.dt_str
-> Hash Left Join (cost=4969.01..18978.95 rows=556325 width=81)
Hash Cond: (a.org_id = b.org_id)
-> Bitmap Heap Scan on table_name a (cost=147.86..5742.84 rows=289 width=36)
Recheck Cond: (dt_str = '2018-06-01'::date)
Filter: (org_id = 'SSS'::bigint)
-> Bitmap Index Scan on idx_table_name_dt_str (cost=0.00..147.79 rows=1515 width=0)
Index Cond: (dt_str = '2018-06-01'::date)
-> Hash (cost=4797.09..4797.09 rows=1925 width=33)
-> Subquery Scan on b (cost=4758.59..4797.09 rows=1925 width=33)
-> HashAggregate (cost=4758.59..4777.84 rows=1925 width=33)
Group Key: table_name1.org_id, table_name1.org_name
-> Bitmap Heap Scan on table_name1 (cost=524.96..4745.33 rows=2652 width=33)
Recheck Cond: (org_id = 'SSS'::bigint)
-> Bitmap Index Scan on idx_horg_org_id (cost=0.00..524.30 rows=2652 width=0)
Index Cond: (org_id = 'SSS'::bigint)
-> Hash Left Join (cost=1965.20..4009.88 rows=57800 width=608)

结论：
对比两个sql语句的执行计划的代码发现，先使用子查询查询明细数据后再包一层sql再计算group by ；数据量小的情况性能差距不大，数据量大后差距会比较明显