AWS Redshift 查询的执行计划(2)

2024-03-23 01:48
文章标签 查询 执行 计划 aws redshift

本文主要是介绍AWS Redshift 查询的执行计划(2)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

– 准备测试表及数据

create table t1_even (id int, col1 varchar(100), col2 varchar(100)) diststyle key distkey (id);
create table t2_even (t1_id int , col1 varchar(100), col2 varchar(100)) diststyle even;
create table t3_key (t1_id int, col1 varchar(100), col2 varchar(100)) diststyle key distkey (t1_id);
create table t4_all (t1_id int, col1 varchar(100), col2 varchar(100)) diststyle all;insert into t1_even values (1,'a','def');
insert into t1_even values (2,'b','def');
insert into t1_even values (3,'c','def');
insert into t1_even values (4,'d','def');
insert into t1_even values (5,'e','def');
insert into t1_even values (6,'f','def');
insert into t1_even values (7,'g','def');
insert into t1_even values (8,'h','def');
insert into t1_even values (9,'i','def');insert into t2_even values (1,'a','def');
insert into t2_even values (2,'b','def');
insert into t2_even values (3,'c','def');
insert into t2_even values (4,'d','def');
insert into t2_even values (5,'e','def');
insert into t2_even values (6,'f','def');
insert into t2_even values (7,'g','def');
insert into t2_even values (8,'h','def');
insert into t2_even values (9,'i','def');insert into t3_key values (1,'a','def');
insert into t3_key values (2,'b','def');
insert into t3_key values (3,'c','def');
insert into t3_key values (4,'d','def');
insert into t3_key values (5,'e','def');
insert into t3_key values (6,'f','def');
insert into t3_key values (7,'g','def');
insert into t3_key values (8,'h','def');
insert into t3_key values (9,'i','def');insert into t4_all values (1,'a','def');
insert into t4_all values (2,'b','def');
insert into t4_all values (3,'c','def');
insert into t4_all values (4,'d','def');
insert into t4_all values (5,'e','def');
insert into t4_all values (6,'f','def');
insert into t4_all values (7,'g','def');
insert into t4_all values (8,'h','def');
insert into t4_all values (9,'i','def');

查看数据分布

testdb=# select name, slice, col, num_values as rows, minvalue, maxvalue
from svv_diskusage
where name in ('t1_even', 't2_even' ,'t3_key','t4_all') and col=0 and rows>0
order by name, slice, col;name     | slice | col | rows | minvalue | maxvalue
--------------+-------+-----+------+----------+----------t1_even      |     1 |   0 |    2 |        1 |        6t1_even      |     2 |   0 |    2 |        4 |        7t1_even      |     3 |   0 |    2 |        3 |        8t1_even      |     4 |   0 |    1 |        5 |        5t1_even      |     6 |   0 |    2 |        2 |        9t2_even      |     0 |   0 |    2 |        3 |        9t2_even      |     1 |   0 |    1 |        4 |        4t2_even      |     3 |   0 |    1 |        7 |        7t2_even      |     4 |   0 |    1 |        6 |        6t2_even      |     5 |   0 |    1 |        8 |        8t2_even      |     6 |   0 |    1 |        2 |        2t2_even      |     7 |   0 |    2 |        1 |        5t3_key       |     1 |   0 |    2 |        1 |        6t3_key       |     2 |   0 |    2 |        4 |        7t3_key       |     3 |   0 |    2 |        3 |        8t3_key       |     4 |   0 |    1 |        5 |        5t3_key       |     6 |   0 |    2 |        2 |        9t4_all       |     0 |   0 |    9 |        1 |        9t4_all       |     2 |   0 |    9 |        1 |        9t4_all       |     4 |   0 |    9 |        1 |        9t4_all       |     6 |   0 |    9 |        1 |        9
(21 rows)

单表查询

