华为云用户手册

基本数据类型映射关系 表1 PL/Java默认数据类型映射关系 GaussDB (DWS) Java BOOLEAN boolean "char" byte bytea byte[] SMALLINT short INTEGER int BIGINT long FLOAT4 float FLOAT8 double CHAR java.lang.String VARCHAR java.lang.String TEXT java.lang.String name java.lang.String DATE java.sql.Timestamp TIME java.sql.Time (stored value treated as local time) TIMETZ java.sql.Time TIMESTAMP java.sql.Timestamp TIMESTAMPTZ java.sql.Timestamp

示例 使用PL/Java函数时，需要首先将Java方法的实现打包为jar包并且部署到数据库中，然后使用数据库管理员账号创建函数，考虑兼容性问题，请使用1.8.0_322版本的JRE进行编译。 编译jar包。 Java方法的实现和出包可以借助IDE来实现，以下是一个通过命令行来进行编译和出包的简单的示例，通过这个简单示例可以创建出一个包含单个方法的jar包文件。 首先，编写一个Example.java文件，在此文件中实现子字符串大写转换的方法，本例中类名为Example，方法名为upperString，内容如下： 1 2 3 4 5 6 7 public class Example { public static String upperString (String text, int beginIndex, int endIndex) { return text.substring(beginIndex, endIndex).toUpperCase(); } } 然后，创建manifest.txt清单文件，文件内容如下： 1 2 3 4 5 6 Manifest-Version: 1.0 Main-Class: Example Specification-Title: "Example" Specification-Version: "1.0" Created-By: 1.6.0_35-b10-428-11M3811 Build-Date: 08/14/2018 10:09 AM 其中，Manifest-Version定义了manifest文件的版本，Main-Class定义了jar文件的入口类，Specification-Title和Specification-Version属于包的扩展属性，Specification-Title定义了扩展规范的标题，Specification-Version定义了扩展规范的版本，Created-By声明了该文件的生成者，Build-Date声明了该文件构建日期。 最后，编译java文件并打包得到javaudf-example.jar 1 2 javac Example.java jar cfm javaudf-example.jar manifest.txt Example.class jar包的命名规则应符合JDK命名要求，如果含有非法字符，在部署或者使用函数时将出错。 部署jar包。 Jar包首先需要放置到OBS服务器中，放置方法具体请参见《 对象存储服务 控制台指南》的上传文件章节。接着创建访问密钥AK/SK，获取访问密钥的具体步骤，请参见创建访问密钥（AK和SK）章节。登录数据库运行gs_extend_library函数，将文件导入到GaussDB(DWS)中： 1 SELECT gs_extend_library('addjar', 'obs://bucket/path/javaudf-example.jar accesskey=access_key_value_to_be_replaced secretkey=secret_access_key_value_to_be_replaced region=region_name libraryname=example'); gs_extend_library函数如何使用请参见 管理jar包和文件。函数中的AK/SK值，请用户根据实际获取值替换。region_name请用户根据实际所在的区 域名 称替换。 使用PL/Java函数。 首先，使用拥有sysadmin权限的数据库用户（例如：dbadmin）登录数据库并创建java_upperstring函数如下： 1 2 3 4 CREATE FUNCTION java_upperstring(VARCHAR, INTEGER, INTEGER) RETURNS VARCHAR AS 'Example.upperString' LANGUAGE JAVA; 函数java_upperstring中定义的数据类型为GaussDB(DWS)的数据类型。该数据类型需要和步骤1中java定义的方法upperString中数据类型一一对应。GaussDB(DWS)与Java数据类型的对应关系，请参见表1。 AS子句用于指定该函数所调用的Java方法的类名和static方法名，格式为“类名.方法名”。该字段需要和步骤1中java定义的类名和方法名一致。 使用PL/Java函数时，LANGUAGE字段应指定为JAVA。 CREATE FUNCTION更多说明，请参见创建函数。 然后，执行java_upperstring函数： 1 SELECT java_upperstring('test', 0, 1); 得到预期结果为： 1 2 3 4 java_upperstring --------------------- T (1 row) 授权普通用户使用PL/Java函数。 创建普通用户，名称为udf_user。 1 CREATE USER udf_user PASSWORD 'password'; 授权普通用户udf_user对java_upperstring函数的使用权限。注意，此处需要把函数所在模式和函数的使用权限同时赋予给用户，用户才可以使用此函数。 1 2 GRANT ALL PRIVILEGES ON SCHEMA public TO udf_user; GRANT ALL PRIVILEGES ON FUNCTION java_upperstring(VARCHAR, INTEGER, INTEGER) TO udf_user; 以普通用户udf_user登录数据库。 1 SET SESSION SESSION AUTHORIZATION udf_user PASSWORD 'password'; 执行java_upperstring函数： 1 SELECT public.java_upperstring('test', 0, 1); 得到预期结果为： 1 2 3 4 java_upperstring --------------------- T (1 row) 删除函数。 如果不再使用该函数可以进行删除： 1 DROP FUNCTION java_upperstring; 卸载jar包。 使用gs_extend_library函数卸载jar包： 1 SELECT gs_extend_library('rmjar', 'libraryname=example');

