一、概述

优化拥有大量的分组和去重列的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不足时，性能提升较大。

最新文章

  1. bzoj1531: [POI2005]Bank notes
  2. A.Kaw矩阵代数初步学习笔记 2. Vectors
  3. [转载]JavaScript内存分析
  4. 基于h5的图片无刷新上传(uploadifive)
  5. [转]asp.net解决高并发的方案.
  6. SP*
  7. 【转】ubuntu64,ndk-r9 编译 ffmpeg 2.1.1的config文件
  8. MongoDB管理与开发精要 书摘
  9. 安装wps for linux无法启动
  10. C#公历转农历算法
  11. Cocos2dx开发（3）——Cocos2dx打包成APK，ANT环境搭建
  12. PHP 10 : 流程控制
  13. 【NOIP2014】【洛谷1941】【CJOJ1672】飞扬的小鸟
  14. Day 2 上午
  15. 虚拟机与Docker有何不同？
  16. 使用cygwin中的awk工具进行mysql binlog日志查看[利刃篇]
  17. 【2017-03-20】HTML基础知识，标记，表格，表格嵌套及布局，超链接
  18. 02-python-垃圾回收机制
  19. 为什么程序员老在改 Bug，就不能一次改好吗？
  20. session和cookie的知识总结

热门文章

  1. sql-扩展sql
  2. 基于.NetCore开发博客项目 StarBlog - (13) 加入友情链接功能
  3. CesiumJS 2022^ 源码解读[7] - 3DTiles 的请求、加载处理流程解析
  4. Halcon 条形码识别
  5. windows 安全
  6. Harbor-私有镜像仓库的安装部署
  7. IDEA快捷键之晨讲篇
  8. Solution -「2020.12.26」 模拟赛
  9. [USACO 2009 Mar S]Look Up_via牛客网
  10. Redis 5 种基本数据结构（String、List、Hash、Set、Sorted Set）详解 | JavaGuide