testdb=# explain select col1 from t1_even where id =5;QUERY PLAN
----------------------------------------------------------XN Seq Scan on t1_even  (cost=0.00..0.11 rows=1 width=5)Filter: (id = 5)
(2 rows)testdb=# explain select col1 from t1_even where id in (1,3,5,7,9);QUERY PLAN
----------------------------------------------------------------------XN Seq Scan on t1_even  (cost=0.00..0.20 rows=5 width=5)Filter: ((id = 1) OR (id = 3) OR (id = 5) OR (id = 7) OR (id = 9))
(2 rows)testdb=# explain select col1 from t3_key where t1_id = 5;QUERY PLAN
---------------------------------------------------------XN Seq Scan on t3_key  (cost=0.00..0.11 rows=1 width=5)Filter: (t1_id = 5)
(2 rows)testdb=# explain select col1 from t4_all where t1_id in (1,3,5,7,9);QUERY PLAN
-------------------------------------------------------------------------------------XN Seq Scan on t4_all  (cost=0.00..0.09 rows=5 width=5)Filter: ((t1_id = 1) OR (t1_id = 3) OR (t1_id = 5) OR (t1_id = 7) OR (t1_id = 9))
(2 rows)testdb=# explain select col1 from t4_all where t1_id = 5;QUERY PLAN
---------------------------------------------------------XN Seq Scan on t4_all  (cost=0.00..0.01 rows=1 width=5)Filter: (t1_id = 5)
(2 rows)

通过每条语句的 Cost 我们可以看出， 当表的分配形式为 ALL 时， 全表查询所花费的 Cost最少。

多表联接

-- t1 与 t2 连接
testdb=# select t1.id , t1.col1, t2.t1_id, t2.col1
testdb-# from  t1_even t1 , t2_even t2 where t1.id = t2.t1_id;id | col1 | t1_id | col1
----+------+-------+------3 | c    |     3 | c8 | h    |     8 | h5 | e    |     5 | e1 | a    |     1 | a6 | f    |     6 | f2 | b    |     2 | b9 | i    |     9 | i4 | d    |     4 | d7 | g    |     7 | g
(9 rows)testdb=# explain select t1.id , t1.col1, t2.t1_id, t2.col1
from  t1_even t1 , t2_even t2 where t1.id = t2.t1_id;QUERY PLAN
---------------------------------------------------------------------------XN Hash Join DS_DIST_INNER  (cost=0.11..4050000.40 rows=9 width=344)Inner Dist Key: t2.t1_idHash Cond: ("outer".id = "inner".t1_id)->  XN Seq Scan on t1_even t1  (cost=0.00..0.09 rows=9 width=172)->  XN Hash  (cost=0.09..0.09 rows=9 width=172)->  XN Seq Scan on t2_even t2  (cost=0.00..0.09 rows=9 width=172)
(6 rows)testdb=# select t1.col1, t2.col1 from t1_even t1, t2_even t2 where t1.id = t2.t1_id and t1.id = 3;col1 | col1
------+------c    | c
(1 row)testdb=# explain select t1.col1, t2.col1 from t1_even t1, t2_even t2 where t1.id = t2.t1_id and t1.id = 3
;QUERY PLAN
-------------------------------------------------------------------------XN Hash Join DS_DIST_INNER  (cost=0.12..50000.25 rows=1 width=10)Inner Dist Key: t2.t1_idHash Cond: ("outer".id = "inner".t1_id)->  XN Seq Scan on t1_even t1  (cost=0.00..0.11 rows=1 width=9)Filter: (id = 3)->  XN Hash  (cost=0.11..0.11 rows=1 width=9)->  XN Seq Scan on t2_even t2  (cost=0.00..0.11 rows=1 width=9)Filter: (t1_id = 3)
(8 rows)

