Greenplum 注意对其数据类型，否则优化器让你好看

最新推荐文章于 2024-04-15 09:48:38 发布
最新推荐文章于 2024-04-15 09:48:38 发布
阅读量482 收藏
点赞数
文章标签： 数据库
原文链接：https://yq.aliyun.com/articles/92
版权 
在测试tpch时，发现有一些SQL跑超时，原因是测试SQL中有一些JOIN查询，而这些JOIN的列数据类型不一致，导致无法使用索引，或者无法使用hash join。
例子：
int和int8，都是整型，只是长度不一样。关联时，会遇到性能问题，因为不能使用HASH JOIN。
digoal=# create table t(id int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
digoal=# create table t1(id int8);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
digoal=# create index idx_t on t(id);
CREATE INDEX
digoal=# create index idx_t1 on t1(id);
CREATE INDEX
digoal=# insert into t select generate_series(1,10000);
INSERT 0 10000
digoal=# insert into t1 select generate_series(1,10000);
INSERT 0 10000
关联字段为int和int8，不能使用hash join，只能用nestloop。
digoal=# explain select * from t ,t1 where t.id=t1.id;
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Gather Motion 22:1  (slice2; segments: 22)  (cost=0.00..218902.00 rows=10000 width=12)
   ->  Nested Loop  (cost=0.00..218902.00 rows=455 width=12)
         ->  Broadcast Motion 22:22  (slice1; segments: 22)  (cost=0.00..2422.00 rows=10000 width=4)
               ->  Seq Scan on t  (cost=0.00..122.00 rows=455 width=4)
         ->  Index Scan using idx_t1 on t1  (cost=0.00..0.97 rows=1 width=8)
               Index Cond: t.id = t1.id
 Settings:  enable_seqscan=off
 Optimizer status: legacy query optimizer
(8 rows)
即使强制关掉nestloop也不行。
digoal=# set enable_nestloop=off;
SET
digoal=# explain select * from t ,t1 where t.id=t1.id;
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Gather Motion 22:1  (slice2; segments: 22)  (cost=0.00..218902.00 rows=10000 width=12)
   ->  Nested Loop  (cost=0.00..218902.00 rows=455 width=12)
         ->  Broadcast Motion 22:22  (slice1; segments: 22)  (cost=0.00..2422.00 rows=10000 width=4)
               ->  Seq Scan on t  (cost=0.00..122.00 rows=455 width=4)
         ->  Index Scan using idx_t1 on t1  (cost=0.00..0.97 rows=1 width=8)
               Index Cond: t.id = t1.id
 Settings:  enable_nestloop=off; enable_seqscan=off
 Optimizer status: legacy query optimizer
(8 rows)
和数据量当然并没有关系。
digoal=# insert into t select generate_series(1,10000000);
INSERT 0 10000000
digoal=# insert into t1 select generate_series(1,10000000);
INSERT 0 10000000
digoal=# explain select * from t ,t1 where t.id=t1.id;
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Gather Motion 22:1  (slice2; segments: 22)  (cost=0.00..218902.00 rows=10000 width=12)
   ->  Nested Loop  (cost=0.00..218902.00 rows=455 width=12)
         ->  Broadcast Motion 22:22  (slice1; segments: 22)  (cost=0.00..2422.00 rows=10000 width=4)
               ->  Seq Scan on t  (cost=0.00..122.00 rows=455 width=4)
         ->  Index Scan using idx_t1 on t1  (cost=0.00..0.97 rows=1 width=8)
               Index Cond: t.id = t1.id
 Settings:  enable_nestloop=off; enable_seqscan=off
 Optimizer status: legacy query optimizer
(8 rows)
接下来，使用同样的数据类型，可以用HASH JOIN关联。性能大幅提升。
digoal=# create table t2(id int8);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
digoal=# insert into t2 select * from t1;
INSERT 0 10010000
digoal=# create index idx_t2 on t2(id);
CREATE INDEX
digoal=# explain select * from t2 join t1 on( t2.id=t1.id );
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Gather Motion 22:1  (slice1; segments: 22)  (cost=236320.07..497677.13 rows=10013203 width=16)
   ->  Hash Join  (cost=236320.07..497677.13 rows=455146 width=16)
         Hash Cond: t1.id = t2.id
         ->  Seq Scan on t1  (cost=0.00..111158.22 rows=455161 width=8)
         ->  Hash  (cost=111155.03..111155.03 rows=455146 width=8)
               ->  Seq Scan on t2  (cost=0.00..111155.03 rows=455146 width=8)
 Optimizer status: legacy query optimizer
(7 rows)

