GaussDB(DWS) 初级开发建议

1. 行存表or列存表 （分区表）

实时小批量入库的表用行存表，每天批入库多做聚合分析等复杂查询的表建议用列存表。

自定义函数中调用的维度表，数据量较小，如果没有在其他复杂语句中跟别的大表做交互，根据以下条件判断是否能建为复制表。

• 对于行存复制表，仅支持两种场景下的delete操作：1）有主键约束的场景；2）执行计划能下推的场景。

• 对于列存复制表只支持执行计划能下推的场景。

有日期字段可作为分区、超过100W的表考虑建分区表。

create table test

(id int,

 da timestamp)

WITH (orientation=column)  -- 列存分区表，行存为row

DISTRIBUTE BY HASH (id)

PARTITION BY RANGE (da) (

    PARTITION p1 START('2020-09-01') END('2020-09-30') EVERY('1 day'),

    PARTITION p_max end(MAXVALUE)

                        );

2. Analyze

  查看表是否做了analyze -- analyze用于收集统计信息，相比于不做analyze生成的执行

  计划更优。

   select * from pg_stats where tablename='table_name'; --不加scehma

  辅助手段：执行完语句或者select count(1) from table_name;

  如果未做analyze, datastudio的信息栏会提示statistics not collected。

3. 表倾斜检查

查看表是否有倾斜 (替换tablename为查询的表名)

a)       select a.count,b.node_name from (select count(*) as count,xc_node_id 

   from tablename group by xc_node_id) a, pgxc_node b

   where a.xc_node_id=b.node_id order by a.count desc;

b)       select table_skewness('table_name');

   b)的返回结果更为直观，但是大表用b) 方式查询较慢，当查询亿级表或者执行时间长于1min,建议使用a) 检查。

  不同DN之间的数据量差距超过5%认为倾斜，10%必须调整分布列

4. 索引

检查表索引是否为有效索引（不要只针对同一个字段创建不同索引）。

例如

Create index_1 on table (id);

Create index_2 on table (id);

建好索引后执行analyze: analyze table_name;

  语句中有点查等场景建索引。建完索引通过explain +语句查看执行计划是否走索引。

   不走索引的计划为：seq scan(行存), cstore scan(列存)。

   走索引的计划为： index scan

5. 子查询优化

检查自己的语句是否有子查询

   a) 尤其是类似下列语句的子查询场景。将该场景改为join或者

   WITH CTE结构。

   select (select c1 from t2 where t2.id=t1.id) as c1 from t1

   可改为 -- 子查询较为简单可直接改为外部关联的时候可用下列改法。

   select t2.c1 as c1 from t1 join t2 on t1.id=t2.id

   或者   --子查询内较为复杂的时候可优先考虑CTE。

   with c as (

     select c1,id from t2

         )

   select c.c1 from t1 join c on t1.id=c.id

   b) select from t1 join (子查询）的场景，如果语句性能较差，可考虑将子查询转为with

   CTE结构。

   实际场景中一个复杂语句通过修改子查询从12小时未执行出结果提升到2分钟内执行完成。

6. 使用update的语句避免使用子查询。  

update t1

  set t1.col = (select col from t2 where t2.id=t1.id);

   修改为

  update t1

  set t1.col = t2.col

  from t2

  where t1.id=t2.id

实际场景尝试过该修改后性能从6-7s提升到毫秒级。

7. 自定义函数

存储过程直接调用： call func(); 

func() 采用默认的not shippable 不下推，func内部语句若可以下推会自动下推到DN执行。

函数使用： select func() from table; 

判断函数是否可以下推规则：

1. 对于固定输入的变量有固定输出的时候，可以将函数设置为immutable让其下推。

2. 若函数中对表做聚合分析等操作，会下推到DN去查找数据，可能导致结果返回不全，与CN上执行结果不一致。如果表不常更新且量级不大，建为复制表让每个DN上都存表的全部数据量，下推到DN查数据可以确保数据的准确性。

8. 分析业务场景

自己执行业务语句的时候运行几个小时未执行成功，不要一直跑到死。停掉语句分析下业务场景，关联条件是否合适。left join较多的情况，可以逐一分析主表每个left join的情况。 是否有多对多的情况发生，每个left join的时间是否合理，数据量是否合适。具体慢在哪个left join的地方都可以以此分析出来。

对于日期字段，根据场景尽可能简化操作。例如：

字段为date 类型，表中数据格式本身就为yyyy-mm-dd'， 在语句中没有必要再做to_date('2020-10-10','yyyy-mm-dd')的操作。加上后反而严重影响性能。某个实际优化的语句案例中： 优化前用了该函数1分钟跑不出结果，去掉不必要的函数后1秒内语句执行完毕。