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。

相关文章

MySQL 多列 IN 查询之语法、性能与实战技巧(最新整理)

MySQL 多列 IN 查询之语法、性能与实战技巧(最新整理)

《MySQL多列IN查询之语法、性能与实战技巧(最新整理)》本文详解MySQL多列IN查询,对比传统OR写法,强调其简洁高效,适合批量匹配复合键,通过联合索引、分批次优化提升性能,兼容多种数据库... 目录一、基础语法：多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析
阅读更多...
mybatis执行insert返回id实现详解

mybatis执行insert返回id实现详解

《mybatis执行insert返回id实现详解》MyBatis插入操作默认返回受影响行数,需通过useGeneratedKeys+keyProperty或selectKey获取主键ID,确保主键为自... 目录 两种方式获取自增 ID：1. ​​useGeneratedKeys+keyProperty（推
阅读更多...
从入门到精通MySQL联合查询

从入门到精通MySQL联合查询

《从入门到精通MySQL联合查询》：本文主要介绍从入门到精通MySQL联合查询,本文通过实例代码给大家介绍的非常详细,需要的朋友可以参考下... 目录摘要1. 多表联合查询时mysql内部原理2. 内连接3. 外连接4. 自连接5. 子查询6. 合并查询7. 插入查询结果摘要前面我们学习了数据库设计时要满
阅读更多...
MySQL查询JSON数组字段包含特定字符串的方法

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案（推荐）2. 模糊匹配方案参数化查询示例使用场景建议性能优
阅读更多...
mysql表操作与查询功能详解

mysql表操作与查询功能详解

《mysql表操作与查询功能详解》本文系统讲解MySQL表操作与查询,涵盖创建、修改、复制表语法,基本查询结构及WHERE、GROUPBY等子句,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友跟随... 目录01.表的操作1.1表操作概览1.2创建表1.3修改表1.4复制表02.基本查询操作2.1 SE
阅读更多...
Golang如何对cron进行二次封装实现指定时间执行定时任务

Golang如何对cron进行二次封装实现指定时间执行定时任务

《Golang如何对cron进行二次封装实现指定时间执行定时任务》：本文主要介绍Golang如何对cron进行二次封装实现指定时间执行定时任务问题,具有很好的参考价值,希望对大家有所帮助,如有错误... 目录背景cron库下载代码示例【1】结构体定义【2】定时任务开启【3】使用示例【4】控制台输出总结背景
阅读更多...
MySQL数据库的内嵌函数和联合查询实例代码

MySQL数据库的内嵌函数和联合查询实例代码

《MySQL数据库的内嵌函数和联合查询实例代码》联合查询是一种将多个查询结果组合在一起的方法,通常使用UNION、UNIONALL、INTERSECT和EXCEPT关键字,下面：本文主要介绍MyS... 目录一.数据库的内嵌函数1.1聚合函数COUNT([DISTINCT] expr)SUM([DISTIN
阅读更多...
XML重复查询一条Sql语句的解决方法

XML重复查询一条Sql语句的解决方法

《XML重复查询一条Sql语句的解决方法》文章分析了XML重复查询与日志失效问题,指出因DTO缺少@Data注解导致日志无法格式化、空指针风险及参数穿透,进而引发性能灾难,解决方案为在Controll... 目录一、核心问题：从SQL重复执行到日志失效二、根因剖析：DTO断裂引发的级联故障三、解决方案：修复
阅读更多...
mysql查询使用_rowid虚拟列的示例

mysql查询使用_rowid虚拟列的示例

《mysql查询使用_rowid虚拟列的示例》MySQL中,_rowid是InnoDB虚拟列,用于无主键表的行ID查询,若存在主键或唯一列,则指向其,否则使用隐藏ID（不稳定）,推荐使用ROW_NUM... 目录1. 基本查询（适用于没有主键的表）2. 检查表是否支持 _rowid3. 注意事项4. 最佳实
阅读更多...
MySQL存储过程之循环遍历查询的结果集详解

MySQL存储过程之循环遍历查询的结果集详解

《MySQL存储过程之循环遍历查询的结果集详解》：本文主要介绍MySQL存储过程之循环遍历查询的结果集,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言1. 表结构2. 存储过程3. 关于存储过程的SQL补充总结前言近来碰到这样一个问题：在生产上导入的数据发现
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2