另一个例子，当char长度不一样，不会有以上类似的问题。
digoal=# create table t(id char(10));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
digoal=# create table t1(id char(20));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
digoal=# insert into t1 select generate_series(1,10000000);
INSERT 0 10000000
digoal=# insert into t select generate_series(1,10000000);
INSERT 0 10000000
digoal=# explain select * from t join t1 on( t.id=t1.id );
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Gather Motion 22:1  (slice1; segments: 22)  (cost=240352.38..503423.59 rows=9975039 width=32)
   ->  Hash Join  (cost=240352.38..503423.59 rows=453411 width=32)
         Hash Cond: t.id = t1.id
         ->  Seq Scan on t  (cost=0.00..113396.58 rows=454303 width=11)
         ->  Hash  (cost=115664.39..115664.39 rows=453411 width=21)
               ->  Seq Scan on t1  (cost=0.00..115664.39 rows=453411 width=21)
 Optimizer status: legacy query optimizer
(7 rows)

digoal=# create index idx_t on t(id);
CREATE INDEX
digoal=# create index idx_t1 on t1(id);
CREATE INDEX
digoal=# explain select * from t join t1 on( t.id=t1.id );
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Gather Motion 22:1  (slice1; segments: 22)  (cost=240352.38..503423.59 rows=9975039 width=32)
   ->  Hash Join  (cost=240352.38..503423.59 rows=453411 width=32)
         Hash Cond: t.id = t1.id
         ->  Seq Scan on t  (cost=0.00..113396.58 rows=454303 width=11)
         ->  Hash  (cost=115664.39..115664.39 rows=453411 width=21)
               ->  Seq Scan on t1  (cost=0.00..115664.39 rows=453411 width=21)
 Optimizer status: legacy query optimizer
(7 rows)

digoal=# explain select * from t join t1 on( t.id=t1.id and t.id='1');
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..401.54 rows=4 width=32)
   ->  Nested Loop  (cost=0.00..401.54 rows=1 width=32)
         ->  Index Scan using idx_t on t  (cost=0.00..200.70 rows=1 width=11)
               Index Cond: id = '1'::bpchar
         ->  Index Scan using idx_t1 on t1  (cost=0.00..200.83 rows=1 width=21)
               Index Cond: '1'::bpchar = id
 Optimizer status: legacy query optimizer
(7 rows)
varchar和char(n)也不存在问题。
postgres=# create table t(id varchar);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
postgres=# create table t1(id char(10));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
postgres=# explain select * from t , t1 where t.id=t1.id;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Gather Motion 22:1  (slice2; segments: 22)  (cost=0.01..0.07 rows=4 width=76)
   ->  Hash Join  (cost=0.01..0.07 rows=1 width=76)
         Hash Cond: t.id::bpchar = t1.id
         ->  Redistribute Motion 22:22  (slice1; segments: 22)  (cost=0.00..0.02 rows=1 width=32)
               Hash Key: t.id::bpchar
               ->  Seq Scan on t  (cost=0.00..0.00 rows=1 width=32)
         ->  Hash  (cost=0.00..0.00 rows=1 width=44)
               ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=44)
 Optimizer status: legacy query optimizer
(9 rows)

数据类型和查询条件的类型不一致时，不走索引的例子。
digoal=# explain select * from t where id=1;
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Gather Motion 22:1  (slice1; segments: 22)  (cost=0.00..163369.87 rows=9995 width=11)
   ->  Seq Scan on t  (cost=0.00..163369.87 rows=455 width=11)
         Filter: id::text = '1'::text
 Optimizer status: legacy query optimizer
(4 rows)

digoal=# explain select * from t where id='1';
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..200.70 rows=1 width=11)
   ->  Index Scan using idx_t on t  (cost=0.00..200.70 rows=1 width=11)
         Index Cond: id = '1'::bpchar
 Optimizer status: legacy query optimizer
(4 rows)
显示转换后，可以走索引。
digoal=# explain select * from t where id=1::bpchar;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..200.70 rows=1 width=11)
   ->  Index Scan using idx_t on t  (cost=0.00..200.70 rows=1 width=11)
         Index Cond: id = '1'::bpchar
 Optimizer status: legacy query optimizer
(4 rows)

greenplum因为用了早期的postgresql版本，所以这一方面优化器并不好，在使用greenplum时，需要注意一下。
greenplum将合并到postgresql 9.5的版本，以上问题可以消除。
postgresql 9.4的例子：
digoal=# create table t(id int);
CREATE TABLE
digoal=# create table t1(id int8);
CREATE TABLE
digoal=# insert into t select generate_series(1,100000);
INSERT 0 100000
digoal=# insert into t1 select generate_series(1,100000);
INSERT 0 100000
digoal=# explain select * from t,t1 where t.id=t1.id;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Hash Join  (cost=2693.00..6136.00 rows=100000 width=12)
   Hash Cond: (t.id = t1.id)
   ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=4)
   ->  Hash  (cost=1443.00..1443.00 rows=100000 width=8)
         ->  Seq Scan on t1  (cost=0.00..1443.00 rows=100000 width=8)
