Group 和 Distinct 列的次序影响查询性能

一、概述

优化拥有大量的分组和去重列的SQL时，这些排序列的次序，也是可以优化的地方。

测试数据结构

kingbase=# select count(distinct txt1 ) txt1, avg(length(txt1))::int ln1, count(distinct txt3 ) txt3 ,avg(length(txt3))::int ln3 from txt01;

 txt1 | ln1  |  txt3   | ln3 
------+------+---------+-----
 1000 | 1000 | 1000000 |  10
(1 行记录)

二、work_mem 满足排序情况

1、Distinct 语句

次序： txt1,txt3

kingbase=# explain (analyse ,buffers ) select /*+ set(work_mem 100MB) */ distinct txt1 ,txt3 from txt01 ;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=269287.33..269687.33 rows=40000 width=64) (actual time=1543.995..1877.527 rows=1000000 loops=1)
   Group Key: txt1, txt3
   Buffers: shared hit=142858
   ->  Seq Scan on txt01  (cost=0.00..227144.22 rows=8428622 width=64) (actual time=0.008..159.858 rows=1000000 loops=1)
         Buffers: shared hit=142858
 Planning Time: 0.081 ms
 Execution Time: 1947.951 ms
(7 行记录)

次序： txt3,txt1

ingbase=# explain (analyse ,buffers ) select /*+ set(work_mem 100MB) */ distinct txt3 ,txt1 from txt01 ;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=269287.33..269687.33 rows=40000 width=64) (actual time=1596.040..1812.380 rows=1000000 loops=1)
   Group Key: txt3, txt1
   Buffers: shared hit=142858
   ->  Seq Scan on txt01  (cost=0.00..227144.22 rows=8428622 width=64) (actual time=0.007..163.399 rows=1000000 loops=1)
         Buffers: shared hit=142858
 Planning Time: 0.075 ms
 Execution Time: 1884.907 ms
(7 行记录)

2、Group by 语句

次序： txt1,txt3

kingbase=# explain (analyse ,buffers ) select /*+ set(work_mem 100MB) */  txt1 ,txt3 from txt01 group by txt1 ,txt3 ;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=269287.33..269687.33 rows=40000 width=64) (actual time=1540.948..1875.917 rows=1000000 loops=1)
   Group Key: txt1, txt3
   Buffers: shared hit=142858
   ->  Seq Scan on txt01  (cost=0.00..227144.22 rows=8428622 width=64) (actual time=0.006..160.419 rows=1000000 loops=1)
         Buffers: shared hit=142858
 Planning Time: 0.084 ms
 Execution Time: 1939.103 ms
(7 行记录)

次序： txt3,txt1

kingbase=# explain (analyse ,buffers ) select /*+ set(work_mem 100MB) */ txt1 ,txt3 from txt01 group by txt3 ,txt1 ;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=269287.33..269687.33 rows=40000 width=64) (actual time=1557.257..1780.662 rows=1000000 loops=1)
   Group Key: txt3, txt1
   Buffers: shared hit=142858
   ->  Seq Scan on txt01  (cost=0.00..227144.22 rows=8428622 width=64) (actual time=0.018..165.221 rows=1000000 loops=1)
         Buffers: shared hit=142858
 Planning Time: 0.330 ms
 Execution Time: 1844.664 ms
(7 行记录)

三、work_mem 不满足排序情况

1、Distinct 语句

次序： txt1,txt3

kingbase=# explain (analyse ,buffers ) select /*+ set(work_mem 1MB) */ distinct txt1 ,txt3 from txt01  ;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=2464313.08..2527527.74 rows=40000 width=64) (actual time=21031.092..22131.259 rows=1000000 loops=1)
   Buffers: shared hit=142858, temp read=125368 written=125369
   ->  Sort  (cost=2464313.08..2485384.63 rows=8428622 width=64) (actual time=21031.089..22002.850 rows=1000000 loops=1)
         Sort Key: txt1, txt3
         Sort Method: external merge  Disk: 1002944kB
         Buffers: shared hit=142858, temp read=125368 written=125369
         ->  Seq Scan on txt01  (cost=0.00..227144.22 rows=8428622 width=64) (actual time=0.039..272.327 rows=1000000 loops=1)
               Buffers: shared hit=142858
 Planning Time: 0.272 ms
 Execution Time: 23648.185 ms