-- t1 与 t3 联接
testdb=# select t1.id , t1.col1, t3.t1_id, t3.col1
testdb-# from  t1_even t1 , t3_key t3 where t1.id = t3.t1_id;id | col1 | t1_id | col1
----+------+-------+------5 | e    |     5 | e4 | d    |     4 | d7 | g    |     7 | g2 | b    |     2 | b9 | i    |     9 | i3 | c    |     3 | c8 | h    |     8 | h1 | a    |     1 | a6 | f    |     6 | f
(9 rows)testdb=# explain select t1.id , t1.col1, t3.t1_id, t3.col1
from  t1_even t1 , t3_key t3 where t1.id = t3.t1_id;QUERY PLAN
------------------------------------------------------------------------------XN Hash Join DS_DIST_NONE  (cost=0.11..0.40 rows=9 width=344)Hash Cond: ("outer".id = "inner".t1_id)->  XN Seq Scan on t1_even t1  (cost=0.00..0.09 rows=9 width=172)->  XN Hash  (cost=0.09..0.09 rows=9 width=172)->  XN Seq Scan on t3_key t3  (cost=0.00..0.09 rows=9 width=172)----- Tables missing statistics: t3_key ---------- Update statistics by running the ANALYZE command on these tables -----
(7 rows)testdb=# select t1.col1, t3.col1 from t1_even t1, t3_key t3 where t1.id = t3.t1_id and t1.id > 6;col1 | col1
------+------h    | hg    | gi    | i
(3 rows)testdb=# explain select t1.col1, t3.col1 from t1_even t1, t3_key t3 where t1.id = t3.t1_id and t1.id > 6;QUERY PLAN
------------------------------------------------------------------------XN Hash Join DS_DIST_NONE  (cost=0.12..0.30 rows=2 width=10)Hash Cond: ("outer".id = "inner".t1_id)->  XN Seq Scan on t1_even t1  (cost=0.00..0.11 rows=4 width=9)Filter: (id > 6)->  XN Hash  (cost=0.11..0.11 rows=4 width=9)->  XN Seq Scan on t3_key t3  (cost=0.00..0.11 rows=4 width=9)Filter: (t1_id > 6)
(7 rows)

-- t1 与 t4 联接
testdb=# select t1.id , t1.col1, t4.t1_id, t4.col1
from  t1_even t1 , t4_all t4 where t1.id = t4.t1_id;id | col1 | t1_id | col1
----+------+-------+------1 | a    |     1 | a6 | f    |     6 | f3 | c    |     3 | c8 | h    |     8 | h5 | e    |     5 | e4 | d    |     4 | d7 | g    |     7 | g2 | b    |     2 | b9 | i    |     9 | i
(9 rows)testdb=# explain select t1.id , t1.col1, t4.t1_id, t4.col1
from  t1_even t1 , t4_all t4 where t1.id = t4.t1_id;QUERY PLAN
------------------------------------------------------------------------XN Hash Join DS_DIST_ALL_NONE  (cost=0.11..0.40 rows=9 width=181)Hash Cond: ("outer".id = "inner".t1_id)->  XN Seq Scan on t1_even t1  (cost=0.00..0.09 rows=9 width=172)->  XN Hash  (cost=0.09..0.09 rows=9 width=9)->  XN Seq Scan on t4_all t4  (cost=0.00..0.09 rows=9 width=9)
(5 rows)testdb=# select t1.id , t1.col1, t4.t1_id, t4.col1
from  t1_even t1 , t4_all t4 where t1.id = t4.t1_id and t1.id = 5;id | col1 | t1_id | col1
----+------+-------+------5 | e    |     5 | e
(1 row)testdb=# explain select t1.id , t1.col1, t4.t1_id, t4.col1
from  t1_even t1 , t4_all t4 where t1.id = t4.t1_id and t1.id = 5;QUERY PLAN
------------------------------------------------------------------------XN Hash Join DS_DIST_ALL_NONE  (cost=0.01..0.15 rows=1 width=18)Hash Cond: ("outer".id = "inner".t1_id)->  XN Seq Scan on t1_even t1  (cost=0.00..0.11 rows=1 width=9)Filter: (id = 5)->  XN Hash  (cost=0.01..0.01 rows=1 width=9)->  XN Seq Scan on t4_all t4  (cost=0.00..0.01 rows=1 width=9)Filter: (t1_id = 5)
(7 rows)

