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。

相关文章

Oracle查询优化之高效实现仅查询前10条记录的方法与实践

Oracle查询优化之高效实现仅查询前10条记录的方法与实践

《Oracle查询优化之高效实现仅查询前10条记录的方法与实践》：本文主要介绍Oracle查询优化之高效实现仅查询前10条记录的相关资料,包括使用ROWNUM、ROW_NUMBER()函数、FET... 目录1. 使用 ROWNUM 查询2. 使用 ROW_NUMBER() 函数3. 使用 FETCH FI
阅读更多...
数据库oracle用户密码过期查询及解决方案

数据库oracle用户密码过期查询及解决方案

《数据库oracle用户密码过期查询及解决方案》：本文主要介绍如何处理ORACLE数据库用户密码过期和修改密码期限的问题,包括创建用户、赋予权限、修改密码、解锁用户和设置密码期限,文中通过代码介绍... 目录前言一、创建用户、赋予权限、修改密码、解锁用户和设置期限二、查询用户密码期限和过期后的修改1.查询用
阅读更多...
使用SQL语言查询多个Excel表格的操作方法

使用SQL语言查询多个Excel表格的操作方法

《使用SQL语言查询多个Excel表格的操作方法》本文介绍了如何使用SQL语言查询多个Excel表格,通过将所有Excel表格放入一个.xlsx文件中,并使用pandas和pandasql库进行读取和... 目录如何用SQL语言查询多个Excel表格如何使用sql查询excel内容1. 简介2. 实现思路3
阅读更多...
MySQL不使用子查询的原因及优化案例

MySQL不使用子查询的原因及优化案例

《MySQL不使用子查询的原因及优化案例》对于mysql,不推荐使用子查询,效率太差,执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响,本文给大家... 目录不推荐使用子查询和JOIN的原因解决方案优化案例案例1：查询所有有库存的商品信息案例2：使用EX
阅读更多...
SpringBoot基于MyBatis-Plus实现Lambda Query查询的示例代码

SpringBoot基于MyBatis-Plus实现Lambda Query查询的示例代码

《SpringBoot基于MyBatis-Plus实现LambdaQuery查询的示例代码》MyBatis-Plus是MyBatis的增强工具,简化了数据库操作,并提高了开发效率,它提供了多种查询方... 目录引言基础环境配置依赖配置（Maven）application.yml 配置表结构设计demo_st
阅读更多...
如何使用 Bash 脚本中的time命令来统计命令执行时间(中英双语)

如何使用 Bash 脚本中的time命令来统计命令执行时间(中英双语)

《如何使用Bash脚本中的time命令来统计命令执行时间(中英双语)》本文介绍了如何在Bash脚本中使用`time`命令来测量命令执行时间,包括`real`、`user`和`sys`三个时间指标,... 使用 Bash 脚本中的 time 命令来统计命令执行时间在日常的开发和运维过程中，性能监控和优化是不
阅读更多...
Redis KEYS查询大批量数据替代方案

Redis KEYS查询大批量数据替代方案

《RedisKEYS查询大批量数据替代方案》在使用Redis时,KEYS命令虽然简单直接,但其全表扫描的特性在处理大规模数据时会导致性能问题,甚至可能阻塞Redis服务,本文将介绍SCAN命令、有序... 目录前言KEYS命令问题背景替代方案1.使用 SCAN 命令2. 使用有序集合（Sorted Set）
阅读更多...
C#如何优雅地取消进程的执行之Cancellation详解

C#如何优雅地取消进程的执行之Cancellation详解

《C#如何优雅地取消进程的执行之Cancellation详解》本文介绍了.NET框架中的取消协作模型,包括CancellationToken的使用、取消请求的发送和接收、以及如何处理取消事件... 目录概述与取消线程相关的类型代码举例操作取消vs对象取消监听并响应取消请求轮询监听通过回调注册进行监听使用Wa
阅读更多...
MyBatis框架实现一个简单的数据查询操作

MyBatis框架实现一个简单的数据查询操作

《MyBatis框架实现一个简单的数据查询操作》本文介绍了MyBatis框架下进行数据查询操作的详细步骤,括创建实体类、编写SQL标签、配置Mapper、开启驼峰命名映射以及执行SQL语句等,感兴趣的... 基于在前面几章我们已经学习了对MyBATis进行环境配置，并利用SqlSessionFactory核
阅读更多...
PHP执行php.exe -v命令报错的解决方案

PHP执行php.exe -v命令报错的解决方案

《PHP执行php.exe-v命令报错的解决方案》：本文主要介绍PHP执行php.exe-v命令报错的解决方案,文中通过图文讲解的非常详细,对大家的学习或工作有一定的帮助,需要的朋友可以参考下... 目录执行phpandroid.exe -v命令报错解决方案执行php.exe -v命令报错-PHP War
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2