SQL语句出错自动重试 GaussDB(DWS)支持在SQL语句执行出错时自动重试（下文简称CN Retry）。对于来自gsql客户端、JDBC、ODBC驱动的SQL语句，在SQL语句执行失败时，CN端能够自动识别语句执行过程中的报错，并重新下发任务进行自动重试。 该功能的限制和约束如下： 功能范围限制： 仅能提高故障发生时SQL语句执行成功率，不能保证100%的执行成功。 CN Retry默认开启，开启后temp表会记录日志，关闭CN Retry后，temp表不会记录日志，因此不能在使用temp表时反复打开/关闭CN Retry开关，否则主备切换后再CN Retry会造成数据不一致。 CN Retry默认开启，开启后新创建的unlogged表会忽视unlogged关键字，建成普通表。关闭CN Retry后，unlogged表不会记录日志，因此不能在使用unlogged表时反复打开/关闭CN Retry开关，否则主备切换后再CN Retry会造成数据不一致。 在使用gds进行数据导出时，支持CN Retry。现有机制导出时会对重复文件进行检测并删除相同的文件，因此建议不要对相同的外表重复导出数据，除非确定数据目录中相同文件名的文件需要删除。 错误类型约束： SQL语句出错时能够被识别和重试的错误，仅限在错误类型列表（请参考表1）中定义的错误。 语句类型约束： 支持单语句CN Retry、存储过程、函数、匿名块。不支持事务块中的语句。 存储过程语句约束： 包含EXCEPTION的存储过程，如果在执行过程中（包含语句块执行和EXCEPTION中的语句执行）错误被抛出，可以retry，且系统内部错误发生时，retry会先于EXCEPTION被执行，而如果报错被EXCEPTION捕获则不能retry。 不支持使用全局变量的package。 不支持DBMS_JOB。 不支持UTL_FILE。 如果存储过程中有输出打印信息（如dbms_output.put_line或raise info等），则发生retry时会重复输出已打印的消息，并会在重复消息前输出“Notice：Retry triggered, some message may be duplicated.”加以提示。 集群状态约束： 仅支持DN、GTM实例故障。 CN Retry有次数限制，如果在CN Retry达到最大尝试次数（最大次数由max_query_retry_times控制）之前，集群状态无法从故障状态恢复到正常状态，那么CN Retry不能保证执行成功。 扩容时不支持CN Retry。 数据导入约束： 不支持COPY FROM STDIN语句。 不支持gsql \copy from元命令。 不支持JDBC CopyManager copyIn导入数据。 CN Retry支持的错误类型列表和对应的错误码信息见表1， 可以通过GUC参数retry_ecode_list设置CN Retry支持的错误类型列表，但不建议用户直接修改该参数，如有修改需求请联系技术工程师协助处理。 表1 CN Retry支持的错误类型列表 错误类型 错误码 备注 对端连接重置（CONNECTION_RESET_BY_PEER） YY001 TCP通信错误：Connection reset by peer（CN和DN间通信） 对端流重置（STREAM_CONNECTION_RESET_BY_PEER） YY002 TCP通信错误：Stream connection reset by peer（DN和DN间通信） 锁等待超时（LOCK_WAIT_TIMEOUT） YY003 锁超时，Lock wait timeout 连接超时（CONNECTION_TIMED_OUT） YY004 TCP通信错误，Connection timed out 查询设置错误（SET_QUERY_ERROR） YY005 SET命令发送失败，Set query 超出逻辑内存（OUT_OF_ LOG ICAL_MEMORY） YY006 内存申请失败，Out of logical memory 通信库内存分配（SCTP_MEMORY_ALLOC） YY007 SCTP通信错误，Memory allocate error 无通信库缓存数据（SCTP_NO_DATA_IN_BUFFER） YY008 SCTP通信错误，SCTP no data in buffer 通信库释放内存关闭（SCTP_RELEASE_MEMORY_CLOSE） YY009 SCTP通信错误，Release memory close SCTP、TCP断开（SCTP_TCP_DISCONNECT） YY010 SCTP通信错误，TCP disconnect 通信库断开（SCTP_DISCONNECT） YY011 SCTP通信错误，SCTP disconnect 通信库远程关闭（SCTP_REMOTE_CLOSE） YY012 SCTP通信错误，Stream closed by remote 等待未知通信库通信（SCTP_WAIT_POLL_UNKNOW） YY013 等待未知通信库通信，SCTP wait poll unknow 无效快照（SNAPSHOT_INVALID） YY014 快照非法，Snapshot invalid 通讯接收信息错误（ERRCODE_CONNECTION_RECEIVE_WRONG） YY015 连接获取错误，Connection receive wrong 内存耗尽（OUT_OF_MEMORY） 53200 内存耗尽，Out of memory 连接失败（CONNECTION_FAILURE） 08006 GTM出错，Connection failure 连接异常（CONNECTION_EXCEPTION） 08000 连接出现错误，和DN的通讯失败，Connection exception 管理员关闭系统（ADMIN_SHUTDOWN） 57P01 管理员关闭系统，Admin shutdown 关闭远程流接口（STREAM_REMOTE_CLOSE_SOCKET） XX003 关闭远程套接字，Stream remote close socket 重复查询编号（ERRCODE_STREAM_DUPLICATE_QUERY_ID） XX009 重复查询，Duplicate query id stream查询并发更新同一行（ERRCODE_STREAM_CONCURRENT_UPDATE） YY016 stream查询并发更新同一行，Stream concurrent update LLVM内存分配错误（ERRCODE_LLVM_BAD_ALLOC_ERROR ） CG003 内存分配错误， Allocate error LLVM致命错误（ERRCODE_LLVM_FATAL_ERROR） CG004 致命错误，Fatal error HashJoin临时文件读取错误(ERRCODE_HASHJOIN_TEMP_FILE_ERROR) F0011 临时文件读取错误，File error 分区个数发生变化(ERRCODE_PARTITION_NUM_CHANGED) 45003 在扫描LIST分区表时，发现此时的分区个数和优化阶段的分区个数不一致，一般出现在查询和ADD/DROP分区并发时。（此错误类型仅8.1.3及以上集群版本支持） 开启CN Retry功能需要设置如下GUC参数： 必选的GUC参数（CN和DN都需设置） max_query_retry_times CN Retry功能开启时会为临时表数据记录日志，为保证数据一致性，在使用临时表时不能切换CN Retry开关状态，保持使用临时表的会话中CN Retry开关始终处于打开状态或者关闭状态。 可选的GUC参数 cn_send_buffer_size max_cn_temp_file_size 父主题： SQL调优