(10 行记录)

次序： txt3,txt1

kingbase=# explain (analyse ,buffers ) select /*+ set(work_mem 1MB) */ distinct txt3 ,txt1 from txt01  ;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=2464313.08..2527527.74 rows=40000 width=64) (actual time=4004.641..4367.218 rows=1000000 loops=1)
   Buffers: shared hit=142858, temp read=125491 written=125492
   ->  Sort  (cost=2464313.08..2485384.63 rows=8428622 width=64) (actual time=4004.639..4239.599 rows=1000000 loops=1)
         Sort Key: txt3, txt1
         Sort Method: external merge  Disk: 1003928kB
         Buffers: shared hit=142858, temp read=125491 written=125492
         ->  Seq Scan on txt01  (cost=0.00..227144.22 rows=8428622 width=64) (actual time=0.011..271.572 rows=1000000 loops=1)
               Buffers: shared hit=142858
 Planning Time: 0.086 ms
 Execution Time: 4457.751 ms
(10 行记录)

2、Group by 语句

次序： txt1,txt3

kingbase=# explain (analyse ,buffers ) select /*+ set(work_mem 1MB) */ txt1 ,txt3 from txt01 group by txt1 ,txt3 ;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Group  (cost=2464313.08..2527527.74 rows=40000 width=64) (actual time=21715.770..22796.166 rows=1000000 loops=1)
   Group Key: txt1, txt3
   Buffers: shared hit=142858, temp read=125368 written=125369
   ->  Sort  (cost=2464313.08..2485384.63 rows=8428622 width=64) (actual time=21715.764..22658.413 rows=1000000 loops=1)
         Sort Key: txt1, txt3
         Sort Method: external merge  Disk: 1002944kB
         Buffers: shared hit=142858, temp read=125368 written=125369
         ->  Seq Scan on txt01  (cost=0.00..227144.22 rows=8428622 width=64) (actual time=0.029..271.335 rows=1000000 loops=1)
               Buffers: shared hit=142858
 Planning Time: 0.285 ms
 Execution Time: 25365.012 ms
(11 行记录)

次序： txt3,txt1

kingbase=# explain (analyse ,buffers ) select /*+ set(work_mem 1MB) */ txt1 ,txt3 from txt01 group by txt3 ,txt1 ;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Group  (cost=2464313.08..2527527.74 rows=40000 width=64) (actual time=4156.296..4541.315 rows=1000000 loops=1)
   Group Key: txt3, txt1
   Buffers: shared hit=142858, temp read=125368 written=125369
   ->  Sort  (cost=2464313.08..2485384.63 rows=8428622 width=64) (actual time=4156.291..4402.265 rows=1000000 loops=1)
         Sort Key: txt3, txt1
         Sort Method: external merge  Disk: 1002944kB
         Buffers: shared hit=142858, temp read=125368 written=125369
         ->  Seq Scan on txt01  (cost=0.00..227144.22 rows=8428622 width=64) (actual time=0.008..270.872 rows=1000000 loops=1)
               Buffers: shared hit=142858
 Planning Time: 0.081 ms
 Execution Time: 4632.567 ms
(11 行记录)

四、总结

次序	txt1,txt1	txt3,txt1
work_mem满足排序	1947.951 ms	1884.907 ms
work_mem不足排序	25365.012 ms	4632.567 ms

字节少数据值多的列，处于排序列的前列，可以带来性能的提升。当work_mem满足排序时，性能差异不大，当work_mem不足时，性能提升较大。