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 中的 JSON 查询案例详解

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》：本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J
阅读更多...
Go语言开发实现查询IP信息的MCP服务器

Go语言开发实现查询IP信息的MCP服务器

《Go语言开发实现查询IP信息的MCP服务器》随着MCP的快速普及和广泛应用,MCP服务器也层出不穷,本文将详细介绍如何在Go语言中使用go-mcp库来开发一个查询IP信息的MCP... 目录前言mcp-ip-geo 服务器目录结构说明查询 IP 信息功能实现工具实现工具管理查询单个 IP 信息工具的实现服
阅读更多...
MySQL索引的优化之LIKE模糊查询功能实现

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》：本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分
阅读更多...
Linux之计划任务和调度命令at/cron详解

Linux之计划任务和调度命令at/cron详解

《Linux之计划任务和调度命令at/cron详解》：本文主要介绍Linux之计划任务和调度命令at/cron的使用,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录linux计划任务和调度命令at/cron一、计划任务二、命令{at}介绍三、命令语法及功能 :at
阅读更多...
SQL表间关联查询实例详解

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括：左连接（leftjoin）、右连接（rightjoin）、全连接（fulljoin）、内连接（innerjoin）、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表
阅读更多...
MySQL高级查询之JOIN、子查询、窗口函数实际案例

MySQL高级查询之JOIN、子查询、窗口函数实际案例

《MySQL高级查询之JOIN、子查询、窗口函数实际案例》：本文主要介绍MySQL高级查询之JOIN、子查询、窗口函数实际案例的相关资料,JOIN用于多表关联查询,子查询用于数据筛选和过滤,窗口函... 目录前言1. JOIN（连接查询）1.1 内连接（INNER JOIN）1.2 左连接（LEFT JOI
阅读更多...
MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

《MySQL中查询VARCHAR类型JSON数据的问题记录》在数据库设计中,有时我们会将JSON数据存储在VARCHAR或TEXT类型字段中,本文将详细介绍如何在MySQL中有效查询存储为V... 目录一、问题背景二、mysql jsON 函数2.1 常用 JSON 函数三、查询示例3.1 基本查询3.2
阅读更多...
MySQL中的交叉连接、自然连接和内连接查询详解

MySQL中的交叉连接、自然连接和内连接查询详解

《MySQL中的交叉连接、自然连接和内连接查询详解》：本文主要介绍MySQL中的交叉连接、自然连接和内连接查询,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、引入二、交php叉连接（cross join）三、自然连接（naturalandroid join）四
阅读更多...
mysql的基础语句和外键查询及其语句详解(推荐)

mysql的基础语句和外键查询及其语句详解(推荐)

《mysql的基础语句和外键查询及其语句详解(推荐)》：本文主要介绍mysql的基础语句和外键查询及其语句详解(推荐),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋... 目录一、mysql 基础语句1. 数据库操作 创建数据库2. 表操作 创建表3. CRUD 操作二、外键
阅读更多...
Mybatis 传参与排序模糊查询功能实现

Mybatis 传参与排序模糊查询功能实现

《Mybatis传参与排序模糊查询功能实现》：本文主要介绍Mybatis传参与排序模糊查询功能实现,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友跟随小编一起看看吧... 目录一、#{ }和${ }传参的区别二、排序三、like查询四、数据库连接池五、mysql 开发企业规范一、#{ }和${ }传参的
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2