示例 设置query_band“JobName=abc”关联资源池p1、队列内优先级Rush、次序为1。 1 2 3 4 5 SELECT * FROM gs_wlm_set_queryband_action('JobName=abc','respool=p1;priority=rush',1); gs_wlm_set_queryband_action ----------------------------- t (1 row) 修改query_band“JobName=abc”的关联资源池为p2。 1 2 3 4 5 SELECT * FROM gs_wlm_set_queryband_action('JobName=abc','respool=p2'); gs_wlm_set_queryband_action ----------------------------- t (1 row) 修改query_band“JobName=abc”的队列内优先级为High。 1 2 3 4 5 SELECT * FROM gs_wlm_set_queryband_action('JobName=abc','priority=high'); gs_wlm_set_queryband_action ----------------------------- t (1 row) 修改query_band“JobName=abc”的次序为3。 1 2 3 4 5 SELECT * FROM gs_wlm_set_queryband_order('JobName=abc',3); gs_wlm_set_queryband_order ----------------------------- t (1 row) 查询query_band关联的负载行为。 1 2 3 4 5 6 SELECT * FROM pg_queryband_action; qband | respool_id | respool | priority | qborder --------------+------------+---------+----------+--------- AppName=test | 16974 | p1 | low | -1 JobName=abc | 17119 | p2 | high | 1 (2 rows)

query_band支持的负载行为 query_band是一个session级别的GUC参数，作为作业标识符本身没有特殊含义，数据类型为字符型，支持赋值任何字符串。但是为方便区分和设置，query_band负载识别仅支持识别键值对形式的query_band，示例： SET query_band='JobName=abc;AppName=test;UserName=user'; 其中，‘JobName=abc’，‘AppName=test’以及‘UserName=user’都是一个独立的键值对。query_band键值对规格： query_band使用键值对方式设置，即'key=value'；session内支持设置多个query_band键值对，多个键值对之间使用分号分隔。query_band键值对和query_band参数值长度的上限均为1024个字符。 query_band键值对支持的有效字符包括：数字0~9、大写字母A~Z、小写字母a-z、'.'、'-'、'_' 以及'#'。 query_band负载识别以键值对为单位设置和识别负载行为，目前支持的负载行为，如表1所示： 表1 QUERY_BAND支持负载行为 类别 行为 行为表现 负载管理(workload) 资源池(respool) query_band关联资源池 负载管理(workload) 优先级(priority) 队列内优先级 次序(order) 队列（respool） 目前为无效字段，主要用于后续扩展。 query_band搜索次序 其中，行为类别用于负载行为归类，不同的负载行为可能属于同一个类别，比如资源池和优先级同属于负载管理类别；负载行为代表query_band键值对关联的是哪种负载行为；行为表现用于记录具体的负载行为是什么；负载类别中的次序用于标记query_band负载行为识别的优先级，一个session内设置多个query_band键值对时，优先使用次序较小的query_band键值对识别负载行为。每一个query_band键值对都可以对应0个或多个负载行为，但是一种负载行为只能关联一个。query_band负载行为详细说明如下： 资源池：query_band支持关联资源池，作业执行时，若query_band指定了资源池，则使用query_band关联的资源池，否则使用用户关联的资源池。 query_band关联资源池时，资源池不存在报错退出，关联失败。 query_band关联资源池时，记录query_band与资源池依赖关系。 query_band关联资源池删除时，提示有query_band依赖，资源池删除失败。 队列内优先级：query_band支持关联作业优先级，支持高中低(High/Medium/Low)三个优先级，同时提供Rush作为特殊优先级（绿色通道），默认优先级为Medium。正常实践过程中，大部分作业使用Medium优先级，优先级较低作业使用Low优先级，特权作业使用High优先级，High作业不建议过多。Rush优先级作为特殊场景下应急使用，平时不建议使用。 队列内优先级实现队列内排队优先级： 静态负载管理场景下，CN并发不足时，触发CN全局队列排队，CN全局队列为优先级队列。 动态负载管理场景下，DN内存不足时，触发CCN全局队列排队，CCN全局队列为优先级队列。 资源池并发或内存不足时，触发资源池排队，资源池队列为优先级队列。 以上优先级队列均遵守以下调度规则： 优先级高作业优先调度。 优先级高作业全部调度完之后调度优先级低作业。 动态负载管理场景下，CN全局队列不支持query_band优先级。 次序：支持设置query_band识别次序，未设置识别次序的使用默认次序-1。除默认次序外，不存在次序相同的两个query_band。设置次序时对query_band次序进行校验，存在相同次序时，已存在次序递归+1直到不存在相同次序为止。 session内设置多个query_band键值对时，使用次序较小的query_band键值对作为负载识别的query_band。 次序最小为0，默认次序-1为最大次序。 次序都为默认次序时，使用设置靠前的query_band作为负载识别的query_band。 示例：set query_band='b=1;a=3;c=1'; b=1，其中b=1次序-1，a=3次序4，c=1次序1，则使用c=1作为负载识别的query_band，此设计可提供负载管理员对负载调度调整能力。

概述 GaussDB(DWS)实现基于query_band的负载识别和队列内优先级控制，一方面提供了更为灵活的负载识别手段，可根据作业类型、应用名称、脚本名称等识别负载队列，使用户根据业务场景可灵活配置query_band识别队列；另一方面实现了队列内作业下发优先级控制，后续将逐步实现队列内资源优先级控制。 管理员用户可根据业务场景及作业类别配置query_band所关联队列及估算内存限制等实现更为灵活的负载控制与资源管控。如果业务未配置query_band或用户未将query_band关联行为时，作业会默认使用用户关联队列和队列内优先级。

