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。

相关文章

解密SQL查询语句执行的过程

解密SQL查询语句执行的过程

《解密SQL查询语句执行的过程》文章讲解了SQL语句的执行流程,涵盖解析、优化、执行三个核心阶段,并介绍执行计划查看方法EXPLAIN,同时提出性能优化技巧如合理使用索引、避免SELECT*、JOIN... 目录1. SQL语句的基本结构2. SQL语句的执行过程3. SQL语句的执行计划4. 常见的性能优
阅读更多...
Linux系统中查询JDK安装目录的几种常用方法

Linux系统中查询JDK安装目录的几种常用方法

《Linux系统中查询JDK安装目录的几种常用方法》：本文主要介绍Linux系统中查询JDK安装目录的几种常用方法,方法分别是通过update-alternatives、Java命令、环境变量及目... 目录方法 1：通过update-alternatives查询（推荐）方法 2：检查所有已安装的 JDK方
阅读更多...
Spring Bean初始化及@PostConstruc执行顺序示例详解

Spring Bean初始化及@PostConstruc执行顺序示例详解

《SpringBean初始化及@PostConstruc执行顺序示例详解》本文给大家介绍SpringBean初始化及@PostConstruc执行顺序,本文通过实例代码给大家介绍的非常详细,对大家的... 目录1. Bean初始化执行顺序2. 成员变量初始化顺序2.1 普通Java类（非Spring环境）(
阅读更多...
Spring Boot 中的默认异常处理机制及执行流程

Spring Boot 中的默认异常处理机制及执行流程

《SpringBoot中的默认异常处理机制及执行流程》SpringBoot内置BasicErrorController,自动处理异常并生成HTML/JSON响应,支持自定义错误路径、配置及扩展,如... 目录Spring Boot 异常处理机制详解默认错误页面功能自动异常转换机制错误属性配置选项默认错误处理
阅读更多...
如何在Java Spring实现异步执行(详细篇)

如何在Java Spring实现异步执行(详细篇)

《如何在JavaSpring实现异步执行(详细篇)》Spring框架通过@Async、Executor等实现异步执行,提升系统性能与响应速度,支持自定义线程池管理并发,本文给大家介绍如何在Sprin... 目录前言1. 使用 @Async 实现异步执行1.1 启用异步执行支持1.2 创建异步方法1.3 调用
阅读更多...
Spring Boot Maven 插件如何构建可执行 JAR 的核心配置

Spring Boot Maven 插件如何构建可执行 JAR 的核心配置

《SpringBootMaven插件如何构建可执行JAR的核心配置》SpringBoot核心Maven插件,用于生成可执行JAR/WAR,内置服务器简化部署,支持热部署、多环境配置及依赖管理... 目录前言一、插件的核心功能与目标1.1 插件的定位1.2 插件的 Goals（目标）1.3 插件定位1.4 核
阅读更多...
MyBatis-Plus通用中等、大量数据分批查询和处理方法

MyBatis-Plus通用中等、大量数据分批查询和处理方法

《MyBatis-Plus通用中等、大量数据分批查询和处理方法》文章介绍MyBatis-Plus分页查询处理,通过函数式接口与Lambda表达式实现通用逻辑,方法抽象但功能强大,建议扩展分批处理及流式... 目录函数式接口获取分页数据接口数据处理接口通用逻辑工具类使用方法简单查询自定义查询方法总结函数式接口
阅读更多...
MySql基本查询之表的增删查改+聚合函数案例详解

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入（单行/多行及冲突处理）,SELECT实现数据检索（列选择、条件过滤、排序分页）,UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更
阅读更多...
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（推
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2