GaussDB SQL调优案例-设置cost_param对查询性能优化

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) with (orientation = column, COMPRESSION = MIDDLE) distribute by hash(L_ORDERKEY);
CREATE TABLE ORDERS(O_ORDERKEY BIGINT NOT NULL, O_CUSTKEY BIGINT NOT NULL, O_ORDERSTATUS CHAR(1) NOT NULL, O_TOTALPRICE DECIMAL(15,2) NOT NULL, O_ORDERDATE DATE NOT NULL, O_ORDERPRIORITY CHAR(15) NOT NULL, O_CLERK CHAR(15) NOT NULL, O_SHIPPRIORITY BIGINT NOT NULL, O_COMMENT VARCHAR(79) NOT NULL)with (orientation = column, COMPRESSION = MIDDLE) distribute by hash(O_ORDERKEY);

explain verbose selectcount(*) as numwait fromlineitem l1,orders whereo_orderkey = l1.l_orderkeyand o_orderstatus = 'F'and l1.l_receiptdate > l1.l_commitdateand not exists (select*fromlineitem l3wherel3.l_orderkey = l1.l_orderkeyand l3.l_suppkey <> l1.l_suppkeyand l3.l_receiptdate > l3.l_commitdate)order bynumwait desc;

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;