优化建议 选取partial cluster key列。 列存表支持创建partial cluster key的类型character varying(n), varchar(n), character(n), char(n), text, nvarchar2, timestamp with time zone, timestamp without time zone, date, time without time zone, time with time zone。 数据的distinct值数量较少，这样能产生较好的过滤效果。 出现在查询where条件中，优先选取能过滤大量数据的列。 partial cluster key中设置多个列时，是先按第一个列排序，当第一个列值相同时，使用第二列比较，后续列依次类推。推荐不要超出3个列。

优化后 where条件中l_shipdate和l_quantity的distinct值数量较少且可以做min max过滤，将字段l_shipdate、l_quantity设置为PCK修改表定义如下： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 CREATE TABLE lineitem ( L_ORDERKEY BIGINT NOT NULL , L_PARTKEY BIGINT NOT NULL , L_SUPPKEY BIGINT NOT NULL , L_LINENUMBER BIGINT NOT NULL , L_QUANTITY DECIMAL(15,2) NOT NULL , L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL , L_DISCOUNT DECIMAL(15,2) NOT NULL , L_TAX DECIMAL(15,2) NOT NULL , L_RETURNFLAG CHAR(1) NOT NULL , L_LINESTATUS CHAR(1) NOT NULL , L_SHIPDATE DATE NOT NULL , L_COMMITDATE DATE NOT NULL , L_RECEIPTDATE DATE NOT NULL , L_SHIPINSTRUCT CHAR(25) NOT NULL , L_SHIPMODE CHAR(10) NOT NULL , L_COMMENT VARCHAR(44) NOT NULL , partial cluster key(l_shipdate, l_quantity) ) with (orientation = column) distribute by hash(L_ORDERKEY); 重新导入数据后执行查询，查看执行时间： 图3 使用partial cluster key 图4 使用partial cluster key后CU加载情况 使用partial cluster key后，5-- CS tore Scan on public.lineitem的时间减少了1.2s，得益于有84个CU被过滤掉了。

优化后 测试发现由于两表结果集过大，导致nestloop耗时过长，超过一小时未返回结果，因此性能优化的关键是消除nestloop，让join走更高效的hashjoin。从语义等价的角度消除any-clause，SQL改写如下： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 select ls_pid_cusr1,COALESCE(max(round(ym/365)),0) from ( ( SELECT ls_pid_cusr1,(current_date-bthdate) as ym FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2 WHERE t1.ls_pid_cusr1 = t2.id and t1.ls_pid_cusr1 != t2.id15 ) union all ( SELECT ls_pid_cusr1,(current_date-bthdate) as ym FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2 WHERE t1.ls_pid_cusr1 = id15 ) ) GROUP BY ls_pid_cusr1; 注意：尽量使用union all代替union。union在合并两个集合时会执行去重操作，而union all则直接将两个结果集合并、不执行去重。执行去重会消耗大量的时间，因此，在一些实际应用场景中，如果通过业务逻辑已确认两个集合不存在重叠，可用union all替代union以便提升性能。 优化后的SQL查询由两个等值join的子查询构成，而每个子查询都可以走更适合此场景的hashjoin。优化后的执行计划如下 优化后，从超过1个小时未返回结果优化到7s返回结果。

优化前 in-clause/any-clause是常见的SQL语句约束条件，有时in或any后面的clause都是常量，类似于： 1 2 3 4 select count(1) from calc_empfyc_c1_result_tmp_t1 where ls_pid_cusr1 in (‘20120405’, ‘20130405’); 或者 1 2 3 4 select count(1) from calc_empfyc_c1_result_tmp_t1 where ls_pid_cusr1 in any(‘20120405’, ‘20130405’); 但是也有一些如下的特殊用法： 1 2 3 4 5 SELECT ls_pid_cusr1,COALESCE(max(round((current_date-bthdate)/365)),0) FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2 WHERE t1.ls_pid_cusr1 = any(values(id),(id15)) GROUP BY ls_pid_cusr1; 其中，id、id15为p10_md_tmp_t2中的两列，“t1.ls_pid_cusr1 = any(values(id),(id15))”等价于“t1.ls_pid_cusr1 = id or t1.ls_pid_cusr1 = id15”。 因此join-condition实质上是一个不等式，这种不等值的join操作必须走nestloop，对应执行计划如下：

总结 通常优化器总会选择最优的执行计划，但是众所周知代价估算，尤其是中间结果集的代价估算一般会有比较大的偏差，这种比较大的偏差就可能会导致agg的计算方式出现比较大的偏差，这时候就需要通过best_agg_plan进行agg计算模型的干预。 一般来说，当agg汇聚的收敛度很小时，即结果集的个数在agg之后并没有明显变少时（经验上以5倍为临界点），选择redistribute+hashagg执行方式，否则选择hashagg+redistribute+hashagg执行方式。

优化后 创建的列存表orders_pck。表定义如下： 使用ALTER TABLE将字段o_orderkey设置为PCK： 执行以下SQL语句，再次查询同样的点查询SQL语句的执行计划： 1 2 3 4 EXPLAIN PERFORMANCE SELECT * FROM orders_pck WHERE o_orderkey = '13095143' ORDER BY o_orderdate; 由下图可知执行时间为5毫秒，查看Datanode Information发现filter时间为0.5毫秒，CUNone比例为82。CUNone比例越高，PCK的性能收益越明显。

优化前 创建一个无局部聚簇（以下称为PCK）的列存表orders_no_pck，表定义如下： 执行以下SQL语句，查询某个点查询的执行计划： 1 2 3 4 EXPLAIN PERFORMANCE SELECT * FROM orders_no_pck WHERE o_orderkey = '13095143' ORDER BY o_orderdate; 由下图可知执行时间为48毫秒，查看Datanode Information发现filter时间为19毫秒，CUNone比例为0。