(5 rows)
weixin_34144848
关注
让你的数据库飞起来！Greenplum查询优化解析
Greenplum中文社区
07-02 1613
6月19日，Greenplum原厂内核研发郭峰和大家直播分享了《深入浅出Greenplum内核》系列直播的第三期《Greenplum内核揭秘之查询优化》。没有参加活动的小伙伴也不用失望，我们已经将视频上传至Greenplum中文社区B站频道，通过点击这里即可观看。本文概括了文章的精华内容，欢迎留言交流。 直播视频（腾讯频道） https://v.qq.com/x/page/i3107m0gi47.html 本文将主要包括两大部分，第一部分是查询优化器的介绍，第二部分是查询优化的具体处理过程，整个过程将被分为
Greenplum数据分布和分区策略
Greenplum中文社区
03-12 3647
Greenplum是一个大规模并行处理数据库，它由一个master和多个segment组成，其数据按照设定的分布策略分布于各个segment上。数据表的单个行会被分配到一个或多个segment上，但是有这么多的segment，它到底会被分到哪个或哪些segment上呢？分布策略会告诉我们。 分布策略 在Greenplum 5中，有2种分布策略： 哈希分布随机分布 在Greenpl...
greenplum 优化
11-13
greenplum优化与维护，日常使用和维护，基本功能的优化，注意事项
Greenplum数据库优化器
u012564911的专栏
03-09 2567
legacy query optimizer      数据库默认使用此优化器，基于统计信息生成执行计划      pivotal  query optimizer       Greenplum数据库4.3.5.0开始出现，数据库默认关闭     系统级别: gpconfig -c optimizer -v on  (仅设置master主机，修改后生效参数：gpstop -u)  
GreenPlum优化器Orca论文笔记
Aegeaner的专栏
12-10 3216
一、简介Orca特性 模块化 可扩展性 适应多核 可验证性 性能 二、预备知识MPP（大规模并行处理） Share-nothing计算结构 两个以上的协调处理器 每个处理器有自己的内存，OS和硬盘 Master负责数据库实例之间的工作协调，每部分数据处理和存储的工作称为Segements。 当查询提交到master，查询会被优化并拆分为小的部分，并被分发为Segments。 通过网络层互联，实现Se
Greenplum的两种优化器
gxd520的专栏
04-27 2016
Greenplum 6.5.0 Greenplum中有两个优化器，一个是Postgres query optimizer(Postgresql自带的优化器，简称PQO)，一个是GPORCA(Greenplum自己开发的新一代优化器，简称GPO)。其中GPO在某些方面进行了优化，让其更适合在大数据量的进行数据分析，性能会更好。但是在数据量不是特别大的情况下，两种优化器的差异不是特别大，甚至PQO的性...
greenplum--orca查询优化器详解1
08-04
greenplum--orca查询优化器详解1
Greenplum执行器位图——让查询更有效
Greenplum中文社区
05-22 334
​为了让查询更加有效，Greenplum引入了索引，但是索引在一些应用场景上也会有访问性能、过滤条件限制等问题，而位图和基于位图的访问很好的解决了这一问题。今天我们通过这篇文章提前来看一下Greenplum执行器的奥秘。 为什么需要位图扫描 首先来看一个普通的查询 SELECT sid FROM student WHERE enroll_year = 2017 OR enroll_year = 2018 OR enroll_year = 2019; 执行上面查询时，Greenplum最简单的方式可以采用对
Greenplum在HTAP场景下的优化和应用
weixin_57459958的博客
04-23 923
近日，由DataFun主办的DataFun Summit——大数据存储架构峰会如约而至。作为全球超融合时序数据库的开创者，yMatrix（四维纵横）受邀出席了此次峰会并发表了主题演讲。小M助手已经帮大家把精华内容整理成了文章供大家回顾，欢迎各位关注。 大家早上好，我是四维纵横（https://www.ymatrix.cn)联合创始人高小明。创立四维纵横前我是Greenplum全球产品总监，领导Greenplum北京产品团队，并兼任Greenplum内核团队的产品经理。 今天非常荣幸和大家一起...
Greenplum架构最详解读（内含视频）
Greenplum中文社区
05-11 2212
​引言 4月17日，我们和阿里云合作，举行了《Greenplum内核揭秘》系列直播的第一场《架构解读》。在本次活动中，来自Greenplum全球总监杨瑜介绍了一些包括数据库、数据库管理系统、关系型数据库、关系模型等基本概念；详细解读了Greenplum的整体架构、存储管理、索引、查询执行、事务与日志等内容； 直播室里气氛热烈，大家都纷纷表示，此次活动演讲内容干货满满，收获多多。没有参加活动的小伙伴也不要气馁，这篇文章帮你牢固掌握本次直播的所有要点。读完别忘了前往askGP上测一测你的学习成果哦！ 很高兴为大
greenplum检索调优
double_happiness的博客
10-22 664
所有的数据库的检索调优基本上主要包括以下几方面 （1）填加分布键 （2）填加分区 （3）填加合适索引 （4）explain与analyze操作 （5）添加分页 （6）使用合适的建表策略  ...
greenplum-部分参数调优
qq_38195893的博客
03-29 505
Greenplum中有两个优化器，一个是Postgres query optimizer(Postgresql自带的优化器，简称PQO)，一个是GPORCA(Greenplum自己开发的新一代优化器，简称GPO)。其中GPO在某些方面进行了优化，让其更适合在大数据量的进行数据分析，性能会更好。但是在数据量不是特别大的情况下，两种优化器的差异不是特别大，甚至PQO的性能还要好于GPO。如果Greenplum要频繁对数据进行DML操作，建议使用PQO。
Greenplum 性能优化之路 --（三）ANALYZE
cloudbigdata的博客
09-30 1417
一、为什么需要 ANALYZE 首先介绍下 RBO 和 CBO，这是数据库引擎在执行 SQL 语句时的2种不同的优化策略。 RBO（Rule-Based Optimizer） 基于规则的优化器，就是优化器在优化查询计划的时候，是根据预先设置好的规则进行的，这些规则无法灵活改变。举个例子，索引优先于扫描，这是一个规则，优化器在遇到所有可以利用索引的地方，都不会选择扫描。这在多数情况下是正确的，但也不完全如此： 比如 一张个人信息表中性别栏目加上索引，由于性别是只有2个值的枚举类，也就是常说的基数非常低
探索GPORCA：Greenplum数据库优化器的新纪元
最新发布
gitblog_00065的博客
04-15 457
探索GPORCA：Greenplum数据库优化器的新纪元 项目地址:https://gitcode.com/greenplum-db/gporca 项目简介 GPORCA是Greenplum数据库的一款先进的查询优化器，旨在提升大规模数据处理的性能和效率。该项目由Pivotal公司开发，现已被Apache软件基金会开源，成为大数据社区的一个重要贡献。 技术解析 GPORCA（Greenplum O...
Greenplum 空间(GIS)数据检索 B-Tree & GiST 索引实践 - 阿里云HybridDB for PostgreSQL最佳实践...
weixin_34195142的博客
08-26 507
标签 PostgreSQL , GIS , PostGIS , Greenplum , 空间检索 , GiST , B-Tree , geohash 背景 气象数据、地震数据、室内定位、室外定位、手机、车联网、还有我们最喜欢的“左划不喜欢、右划喜欢”，越来越多的位置属性的数据。将来会越来越多。 基于GIS的数据分析、OLTP业务也越来越受到决策者的青...
GreenPlum常用数据类型
weberhuangxingbo的博客
03-05 6261
参考《GreenPlum企业应用实战》 1、数值类型： 2、字符类型： 3、时间类型： 转自：https://blog.csdn.net/u010256965/article/details/50508878/
GreenPlum DB向GBase迁移_INT类型
zhu1981hui的博客
07-29 120
标准的SQL数据类型，存储长度4字节，存储范围-2147483648～+2147483647，GBase可以使用INT,INTEGER数据类型替代。
GreenPlum初窥
micklf的博客
05-16 5200
介绍greenplum 4.3.8.0，基于PostgreSQL 8.2.15。Greenplum is regarded as the most scalable mission-critical analytical database。GP是Pivotal公司，将PostgreSQL增强后，把多实例组合，一起提供服务的MPP架构数据库。PB级的装载能力（支持50PB） 遵从SQL-92,
GreenPlum无符号tinyint开发
blueskylxb的专栏
06-28 982
一背景 1 有符号tinyint的使用 2 有符号tinyint的扩展 二无符号tinyint设计与编码 1 无符号tinyint的设计 2 无符号tinyint编码 三遇到的问题 1 刚开始编译扩展包的时候编译不成功 2 climits limitsh 四附加内容 1 无符号tinyint扩展包的编译安装 2 create function 五总结与展望 1 总结 2 下一步扩展 一、背景1.1
Orca查询优化器：Greenplum与大数据分析的核心
"greenplum--orca查询优化器详解1" 在大数据分析领域，查询优化器是数据管理系统性能的关键因素。随着数据量的增加和复杂分析查询需求的上升，Pivotal公司开发了一种新的查询优化器——Orca。Orca是Pivotal所有数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值