关于oushudb-hawq:OushuDB-体验新执行器

本章节通过 TPCH lineitem 表来展现新执行器的应用。

建设 e_lineitem 内部表用来生成 TPCH lineitem 数据,

CREATE EXTERNAL WEB TABLE E_LINEITEM (L_ORDERKEY    INT8 ,L_PARTKEY     INTEGER ,L_SUPPKEY     INTEGER ,L_LINENUMBER  INTEGER ,L_QUANTITY    FLOAT ,L_EXTENDEDPRICE  FLOAT ,L_DISCOUNT    FLOAT ,L_TAX         FLOAT ,L_RETURNFLAG  VARCHAR(1) ,L_LINESTATUS  VARCHAR(1) ,L_SHIPDATE    TEXT ,L_COMMITDATE  TEXT ,L_RECEIPTDATE TEXT ,L_SHIPINSTRUCT CHAR(25) ,L_SHIPMODE     VARCHAR(10) ,L_COMMENT      VARCHAR(44) )EXECUTE 'bash -c"$GPHOME/bin/dbgen -b $GPHOME/bin/dists.dss -T L -s 1 -N 6 -n $((GP_SEGMENT_ID + 1))"'on 6 format'text'(delimiter'|');

创立 ORC 表

CREATE TABLE lineitem(L_ORDERKEY    INT8,L_PARTKEY     INTEGER,L_SUPPKEY     INTEGER,L_LINENUMBER  INTEGER,L_QUANTITY    FLOAT,L_EXTENDEDPRICE  FLOAT,L_DISCOUNT    FLOAT,L_TAX         FLOAT,L_RETURNFLAG  TEXT,L_LINESTATUS  TEXT,L_SHIPDATE    TEXT,L_COMMITDATE  TEXT,L_RECEIPTDATE TEXT,L_SHIPINSTRUCT TEXT,L_SHIPMODE     TEXT,L_COMMENT      TEXT)WITH (APPENDONLY = true, OIDS = FALSE, ORIENTATION = orc);

插入数据
INSERT INTO lineitem SELECT * FROM e_lineitem;
从上面的例子能够看到新执行器对于性能的大幅改良。

----- 获取表行数 ------postgres=# set new_executor = on;SETpostgres=# SELECT COUNT(*) FROM lineitem;count---------6001215(1 row)Time: 17.006 mspostgres=# set new_executor = off;SETpostgres=# SELECT COUNT(*) FROM lineitem;count---------6001215(1 row)Time: 213.248 ms-----TPCH 查问 1 ------postgres=# set new_executor = on;SETpostgres=#  SELECTl_returnflag,l_linestatus,sum(l_quantity)::bigint as sum_qty,sum(l_extendedprice)::bigint as sum_base_price,sum(l_extendedprice * (1 - l_discount))::bigint as sum_disc_price,sum(l_extendedprice * (1 - l_discount) * (1 + l_tax))::bigint as sum_charge,avg(l_quantity)::bigint as avg_qty,avg(l_extendedprice)::bigint as avg_price,avg(l_discount)::bigint as avg_disc,count(*) as count_orderFROMlineitemWHEREl_shipdate <= '1998-08-20'GROUP BYl_returnflag,l_linestatus;l_returnflag | l_linestatus | sum_qty  | sum_base_price | sum_disc_price |  sum_charge  | avg_qty | avg_price | avg_disc | count_order--------------+--------------+----------+----------------+----------------+--------------+---------+-----------+----------+-------------R            | F            | 37719753 |    56568041381 |    53741292685 |  55889619120 |      26 |     38251 |        0 |     1478870N            | F            |   991417 |     1487504710 |     1413082168 |   1469649223 |      26 |     38284 |        0 |       38854A            | F            | 37734107 |    56586554401 |    53758257135 |  55909065223 |      26 |     38273 |        0 |     1478493N            | O            | 73808911 |   110700990251 |   105167436999 | 109377979031 |      26 |     38248 |        0 |     2894278(4 rows)Time: 234.376 mspostgres=# set new_executor = off;SETpostgres=#  SELECTl_returnflag,l_linestatus,sum(l_quantity)::bigint as sum_qty,sum(l_extendedprice)::bigint as sum_base_price,sum(l_extendedprice * (1 - l_discount))::bigint as sum_disc_price,sum(l_extendedprice * (1 - l_discount) * (1 + l_tax))::bigint as sum_charge,avg(l_quantity)::bigint as avg_qty,avg(l_extendedprice)::bigint as avg_price,avg(l_discount)::bigint as avg_disc,count(*) as count_orderFROMlineitemWHEREl_shipdate <= '1998-08-20'GROUP BYl_returnflag,l_linestatus;l_returnflag | l_linestatus | sum_qty  | sum_base_price | sum_disc_price |  sum_charge  | avg_qty | avg_price | avg_disc | count_order--------------+--------------+----------+----------------+----------------+--------------+---------+-----------+----------+-------------R            | F            | 37719753 |    56568041381 |    53741292685 |  55889619120 |      26 |     38251 |        0 |     1478870N            | F            |   991417 |     1487504710 |     1413082168 |   1469649223 |      26 |     38284 |        0 |       38854A            | F            | 37734107 |    56586554401 |    53758257135 |  55909065223 |      26 |     38273 |        0 |     1478493N            | O            | 73808911 |   110700990251 |   105167436999 | 109377979031 |      26 |     38248 |        0 |     2894278(4 rows)Time: 2341.147 ms