场景二：优化前 当cost_param的bit1（set cost_param=2）为1时，表示求多个过滤条件（Filter）的选择率时，选择最小的作为总的选择率，而非两者乘积，此方法在过滤条件的列之间关联性较强时估算更加准确。下面查询的例子是cost_param的bit1为1时的优化场景。 表结构如下所示： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 CREATE TABLE NATION ( N_NATIONKEYINT NOT NULL , N_NAMECHAR(25) NOT NULL , N_REGIONKEYINT NOT NULL , N_COMMENTVARCHAR(152) ) distribute by replication; CREATE TABLE SUPPLIER ( S_SUPPKEYBIGINT NOT NULL , S_NAMECHAR(25) NOT NULL , S_ADDRESSVARCHAR(40) NOT NULL , S_NATIONKEYINT NOT NULL , S_PHONECHAR(15) NOT NULL , S_ACCTBALDECIMAL(15,2) NOT NULL , S_COMMENTVARCHAR(101) NOT NULL ) distribute by hash(S_SUPPKEY); CREATE TABLE PARTSUPP ( PS_PARTKEYBIGINT NOT NULL , PS_SUPPKEYBIGINT NOT NULL , PS_AVAILQTYBIGINT NOT NULL , PS_SUPPLYCOSTDECIMAL(15,2)NOT NULL , PS_COMMENTVARCHAR(199) NOT NULL )distribute by hash(PS_PARTKEY); 查询语句如下所示： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 set cost_param=2; explain verbose select nation, sum(amount) as sum_profit from ( select n_name as nation, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from supplier, lineitem, partsupp, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and s_nationkey = n_nationkey ) as profit group by nation order by nation; 当cost_param的bit1为0时，执行计划如下图所示：

场景二：优化后 在以上查询中，supplier、lineitem、partsupp三表做hashjoin的条件为(lineitem.l_suppkey = supplier.s_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey)，此hashjoin条件中存在两个过滤条件，这前一个过滤条件中的lineitem.l_suppkey和后一个过滤条件中的lineitem.l_partkey同为lineitem表的两列，这两列存在强相关的关联关系。在这种情况，估算hashjoin条件的选择率时，如果使用cost_param的bit1为0时，实际是将AND的两个过滤条件分别计算的2个选择率的值相乘来得到hashjoin条件的选择率，导致行数估算不准确，查询性能较差。所以需要将cost_param的bit1为1时，选择最小的选择率作为总的选择率估算行数比较准确，查询性能较好，优化后的计划如下图所示：

优化前 将a作为t1和t2的分布列，表定义如下： 1 2 CREATE TABLE t1 (a int, b int) DISTRIBUTE BY HASH (a); CREATE TABLE t2 (a int, b int) DISTRIBUTE BY HASH (a); 执行如下查询： 1 SELECT * FROM t1, t2 WHERE t1.a = t2.b; 则执行计划存在“Streaming(type: REDISTRIBUTE)”，即DN根据选定的列把数据重分布到所有的DN，这将导致DN之间存在较大通信数据量，如图1所示。 图1 选择合适的分布列案例（一）

优化后 将查询中的关联条件作为分布键，执行下列语句修改b作为t2的分布列： 1 ALTER TABLE t2 DISTRIBUTE BY HASH (b); 将表t2的分布列改为b列之后，执行计划将不再包含“Streaming(type: REDISTRIBUTE)”，减少了DN之间存在的通信数据量的同时，执行时间也从8.7毫秒降低至2.7毫秒，从而提升查询性能，如图2所示。 图2 选择合适的分布列案例（二）

操作步骤 假定在导入表“areaS”上的“area_id”字段上存在普通索引“areaS_idx”。重建索引有以下两种方式： 先删除索引（DROP INDEX），再创建索引（CREATE INDEX） 删除索引。 DROP INDEX areaS_idx; 创建索引。 CREATE INDEX areaS_idx ON areaS (area_id); 使用REINDEX重建索引。 使用REINDEX TABLE语句重建索引。 REINDEX TABLE areaS; 使用REINDEX INTERNAL TABLE重建desc表（包括）的索引。 REINDEX INTERNAL TABLE areaS;

重建索引 重建索引有以下两种方式： 先删除索引（DROP INDEX），再创建索引（CREATE INDEX）。 在删除索引过程中，会在父表上增加一个短暂的排他锁，阻止相关读写操作。在创建索引过程中，会锁住写操作但是不会锁住读操作，此时读操作只能使用顺序扫描。 使用REINDEX语句重建索引。 使用REINDEX TABLE语句重建索引，会在重建过程中增加排他锁，阻止相关读写操作。 使用REINDEX INTERNAL TABLE语句重建desc表（包括）的索引，会在重建过程中增加排他锁，阻止相关读写操作。

背景信息 数据库经过多次删除操作后，索引页面上的索引键将被删除，造成索引膨胀。例行重建索引，可有效的提高查询效率。 数据库支持的索引类型包含B-tree索引、GIN索引和PSORT索引。 对于B-tree索引，例行重建索引可有效的提高查询效率。 如果数据发生大量删除后，索引页面上的索引键将被删除，导致索引页面数量的减少，造成索引膨胀。重建索引可回收浪费的空间。 新建的索引中逻辑结构相邻的页面，通常在物理结构中也是相邻的，所以一个新建的索引比更新了多次的索引访问速度要快。 对于非B-tree索引，不建议例行重建。