-- t1 与 t2, t3 联接
testdb=# select t1.col1, t2.col1, t3.col1 from t1_even t1, t2_even t2, t3_key t3 where t1.id = t2.t1_id and t1.id = t3.t1_id ;col1 | col1 | col1
------+------+------d    | d    | dg    | g    | ge    | e    | eb    | b    | bi    | i    | ic    | c    | ch    | h    | ha    | a    | af    | f    | f
(9 rows)testdb=# explain select t1.col1, t2.col1, t3.col1 from t1_even t1, t2_even t2, t3_key t3 where t1.id = t2.t1_id and t1.id = t3.t1_id ;QUERY PLAN
------------------------------------------------------------------------------XN Hash Join DS_DIST_INNER  (cost=0.22..450000.72 rows=9 width=15)Inner Dist Key: t2.t1_idHash Cond: ("outer".id = "inner".t1_id)->  XN Hash Join DS_DIST_NONE  (cost=0.11..0.40 rows=9 width=18)Hash Cond: ("outer".id = "inner".t1_id)->  XN Seq Scan on t1_even t1  (cost=0.00..0.09 rows=9 width=9)->  XN Hash  (cost=0.09..0.09 rows=9 width=9)->  XN Seq Scan on t3_key t3  (cost=0.00..0.09 rows=9 width=9)->  XN Hash  (cost=0.09..0.09 rows=9 width=9)->  XN Seq Scan on t2_even t2  (cost=0.00..0.09 rows=9 width=9)
(10 rows)testdb=# select t1.col1, t2.col1, t3.col1 from t1_even t1, t2_even t2, t3_key t3 where t1.id = t2.t1_id and t1.id = t3.t1_id and t1.id = 5;col1 | col1 | col1
------+------+------e    | e    | e
(1 row)testdb=# explain select t1.col1, t2.col1, t3.col1 from t1_even t1, t2_even t2, t3_key t3 where t1.id = t2.t1_id and t1.id = t3.t1_id and t1.id = 5;QUERY PLAN
------------------------------------------------------------------------------XN Hash Join DS_DIST_INNER  (cost=0.23..50000.39 rows=1 width=15)Inner Dist Key: t2.t1_idHash Cond: ("outer".id = "inner".t1_id)->  XN Hash Join DS_DIST_NONE  (cost=0.12..0.25 rows=1 width=18)Hash Cond: ("outer".id = "inner".t1_id)->  XN Seq Scan on t1_even t1  (cost=0.00..0.11 rows=1 width=9)Filter: (id = 5)->  XN Hash  (cost=0.11..0.11 rows=1 width=9)->  XN Seq Scan on t3_key t3  (cost=0.00..0.11 rows=1 width=9)Filter: (t1_id = 5)->  XN Hash  (cost=0.11..0.11 rows=1 width=9)->  XN Seq Scan on t2_even t2  (cost=0.00..0.11 rows=1 width=9)Filter: (t1_id = 5)
(13 rows)

