云数据库 GaussDB-案例：设置cost_param对查询性能优化:现象描述2

时间：2023-11-01 16:19:21

云数据库 GaussDB

现象描述2

当cost_param的bit1(set cost_param=2)为1时，表示求多个过滤条件（Filter）的选择率时，选择最小的作为总的选择率，而非两者乘积，此方法在过滤条件的列之间关联性较强时估算更加准确。下面查询的例子是cost_param的bit1为1时的优化场景。

表结构如下所示：

  
       CREATE TABLE NATION(  N_NATIONKEY INT NOT NULL, N_NAME      CHAR(25) NOT NULL, N_REGIONKEY INT NOT NULL, N_COMMENT   VARCHAR(152)) distribute by replication;CREATE TABLE SUPPLIER(  S_SUPPKEY   BIGINT NOT NULL, S_NAME      CHAR(25) NOT NULL, S_ADDRESS   VARCHAR(40) NOT NULL, S_NATIONKEY INT NOT NULL, S_PHONE     CHAR(15) NOT NULL, S_ACCTBAL   DECIMAL(15,2) NOT NULL, S_COMMENT   VARCHAR(101) NOT NULL) distribute by hash(S_SUPPKEY);CREATE TABLE PARTSUPP(  PS_PARTKEY    BIGINT NOT NULL, PS_SUPPKEY    BIGINT NOT NULL, PS_AVAILQTY   BIGINT NOT NULL, PS_SUPPLYCOST DECIMAL(15,2)NOT NULL, PS_COMMENT    VARCHAR(199) NOT NULL)distribute by hash(PS_PARTKEY);

查询语句如下所示：

  
       set cost_param=2;explain verbose selectnation,sum(amount) as sum_profit from(selectn_name as nation,l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amountfromsupplier,lineitem,partsupp,nationwheres_suppkey = l_suppkeyand ps_suppkey = l_suppkeyand ps_partkey = l_partkeyand s_nationkey = n_nationkey) as profit group by nation order by nation;