操作步骤 使用VACUUM或VACUUM FULL命令，进行磁盘空间回收。 VACUUM： 对表执行VACUUM操作。 VACUUM customer; 可以与数据库操作命令并行运行。（执行期间，可正常使用的语句：SELECT、INSERT、UPDATE和DELETE。不可正常使用的语句：ALTER TABLE）。 对表分区执行VACUUM操作。 VACUUM customer_par PARTITION ( P1 ); VACUUM FULL： VACUUM FULL customer; 需要向正在执行的表增加排他锁，且需要停止其他所有数据库操作。 在进行磁盘空间回收时，用户可以使用如下命令查询集群中最早事务对应session，再根据需要结束最早执行的长事务，从而更加高效的利用磁盘空间。 使用命令从GTM上查询oldestxmin SELECT * FROM pgxc_gtm_snapshot_status(); 从CN上查询对应的session的pid，此处xmin为上一步的oldestxmin。 SELECT * FROM pgxc_running_xacts() where xmin=1400202010; 使用ANALYZE语句更新统计信息。 ANALYZE customer; 使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息。 ANALYZE VERBOSE customer; 也可以同时执行VACUUM ANALYZE命令进行查询优化。 VACUUM ANALYZE customer; VACUUM和ANALYZE会导致I/O流量的大幅增加，这可能会影响其他活动会话的性能。因此，建议通过“vacuum_cost_delay”参数设置。

相关概念 使用VACUUM、VACUUM FULL和ANALYZE命令定期对每个表进行维护，主要有以下原因： VACUUM FULL可回收已更新或已删除的数据所占据的磁盘空间，同时将小数据文件合并。 VACUUM对每个表维护了一个可视化映射来跟踪包含对别的活动事务可见的数组的页。一个普通的索引扫描首先通过可视化映射来获取对应的数组，来检查是否对当前事务可见。若无法获取，再通过堆数组抓取的方式来检查。因此更新表的可视化映射，可加速唯一索引扫描。 VACUUM可避免执行的事务数超过数据库阈值时，事务ID重叠造成的原有数据丢失。 ANALYZE可收集与数据库中表内容相关的统计信息。统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，生成最有效的执行计划。

