-- 0. aiEngine
-- 0.1 illegal ip
select check_engine_status(' ',' ');
INFO: ip address is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
select check_engine_status('111','11');
INFO: ip address 111 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
select check_engine_status('11.11.11','11');
INFO: ip address 11.11.11 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
select check_engine_status('.11.11.11','11');
INFO: ip address .11.11.11 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
select check_engine_status('.11.11.11','11');
INFO: ip address .11.11.11 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
select check_engine_status('1111.11.11.11','11');
INFO: ip address 1111.11.11.11 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
select check_engine_status('111.1111.11.11','11');
INFO: ip address 111.1111.11.11 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
select check_engine_status('111.11.1111.11','11');
INFO: ip address 111.11.1111.11 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
select check_engine_status('111.11.11.1111','11');
INFO: ip address 111.11.11.1111 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
-- 0.2 illegal port
select check_engine_status('11.11.11.11','-1');
INFO: port number -1 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
select check_engine_status('11.11.11.11','65537');
INFO: port number 65537 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
select check_engine_status('11.11.11.11','100000');
INFO: port number 100000 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
select check_engine_status('11.11.11.11','0');
INFO: port number 0 is not valid
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
-- 0.3 unreachable engine
select check_engine_status('11.11.11.11','11');
INFO: AI engine @11.11.11.11:11 is not running
CONTEXT: referenced column: check_engine_status
check_engine_status
---------------------
Failed
(1 row)
\c postgres
-- 1.TPCH Q1
-- 1.1 clean temporary tables
delete from gs_wlm_plan_encoding_table;
delete from gs_wlm_plan_operator_info;
select create_wlm_operator_info(0);
create_wlm_operator_info
--------------------------
0
(1 row)
\c regression
-- 1.2 run query
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)/1000) as sum_charge, --add /1000
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '3 day'
group by
l_returnflag,
l_linestatus
order by
l_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
--------------+--------------+-----------+----------------+-----------------+------------------------------+---------------------+--------------------+-----------------------+-------------
A | F | 380456.00 | 532348211.65 | 505822441.4861 | 526165.93400083900000000000 | 25.5751546114546921 | 35785.709306937349 | .05008133906964237698 | 14876
N | F | 8971.00 | 12384801.37 | 11798257.2080 | 12282.48505693300000000000 | 25.7787356321839080 | 35588.509683908046 | .04775862068965517241 | 348
N | O | 765181.00 | 1072769060.24 | 1019433269.1967 | 1060338.99976188300000000000 | 25.4661363863280860 | 35703.033921522947 | .04992811262355642826 | 30047
R | F | 381449.00 | 534594445.35 | 507996454.4067 | 528524.21935890300000000000 | 25.5971681653469333 | 35874.006532680177 | .04982753992752650651 | 14902
(4 rows)
\c postgres
-- 1.3 collect temporary information to _info tables
select create_wlm_operator_info(1);
create_wlm_operator_info
--------------------------
0
(1 row)
-- 1.4 show plan_operator_info
select * from gs_wlm_plan_operator_info order by queryid, plan_node_id;
--? datname | queryid | plan_node_id | startup_time | total_time | actual_rows | max_peak_memory | query_dop | parent_node_id | left_child_id | right_child_id | operation | orientation | strategy | options | condition | projection
------------+-----------------+--------------+--------------+------------+-------------+-----------------+-----------+----------------+---------------+----------------+-----------+-------------+----------+---------+----------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--? regression | .* | 1 | 0 | .* | 4 | 1 | 1 | 0 | 2 | 0 | SORT | ROW | | | | public.lineitem.l_returnflag, public.lineitem.l_linestatus, \(sum\(lineitem.l_quantity\)\), \(sum\(lineitem.l_extendedprice\)\), \(sum\(\(lineitem.l_extendedprice \* \(1::numeric - lineitem.l_discount\)\)\)\), \(sum\(\(\(\(lineitem.l_extendedprice \* \(1::numeric - lineitem.l_discount\)\) \* \(1::numeric + lineitem.l_tax\)\) / 1000::numeric\)\)\), \(avg\(lineitem.l_quantity\)\), \(avg\(lineitem.l_extendedprice\)\), \(avg\(lineitem.l_discount\)\), \(count\(*\)\)
--? regression | .* | 2 | 0 | .* | 4 | 1 | 1 | 1 | 3 | 0 | AGG | ROW | HASHED | | && | public.lineitem.l_returnflag, public.lineitem.l_linestatus, sum\(lineitem.l_quantity\), sum\(lineitem.l_extendedprice\), sum\(\(lineitem.l_extendedprice \* \(1::numeric - lineitem.l_discount\)\)\), sum\(\(\(\(lineitem.l_extendedprice \* \(1::numeric - lineitem.l_discount\)\) \* \(1::numeric + lineitem.l_tax\)\) / 1000::numeric\)\), avg\(lineitem.l_quantity\), avg\(lineitem.l_extendedprice\), avg\(lineitem.l_discount\), count\(*\)
--? regression | .* | 3 | 0 | .* | 60173 | 1 | 1 | 2 | 0 | 0 | SCAN | ROW | SEQ | | && \(public.lineitem.l_shipdate <= 'Sat Nov 28 00:00:00 1998'::timestamp without time zone\) | public.lineitem.l_returnflag, public.lineitem.l_linestatus, public.lineitem.l_quantity, public.lineitem.l_extendedprice, public.lineitem.l_discount, public.lineitem.l_tax
(3 rows)
-- 1.5 run plan encoding
select gather_encoding_info('regression');
gather_encoding_info
----------------------
0
(1 row)
-- 1.6 show plan encoding
select * from gs_wlm_plan_encoding_table;
queryid | plan_node_id | parent_node_id | startup_time | total_time | rows | peak_memory | encode
-----------------+--------------+----------------+--------------+------------+-------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------
--? .* | 1 | 0 | 0 | .* | 4 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 0 1 0 1 1 1 1 0 0 1 1 0
--? .* | 2 | 1 | 0 | .* | 4 | 1 | 1.000 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1 1 1 1 1 0 0 0 1 1 1 1 0 1 0
--? .* | 3 | 2 | 0 | .* | 60173 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1 1 0 0 0 1 1 1 1 1 1 0 0
(3 rows)
-- 2.TPCH Q2
-- 2.1 clean temporary tables
delete from gs_wlm_plan_encoding_table;
delete from gs_wlm_plan_operator_info;
select create_wlm_operator_info(0);
create_wlm_operator_info
--------------------------
0
(1 row)
\c regression
-- 2.2 run query
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 15
and p_type like 'SMALL%'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE '
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE '
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100
;
s_acctbal | s_name | n_name | p_partkey | p_mfgr | s_address | s_phone | s_comment
-----------+---------------------------+---------------------------+-----------+---------------------------+--------------------------------------+-----------------+----------------------------------------------------------------------------------
3556.47 | Supplier#000000032 | UNITED KINGDOM | 331 | Manufacturer#2 | yvoD3TtZSx1skQNCK8agk5bZlZLug | 33-484-637-7873 | usly even depths. quickly ironic theodolites s
2972.26 | Supplier#000000016 | RUSSIA | 486 | Manufacturer#4 | YjP5C55zHDXL7LalK27zfQnwejdpin4AMpvh | 32-822-502-4215 | ously express ideas haggle quickly dugouts? fu
-963.79 | Supplier#000000065 | RUSSIA | 1192 | Manufacturer#4 | BsAnHUmSFArppKrM | 32-444-835-2434 | l ideas wake carefully around the regular packages. furiously ruthless pinto bea
(3 rows)
\c postgres
-- 2.3 collect temporary information to _info tables
select create_wlm_operator_info(1);
create_wlm_operator_info
--------------------------
0
(1 row)
-- 2.4 show plan_operator_info
select * from gs_wlm_plan_operator_info order by queryid, plan_node_id;
--? datname | queryid | plan_node_id | startup_time | total_time | actual_rows | max_peak_memory | query_dop | parent_node_id | left_child_id | right_child_id | operation | orientation | strategy | options | condition | projection
--?
--? regression | 281474976710850 | 1 | 0 | 12 | 3 | 1 | 1 | 0 | 2 | 0 | LIMIT | ROW | | | | public.supplier.s_acctbal, public.supplier.s_name, public.nation.n_name, public.part.p_partkey, public.part.p_mfgr, public.supplier.s_address, public.supplier.s_phone, public.supplier.s_comment
--? regression | 281474976710850 | 2 | 0 | 12 | 3 | 1 | 1 | 1 | 3 | 0 | SORT | ROW | | | | public.supplier.s_acctbal, public.supplier.s_name, public.nation.n_name, public.part.p_partkey, public.part.p_mfgr, public.supplier.s_address, public.supplier.s_phone, public.supplier.s_comment
--? regression | 281474976710850 | 3 | 0 | 12 | 3 | 1 | 1 | 2 | 4 | 28 | JOIN | ROW | NESTED_LOOP | INNER | && (public.nation.n_regionkey = public.region.r_regionkey) && | public.supplier.s_acctbal, public.supplier.s_name, public.nation.n_name, public.part.p_partkey, public.part.p_mfgr, public.supplier.s_address, public.supplier.s_phone, public.supplier.s_comment
--? regression | 281474976710850 | 4 | 0 | 12 | 3 | 1 | 1 | 3 | 5 | 27 | JOIN | ROW | NESTED_LOOP | INNER | && (public.supplier.s_nationkey = public.nation.n_nationkey) && | public.part.p_partkey, public.part.p_mfgr, public.supplier.s_acctbal, public.supplier.s_name, public.supplier.s_address, public.supplier.s_phone, public.supplier.s_comment, public.nation.n_name, public.nation.n_regionkey
--? regression | 281474976710850 | 5 | 0 | 12 | 3 | 1 | 1 | 4 | 6 | 26 | JOIN | ROW | NESTED_LOOP | INNER | && (public.partsupp.ps_suppkey = public.supplier.s_suppkey) && | public.part.p_partkey, public.part.p_mfgr, public.supplier.s_acctbal, public.supplier.s_name, public.supplier.s_address, public.supplier.s_phone, public.supplier.s_comment, public.supplier.s_nationkey
--? regression | 281474976710850 | 6 | 0 | 11 | 3 | 1 | 1 | 5 | 7 | 11 | JOIN | ROW | NESTED_LOOP | INNER | && ((public.part.p_partkey = subquery."?column?") AND (public.partsupp.ps_supplycost = subquery.min)) && | public.part.p_partkey, public.part.p_mfgr, public.partsupp.ps_suppkey
--? regression | 281474976710850 | 7 | 0 | 5 | 12 | 1 | 1 | 6 | 8 | 9 | JOIN | ROW | HASH | INNER | && (public.partsupp.ps_partkey = public.part.p_partkey) && && | public.part.p_partkey, public.part.p_mfgr, public.partsupp.ps_partkey, public.partsupp.ps_suppkey, public.partsupp.ps_supplycost
--? regression | 281474976710850 | 8 | 0 | 2 | 8000 | 1 | 1 | 7 | 0 | 0 | SCAN | ROW | SEQ | | && | public.partsupp.ps_partkey, public.partsupp.ps_suppkey, public.partsupp.ps_availqty, public.partsupp.ps_supplycost, public.partsupp.ps_comment
--? regression | 281474976710850 | 9 | 0 | 0 | 3 | 1 | 1 | 7 | 10 | 0 | HASH | ROW | | | | public.part.p_partkey, public.part.p_mfgr
--? regression | 281474976710850 | 10 | 0 | 0 | 3 | 1 | 1 | 9 | 0 | 0 | SCAN | ROW | SEQ | | && (((public.part.p_type)::text ~~ 'SMALL%'::text) AND (public.part.p_size = 15)) | public.part.p_partkey, public.part.p_mfgr
--? regression | 281474976710850 | 11 | 0 | 6 | 36 | 1 | 1 | 6 | 12 | 0 | MATERIALIZE | ROW | | | | subquery."?column?", subquery.min
--? regression | 281474976710850 | 12 | 0 | 6 | 3 | 1 | 1 | 11 | 0 | 0 | SCAN | ROW | SUBQUERY | | && | subquery."?column?", subquery.min
--? regression | 281474976710850 | 13 | 0 | 6 | 3 | 1 | 1 | 12 | 14 | 0 | AGG | ROW | HASHED | | && | min(public.partsupp.ps_supplycost), public.partsupp.ps_partkey
--? regression | 281474976710850 | 14 | 0 | 6 | 4 | 1 | 1 | 13 | 15 | 18 | JOIN | ROW | NESTED_LOOP | INNER | && (public.nation.n_nationkey = public.supplier.s_nationkey) && | public.partsupp.ps_partkey, public.partsupp.ps_supplycost
--? regression | 281474976710850 | 15 | 0 | 0 | 5 | 1 | 1 | 14 | 16 | 17 | JOIN | ROW | NESTED_LOOP | INNER | && (public.nation.n_regionkey = public.region.r_regionkey) && | public.nation.n_nationkey
--? regression | 281474976710850 | 16 | 0 | 0 | 1 | 1 | 1 | 15 | 0 | 0 | SCAN | ROW | SEQ | | && (public.region.r_name = 'EUROPE '::bpchar) | public.region.r_regionkey, public.region.r_name, public.region.r_comment
--? regression | 281474976710850 | 17 | 0 | 0 | 25 | 1 | 1 | 15 | 0 | 0 | SCAN | ROW | SEQ | | && | public.nation.n_nationkey, public.nation.n_name, public.nation.n_regionkey, public.nation.n_comment
--? regression | 281474976710850 | 18 | 0 | 5 | 60 | 1 | 1 | 14 | 19 | 0 | MATERIALIZE | ROW | | | | public.partsupp.ps_partkey, public.partsupp.ps_supplycost, public.supplier.s_nationkey
--? regression | 281474976710850 | 19 | 0 | 5 | 12 | 1 | 1 | 18 | 20 | 24 | JOIN | ROW | HASH | INNER | && (public.partsupp.ps_suppkey = public.supplier.s_suppkey) && && | public.partsupp.ps_partkey, public.partsupp.ps_supplycost, public.supplier.s_nationkey
--? regression | 281474976710850 | 20 | 0 | 4 | 12 | 1 | 1 | 19 | 21 | 22 | JOIN | ROW | HASH | SEMI | && (public.partsupp.ps_partkey = public.part.p_partkey) && && | public.partsupp.ps_partkey, public.partsupp.ps_supplycost, public.partsupp.ps_suppkey
--? regression | 281474976710850 | 21 | 0 | 2 | 8000 | 1 | 1 | 20 | 0 | 0 | SCAN | ROW | SEQ | | && | public.partsupp.ps_partkey, public.partsupp.ps_suppkey, public.partsupp.ps_availqty, public.partsupp.ps_supplycost, public.partsupp.ps_comment
--? regression | 281474976710850 | 22 | 0 | 0 | 3 | 1 | 1 | 20 | 23 | 0 | HASH | ROW | | | | public.part.p_partkey
--? regression | 281474976710850 | 23 | 0 | 0 | 3 | 1 | 1 | 22 | 0 | 0 | SCAN | ROW | SEQ | | && (((public.part.p_type)::text ~~ 'SMALL%'::text) AND (public.part.p_size = 15)) | public.part.p_partkey
--? regression | 281474976710850 | 24 | 0 | 0 | 100 | 1 | 1 | 19 | 25 | 0 | HASH | ROW | | | | public.supplier.s_suppkey, public.supplier.s_nationkey
--? regression | 281474976710850 | 25 | 0 | 0 | 100 | 1 | 1 | 24 | 0 | 0 | SCAN | ROW | SEQ | | && | public.supplier.s_suppkey, public.supplier.s_nationkey
--? regression | 281474976710850 | 26 | 0 | 0 | 300 | 1 | 1 | 5 | 0 | 0 | SCAN | ROW | SEQ | | && | public.supplier.s_suppkey, public.supplier.s_name, public.supplier.s_address, public.supplier.s_nationkey, public.supplier.s_phone, public.supplier.s_acctbal, public.supplier.s_comment
--? regression | 281474976710850 | 27 | 0 | 0 | 75 | 1 | 1 | 4 | 0 | 0 | SCAN | ROW | SEQ | | && | public.nation.n_nationkey, public.nation.n_name, public.nation.n_regionkey, public.nation.n_comment
--? regression | 281474976710850 | 28 | 0 | 0 | 3 | 1 | 1 | 3 | 0 | 0 | SCAN | ROW | SEQ | | && (public.region.r_name = 'EUROPE '::bpchar) | public.region.r_regionkey, public.region.r_name, public.region.r_comment
(28 rows)
-- 2.5 run plan encoding
select gather_encoding_info('regression');
gather_encoding_info
----------------------
0
(1 row)
-- 2.6 show plan encoding
select * from gs_wlm_plan_encoding_table;
--? queryid | plan_node_id | parent_node_id | startup_time | total_time | rows | peak_memory | encode
--?
--? 281474976710850 | 1 | 0 | 0 | 12 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 0 1 0 0 0 0 0 0 0 0 1 1
--? 281474976710850 | 2 | 1 | 0 | 12 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 0 1 0 0 0 0 0 0 0 0 1 1
--? 281474976710850 | 3 | 2 | 0 | 12 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 1 0 1 0 0 0 0 0 0 0 0 1 1
--? 281474976710850 | 4 | 3 | 0 | 12 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 1 1 1 1 0 0 1 0 0 1 1 0 0 0 0 1 1 0 0 0
--? 281474976710850 | 5 | 4 | 0 | 12 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 0 0 1 0 1 0 1 1 1 1 1 0 1 1 1 0 1 0
--? 281474976710850 | 6 | 5 | 0 | 11 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 0 1 1 1 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0
--? 281474976710850 | 7 | 6 | 0 | 5 | 12 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 0 1 0 1 1 1 0 1 0 1 1 0 0 1 1 0 0 0 1
--? 281474976710850 | 8 | 7 | 0 | 2 | 8000 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1 1 1 1 0 0 1 0 1 1 0 1 0 1 0
--? 281474976710850 | 9 | 7 | 0 | 0 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 1 1 1 0 1 0 1 1 0 0 0 0
--? 281474976710850 | 10 | 9 | 0 | 0 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1 1 0 1 1 0 0 0 1 1 1 0 1 0 1 1 0 0 0 0
--? 281474976710850 | 11 | 6 | 0 | 6 | 36 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 0 0 0 0 1 1 0 0 0 0 1 1
--? 281474976710850 | 12 | 11 | 0 | 6 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1 1 1 0 0 0 0 1 1 0 0 0 0 1 1
--? 281474976710850 | 13 | 12 | 0 | 6 | 3 | 1 | 1.000 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1 1 1 0 0 0 1 0 1 0 1 0 1 0 0
--? 281474976710850 | 14 | 13 | 0 | 6 | 4 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 0 0 1 1 1 1 0 1 1 1 1 1 1 0 0
--? 281474976710850 | 15 | 14 | 0 | 0 | 5 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 1 1 1 0 0 0 0 0 1 1 1 1 1
--? 281474976710850 | 16 | 15 | 0 | 0 | 1 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 1 0 1 1 0 0 1 1 0 1 0 0 0 0 1 1
--? 281474976710850 | 17 | 15 | 0 | 0 | 25 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1 1 1 0 0 0 1 1 0 0 0 1 1 1 0
--? 281474976710850 | 18 | 14 | 0 | 5 | 60 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 1 0 1 1 1 0 0 0 0 1 0 1
--? 281474976710850 | 19 | 18 | 0 | 5 | 12 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 0 1 1 1 1 1 1 0 1 1 1 0 0 0 0 1 0 1
--? 281474976710850 | 20 | 19 | 0 | 4 | 12 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 0 1 0 1 1 1 0 0 1 1 0 0 0 0 0 0 1 1 0
--? 281474976710850 | 21 | 20 | 0 | 2 | 8000 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1 1 1 1 0 0 1 0 1 1 0 1 0 1 0
--? 281474976710850 | 22 | 20 | 0 | 0 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 1 0 0 0 0 1 0 1 0 0 0 0
--? 281474976710850 | 23 | 22 | 0 | 0 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1 1 0 1 1 0 0 0 1 0 0 0 0 1 0 1 0 0 0 0
--? 281474976710850 | 24 | 19 | 0 | 0 | 100 | 1 | 1.000 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 1 1 0 1 1 0 1 1 0 1 1 1
--? 281474976710850 | 25 | 24 | 0 | 0 | 100 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1 1 1 1 1 0 1 1 0 1 1 0 1 1 1
--? 281474976710850 | 26 | 5 | 0 | 0 | 300 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1 1 1 1 0 0 1 1 1 0 0 1 1 0 0
--? 281474976710850 | 27 | 4 | 0 | 0 | 75 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1 1 1 0 0 0 1 1 0 0 0 1 1 1 0
--? 281474976710850 | 28 | 3 | 0 | 0 | 3 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 1 0 1 1 0 0 1 1 0 1 0 0 0 0 1 1
(28 rows)
-- 3.TPCH Q3
-- 3.1 clean temporary tables
delete from gs_wlm_plan_encoding_table;
delete from gs_wlm_plan_operator_info;
select create_wlm_operator_info(0);
create_wlm_operator_info
--------------------------
0
(1 row)
\c regression
-- 3.2 run query
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'BUILDING'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < '1995-03-15'::date
and l_shipdate > '1995-03-15'::date
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
limit 10
;
l_orderkey | revenue | o_orderdate | o_shippriority
------------+-------------+--------------------------+----------------
47714 | 267010.5894 | Sat Mar 11 00:00:00 1995 | 0
22276 | 266351.5562 | Sun Jan 29 00:00:00 1995 | 0
32965 | 263768.3414 | Sat Feb 25 00:00:00 1995 | 0
21956 | 254541.1285 | Thu Feb 02 00:00:00 1995 | 0
1637 | 243512.7981 | Wed Feb 08 00:00:00 1995 | 0
10916 | 241320.0814 | Sat Mar 11 00:00:00 1995 | 0
30497 | 208566.6969 | Tue Feb 07 00:00:00 1995 | 0
450 | 205447.4232 | Sun Mar 05 00:00:00 1995 | 0
47204 | 204478.5213 | Mon Mar 13 00:00:00 1995 | 0
9696 | 201502.2188 | Mon Feb 20 00:00:00 1995 | 0
(10 rows)
\c postgres
-- 3.3 collect temporary information to _info tables
select create_wlm_operator_info(1);
create_wlm_operator_info
--------------------------
0
(1 row)
-- 3.4 show plan_operator_info
select * from gs_wlm_plan_operator_info order by queryid, plan_node_id;
--? datname | queryid | plan_node_id | startup_time | total_time | actual_rows | max_peak_memory | query_dop | parent_node_id | left_child_id | right_child_id | operation | orientation | strategy | options | condition | projection
--?
--? regression | 281474976710862 | 1 | 0 | 44 | 10 | 1 | 1 | 0 | 2 | 0 | LIMIT | ROW | | | | public.lineitem.l_orderkey, (sum((lineitem.l_extendedprice * (1::numeric - lineitem.l_discount)))), public.orders.o_orderdate, public.orders.o_shippriority
--? regression | 281474976710862 | 2 | 0 | 44 | 10 | 1 | 1 | 1 | 3 | 0 | SORT | ROW | | | | public.lineitem.l_orderkey, (sum((lineitem.l_extendedprice * (1::numeric - lineitem.l_discount)))), public.orders.o_orderdate, public.orders.o_shippriority
--? regression | 281474976710862 | 3 | 0 | 44 | 138 | 1 | 1 | 2 | 4 | 0 | AGG | ROW | HASHED | | && | public.lineitem.l_orderkey, sum((lineitem.l_extendedprice * (1::numeric - lineitem.l_discount))), public.orders.o_orderdate, public.orders.o_shippriority
--? regression | 281474976710862 | 4 | 0 | 42 | 356 | 1 | 1 | 3 | 5 | 6 | JOIN | ROW | HASH | INNER | && (public.lineitem.l_orderkey = public.orders.o_orderkey) && && | public.orders.o_orderdate, public.orders.o_shippriority, public.lineitem.l_orderkey, public.lineitem.l_extendedprice, public.lineitem.l_discount
--? regression | 281474976710862 | 5 | 0 | 25 | 32260 | 1 | 1 | 4 | 0 | 0 | SCAN | ROW | SEQ | | && (public.lineitem.l_shipdate > 'Wed Mar 15 00:00:00 1995'::timestamp(0) without time zone) | public.lineitem.l_orderkey, public.lineitem.l_partkey, public.lineitem.l_suppkey, public.lineitem.l_linenumber, public.lineitem.l_quantity, public.lineitem.l_extendedprice, public.lineitem.l_discount, public.lineitem.l_tax, public.lineitem.l_returnflag, public.lineitem.l_linestatus, public.lineitem.l_shipdate, public.lineitem.l_commitdate, public.lineitem.l_receiptdate, public.lineitem.l_shipinstruct, public.lineitem.l_shipmode, public.lineitem.l_comment
--? regression | 281474976710862 | 6 | 0 | 9 | 1797 | 1 | 1 | 4 | 7 | 0 | HASH | ROW | | | | public.orders.o_orderdate, public.orders.o_shippriority, public.orders.o_orderkey
--? regression | 281474976710862 | 7 | 0 | 9 | 1797 | 1 | 1 | 6 | 8 | 9 | JOIN | ROW | HASH | INNER | && (public.orders.o_custkey = public.customer.c_custkey) && && | public.orders.o_orderdate, public.orders.o_shippriority, public.orders.o_orderkey
--? regression | 281474976710862 | 8 | 0 | 5 | 7286 | 1 | 1 | 7 | 0 | 0 | SCAN | ROW | SEQ | | && (public.orders.o_orderdate < 'Wed Mar 15 00:00:00 1995'::timestamp(0) without time zone) | public.orders.o_orderkey, public.orders.o_custkey, public.orders.o_orderstatus, public.orders.o_totalprice, public.orders.o_orderdate, public.orders.o_orderpriority, public.orders.o_clerk, public.orders.o_shippriority, public.orders.o_comment
--? regression | 281474976710862 | 9 | 0 | 0 | 337 | 1 | 1 | 7 | 10 | 0 | HASH | ROW | | | | public.customer.c_custkey
--? regression | 281474976710862 | 10 | 0 | 0 | 337 | 1 | 1 | 9 | 0 | 0 | SCAN | ROW | SEQ | | && (public.customer.c_mktsegment = 'BUILDING'::bpchar) | public.customer.c_custkey
(10 rows)
-- 3.5 run plan encoding
select gather_encoding_info('regression');
gather_encoding_info
----------------------
0
(1 row)
-- 3.6 show plan encoding
select * from gs_wlm_plan_encoding_table;
--? queryid | plan_node_id | parent_node_id | startup_time | total_time | rows | peak_memory | encode
--?
--? 281474976710862 | 1 | 0 | 0 | 44 | 10 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 0 0 1 0 1 1 1 1 1 0 1 0
--? 281474976710862 | 2 | 1 | 0 | 44 | 10 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 0 0 1 0 1 1 1 1 1 0 1 0
--? 281474976710862 | 3 | 2 | 0 | 44 | 138 | 1 | 1.000 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1 1 1 0 1 1 0 1 0 0 1 0 0 1 1
--? 281474976710862 | 4 | 3 | 0 | 42 | 356 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 1 0 0 1 1 1 0 1 0 0 0 1 0 0 1 0
--? 281474976710862 | 5 | 4 | 0 | 25 | 32260 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 1 1 1 1 0 1 0 1 1 1 0 1 0 0 0 1 1 0 0 1 1
--? 281474976710862 | 6 | 4 | 0 | 9 | 1797 | 1 | 1.000 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 0 0 0 1 1 1 0 1 1 1 1 0
--? 281474976710862 | 7 | 6 | 0 | 9 | 1797 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 0 0 0 1 1 1 0 0 0 0 0 1 1 1 0 1 1 1 1 0
--? 281474976710862 | 8 | 7 | 0 | 5 | 7286 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 1 1 0 1 1 0 0 0 1 1 0 0 1 0 0 1 1 1 0 1
--? 281474976710862 | 9 | 7 | 0 | 0 | 337 | 1 | 1.000 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 0 0 0 0 1 0 1 0 0 0 0 0
--? 281474976710862 | 10 | 9 | 0 | 0 | 337 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 1 0 1 1 0 0 0 0 1 0 1 0 0 0 0 0
(10 rows)
-- 4.TPCH Q4
-- 4.1 clean temporary tables
delete from gs_wlm_plan_encoding_table;
delete from gs_wlm_plan_operator_info;
select create_wlm_operator_info(0);
create_wlm_operator_info
--------------------------
0
(1 row)
\c regression
-- 4.2 run query
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= '1993-07-01'::date
and o_orderdate < '1993-07-01'::date + interval '3 month'
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority;
o_orderpriority | order_count
-----------------+-------------
1-URGENT | 93
2-HIGH | 103
3-MEDIUM | 109
4-NOT SPECIFIED | 102
5-LOW | 128
(5 rows)
\c postgres
-- 4.3 collect temporary information to _info tables
select create_wlm_operator_info(1);
create_wlm_operator_info
--------------------------
0
(1 row)
-- 4.4 show plan_operator_info
select * from gs_wlm_plan_operator_info order by queryid, plan_node_id;
--? datname | queryid | plan_node_id | startup_time | total_time | actual_rows | max_peak_memory | query_dop | parent_node_id | left_child_id | right_child_id | operation | orientation | strategy | options | condition | projection
--?
--? regression | 281474976710874 | 1 | 0 | 43 | 5 | 1 | 1 | 0 | 2 | 0 | SORT | ROW | | | | public.orders.o_orderpriority,
--? regression | 281474976710874 | 2 | 0 | 43 | 5 | 1 | 1 | 1 | 3 | 0 | AGG | ROW | HASHED | | && | public.orders.o_orderpriority,
--? regression | 281474976710874 | 3 | 0 | 42 | 535 | 1 | 1 | 2 | 4 | 5 | JOIN | ROW | HASH | RIGHT SEMI | && (public.lineitem.l_orderkey = public.orders.o_orderkey) && && | public.orders.o_orderpriority
--? regression | 281474976710874 | 4 | 0 | 28 | 37897 | 1 | 1 | 3 | 0 | 0 | SCAN | ROW | SEQ | | && (public.lineitem.l_commitdate < public.lineitem.l_receiptdate) | public.lineitem.l_orderkey, public.lineitem.l_partkey, public.lineitem.l_suppkey, public.lineitem.l_linenumber, public.lineitem.l_quantity, public.lineitem.l_extendedprice, public.lineitem.l_discount, public.lineitem.l_tax, public.lineitem.l_returnflag, public.lineitem.l_linestatus, public.lineitem.l_shipdate, public.lineitem.l_commitdate, public.lineitem.l_receiptdate, public.lineitem.l_shipinstruct, public.lineitem.l_shipmode, public.lineitem.l_comment
--? regression | 281474976710874 | 5 | 0 | 6 | 582 | 1 | 1 | 3 | 6 | 0 | HASH | ROW | | | | public.orders.o_orderpriority, public.orders.o_orderkey
--? regression | 281474976710874 | 6 | 0 | 5 | 582 | 1 | 1 | 5 | 0 | 0 | SCAN | ROW | SEQ | | && ((public.orders.o_orderdate >= 'Thu Jul 01 00:00:00 1993'::timestamp(0) without time zone) AND (public.orders.o_orderdate < 'Fri Oct 01 00:00:00 1993'::timestamp without time zone)) | public.orders.o_orderpriority, public.orders.o_orderkey
(6 rows)
-- 4.5 run plan encoding
select gather_encoding_info('regression');
gather_encoding_info
----------------------
0
(1 row)
-- 4.6 show plan encoding
select * from gs_wlm_plan_encoding_table;
--? queryid | plan_node_id | parent_node_id | startup_time | total_time | rows | peak_memory | encode
--?
--? 281474976710874 | 1 | 0 | 0 | 43 | 5 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 0 1 0 0 1 0 1 1 1 1 1 1
--? 281474976710874 | 2 | 1 | 0 | 43 | 5 | 1 | 1.000 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1 1 1 0 1 0 1 0 1 1 0 0 0 1 0
--? 281474976710874 | 3 | 2 | 0 | 42 | 535 | 1 | 1.000 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 1 0 0 1 0 1 1 0 0 1 1 0 0 1 0 1
--? 281474976710874 | 4 | 3 | 0 | 28 | 37897 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 1 0 0 1 0 0 1 0 1 0 0 0 1 1 0 0 1 1
--? 281474976710874 | 5 | 3 | 0 | 6 | 582 | 1 | 1.000 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 1 0 0 0 0 1 1 0 1 1 1 0 1 0 0 1 1
--? 281474976710874 | 6 | 5 | 0 | 5 | 582 | 1 | 1.000 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 1 1 1 1 0 0 1 1 0 1 1 1 0 1 0 0 1 1
(6 rows)
-- 5 model train
-- 5.1 train with model not existed in gs_opt_model
delete from gs_opt_model;
select * from model_train_opt('void', 'void');
ERROR: void is not supported as template_name.
select * from model_train_opt('rlstm', 'void');
ERROR: OPT_Model not found for model name void
-- 5. invalid template_name
insert into gs_opt_model values('rlstm1', 'tmp_name', 'regression', '128.0.0.1', 5000, 0, 0, 0, 0, 0, 0, false, false, '{S, T}', '{0,0}', '{0,0}', 'Description');
select model_train_opt('rlstm1', 'tmp_name');
ERROR: rlstm1 is not supported as template_name.
CONTEXT: referenced column: model_train_opt
-- 5. train with false label targets
delete from gs_opt_model;
insert into gs_opt_model values('rlstm', 'rlstm_tmp', 'regression', '128.0.0.1', 5000, 1000, 1, -1, 50, 2000, 0, false, false, '{}', '{}', '{}', 'Description');
select model_train_opt('rlstm', 'rlstm_tmp');
ERROR: Model labels are not a 1-D char array.
CONTEXT: referenced column: model_train_opt
update gs_opt_model set label = '{t}';
update gs_opt_model set label = '{T,T}';
select model_train_opt('rlstm', 'rlstm_tmp');
ERROR: Repetitive labels found in model labels attribute for template name rlstm model name rlstm_tmp
CONTEXT: referenced column: model_train_opt
-- 5. positive cases: connection failure
update gs_opt_model set label = '{T}';
select model_train_opt('rlstm', 'rlstm_tmp');
ERROR: AI engine connection failed.
CONTEXT: referenced column: model_train_opt
update gs_opt_model set label = '{S,T}';
select model_train_opt('rlstm', 'rlstm_tmp');
ERROR: AI engine connection failed.
CONTEXT: referenced column: model_train_opt
-- 6 model predict
-- 6.1 predict with model not existed in gs_opt_model
delete from gs_opt_model;
\c regression
explain (analyze on, predictor rlstm_tmp)
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'BUILDING'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < '1995-03-15'::date
and l_shipdate > '1995-03-15'::date
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
limit 10
;
WARNING: MLModel not found for model name rlstm_tmp
--?
--?
--? Limit (cost=.* rows=10 loops=1)
--? -> Sort (cost=.* rows=10 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * (1::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
--? Sort Method: top-N heapsort Memory: .*kB
--? -> HashAggregate (cost=.* rows=138 loops=1)
Group By Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
--? -> Hash Join (cost=.* rows=356 loops=1)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
--? -> Seq Scan on lineitem (cost=.* rows=32260 loops=1)
Filter: (l_shipdate > 'Wed Mar 15 00:00:00 1995'::timestamp(0) without time zone)
Rows Removed by Filter: 27915
--? -> Hash (cost=.* rows=1797 loops=1)
--? Buckets: 32768 Batches: 1 Memory Usage: .*kB
--? -> Hash Join (cost=.* rows=1797 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
--? -> Seq Scan on orders (cost=.* rows=7286 loops=1)
Filter: (o_orderdate < 'Wed Mar 15 00:00:00 1995'::timestamp(0) without time zone)
Rows Removed by Filter: 7714
--? -> Hash (cost=.* rows=337 loops=1)
--? Buckets: 32768 Batches: 1 Memory Usage: .*kB
--? -> Seq Scan on customer (cost=.* rows=337 loops=1)
Filter: (c_mktsegment = 'BUILDING'::bpchar)
Rows Removed by Filter: 1163
--? Total runtime: .* ms
(24 rows)
-- 6.2 wrong database
insert into gs_opt_model values('rlstm', 'rlstm_tmp', 'postgres', '128.0.0.1', 5000, 1000, 1, -1, 50, 2000, 0, false, false, '{S,T}', '{10000,10000}', '{10,10}', 'Description');
explain (analyze on, predictor rlstm_tmp)
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'BUILDING'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < '1995-03-15'::date
and l_shipdate > '1995-03-15'::date
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
limit 10
;
WARNING: Predictor rlstm_tmp is not for current database
--?
--?
--? Limit (cost=.* rows=10 loops=1)
--? -> Sort (cost=.* rows=10 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * (1::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
--? Sort Method: top-N heapsort Memory: .*kB
--? -> HashAggregate (cost=.* rows=138 loops=1)
Group By Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
--? -> Hash Join (cost=.* rows=356 loops=1)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
--? -> Seq Scan on lineitem (cost=.* rows=32260 loops=1)
Filter: (l_shipdate > 'Wed Mar 15 00:00:00 1995'::timestamp(0) without time zone)
Rows Removed by Filter: 27915
--? -> Hash (cost=.* rows=1797 loops=1)
--? Buckets: 32768 Batches: 1 Memory Usage: .*kB
--? -> Hash Join (cost=.* rows=1797 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
--? -> Seq Scan on orders (cost=.* rows=7286 loops=1)
Filter: (o_orderdate < 'Wed Mar 15 00:00:00 1995'::timestamp(0) without time zone)
--? Rows Removed by Filter: 7714
--? -> Hash (cost=.* rows=337 loops=1)
--? Buckets: 32768 Batches: 1 Memory Usage: .*kB
--? -> Seq Scan on customer (cost=.* rows=337 loops=1)
Filter: (c_mktsegment = 'BUILDING'::bpchar)
Rows Removed by Filter: 1163
--? Total runtime: .* ms
(24 rows)
-- 6.3 connection failed
update gs_opt_model set datname = 'regression';
explain (analyze on, predictor rlstm_tmp)
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'BUILDING'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < '1995-03-15'::date
and l_shipdate > '1995-03-15'::date
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
limit 10
;
WARNING: Predictor rlstm_tmp is not converged, results are not reliable
ERROR: AI engine connection failed.
-- final cleanup
\c postgres
select create_wlm_operator_info(0);
create_wlm_operator_info
--------------------------
0
(1 row)
select create_wlm_session_info(0);
create_wlm_session_info
-------------------------
0
(1 row)
drop database regression;
create database regression;