-- t1 与 t2, t4 联接testdb=# select t1.col1, t2.col1, t4.col1 from t1_even t1, t2_even t2, t4_all t4 where t1.id = t2.t1_id and t1.id = t4.t1_id ;col1 | col1 | col1
------+------+------e    | e    | ed    | d    | dg    | g    | gc    | c    | ch    | h    | ha    | a    | af    | f    | fb    | b    | bi    | i    | i
(9 rows)testdb=# explain select t1.col1, t2.col1, t4.col1 from t1_even t1, t2_even t2, t4_all t4 where t1.id = t2.t1_id and t1.id = t4.t1_id ;QUERY PLAN
------------------------------------------------------------------------------XN Hash Join DS_DIST_INNER  (cost=0.22..450000.72 rows=9 width=15)Inner Dist Key: t2.t1_idHash Cond: ("outer".id = "inner".t1_id)->  XN Hash Join DS_DIST_ALL_NONE  (cost=0.11..0.40 rows=9 width=18)Hash Cond: ("outer".id = "inner".t1_id)->  XN Seq Scan on t1_even t1  (cost=0.00..0.09 rows=9 width=9)->  XN Hash  (cost=0.09..0.09 rows=9 width=9)->  XN Seq Scan on t4_all t4  (cost=0.00..0.09 rows=9 width=9)->  XN Hash  (cost=0.09..0.09 rows=9 width=9)->  XN Seq Scan on t2_even t2  (cost=0.00..0.09 rows=9 width=9)
(10 rows)testdb=# select t1.col1, t2.col1, t4.col1 from t1_even t1, t2_even t2, t4_all t4 where t1.id = t2.t1_id and t1.id = t4.t1_id and t1.id = 5;col1 | col1 | col1
------+------+------e    | e    | e
(1 row)testdb=# explain select t1.col1, t2.col1, t4.col1 from t1_even t1, t2_even t2, t4_all t4 where t1.id = t2.t1_id and t1.id = t4.t1_id and t1.id = 5;QUERY PLAN
------------------------------------------------------------------------------XN Hash Join DS_DIST_INNER  (cost=0.13..50000.29 rows=1 width=15)Inner Dist Key: t2.t1_idHash Cond: ("outer".id = "inner".t1_id)->  XN Hash Join DS_DIST_ALL_NONE  (cost=0.01..0.15 rows=1 width=18)Hash Cond: ("outer".id = "inner".t1_id)->  XN Seq Scan on t1_even t1  (cost=0.00..0.11 rows=1 width=9)Filter: (id = 5)->  XN Hash  (cost=0.01..0.01 rows=1 width=9)->  XN Seq Scan on t4_all t4  (cost=0.00..0.01 rows=1 width=9)Filter: (t1_id = 5)->  XN Hash  (cost=0.11..0.11 rows=1 width=9)->  XN Seq Scan on t2_even t2  (cost=0.00..0.11 rows=1 width=9)Filter: (t1_id = 5)
(13 rows)

连接时的执行计划：

  1. 当联接一个分配方式为ALL的表时， Join 方式为 DS_DIST_ALL_NONE，表示 “不需要重新分配，因为表的分配方式为 ALL，数据已经存在在每个节点”。
  2. 在大部分的分配方式为 even 情况下时，Join方式为 DS_DIST_INNER， 表示 “内部表被重新分配”。这是因为表的数据都分布在不同的节点中。
  3. 在有些情况下，我们可以看到 DS_DIST_NONE, 表示 “有没有表被重新分配，在没有在节点之间移动数据的情况下联接了相应的片”。

这篇关于AWS Redshift 查询的执行计划(2)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/836760。

相关文章

Spring定时任务只执行一次的原因分析与解决方案

Spring定时任务只执行一次的原因分析与解决方案

《Spring定时任务只执行一次的原因分析与解决方案》在使用Spring的@Scheduled定时任务时,你是否遇到过任务只执行一次,后续不再触发的情况？这种情况可能由多种原因导致,如未启用调度、线程... 目录1. 问题背景2. Spring定时任务的基本用法3. 为什么定时任务只执行一次？3.1 未启用
阅读更多...
浅谈mysql的sql_mode可能会限制你的查询

浅谈mysql的sql_mode可能会限制你的查询

《浅谈mysql的sql_mode可能会限制你的查询》本文主要介绍了浅谈mysql的sql_mode可能会限制你的查询,这个问题主要说明的是,我们写的sql查询语句违背了聚合函数groupby的规则... 目录场景：问题描述原因分析：解决方案：第一种：修改后，只有当前生效，若是mysql服务重启，就会失效；
阅读更多...
MySQL多列IN查询的实现