Plan Hint实际调优案例 本节以TPC-DS标准测试的Q24的部分语句为例，在1000X，24DN环境上，说明使用plan hint进行实际调优的过程。示例如下： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 select avg(netpaid) from (select c_last_name ,c_first_name ,s_store_name ,ca_state ,s_state ,i_color ,i_current_price ,i_manager_id ,i_units ,i_size ,sum(ss_sales_price) netpaid from store_sales ,store_returns ,store ,item ,customer ,customer_address where ss_ticket_number = sr_ticket_number and ss_item_sk = sr_item_sk and ss_customer_sk = c_customer_sk and ss_item_sk = i_item_sk and ss_store_sk = s_store_sk and c_birth_country = upper(ca_country) and s_zip = ca_zip and s_market_id=7 group by c_last_name ,c_first_name ,s_store_name ,ca_state ,s_state ,i_color ,i_current_price ,i_manager_id ,i_units ,i_size); 该语句的初始计划如下，运行时间110s： 图1 语句初始计划 该计划中，第10层算子使用broadcast性能较差，由于第11层算子估算行数为2140，比实际行数严重低估。错误行数估算主要来源于第13层算子的行数低估，根因是第13层hashjoin中，使用store_sales的(ss_ticket_number, ss_item_sk)列和store_returns的(sr_ticket_number, sr_item_sk)列进行关联，由于缺少多列相关性的估算导致行数严重低估。 使用如下的rows hint进行调优后，计划如下，运行时间318s： 1 2 select avg(netpaid) from (select /*+rows(store_sales store_returns * 11270)*/ c_last_name ... 图2 使用rows hint进行调优 时间反而劣化了，原因是第8层hashjoin过慢引起第9层redistribute时间过慢导致，其中第9层redistribute并没有数据倾斜，hashjoin慢的原因是由于第18层redistribute后数据倾斜导致。 经过实际数据查证，customer_address的两个join列的不同值数目较少，使用其进行join容易出现数据倾斜，故把customer_address放到最后进行join。使用如下的hint进行调优后，计划如下，运行时间116s： 1 2 3 4 select avg(netpaid) from (select /*+rows(store_sales store_returns *11270) leading((store_sales store_returns store item customer) customer_address)*/ c_last_name ... 图3 hint调优 发现时间基本花在了第6层redistribute算子上，需要进一步优化。 由于最后一层redistribute包含倾斜，所以时间较长。为了避免倾斜，需要将item表放在最后join，由于item表的join并不能使行数减少。修改hint如下并执行，计划如下，运行时间120s： 1 2 3 4 select avg(netpaid) from (select /*+rows(store_sales store_returns *11270) leading((customer_address (store_sales store_returns store customer) item)) c_last_name ... 图4 修改hint并执行语句 该计划中的redistribute问题并没有解决，因为第22层item表做了broadcast，导致与customer_address表join后的倾斜并没有被消除掉。 增加如下禁止item表做broadcast的hint，使与customer_address join的表做redistribute（也可以进行join表redistribute的hint），计划如下，运行时间105s： 1 2 3 4 5 select avg(netpaid) from (select /*+rows(store_sales store_returns *11270) leading((customer_address (store_sales store_returns store customer) item)) no broadcast(item)*/ c_last_name ... 图5 执行计划 发现最后一层使用单层Agg，但行数缩减较多。使用相同的hint，同时结合参数best_agg_plan=3进行双层Agg调优，最终计划如下图所示，运行时间94s，完成调优。 图6 最终调优计划 如果有统计信息变更引起的查询劣化，可以考虑用plan hint来调整到之前的查询计划。这里以TPCH-Q17为例，在收集default_statistics_target设置为–2的统计信息之后，计划相比于默认统计信息发生劣化。 默认统计信息（default_statistics_target设置为100）的计划如下： 图7 默认统计信息 统计信息变更（default_statistics_target设置为–2）的计划如下： 图8 统计信息变更 经过对比，劣化的原因主要为lineitem和part表join时stream类型由BroadCast变更为Redistribute导致。可以对语句进行stream方式的hint来调整到之前的计划，例如： 图9 调整语句 父主题： 使用Plan Hint进行调优

hint的错误类型 语法错误 语法规则树归约失败，会报错，指出出错的位置。 例如：hint关键字错误，leading hint或join hint指定2个表以下，其它hint未指定表等。一旦发现语法错误，则立即终止hint的解析，所以此时只有错误前面的解析完的hint有效。 例如： 1 leading((t1 t2)) nestloop(t1) rows(t1 t2 #10) nestloop(t1)存在语法错误，则终止解析，可用hint只有之前解析的leading((t1 t2))。 语义错误 表不存在，存在多个，或在leading或join中出现多次，均会报语义错误。 scanhint中的index不存在，会报语义错误。 另外，如果子查询提升后，同一层出现多个名称相同的表，且其中某个表需要被hint，hint会存在歧义，无法使用，需要为相同表增加别名规避。 hint重复或冲突 如果存在hint重复或冲突，只有第一个hint生效，其它hint均会失效，会给出提示。 hint重复是指，hint的方法及表名均相同。例如：nestloop(t1 t2) nestloop(t1 t2)。 hint冲突是指，table list一样的hint，存在不一样的hint，hint的冲突仅对于每一类hint方法检测冲突。 例如：nestloop (t1 t2) hashjoin (t1 t2)，则后面与前面冲突，此时hashjoin的hint失效。注意：nestloop(t1 t2)和no mergejoin(t1 t2)不冲突。 leading hint中的多个表会进行拆解。例如：leading ((t1 t2 t3))会拆解成：leading((t1 t2)) leading(((t1 t2) t3))，此时如果存在leading((t2 t1))，则两者冲突，后面的会被丢弃。（例外：指定内外表的hint若与不指定内外表的hint重复，则始终丢弃不指定内外表的hint。） 子链接提升后hint失效 子链接提升后的hint失效，会给出提示。通常出现在子链接中存在多个表连接的场景。提升后，子链接中的多个表不再作为一个整体出现在join中。 列类型不支持重分布 对于skew hint来说，目的是为了进行重分布时的调优，所以当hint列的类型不支持重分布时，hint将无效。 hint未被使用 非等值join使用hashjoin hint或mergejoin hint 不包含索引的表使用indexscan hint或indexonlyscan hint 通常只有在索引列上使用过滤条件才会生成相应的索引路径，全表扫描将不会使用索引，因此使用indexscan hint或indexonlyscan hint将不会使用 indexonlyscan只有输出列仅包含索引列才会使用，否则指定时hint不会被使用 多个表存在等值连接时，仅尝试有等值连接条件的表的连接，此时没有关联条件的表之间的路径将不会生成，所以指定相应的leading，join，rows hint将不使用，例如：t1 t2 t3表join，t1和t2，t2和t3有等值连接条件，则t1和t3不会优先连接，leading(t1 t3)不会被使用。 生成stream计划时，如果表的分布列与join列相同，则不会生成redistribute的计划；如果不同，且另一表分布列与join列相同，只能生成redistribute的计划，不会生成broadcast的计划，指定相应的hint则不会被使用。 对于AGG重分布列的hint，hint未被使用的可能原因如下： 指定的分布键包含不支持重分布的数据类型。 执行计划中不需要重分布。 执行的分布键的序号有误。 对于使用grouping sets子句和cube子句的AP函数，window agg中的分布键，不支持hint。 指定Agg重分布列Hint，仅8.1.3.100及以上集群版本支持。 如果子链接未被提升，则blockname hint不会被使用。 对于skew hint，hint未被使用的可能原因如下： 计划中不需要进行重分布。 hint指定的列为包含分布键。 hint指定倾斜信息有误或不完整，如对于join优化未指定值。 倾斜优化的GUC参数处于关闭状态。 对于guc hint，hint未被使用的可能原因如下： 配置参数不存在。 配置参数不支持guc hint。 配置参数的值无效。 语句级别的guc hint没有被写在顶层查询中。 子查询级别的guc hint设置的配置参数不支持在子查询级别设置。 guc hint所在的子查询被提升。

参数说明 global表示hint设置的配置参数在语句级别生效，不加global表示hint设置的配置参数在子查询级别生效，即仅在hint所在的子查询中生效，在该语句的其它子查询中不生效。 guc_name表示hint指定的配置参数的名称。 guc_value表示hint指定的配置参数的值。 如果hint设置的配置参数在语句级别生效，则该hint必须写在顶层查询中，而不能写在子查询中。对于UNION、INTERSECT、EXCEPT和MINUS语句，可以将在语句级别的guc hint写在参与集合运算的任意一个SELECT子句上，该guc hint设置的配置参数会在参与集合运算的每个SELECT子句上生效。 子查询提升时，该子查询上的所有guc hint会被丢弃。 如果一个配置参数既被语句级别的guc hint设置，又被子查询级别的guc hint设置，则子查询级别的guc hint在对应的子查询中生效，语句级别的guc hint在语句的其它子查询中生效。

示例 对示例中原语句使用如下hint： 1 2 explain select /*+ no redistribute(store_sales store_returns item store) leading(((store_sales store_returns item store) customer)) */ i_product_name product_name ... 原计划中，(store_sales store_returns item store)和customer做join时，前者做了重分布，此hint表示禁止前者混合表做重分布，但仍然保持join顺序，则生成计划如下所示：

建议 通常优化器会根据统计信息选择一组不倾斜的分布键进行数据重分布。当默认选择的分布键有倾斜时，可以手动指定重分布的列，避免数据倾斜。 在选择分布键的时候，通常要根据数据分布特征选取一组distinct值比较高的列作为分布列，这样可以保证重分布后，数据均匀的分布到各个DN。 在编写好hint后，可以通过explain verbose+SQL打印执行计划，查看指定的分布键是否有效，如果指定的分布键无效会有warning提示。

建议 推荐使用两个表*的hint。对于两个表的采用*操作符的hint，只要两个表出现在join的两端，都会触发hint。例如：设置hint为rows(t1 t2 * 3)，对于(t1 t3 t4)和(t2 t5 t6)join时，由于t1和t2出现在join的两端，所以其join的结果集也会应用该hint规则乘以3。 rows hint支持在单表、多表、function table及subquery scan table的结果集上指定hint。

存储层数据倾斜 GaussDB(DWS)数据库中，数据分布存储在各个DN上，通过分布式执行提高查询的效率。但是，如果数据分布存在倾斜，则会导致分布式执行某些DN成为瓶颈，影响查询性能。这种情况通常是由于分布列选择不合理，可以通过调整分布列的方式解决。 例如下例： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 explain performance select count(*) from inventory; 5 --CStore Scan on lmz.inventory dn_6001_6002 (actual time=0.444..83.127 rows=42000000 loops=1) dn_6003_6004 (actual time=0.512..63.554 rows=27000000 loops=1) dn_6005_6006 (actual time=0.722..99.033 rows=45000000 loops=1) dn_6007_6008 (actual time=0.529..100.379 rows=51000000 loops=1) dn_6009_6010 (actual time=0.382..71.341 rows=36000000 loops=1) dn_6011_6012 (actual time=0.547..100.274 rows=51000000 loops=1) dn_6013_6014 (actual time=0.596..118.289 rows=60000000 loops=1) dn_6015_6016 (actual time=1.057..132.346 rows=63000000 loops=1) dn_6017_6018 (actual time=0.940..110.310 rows=54000000 loops=1) dn_6019_6020 (actual time=0.231..41.198 rows=21000000 loops=1) dn_6021_6022 (actual time=0.927..114.538 rows=54000000 loops=1) dn_6023_6024 (actual time=0.637..118.385 rows=60000000 loops=1) dn_6025_6026 (actual time=0.288..32.240 rows=15000000 loops=1) dn_6027_6028 (actual time=0.566..118.096 rows=60000000 loops=1) dn_6029_6030 (actual time=0.423..82.913 rows=42000000 loops=1) dn_6031_6032 (actual time=0.395..78.103 rows=39000000 loops=1) dn_6033_6034 (actual time=0.376..51.052 rows=24000000 loops=1) dn_6035_6036 (actual time=0.569..79.463 rows=39000000 loops=1) 在performance信息中，可以看到inventory表各DN的scan行数，发现各DN的行数差距较大，最大的为63000000，最小的只有15000000，差了4倍。这个差距对于数据扫描的性能影响还可以接受，但如果上层有join算子，则影响较大。 通常，数据表在各DN上是hash分布的，因此分布列的选择很重要。通过table_skewness()函数来查看上述inventory表在各DN的数据分布倾斜，查询结果如下： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 select table_skewness('inventory'); table_skewness ------------------------------------------ ("dn_6015_6016 ",63000000,8.046%) ("dn_6013_6014 ",60000000,7.663%) ("dn_6023_6024 ",60000000,7.663%) ("dn_6027_6028 ",60000000,7.663%) ("dn_6017_6018 ",54000000,6.897%) ("dn_6021_6022 ",54000000,6.897%) ("dn_6007_6008 ",51000000,6.513%) ("dn_6011_6012 ",51000000,6.513%) ("dn_6005_6006 ",45000000,5.747%) ("dn_6001_6002 ",42000000,5.364%) ("dn_6029_6030 ",42000000,5.364%) ("dn_6031_6032 ",39000000,4.981%) ("dn_6035_6036 ",39000000,4.981%) ("dn_6009_6010 ",36000000,4.598%) ("dn_6003_6004 ",27000000,3.448%) ("dn_6033_6034 ",24000000,3.065%) ("dn_6019_6020 ",21000000,2.682%) ("dn_6025_6026 ",15000000,1.916%) (18 rows) 通过查询建表定义，可以发现，目前该表是以inv_date_sk作为分布列的，导致存在倾斜。通过查看各列的数据分布情况，改为inv_item_sk作为分布列，则倾斜情况分布如下： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 select table_skewness('inventory'); table_skewness ------------------------------------------ ("dn_6001_6002 ",43934200,5.611%) ("dn_6007_6008 ",43829420,5.598%) ("dn_6003_6004 ",43781960,5.592%) ("dn_6031_6032 ",43773880,5.591%) ("dn_6033_6034 ",43763280,5.589%) ("dn_6011_6012 ",43683600,5.579%) ("dn_6013_6014 ",43551660,5.562%) ("dn_6027_6028 ",43546340,5.561%) ("dn_6009_6010 ",43508700,5.557%) ("dn_6023_6024 ",43484540,5.554%) ("dn_6019_6020 ",43466800,5.551%) ("dn_6021_6022 ",43458500,5.550%) ("dn_6017_6018 ",43448040,5.549%) ("dn_6015_6016 ",43247700,5.523%) ("dn_6005_6006 ",43200240,5.517%) ("dn_6029_6030 ",43181360,5.515%) ("dn_6025_6026 ",43179700,5.515%) ("dn_6035_6036 ",42960080,5.487%) (18 rows) 数据分布倾斜的问题得到解决。 除了table_skewness()函数外，当前版本还提供了table_distribution函数和PGXC_GET_TABLE_SKEWNESS视图，可以更加高效的查询各表的数据倾斜情况。

算子级调优介绍 一个查询语句要经过多个算子步骤才会输出最终的结果。由于个别算子耗时过长导致整体查询性能下降的情况比较常见。这些算子是整个查询的瓶颈算子。通用的优化手段是EXPLAIN ANALYZE/PERFORMANCE命令查看执行过程的瓶颈算子，然后进行针对性优化。 如下面的执行过程信息中，Hashagg算子的执行时间占总时间的：(51016-13535)/ 56476 ≈66%，此处Hashagg算子就是这个查询的瓶颈算子，在进行性能优化时应当优先考虑此算子的优化。