create schema uint_smp;
set current_schema to 'uint_smp';
set enable_opfusion = on;
set opfusion_debug_mode = log;
drop table if exists t2 ;
NOTICE: table "t2" does not exist, skipping
create table t2(a uint1);
insert into t2 select generate_series(1,255);
insert into t2 select generate_series(1,255);
insert into t2 select generate_series(1,255);
insert into t2 select generate_series(1,255);
insert into t2 select generate_series(1,255);
insert into t2 select generate_series(1,255);
insert into t2 select generate_series(1,255);
insert into t2 select generate_series(1,255);
insert into t2 select generate_series(1,255);
insert into t2 select generate_series(1,255);
insert into t2 select generate_series(1,255);
insert into t2 select generate_series(1,255);
explain(costs off, verbose) select * from t2 where a = 2;
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Seq Scan on uint_smp.t2
Output: a
Filter: (t2.a = 2)
(4 rows)
explain(costs off, verbose) select * from t2 where a = 2 and t2.a in (select a from t2 where a > 500);
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Nested Loop Semi Join
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a = 2) AND (uint_smp.t2.a > 500))
-> Materialize
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a > 500) AND (uint_smp.t2.a = 2))
(11 rows)
set query_dop = 4;
explain(costs off, verbose) select * from t2 where a = 2;
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Seq Scan on uint_smp.t2
Output: a
Filter: (t2.a = 2)
(4 rows)
explain(costs off, verbose) select * from t2 where a = 2 and t2.a in (select a from t2 where a > 500);
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Nested Loop Semi Join
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a = 2) AND (uint_smp.t2.a > 500))
-> Materialize
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a > 500) AND (uint_smp.t2.a = 2))
(11 rows)
set query_dop = 1;
drop table if exists t2 ;
create table t2(a uint2);
insert into t2 select generate_series(1,25500);
insert into t2 select generate_series(1,25500);
insert into t2 select generate_series(1,25500);
insert into t2 select generate_series(1,25500);
insert into t2 select generate_series(1,25500);
insert into t2 select generate_series(1,25500);
insert into t2 select generate_series(1,25500);
insert into t2 select generate_series(1,25500);
explain(costs off, verbose) select * from t2 where a = 2;
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Seq Scan on uint_smp.t2
Output: a
Filter: (t2.a = 2)
(4 rows)
explain(costs off, verbose) select * from t2 where a = 2 and t2.a in (select a from t2 where a > 500);
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Nested Loop Semi Join
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a = 2) AND (uint_smp.t2.a > 500))
-> Materialize
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a > 500) AND (uint_smp.t2.a = 2))
(11 rows)
set query_dop = 4;
explain(costs off, verbose) select * from t2 where a = 2;
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Seq Scan on uint_smp.t2
Output: a
Filter: (t2.a = 2)
(4 rows)
explain(costs off, verbose) select * from t2 where a = 2 and t2.a in (select a from t2 where a > 500);
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Nested Loop Semi Join
Output: uint_smp.t2.a
-> Streaming(type: LOCAL GATHER dop: 1/4)
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a = 2) AND (uint_smp.t2.a > 500))
-> Materialize
Output: uint_smp.t2.a
-> Streaming(type: LOCAL GATHER dop: 1/4)
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a > 500) AND (uint_smp.t2.a = 2))
(15 rows)
set query_dop = 1;
drop table if exists t2 ;
create table t2(a uint4);
insert into t2 select generate_series(1,200000);
insert into t2 select generate_series(1,200000);
explain(costs off, verbose) select * from t2 where a = 2;
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Seq Scan on uint_smp.t2
Output: a
Filter: (t2.a = 2)
(4 rows)
explain(costs off, verbose) select * from t2 where a = 2 and t2.a in (select a from t2 where a < 500);
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Nested Loop Semi Join
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a = 2) AND (uint_smp.t2.a < 500))
-> Materialize
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a < 500) AND (uint_smp.t2.a = 2))
(11 rows)
set query_dop = 4;
explain(costs off, verbose) select * from t2 where a = 2;
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Streaming(type: LOCAL GATHER dop: 1/4)
Output: a
-> Seq Scan on uint_smp.t2
Output: a
Filter: (t2.a = 2)
(6 rows)
explain(costs off, verbose) select * from t2 where a = 2 and t2.a in (select a from t2 where a < 500);
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Nested Loop Semi Join
Output: uint_smp.t2.a
-> Streaming(type: LOCAL GATHER dop: 1/4)
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a = 2) AND (uint_smp.t2.a < 500))
-> Materialize
Output: uint_smp.t2.a
-> Streaming(type: LOCAL GATHER dop: 1/4)
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a < 500) AND (uint_smp.t2.a = 2))
(15 rows)
set query_dop = 1;
drop table if exists t2 ;
create table t2(a uint8);
insert into t2 select generate_series(1,200000);
insert into t2 select generate_series(1,200000);
explain(costs off, verbose) select * from t2 where a = 2;
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Seq Scan on uint_smp.t2
Output: a
Filter: (t2.a = 2::bigint)
(4 rows)
explain(costs off, verbose) select * from t2 where a = 2 and t2.a in (select a from t2 where a < 500);
QUERY PLAN
---------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Nested Loop Semi Join
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a = 2::bigint) AND (uint_smp.t2.a < 500::bigint))
-> Materialize
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a < 500::bigint) AND (uint_smp.t2.a = 2::bigint))
(11 rows)
set query_dop = 4;
explain(costs off, verbose) select * from t2 where a = 2;
QUERY PLAN
------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Streaming(type: LOCAL GATHER dop: 1/4)
Output: a
-> Seq Scan on uint_smp.t2
Output: a
Filter: (t2.a = 2::bigint)
(6 rows)
explain(costs off, verbose) select * from t2 where a = 2 and t2.a in (select a from t2 where a < 500);
QUERY PLAN
---------------------------------------------------------------------------------------------
[No Bypass]reason: Bypass not executed because query's scan operator is not index.
Nested Loop Semi Join
Output: uint_smp.t2.a
-> Streaming(type: LOCAL GATHER dop: 1/4)
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a = 2::bigint) AND (uint_smp.t2.a < 500::bigint))
-> Materialize
Output: uint_smp.t2.a
-> Streaming(type: LOCAL GATHER dop: 1/4)
Output: uint_smp.t2.a
-> Seq Scan on uint_smp.t2
Output: uint_smp.t2.a
Filter: ((uint_smp.t2.a < 500::bigint) AND (uint_smp.t2.a = 2::bigint))
(15 rows)
set query_dop = 1;
set opfusion_debug_mode = off;
create table join_1(a uint4);
create table join_2(a uint8);
insert into join_1 select generate_series(1, 150000);
insert into join_2 select generate_series(1, 150000);
set query_dop = 2;
explain(costs off, verbose) select * from join_1 join join_2;
QUERY PLAN
-------------------------------------------------
Streaming(type: LOCAL GATHER dop: 1/2)
Output: join_1.a, join_2.a
-> Nested Loop
Output: join_1.a, join_2.a
-> Streaming(type: BROADCAST dop: 2/2)
Output: join_2.a
-> Seq Scan on uint_smp.join_2
Output: join_2.a
-> Materialize
Output: join_1.a
-> Seq Scan on uint_smp.join_1
Output: join_1.a
(12 rows)
explain(costs off, verbose) select * from join_1 join join_2 on join_1.a = join_2.a;
QUERY PLAN
-------------------------------------------------
Streaming(type: LOCAL GATHER dop: 1/2)
Output: join_1.a, join_2.a
-> Hash Join
Output: join_1.a, join_2.a
Hash Cond: (join_1.a = join_2.a)
-> Streaming(type: BROADCAST dop: 2/2)
Output: join_1.a
-> Seq Scan on uint_smp.join_1
Output: join_1.a
-> Hash
Output: join_2.a
-> Seq Scan on uint_smp.join_2
Output: join_2.a
(13 rows)
explain(costs off, verbose) select * from join_1 left join join_2 on join_1.a = join_2.a;
QUERY PLAN
-------------------------------------------------
Streaming(type: LOCAL GATHER dop: 1/2)
Output: join_1.a, join_2.a
-> Hash Right Join
Output: join_1.a, join_2.a
Hash Cond: (join_2.a = join_1.a)
-> Streaming(type: BROADCAST dop: 2/2)
Output: join_2.a
-> Seq Scan on uint_smp.join_2
Output: join_2.a
-> Hash
Output: join_1.a
-> Seq Scan on uint_smp.join_1
Output: join_1.a
(13 rows)
explain(costs off, verbose) select * from join_1 right join join_2 on join_1.a = join_2.a;
QUERY PLAN
-------------------------------------------------
Streaming(type: LOCAL GATHER dop: 1/2)
Output: join_1.a, join_2.a
-> Hash Right Join
Output: join_1.a, join_2.a
Hash Cond: (join_1.a = join_2.a)
-> Streaming(type: BROADCAST dop: 2/2)
Output: join_1.a
-> Seq Scan on uint_smp.join_1
Output: join_1.a
-> Hash
Output: join_2.a
-> Seq Scan on uint_smp.join_2
Output: join_2.a
(13 rows)
explain(costs off, verbose) select * from join_1 inner join join_2 on join_1.a = join_2.a;
QUERY PLAN
-------------------------------------------------
Streaming(type: LOCAL GATHER dop: 1/2)
Output: join_1.a, join_2.a
-> Hash Join
Output: join_1.a, join_2.a
Hash Cond: (join_1.a = join_2.a)
-> Streaming(type: BROADCAST dop: 2/2)
Output: join_1.a
-> Seq Scan on uint_smp.join_1
Output: join_1.a
-> Hash
Output: join_2.a
-> Seq Scan on uint_smp.join_2
Output: join_2.a
(13 rows)
explain(costs off, verbose) select /*+ nestloop(join_1 join_2)*/ * from join_1 left join join_2 on join_1.a = join_2.a;
QUERY PLAN
-------------------------------------------------------
Streaming(type: LOCAL GATHER dop: 1/2)
Output: join_1.a, join_2.a
-> Nested Loop Left Join
Output: join_1.a, join_2.a
Join Filter: (join_1.a = join_2.a)
-> Seq Scan on uint_smp.join_1
Output: join_1.a
-> Materialize
Output: join_2.a
-> Streaming(type: BROADCAST dop: 2/2)
Output: join_2.a
-> Seq Scan on uint_smp.join_2
Output: join_2.a
(13 rows)
explain(costs off, verbose) select /*+ hashjoin(join_1 join_2)*/ * from join_1 left join join_2 on join_1.a = join_2.a;
QUERY PLAN
-------------------------------------------------
Streaming(type: LOCAL GATHER dop: 1/2)
Output: join_1.a, join_2.a
-> Hash Right Join
Output: join_1.a, join_2.a
Hash Cond: (join_2.a = join_1.a)
-> Streaming(type: BROADCAST dop: 2/2)
Output: join_2.a
-> Seq Scan on uint_smp.join_2
Output: join_2.a
-> Hash
Output: join_1.a
-> Seq Scan on uint_smp.join_1
Output: join_1.a
(13 rows)
explain(costs off, verbose) select /*+ mergejoin(join_1 join_2)*/ * from join_1 left join join_2 on join_1.a = join_2.a;
--?ERROR: operator.*
-- test
set query_dop = 6;
drop table if exists t_t_mutil_t1;
NOTICE: table "t_t_mutil_t1" does not exist, skipping
drop table if exists t_t_mutil_t2;
NOTICE: table "t_t_mutil_t2" does not exist, skipping
drop table if exists t_t_mutil_t3;
NOTICE: table "t_t_mutil_t3" does not exist, skipping
create table t_t_mutil_t1(col1 int,col2 int);
create table t_t_mutil_t2(col1 int,col2 int);
create table t_t_mutil_t3(col1 int,col2 int);
insert into t_t_mutil_t1 values(generate_series(1,1000000),generate_series(1,1000000));
insert into t_t_mutil_t2 values(generate_series(1,1000000),generate_series(1,1000000));
insert into t_t_mutil_t3 values(generate_series(1,1000000),generate_series(1,1000000));
explain(costs off, verbose) update/*+nestloop(a b)*/ t_t_mutil_t1 a,t_t_mutil_t2 b set b.col1=5,a.col2=4 where a.col1=b.col1;
QUERY PLAN
--------------------------------------------------------------------------------------------
Update on MULTI-RELATION
-> LockRows
Output: a.col1, (4), a.ctid, a.ctid, b.ctid, (5), b.col2, b.ctid, a.ctid, b.ctid
-> Nested Loop
Output: a.col1, 4, a.ctid, a.ctid, b.ctid, 5, b.col2, b.ctid, a.ctid, b.ctid
Join Filter: (a.col1 = b.col1)
-> Seq Scan on uint_smp.t_t_mutil_t1 a
Output: a.col1, a.ctid
-> Seq Scan on uint_smp.t_t_mutil_t2 b
Output: b.col2, b.ctid, b.col1
(10 rows)
drop table if exists t_t_mutil_t1;
drop table if exists t_t_mutil_t2;
drop table if exists t_t_mutil_t3;
reset query_dop;
drop schema uint_smp cascade;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to table t2
drop cascades to table join_1
drop cascades to table join_2
reset current_schema;