MySQL多列IN查询的实现

《MySQL多列IN查询的实现》多列IN查询是一种强大的筛选工具,它允许通过多字段组合快速过滤数据,本文主要介绍了MySQL多列IN查询的实现,具有一定的参考价值,感兴趣的可以了解一下... 目录一、基础语法：多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析与优化1.
阅读更多...
mybatis-plus 实现查询表名动态修改的示例代码

mybatis-plus 实现查询表名动态修改的示例代码

《mybatis-plus实现查询表名动态修改的示例代码》通过MyBatis-Plus实现表名的动态替换,根据配置或入参选择不同的表,本文主要介绍了mybatis-plus实现查询表名动态修改的示... 目录实现数据库初始化依赖包配置读取类设置 myBATis-plus 插件测试通过 mybatis-plu
阅读更多...
MySQL中实现多表查询的操作方法(配sql+实操图+案例巩固 通俗易懂版)

MySQL中实现多表查询的操作方法(配sql+实操图+案例巩固 通俗易懂版)

《MySQL中实现多表查询的操作方法(配sql+实操图+案例巩固通俗易懂版)》本文主要讲解了MySQL中的多表查询,包括子查询、笛卡尔积、自连接、多表查询的实现方法以及多列子查询等,通过实际例子和操... 目录复合查询1. 回顾查询基本操作group by 分组having1. 显示部门号为10的部门名，员
阅读更多...
mysql关联查询速度慢的问题及解决

mysql关联查询速度慢的问题及解决

《mysql关联查询速度慢的问题及解决》：本文主要介绍mysql关联查询速度慢的问题及解决方案,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql关联查询速度慢1. 记录原因1.1 在一次线上的服务中1.2 最终发现2. 解决方案3. 具体操作总结mysql
阅读更多...
mysql线上查询之前要性能调优的技巧及示例

mysql线上查询之前要性能调优的技巧及示例

《mysql线上查询之前要性能调优的技巧及示例》文章介绍了查询优化的几种方法,包括使用索引、避免不必要的列和行、有效的JOIN策略、子查询和派生表的优化、查询提示和优化器提示等,这些方法可以帮助提高数... 目录避免不必要的列和行使用有效的JOIN策略使用子查询和派生表时要小心使用查询提示和优化器提示其他常
阅读更多...
grom设置全局日志实现执行并打印sql语句

grom设置全局日志实现执行并打印sql语句

《grom设置全局日志实现执行并打印sql语句》本文主要介绍了grom设置全局日志实现执行并打印sql语句,包括设置日志级别、实现自定义Logger接口以及如何使用GORM的默认logger,通过这些... 目录gorm中的自定义日志gorm中日志的其他操作日志级别Debug自定义 Loggergorm中的
阅读更多...
JavaScript中的reduce方法执行过程、使用场景及进阶用法

JavaScript中的reduce方法执行过程、使用场景及进阶用法

《JavaScript中的reduce方法执行过程、使用场景及进阶用法》：本文主要介绍JavaScript中的reduce方法执行过程、使用场景及进阶用法的相关资料,reduce是JavaScri... 目录1. 什么是reduce2. reduce语法2.1 语法2.2 参数说明3. reduce执行过程
阅读更多...
SQL 中多表查询的常见连接方式详解

SQL 中多表查询的常见连接方式详解

《SQL中多表查询的常见连接方式详解》本文介绍SQL中多表查询的常见连接方式,包括内连接（INNERJOIN）、左连接（LEFTJOIN）、右连接（RIGHTJOIN）、全外连接（FULLOUTER... 目录一、连接类型图表（ASCII 形式）二、前置代码（创建示例表）三、连接方式代码示例1. 内连接（I
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2