Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / partial_table.out
blob: a9530fb3c9b760daa9e7076b03957d2f362a0b5c [file] [log] [blame]
-- TODO: inherit tables
-- TODO: partition tables
-- TODO: ao tables
-- TODO: tables and temp tables
\set explain 'explain analyze'
create extension if not exists gp_debug_numsegments;
drop schema if exists test_partial_table;
NOTICE: schema "test_partial_table" does not exist, skipping
create schema test_partial_table;
set search_path=test_partial_table,public;
set allow_system_table_mods=true;
--
-- prepare kinds of tables
--
select gp_debug_set_create_table_default_numsegments(1);
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
1
(1 row)
create table t1 (c1 int, c2 int, c3 int, c4 int) distributed by (c1, c2);
create table d1 (c1 int, c2 int, c3 int, c4 int) distributed replicated;
create table r1 (c1 int, c2 int, c3 int, c4 int) distributed randomly;
select gp_debug_set_create_table_default_numsegments(2);
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
2
(1 row)
create table t2 (c1 int, c2 int, c3 int, c4 int) distributed by (c1, c2);
create table d2 (c1 int, c2 int, c3 int, c4 int) distributed replicated;
create table r2 (c1 int, c2 int, c3 int, c4 int) distributed randomly;
select gp_debug_reset_create_table_default_numsegments();
gp_debug_reset_create_table_default_numsegments
-------------------------------------------------
(1 row)
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in (
't1'::regclass, 'd1'::regclass, 'r1'::regclass,
't2'::regclass, 'd2'::regclass, 'r2'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t1 | 1 2 | p | 1
d1 | | r | 1
r1 | | p | 1
t2 | 1 2 | p | 2
d2 | | r | 2
r2 | | p | 2
(6 rows)
analyze t1;
analyze d1;
analyze r1;
analyze t2;
analyze d2;
analyze r2;
--
-- regression tests
--
-- Test numsegments properity cannot be larger than the size of cluster
create table size_sanity_check(c1 int, c2 int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
update gp_distribution_policy set numsegments = 10 where localoid = 'size_sanity_check'::regclass;
select * from size_sanity_check;
ERROR: cannot access table "size_sanity_check" in current transaction
LINE 1: select * from size_sanity_check;
^
DETAIL: New segments are concurrently added to the cluster during the execution of current transaction, the table has data on some of the new segments, but these new segments are invisible and inaccessible to current transaction.
HINT: Re-run the query in a new transaction.
update gp_distribution_policy set numsegments = 3 where localoid = 'size_sanity_check'::regclass;
-- a temp table is created during reorganization, its numsegments should be
-- the same with original table, otherwise some data will be lost after the
-- reorganization.
--
-- in most cases the temp table is created with CTAS.
begin;
insert into t1 select i, i from generate_series(1,10) i;
select gp_segment_id, * from t1;
gp_segment_id | c1 | c2 | c3 | c4
---------------+----+----+----+----
0 | 1 | 1 | |
0 | 2 | 2 | |
0 | 3 | 3 | |
0 | 4 | 4 | |
0 | 5 | 5 | |
0 | 6 | 6 | |
0 | 7 | 7 | |
0 | 8 | 8 | |
0 | 9 | 9 | |
0 | 10 | 10 | |
(10 rows)
select gp_debug_set_create_table_default_numsegments('full');
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
FULL
(1 row)
alter table t1 set with (reorganize=true) distributed by (c1);
select gp_segment_id, * from t1;
gp_segment_id | c1 | c2 | c3 | c4
---------------+----+----+----+----
0 | 1 | 1 | |
0 | 2 | 2 | |
0 | 3 | 3 | |
0 | 4 | 4 | |
0 | 5 | 5 | |
0 | 6 | 6 | |
0 | 7 | 7 | |
0 | 8 | 8 | |
0 | 9 | 9 | |
0 | 10 | 10 | |
(10 rows)
abort;
-- but there are also cases the temp table is created with CREATE + INSERT.
-- case 1: with dropped columns
begin;
insert into t1 select i, i from generate_series(1,10) i;
select gp_segment_id, * from t1;
gp_segment_id | c1 | c2 | c3 | c4
---------------+----+----+----+----
0 | 1 | 1 | |
0 | 2 | 2 | |
0 | 3 | 3 | |
0 | 4 | 4 | |
0 | 5 | 5 | |
0 | 6 | 6 | |
0 | 7 | 7 | |
0 | 8 | 8 | |
0 | 9 | 9 | |
0 | 10 | 10 | |
(10 rows)
alter table t1 drop column c4;
select gp_debug_set_create_table_default_numsegments('full');
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
FULL
(1 row)
alter table t1 set with (reorganize=true) distributed by (c1);
select gp_segment_id, * from t1;
gp_segment_id | c1 | c2 | c3
---------------+----+----+----
0 | 1 | 1 |
0 | 2 | 2 |
0 | 3 | 3 |
0 | 4 | 4 |
0 | 5 | 5 |
0 | 6 | 6 |
0 | 7 | 7 |
0 | 8 | 8 |
0 | 9 | 9 |
0 | 10 | 10 |
(10 rows)
abort;
-- case 2: AOCO
begin;
select gp_debug_set_create_table_default_numsegments('minimal');
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
MINIMAL
(1 row)
create table t (c1 int, c2 int)
with (appendonly=true, orientation=column)
distributed by (c1, c2);
insert into t select i, i from generate_series(1,10) i;
select gp_segment_id, * from t;
gp_segment_id | c1 | c2
---------------+----+----
0 | 1 | 1
0 | 2 | 2
0 | 3 | 3
0 | 4 | 4
0 | 5 | 5
0 | 6 | 6
0 | 7 | 7
0 | 8 | 8
0 | 9 | 9
0 | 10 | 10
(10 rows)
select gp_debug_set_create_table_default_numsegments('full');
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
FULL
(1 row)
alter table t set with (reorganize=true) distributed by (c1);
select gp_segment_id, * from t;
gp_segment_id | c1 | c2
---------------+----+----
0 | 1 | 1
0 | 2 | 2
0 | 3 | 3
0 | 4 | 4
0 | 5 | 5
0 | 6 | 6
0 | 7 | 7
0 | 8 | 8
0 | 9 | 9
0 | 10 | 10
(10 rows)
abort;
-- case 3: AO + index
begin;
select gp_debug_set_create_table_default_numsegments('minimal');
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
MINIMAL
(1 row)
create table t (c1 int, c2 int)
with (appendonly=true, orientation=row)
distributed by (c1, c2);
create index ti on t (c2);
insert into t select i, i from generate_series(1,10) i;
select gp_segment_id, * from t;
gp_segment_id | c1 | c2
---------------+----+----
0 | 1 | 1
0 | 2 | 2
0 | 3 | 3
0 | 4 | 4
0 | 5 | 5
0 | 6 | 6
0 | 7 | 7
0 | 8 | 8
0 | 9 | 9
0 | 10 | 10
(10 rows)
select gp_debug_set_create_table_default_numsegments('full');
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
FULL
(1 row)
alter table t set with (reorganize=true) distributed by (c1);
select gp_segment_id, * from t;
gp_segment_id | c1 | c2
---------------+----+----
0 | 1 | 1
0 | 2 | 2
0 | 3 | 3
0 | 4 | 4
0 | 5 | 5
0 | 6 | 6
0 | 7 | 7
0 | 8 | 8
0 | 9 | 9
0 | 10 | 10
(10 rows)
abort;
-- restore the analyze information
analyze t1;
select gp_debug_reset_create_table_default_numsegments();
gp_debug_reset_create_table_default_numsegments
-------------------------------------------------
(1 row)
-- append SingleQE of different sizes
select max(c1) as v, 1 as r from t2 union all select 1 as v, 2 as r;
v | r
---+---
| 1
1 | 2
(2 rows)
-- append node should use the max numsegments of all the subpaths
begin;
-- insert enough data to ensure executors got reached on segments
insert into t1 select i from generate_series(1,100) i;
insert into t2 select i from generate_series(1,100) i;
:explain select * from t2 a join t2 b using(c2)
union all select * from t1 c join t1 d using(c2) ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..4.37 rows=7 width=28) (actual time=8.836..8.836 rows=0 loops=1)
-> Append (cost=1.03..4.24 rows=4 width=28) (never executed)
-> Hash Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c2 = b.c2)
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c2
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (actual time=0.083..0.159 rows=52 loops=1)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 262144 Batches: 1 Memory Usage: 2048kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (actual time=0.065..0.216 rows=100 loops=1)
Hash Key: b.c2
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (actual time=0.068..0.120 rows=52 loops=1)
-> Hash Join (cost=1.01..2.05 rows=2 width=28) (never executed)
Hash Cond: (c.c2 = d.c2)
-> Seq Scan on t1 c (cost=0.00..1.00 rows=1 width=16) (actual time=0.058..0.058 rows=1 loops=1)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 262144 Batches: 1 Memory Usage: 2048kB
-> Seq Scan on t1 d (cost=0.00..1.00 rows=1 width=16) (actual time=0.022..0.113 rows=100 loops=1)
Planning Time: 3.988 ms
(slice0) Executor memory: 75K bytes.
(slice1) Executor memory: 2093K bytes avg x 2 workers, 2098K bytes max (seg0). Work_mem: 2048K bytes max.
(slice2) Executor memory: 38K bytes avg x 2 workers, 38K bytes max (seg1).
(slice3) Executor memory: 38K bytes avg x 2 workers, 38K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 35.279 ms
(26 rows)
:explain select * from t1 a join t1 b using(c2)
union all select * from t2 c join t2 d using(c2) ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..4.37 rows=7 width=28) (actual time=4.023..4.024 rows=0 loops=1)
-> Append (cost=1.01..4.24 rows=4 width=28) (never executed)
-> Hash Join (cost=1.01..2.05 rows=2 width=28) (never executed)
Hash Cond: (a.c2 = b.c2)
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (actual time=0.055..0.055 rows=1 loops=1)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 262144 Batches: 1 Memory Usage: 2048kB
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (actual time=0.022..0.110 rows=100 loops=1)
-> Hash Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (c.c2 = d.c2)
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: c.c2
-> Seq Scan on t2 c (cost=0.00..1.00 rows=1 width=16) (actual time=0.047..0.099 rows=52 loops=1)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 262144 Batches: 1 Memory Usage: 2048kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (actual time=0.438..0.582 rows=100 loops=1)
Hash Key: d.c2
-> Seq Scan on t2 d (cost=0.00..1.00 rows=1 width=16) (actual time=0.048..0.101 rows=52 loops=1)
Planning Time: 3.474 ms
(slice0) Executor memory: 75K bytes.
(slice1) Executor memory: 2096K bytes avg x 2 workers, 2099K bytes max (seg0). Work_mem: 2048K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.901 ms
(26 rows)
abort;
-- partitioned table should have the same numsegments for parent and children
-- even in RANDOM mode.
select gp_debug_set_create_table_default_numsegments('random');
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
RANDOM
(1 row)
begin;
create table t (c1 int, c2 int) distributed by (c1)
partition by range(c2) (start(0) end(20) every(1));
-- verify that parent and children have the same numsegments
select count(a.localoid)
from gp_distribution_policy a
join pg_class c
on a.localoid = c.oid
and c.relname like 't_1_prt_%'
join gp_distribution_policy b
on a.numsegments = b.numsegments
and b.localoid = 't'::regclass
;
count
-------
20
(1 row)
abort;
select gp_debug_reset_create_table_default_numsegments();
gp_debug_reset_create_table_default_numsegments
-------------------------------------------------
(1 row)
-- verify numsegments in subplans
:explain select * from t1, t2
where t1.c1 > any (select max(t2.c1) from t2 where t2.c2 = t1.c2)
and t2.c1 > any (select max(t1.c1) from t1 where t1.c2 = t2.c2);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=10000000000.00..10000000002.10 rows=4 width=32) (actual time=1.020..1.020 rows=0 loops=1)
-> Nested Loop (cost=10000000000.00..10000000002.03 rows=4 width=32) (never executed)
-> Seq Scan on t1 (cost=0.00..1.00 rows=1 width=16) (never executed)
Filter: (SubPlan 1)
SubPlan 1
-> Aggregate (cost=1.04..1.05 rows=1 width=4) (never executed)
-> Result (cost=0.00..1.03 rows=1 width=4) (never executed)
Filter: (t2_1.c2 = t1.c2)
-> Materialize (cost=0.00..1.02 rows=1 width=8) (never executed)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=8) (never executed)
-> Seq Scan on t2 t2_1 (cost=0.00..1.00 rows=1 width=8) (never executed)
-> Materialize (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Broadcast Motion 2:1 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on t2 (cost=0.00..1.00 rows=1 width=16) (never executed)
Filter: (SubPlan 2)
SubPlan 2
-> Aggregate (cost=1.04..1.05 rows=1 width=4) (never executed)
-> Result (cost=0.00..1.03 rows=1 width=4) (never executed)
Filter: (t1_1.c2 = t2.c2)
-> Materialize (cost=0.00..1.02 rows=1 width=8) (never executed)
-> Broadcast Motion 1:2 (slice4; segments: 1) (cost=0.00..1.02 rows=1 width=8) (never executed)
-> Seq Scan on t1 t1_1 (cost=0.00..1.00 rows=1 width=8) (never executed)
Planning Time: 1.044 ms
(slice0) Executor memory: 90K bytes.
(slice1) Executor memory: 51K bytes (seg0).
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 51K bytes avg x 2 workers, 51K bytes max (seg0).
(slice4) Executor memory: 38K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 7.309 ms
(31 rows)
--
-- It is used to test this case:
-- A: replicated table, distributed on 2 segments
-- B: replicated table, distributed on 1 segments
-- UPDATE A SET XXX FROM B WHERE XXX;
-- We have to add a broadcast motion on B so that A can update/delete correctly.
--
begin;
insert into d1 select i,i,i,i from generate_series(1,2) i;
insert into d2 select i,i,i,i from generate_series(1,3) i;
explain update d2 a set c3=b.c3 from d1 b returning *;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Explicit Gather Motion 2:1 (slice1; segments: 2) (cost=10000000002.06..10000000002.06 rows=4 width=44)
-> Update on d2 a (cost=10000000000.00..10000000002.06 rows=2 width=44)
-> Nested Loop (cost=10000000000.00..10000000002.06 rows=2 width=44)
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=22)
-> Materialize (cost=0.00..1.04 rows=1 width=22)
-> Broadcast Motion 1:2 (slice2; segments: 1) (cost=0.00..1.03 rows=2 width=22)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=22)
Optimizer: Postgres query optimizer
(8 rows)
update d2 a set c3=b.c3 from d1 b returning *;
c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4
----+----+----+----+----+----+----+----
1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
2 | 2 | 1 | 2 | 1 | 1 | 1 | 1
3 | 3 | 1 | 3 | 1 | 1 | 1 | 1
(3 rows)
explain update d1 a set c3=b.c3 from d2 b returning *;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Explicit Gather Motion 1:1 (slice1; segments: 1) (cost=10000000002.01..10000000002.01 rows=4 width=44)
-> Update on d1 a (cost=10000000000.00..10000000002.01 rows=4 width=44)
-> Nested Loop (cost=10000000000.00..10000000002.01 rows=4 width=44)
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=22)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=22)
Optimizer: Postgres query optimizer
(6 rows)
update d1 a set c3=b.c3 from d2 b returning *;
c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4
----+----+----+----+----+----+----+----
1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
2 | 2 | 1 | 2 | 1 | 1 | 1 | 1
(2 rows)
abort;
-- restore the analyze information
analyze d1;
analyze d2;
--
-- create table: LIKE, INHERITS and DISTRIBUTED BY
--
-- tables are always created with DEFAULT as numsegments,
-- no matter there is LIKE, INHERITS or DISTRIBUTED BY.
select gp_debug_set_create_table_default_numsegments(2);
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
2
(1 row)
-- none of the clauses
create table t ();
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry.
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | | p | 2
(1 row)
drop table t;
-- DISTRIBUTED BY only
create table t () distributed randomly;
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | | p | 2
(1 row)
drop table t;
-- INHERITS only
create table t () inherits (t2);
NOTICE: table has parent, setting distribution columns to match parent table
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | 1 2 | p | 2
(1 row)
drop table t;
-- LIKE only
create table t (like d1);
NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | | r | 2
(1 row)
drop table t;
-- DISTRIBUTED BY + INHERITS
create table t () inherits (t2) distributed randomly;
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | | p | 2
(1 row)
drop table t;
-- DISTRIBUTED BY + LIKE
create table t (like d1) distributed randomly;
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | | p | 2
(1 row)
drop table t;
-- INHERITS + LIKE
create table t (like d1) inherits (t2);
NOTICE: table has parent, setting distribution columns to match parent table
NOTICE: merging column "c1" with inherited definition
NOTICE: merging column "c2" with inherited definition
NOTICE: merging column "c3" with inherited definition
NOTICE: merging column "c4" with inherited definition
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | 1 2 | p | 2
(1 row)
drop table t;
-- DISTRIBUTED BY + INHERITS + LIKE
create table t (like d1) inherits (t2) distributed randomly;
NOTICE: merging column "c1" with inherited definition
NOTICE: merging column "c2" with inherited definition
NOTICE: merging column "c3" with inherited definition
NOTICE: merging column "c4" with inherited definition
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | | p | 2
(1 row)
drop table t;
-- INHERITS from multiple parents
create table t () inherits (r1, t2);
NOTICE: table has parent, setting distribution columns to match parent table
NOTICE: merging multiple inherited definitions of column "c1"
NOTICE: merging multiple inherited definitions of column "c2"
NOTICE: merging multiple inherited definitions of column "c3"
NOTICE: merging multiple inherited definitions of column "c4"
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | | p | 2
(1 row)
drop table t;
-- DISTRIBUTED BY + INHERITS from multiple parents
create table t () inherits (r1, t2) distributed by (c1);
NOTICE: merging multiple inherited definitions of column "c1"
NOTICE: merging multiple inherited definitions of column "c2"
NOTICE: merging multiple inherited definitions of column "c3"
NOTICE: merging multiple inherited definitions of column "c4"
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | 1 | p | 2
(1 row)
drop table t;
select gp_debug_reset_create_table_default_numsegments();
gp_debug_reset_create_table_default_numsegments
-------------------------------------------------
(1 row)
-- CTAS set numsegments with DEFAULT,
-- let it be a fixed value to get stable output
select gp_debug_set_create_table_default_numsegments('full');
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
FULL
(1 row)
create table t as table t1;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'c1, c2' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | 1 2 | p | 3
(1 row)
drop table t;
create table t as select * from t1;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'c1, c2' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | 1 2 | p | 3
(1 row)
drop table t;
create table t as select * from t1 distributed by (c1, c2);
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | 1 2 | p | 3
(1 row)
drop table t;
create table t as select * from t1 distributed replicated;
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | | r | 3
(1 row)
drop table t;
create table t as select * from t1 distributed randomly;
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | | p | 3
(1 row)
drop table t;
select * into table t from t1;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'c1, c2' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | 1 2 | p | 3
(1 row)
drop table t;
select gp_debug_reset_create_table_default_numsegments();
gp_debug_reset_create_table_default_numsegments
-------------------------------------------------
(1 row)
--
-- alter table
--
-- numsegments should not be changed
select gp_debug_set_create_table_default_numsegments(1);
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
1
(1 row)
create table t (like t1);
NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | 1 2 | p | 1
(1 row)
alter table t set distributed replicated;
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | | r | 1
(1 row)
alter table t set distributed randomly;
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | | p | 1
(1 row)
alter table t set distributed by (c1, c2);
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | 1 2 | p | 1
(1 row)
alter table t add column c10 int;
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | 1 2 | p | 1
(1 row)
alter table t alter column c10 type text;
select localoid::regclass, distkey, policytype, numsegments
from gp_distribution_policy where localoid in ('t'::regclass);
localoid | distkey | policytype | numsegments
----------+---------+------------+-------------
t | 1 2 | p | 1
(1 row)
drop table t;
select gp_debug_reset_create_table_default_numsegments();
gp_debug_reset_create_table_default_numsegments
-------------------------------------------------
(1 row)
-- below join cases cover all the combinations of
--
-- select * from {t,d,r}{1,2} a
-- {left,} join {t,d,r}{1,2} b
-- using (c1{',c2',});
--
-- there might be some duplicated ones, like 't1 join d1' and 'd1 join t1',
-- or 'd1 join r1 using (c1)' and 'd1 join r1 using (c1, c2)', this is because
-- we generate them via scripts and do not clean them up manually.
--
-- please do not remove the duplicated ones as we care about the motion
-- direction of different join orders, e.g. 't2 join t1' and 't1 join t2'
-- should both distribute t2 to t1.
--
-- JOIN
--
-- x1 join y1
:explain select * from t1 a join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=28) (actual time=0.902..0.902 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.347 ms
(slice0) Executor memory: 37K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.132 ms
(12 rows)
:explain select * from t1 a join t1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.820..0.820 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.427 ms
(slice0) Executor memory: 38K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 1.957 ms
(12 rows)
:explain select * from t1 a join d1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=28) (actual time=0.949..0.950 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.380 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.215 ms
(12 rows)
:explain select * from t1 a join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.926..0.927 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.391 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.217 ms
(12 rows)
:explain select * from t1 a join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.11 rows=4 width=28) (actual time=0.972..0.972 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 3.154 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.202 ms
(12 rows)
:explain select * from t1 a join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.969..0.970 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.381 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.194 ms
(12 rows)
:explain select * from d1 a join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=28) (actual time=0.921..0.922 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.309 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.192 ms
(12 rows)
:explain select * from d1 a join t1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.938..0.939 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.579 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.185 ms
(12 rows)
:explain select * from d1 a join d1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.05..2.05 rows=4 width=28) (actual time=0.834..0.835 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.332 ms
(slice0) Executor memory: 37K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 1.965 ms
(12 rows)
:explain select * from d1 a join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.06..2.06 rows=4 width=24) (actual time=0.802..0.802 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.372 ms
(slice0) Executor memory: 38K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 1.982 ms
(12 rows)
:explain select * from d1 a join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.11 rows=4 width=28) (actual time=0.993..0.993 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.827 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.213 ms
(12 rows)
:explain select * from d1 a join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.973..0.973 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.930 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.199 ms
(12 rows)
:explain select * from r1 a join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.11 rows=4 width=28) (actual time=0.963..0.963 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.851 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 40K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.172 ms
(12 rows)
:explain select * from r1 a join t1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.948..0.949 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.011 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 40K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.094 ms
(12 rows)
:explain select * from r1 a join d1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.11 rows=4 width=28) (actual time=0.943..0.943 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.836 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 40K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.135 ms
(12 rows)
:explain select * from r1 a join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.987..0.987 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.933 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 40K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.169 ms
(12 rows)
:explain select * from r1 a join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=28) (actual time=0.873..0.874 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.220 ms
(slice0) Executor memory: 38K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 1.997 ms
(12 rows)
:explain select * from r1 a join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.875..0.876 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.339 ms
(slice0) Executor memory: 38K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 1.948 ms
(12 rows)
-- x1 join y2
:explain select * from t1 a join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=28) (actual time=4.534..4.535 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.242 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 4121K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.856 ms
(15 rows)
:explain select * from t1 a join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.04..2.13 rows=3 width=24) (actual time=8.944..8.944 rows=0 loops=1)
-> Hash Join (cost=1.04..2.08 rows=2 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Seq Scan on t2 b (cost=0.00..1.01 rows=1 width=16) (never executed)
-> Hash (cost=1.03..1.03 rows=1 width=16) (never executed)
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.03 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on t1 a (cost=0.00..1.01 rows=1 width=16) (never executed)
Planning time: 0.630 ms
(slice0) Executor memory: 53K bytes.
(slice1) Executor memory: 4124K bytes avg x 2 workers, 4124K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution time: 13.952 ms
(15 rows)
:explain select * from t1 a join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=28) (actual time=1.011..1.012 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.332 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.206 ms
(12 rows)
:explain select * from t1 a join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.969..0.970 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.432 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.185 ms
(12 rows)
:explain select * from t1 a join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=28) (actual time=2.017..2.018 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.160 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 4121K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.230 ms
(15 rows)
:explain select * from t1 a join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=24) (actual time=1.959..1.960 rows=0 loops=1)
-> Hash Join (cost=1.01..2.08 rows=4 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.468 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4125K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.388 ms
(16 rows)
:explain select * from d1 a join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice3; segments: 1) (cost=1.03..2.09 rows=4 width=28) (actual time=2.033..2.033 rows=0 loops=1)
-> Hash Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 1:1 (slice1; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning time: 0.895 ms
(slice0) Executor memory: 135K bytes.
(slice1) Executor memory: 44K bytes (seg0).
(slice2) Executor memory: 44K bytes avg x 2 workers, 44K bytes max (seg0).
(slice3) Executor memory: 4184K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution time: 4.332 ms
(18 rows)
:explain select * from d1 a join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=5.086..5.087 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.168 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes avg x 2 workers, 4126K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 6.570 ms
(16 rows)
:explain select * from d1 a join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.05..2.05 rows=4 width=28) (actual time=1.069..1.070 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.473 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.461 ms
(12 rows)
:explain select * from d1 a join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.06..2.06 rows=4 width=24) (actual time=1.044..1.044 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.558 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.323 ms
(12 rows)
:explain select * from d1 a join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice3; segments: 1) (cost=1.03..2.09 rows=4 width=28) (actual time=2.127..2.127 rows=0 loops=1)
-> Hash Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 1:1 (slice1; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning time: 0.754 ms
(slice0) Executor memory: 135K bytes.
(slice1) Executor memory: 44K bytes (seg0).
(slice2) Executor memory: 44K bytes avg x 2 workers, 44K bytes max (seg0).
(slice3) Executor memory: 4184K bytes (seg0).
Memory used: 128000kB
Optimizer: legacy query optimizer
Execution time: 2.734 ms
(18 rows)
:explain select * from d1 a join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice3; segments: 1) (cost=1.03..2.10 rows=4 width=24) (actual time=3.548..3.548 rows=0 loops=1)
-> Hash Join (cost=1.03..2.10 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:1 (slice1; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning time: 1.075 ms
(slice0) Executor memory: 135K bytes.
(slice1) Executor memory: 44K bytes (seg0).
(slice2) Executor memory: 44K bytes avg x 2 workers, 44K bytes max (seg0).
(slice3) Executor memory: 4184K bytes (seg0).
Memory used: 128000kB
Optimizer: legacy query optimizer
Execution time: 4.174 ms
(18 rows)
:explain select * from r1 a join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=28) (actual time=2.200..2.201 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.663 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 4121K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.745 ms
(15 rows)
:explain select * from r1 a join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=4.970..4.970 rows=0 loops=1)
-> Hash Join (cost=1.01..2.08 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.867 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes avg x 2 workers, 4126K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 6.643 ms
(16 rows)
:explain select * from r1 a join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.11 rows=4 width=28) (actual time=1.223..1.224 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.134 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 40K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.836 ms
(12 rows)
:explain select * from r1 a join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=1.037..1.038 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.230 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 40K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.436 ms
(12 rows)
:explain select * from r1 a join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.13 rows=4 width=28) (actual time=2.395..2.396 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.989 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 4121K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.986 ms
(15 rows)
:explain select * from r1 a join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=24) (actual time=2.454..2.455 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=4 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.183 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 4125K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.929 ms
(15 rows)
-- x2 join y1
:explain select * from t2 a join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=28) (actual time=2.462..2.463 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.131 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4122K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.996 ms
(15 rows)
:explain select * from t2 a join t1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.04..2.13 rows=3 width=24) (actual time=5.997..5.997 rows=0 loops=1)
-> Hash Join (cost=1.04..2.08 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t2 a (cost=0.00..1.01 rows=1 width=16) (never executed)
-> Hash (cost=1.03..1.03 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.03 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on t1 b (cost=0.00..1.01 rows=1 width=16) (never executed)
Planning Time: 2.218 ms
(slice0) Executor memory: 45K bytes.
(slice1) Executor memory: 4121K bytes avg x 2 workers, 4121K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 7.689 ms
(16 rows)
:explain select * from t2 a join d1 b using (c1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.04..2.17 rows=3 width=28) (actual time=12.118..12.118 rows=0 loops=1)
-> Hash Join (cost=1.04..2.11 rows=3 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Redistribute Motion 1:1 (slice2; segments: 1) (cost=0.00..1.03 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on d1 b (cost=0.00..1.01 rows=1 width=16) (never executed)
-> Hash (cost=1.03..1.03 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:1 (slice3; segments: 2) (cost=0.00..1.03 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on t2 a (cost=0.00..1.01 rows=1 width=16) (never executed)
Planning Time: 0.918 ms
(slice0) Executor memory: 45K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 12.955 ms
(19 rows)
:explain select * from t2 a join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=2.615..2.615 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=2 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.255 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4125K bytes avg x 2 workers, 4125K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.180 ms
(16 rows)
:explain select * from t2 a join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=28) (actual time=2.511..2.511 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.605 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4122K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.088 ms
(15 rows)
:explain select * from t2 a join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=3.837..3.837 rows=0 loops=1)
-> Hash Join (cost=1.01..2.08 rows=2 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.840 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4125K bytes avg x 2 workers, 4125K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.512 ms
(16 rows)
:explain select * from d2 a join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=28) (actual time=0.981..0.982 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.502 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.415 ms
(12 rows)
:explain select * from d2 a join t1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=1.013..1.013 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.564 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.363 ms
(12 rows)
:explain select * from d2 a join d1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.05..2.05 rows=4 width=28) (actual time=0.974..0.974 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.455 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.288 ms
(12 rows)
:explain select * from d2 a join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.06..2.06 rows=4 width=24) (actual time=1.025..1.026 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.533 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.360 ms
(12 rows)
:explain select * from d2 a join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.11 rows=4 width=28) (actual time=0.998..0.998 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.060 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.338 ms
(12 rows)
:explain select * from d2 a join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.933..0.934 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.161 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.127 ms
(12 rows)
:explain select * from r2 a join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=28) (actual time=2.247..2.248 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.265 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4122K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.608 ms
(15 rows)
:explain select * from r2 a join t1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=24) (actual time=2.288..2.288 rows=0 loops=1)
-> Hash Join (cost=1.01..2.08 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.453 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.716 ms
(16 rows)
:explain select * from r2 a join d1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.16 rows=4 width=28) (actual time=3.371..3.371 rows=0 loops=1)
-> Hash Join (cost=1.03..2.09 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.345 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.845 ms
(19 rows)
:explain select * from r2 a join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.16 rows=4 width=24) (actual time=2.862..2.863 rows=0 loops=1)
-> Hash Join (cost=1.03..2.10 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.447 ms
(slice0) Executor memory: 47K bytes.
(slice1) Executor memory: 4122K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.385 ms
(19 rows)
:explain select * from r2 a join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.13 rows=4 width=28) (actual time=2.160..2.161 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.816 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4122K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.570 ms
(15 rows)
:explain select * from r2 a join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=24) (actual time=2.410..2.411 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.076 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.004 ms
(15 rows)
-- x2 join y2
:explain select * from t2 a join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.16 rows=4 width=28) (actual time=2.484..2.485 rows=0 loops=1)
-> Hash Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.820 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4119K bytes avg x 2 workers, 4119K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.124 ms
(19 rows)
:explain select * from t2 a join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.12 rows=4 width=24) (actual time=0.975..0.976 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.804 ms
(slice0) Executor memory: 38K bytes.
(slice1) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.317 ms
(12 rows)
:explain select * from t2 a join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.11 rows=4 width=28) (actual time=1.046..1.047 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=2 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.178 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 40K bytes avg x 2 workers, 40K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.367 ms
(12 rows)
:explain select * from t2 a join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.12 rows=4 width=24) (actual time=1.034..1.035 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=2 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.262 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 40K bytes avg x 2 workers, 40K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.360 ms
(12 rows)
:explain select * from t2 a join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.15 rows=4 width=28) (actual time=3.770..3.770 rows=0 loops=1)
-> Hash Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.160 ms
(slice0) Executor memory: 45K bytes.
(slice1) Executor memory: 4119K bytes avg x 2 workers, 4119K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.619 ms
(19 rows)
:explain select * from t2 a join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=3.870..3.870 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=2 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.288 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4125K bytes avg x 2 workers, 4125K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.601 ms
(16 rows)
:explain select * from d2 a join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.11 rows=4 width=28) (actual time=1.116..1.117 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.226 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes avg x 2 workers, 41K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.846 ms
(12 rows)
:explain select * from d2 a join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.12 rows=4 width=24) (actual time=1.048..1.048 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.278 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes avg x 2 workers, 41K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.478 ms
(12 rows)
:explain select * from d2 a join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.05..2.05 rows=4 width=28) (actual time=1.019..1.019 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.752 ms
(slice0) Executor memory: 37K bytes.
(slice1) Executor memory: 37K bytes (seg1).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.366 ms
(12 rows)
:explain select * from d2 a join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.06..2.06 rows=4 width=24) (actual time=0.980..0.980 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.561 ms
(slice0) Executor memory: 38K bytes.
(slice1) Executor memory: 37K bytes (seg1).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.185 ms
(12 rows)
:explain select * from d2 a join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.12 rows=4 width=28) (actual time=1.005..1.006 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.467 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes avg x 2 workers, 41K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.397 ms
(12 rows)
:explain select * from d2 a join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.12 rows=4 width=24) (actual time=0.987..0.987 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.545 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes avg x 2 workers, 41K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.321 ms
(12 rows)
:explain select * from r2 a join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.15 rows=4 width=28) (actual time=3.113..3.113 rows=0 loops=1)
-> Hash Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.107 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes avg x 2 workers, 4119K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.821 ms
(19 rows)
:explain select * from r2 a join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=2.583..2.584 rows=0 loops=1)
-> Hash Join (cost=1.01..2.07 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.284 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes avg x 2 workers, 4126K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.111 ms
(16 rows)
:explain select * from r2 a join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.12 rows=4 width=28) (actual time=0.986..0.986 rows=0 loops=1)
-> Hash Join (cost=1.01..2.05 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.519 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes avg x 2 workers, 41K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.333 ms
(12 rows)
:explain select * from r2 a join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.12 rows=4 width=24) (actual time=0.990..0.991 rows=0 loops=1)
-> Hash Join (cost=1.01..2.06 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.540 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes avg x 2 workers, 41K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.313 ms
(12 rows)
:explain select * from r2 a join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.16 rows=4 width=28) (actual time=3.680..3.681 rows=0 loops=1)
-> Hash Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.505 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4119K bytes avg x 2 workers, 4119K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.208 ms
(19 rows)
:explain select * from r2 a join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.16 rows=4 width=24) (actual time=3.184..3.185 rows=0 loops=1)
-> Hash Join (cost=1.03..2.10 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.624 ms
(slice0) Executor memory: 47K bytes.
(slice1) Executor memory: 4122K bytes avg x 2 workers, 4122K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.828 ms
(19 rows)
-- x1 left join y1
:explain select * from t1 a left join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=28) (actual time=0.929..0.929 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.584 ms
(slice0) Executor memory: 37K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.162 ms
(12 rows)
:explain select * from t1 a left join t1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.961..0.962 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.599 ms
(slice0) Executor memory: 38K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.160 ms
(12 rows)
:explain select * from t1 a left join d1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=28) (actual time=1.025..1.025 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.525 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.307 ms
(12 rows)
:explain select * from t1 a left join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=1.060..1.061 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.568 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.358 ms
(12 rows)
:explain select * from t1 a left join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.11 rows=4 width=28) (actual time=1.189..1.189 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.508 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.948 ms
(12 rows)
:explain select * from t1 a left join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=1.128..1.128 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.05 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.334 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.551 ms
(12 rows)
:explain select * from d1 a left join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.16 rows=4 width=28) (actual time=3.594..3.595 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 1:1 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.817 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg0).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.306 ms
(19 rows)
:explain select * from d1 a left join t1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=24) (actual time=2.972..2.973 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.08 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:1 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.627 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.554 ms
(16 rows)
:explain select * from d1 a left join d1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.05..2.05 rows=4 width=28) (actual time=0.901..0.901 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.515 ms
(slice0) Executor memory: 37K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.212 ms
(12 rows)
:explain select * from d1 a left join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.06..2.06 rows=4 width=24) (actual time=0.904..0.905 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.552 ms
(slice0) Executor memory: 38K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.189 ms
(12 rows)
:explain select * from d1 a left join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.15 rows=4 width=28) (actual time=3.508..3.508 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 1:1 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.254 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg0).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.144 ms
(19 rows)
:explain select * from d1 a left join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.16 rows=4 width=24) (actual time=3.818..3.818 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:1 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.293 ms
(slice0) Executor memory: 47K bytes.
(slice1) Executor memory: 4122K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg0).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.480 ms
(19 rows)
:explain select * from r1 a left join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.11 rows=4 width=28) (actual time=2.189..2.190 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.179 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 4120K bytes (seg0). Work_mem: 4096K bytes max.
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.599 ms
(13 rows)
:explain select * from r1 a left join t1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=2.244..2.244 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.05 rows=4 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.323 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 4124K bytes (seg0). Work_mem: 4096K bytes max.
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.663 ms
(13 rows)
:explain select * from r1 a left join d1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.11 rows=4 width=28) (actual time=2.322..2.323 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.147 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 4120K bytes (seg0). Work_mem: 4096K bytes max.
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.694 ms
(13 rows)
:explain select * from r1 a left join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=2.139..2.139 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.05 rows=4 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.418 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 4124K bytes (seg0). Work_mem: 4096K bytes max.
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.493 ms
(13 rows)
:explain select * from r1 a left join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=28) (actual time=0.885..0.885 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.608 ms
(slice0) Executor memory: 38K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.069 ms
(12 rows)
:explain select * from r1 a left join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.860..0.860 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.688 ms
(slice0) Executor memory: 38K bytes.
(slice1) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.010 ms
(12 rows)
-- x1 left join y2
:explain select * from t1 a left join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=28) (actual time=2.243..2.243 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.088 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 4121K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.603 ms
(15 rows)
:explain select * from t1 a left join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.04..2.13 rows=3 width=24) (actual time=3.689..3.689 rows=0 loops=1)
-> Hash Right Join (cost=1.04..2.08 rows=2 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Seq Scan on t2 b (cost=0.00..1.01 rows=1 width=16) (never executed)
-> Hash (cost=1.03..1.03 rows=1 width=16) (never executed)
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.03 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on t1 a (cost=0.00..1.01 rows=1 width=16) (never executed)
Planning time: 0.826 ms
(slice0) Executor memory: 53K bytes.
(slice1) Executor memory: 4124K bytes avg x 2 workers, 4124K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution time: 8.014 ms
(15 rows)
:explain select * from t1 a left join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=28) (actual time=0.991..0.991 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.524 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.555 ms
(12 rows)
:explain select * from t1 a left join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=0.876..0.876 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.873 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.193 ms
(12 rows)
:explain select * from t1 a left join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=28) (actual time=2.183..2.183 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.777 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 4121K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.840 ms
(15 rows)
:explain select * from t1 a left join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=24) (actual time=2.194..2.195 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.08 rows=4 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.724 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4125K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.722 ms
(16 rows)
:explain select * from d1 a left join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice3; segments: 1) (cost=1.03..2.09 rows=4 width=28) (actual time=3.615..3.615 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 1:1 (slice1; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning time: 1.058 ms
(slice0) Executor memory: 135K bytes.
(slice1) Executor memory: 44K bytes (seg0).
(slice2) Executor memory: 44K bytes avg x 2 workers, 44K bytes max (seg0).
(slice3) Executor memory: 4184K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution time: 4.414 ms
(18 rows)
:explain select * from d1 a left join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=2.355..2.355 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.07 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.331 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes avg x 2 workers, 4126K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.894 ms
(16 rows)
:explain select * from d1 a left join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.05..2.05 rows=4 width=28) (actual time=1.069..1.069 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.484 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.400 ms
(12 rows)
:explain select * from d1 a left join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.06..2.06 rows=4 width=24) (actual time=1.014..1.015 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.569 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.345 ms
(12 rows)
:explain select * from d1 a left join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice3; segments: 1) (cost=1.03..2.09 rows=4 width=28) (actual time=3.753..3.753 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 1:1 (slice1; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning time: 0.878 ms
(slice0) Executor memory: 135K bytes.
(slice1) Executor memory: 44K bytes (seg0).
(slice2) Executor memory: 44K bytes avg x 2 workers, 44K bytes max (seg0).
(slice3) Executor memory: 4184K bytes (seg0).
Memory used: 128000kB
Optimizer: legacy query optimizer
Execution time: 4.500 ms
(18 rows)
:explain select * from d1 a left join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice3; segments: 1) (cost=1.03..2.10 rows=4 width=24) (actual time=3.582..3.582 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.10 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:1 (slice1; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on d1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning time: 1.383 ms
(slice0) Executor memory: 135K bytes.
(slice1) Executor memory: 44K bytes (seg0).
(slice2) Executor memory: 44K bytes avg x 2 workers, 44K bytes max (seg0).
(slice3) Executor memory: 4184K bytes (seg0).
Memory used: 128000kB
Optimizer: legacy query optimizer
Execution time: 4.357 ms
(18 rows)
:explain select * from r1 a left join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=28) (actual time=2.237..2.238 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.720 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 4121K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.739 ms
(15 rows)
:explain select * from r1 a left join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=2.351..2.351 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.08 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 3.491 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes avg x 2 workers, 4126K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.891 ms
(16 rows)
:explain select * from r1 a left join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.11 rows=4 width=28) (actual time=2.248..2.249 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.175 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 4120K bytes (seg0). Work_mem: 4096K bytes max.
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.657 ms
(13 rows)
:explain select * from r1 a left join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.12 rows=4 width=24) (actual time=2.282..2.283 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.05 rows=4 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.281 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 4124K bytes (seg0). Work_mem: 4096K bytes max.
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.709 ms
(13 rows)
:explain select * from r1 a left join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.13 rows=4 width=28) (actual time=2.330..2.330 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.07 rows=4 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.378 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 4121K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.963 ms
(15 rows)
:explain select * from r1 a left join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=24) (actual time=2.468..2.468 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.07 rows=4 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Broadcast Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on r1 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.318 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4125K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.897 ms
(15 rows)
-- x2 left join y1
:explain select * from t2 a left join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.04..2.17 rows=3 width=28) (actual time=3.356..3.356 rows=0 loops=1)
-> Hash Right Join (cost=1.04..2.11 rows=3 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Redistribute Motion 1:1 (slice2; segments: 1) (cost=0.00..1.03 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on t1 b (cost=0.00..1.01 rows=1 width=16) (never executed)
-> Hash (cost=1.03..1.03 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:1 (slice3; segments: 2) (cost=0.00..1.03 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on t2 a (cost=0.00..1.01 rows=1 width=16) (never executed)
Planning Time: 0.827 ms
(slice0) Executor memory: 45K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.938 ms
(19 rows)
:explain select * from t2 a left join t1 b using (c1, c2);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.04..2.13 rows=3 width=24) (actual time=14.945..14.945 rows=0 loops=1)
-> Hash Left Join (cost=1.04..2.08 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t2 a (cost=0.00..1.01 rows=1 width=16) (never executed)
-> Hash (cost=1.03..1.03 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.03 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on t1 b (cost=0.00..1.01 rows=1 width=16) (never executed)
Planning Time: 1.154 ms
(slice0) Executor memory: 45K bytes.
(slice1) Executor memory: 4125K bytes avg x 2 workers, 4125K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 24.481 ms
(16 rows)
:explain select * from t2 a left join d1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.04..2.17 rows=3 width=28) (actual time=1.776..1.776 rows=0 loops=1)
-> Hash Right Join (cost=1.04..2.11 rows=3 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Redistribute Motion 1:1 (slice2; segments: 1) (cost=0.00..1.03 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on d1 b (cost=0.00..1.01 rows=1 width=16) (never executed)
-> Hash (cost=1.03..1.03 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:1 (slice3; segments: 2) (cost=0.00..1.03 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on t2 a (cost=0.00..1.01 rows=1 width=16) (never executed)
Planning Time: 0.818 ms
(slice0) Executor memory: 45K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.950 ms
(19 rows)
:explain select * from t2 a left join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=2.339..2.340 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.07 rows=2 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.319 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4125K bytes avg x 2 workers, 4125K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.904 ms
(16 rows)
:explain select * from t2 a left join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.16 rows=4 width=28) (actual time=2.956..2.956 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.785 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.640 ms
(19 rows)
:explain select * from t2 a left join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=2.354..2.355 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.08 rows=2 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.895 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4125K bytes avg x 2 workers, 4125K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.945 ms
(16 rows)
:explain select * from d2 a left join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.16 rows=4 width=28) (actual time=3.094..3.095 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 1:1 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.641 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg1).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.697 ms
(19 rows)
:explain select * from d2 a left join t1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=24) (actual time=2.175..2.175 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.08 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:1 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.611 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg1).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.733 ms
(16 rows)
:explain select * from d2 a left join d1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.05..2.05 rows=4 width=28) (actual time=1.049..1.049 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.699 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.517 ms
(12 rows)
:explain select * from d2 a left join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.06..2.06 rows=4 width=24) (actual time=1.053..1.053 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.659 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.509 ms
(12 rows)
:explain select * from d2 a left join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.15 rows=4 width=28) (actual time=3.505..3.506 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 1:1 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.619 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg1).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.410 ms
(19 rows)
:explain select * from d2 a left join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.16 rows=4 width=24) (actual time=3.284..3.285 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:1 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.389 ms
(slice0) Executor memory: 47K bytes.
(slice1) Executor memory: 4122K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg1).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.039 ms
(19 rows)
:explain select * from r2 a left join t1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.16 rows=4 width=28) (actual time=2.857..2.857 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.535 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.548 ms
(19 rows)
:explain select * from r2 a left join t1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.01..2.14 rows=4 width=24) (actual time=2.958..2.958 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.08 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.608 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.653 ms
(16 rows)
:explain select * from r2 a left join d1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.16 rows=4 width=28) (actual time=3.035..3.035 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.531 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.778 ms
(19 rows)
:explain select * from r2 a left join d1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.16 rows=4 width=24) (actual time=3.223..3.224 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.10 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on d1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.736 ms
(slice0) Executor memory: 47K bytes.
(slice1) Executor memory: 4122K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.926 ms
(19 rows)
:explain select * from r2 a left join r1 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.15 rows=4 width=28) (actual time=2.919..2.920 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.128 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.763 ms
(19 rows)
:explain select * from r2 a left join r1 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=1.03..2.16 rows=4 width=24) (actual time=2.944..2.945 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 2:1 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 1:1 (slice3; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r1 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.820 ms
(slice0) Executor memory: 47K bytes.
(slice1) Executor memory: 4123K bytes (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.613 ms
(19 rows)
-- x2 left join y2
:explain select * from t2 a left join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.16 rows=4 width=28) (actual time=3.265..3.265 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.801 ms
(slice0) Executor memory: 44K bytes.
(slice1) Executor memory: 4119K bytes avg x 2 workers, 4119K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.948 ms
(19 rows)
:explain select * from t2 a left join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.12 rows=4 width=24) (actual time=0.961..0.962 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.06 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.789 ms
(slice0) Executor memory: 39K bytes.
(slice1) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.309 ms
(12 rows)
:explain select * from t2 a left join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.11 rows=4 width=28) (actual time=2.480..2.481 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.05 rows=2 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.116 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 4120K bytes avg x 2 workers, 4120K bytes max (seg0). Work_mem: 4096K bytes max.
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.948 ms
(13 rows)
:explain select * from t2 a left join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.12 rows=4 width=24) (actual time=2.462..2.463 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.05 rows=2 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.286 ms
(slice0) Executor memory: 40K bytes.
(slice1) Executor memory: 4124K bytes avg x 2 workers, 4124K bytes max (seg0). Work_mem: 4096K bytes max.
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 3.982 ms
(13 rows)
:explain select * from t2 a left join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.15 rows=4 width=28) (actual time=3.137..3.137 rows=0 loops=1)
-> Hash Right Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (b.c1 = a.c1)
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.210 ms
(slice0) Executor memory: 45K bytes.
(slice1) Executor memory: 4119K bytes avg x 2 workers, 4119K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.849 ms
(19 rows)
:explain select * from t2 a left join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=2.444..2.445 rows=0 loops=1)
-> Hash Right Join (cost=1.01..2.07 rows=2 width=24) (never executed)
Hash Cond: ((b.c1 = a.c1) AND (b.c2 = a.c2))
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.298 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4125K bytes avg x 2 workers, 4125K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.103 ms
(16 rows)
:explain select * from d2 a left join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.15 rows=4 width=28) (actual time=3.713..3.714 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.177 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes avg x 2 workers, 4119K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg1).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.365 ms
(19 rows)
:explain select * from d2 a left join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=2.453..2.453 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.07 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.276 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes avg x 2 workers, 4126K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes (seg1).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.001 ms
(16 rows)
:explain select * from d2 a left join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.05..2.05 rows=4 width=28) (actual time=0.975..0.976 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.05 rows=4 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.506 ms
(slice0) Executor memory: 37K bytes.
(slice1) Executor memory: 37K bytes (seg1).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.338 ms
(12 rows)
:explain select * from d2 a left join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=2.06..2.06 rows=4 width=24) (actual time=0.967..0.967 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.06 rows=4 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.134 ms
(slice0) Executor memory: 38K bytes.
(slice1) Executor memory: 37K bytes (seg1).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.305 ms
(12 rows)
:explain select * from d2 a left join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.16 rows=4 width=28) (actual time=3.189..3.190 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.612 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes avg x 2 workers, 4119K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg1).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.896 ms
(19 rows)
:explain select * from d2 a left join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.16 rows=4 width=24) (actual time=3.033..3.033 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.10 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 1:2 (slice2; segments: 1) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on d2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.647 ms
(slice0) Executor memory: 47K bytes.
(slice1) Executor memory: 4122K bytes avg x 2 workers, 4122K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes (seg1).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.678 ms
(19 rows)
:explain select * from r2 a left join t2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.15 rows=4 width=28) (actual time=2.570..2.570 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.193 ms
(slice0) Executor memory: 46K bytes.
(slice1) Executor memory: 4119K bytes avg x 2 workers, 4119K bytes max (seg1). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.372 ms
(19 rows)
:explain select * from r2 a left join t2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.14 rows=4 width=24) (actual time=3.108..3.109 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.07 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Seq Scan on t2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 2.290 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4126K bytes avg x 2 workers, 4126K bytes max (seg1). Work_mem: 4096K bytes max.
(slice2) Executor memory: 36K bytes avg x 2 workers, 36K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 4.763 ms
(16 rows)
:explain select * from r2 a left join d2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.12 rows=4 width=28) (actual time=1.029..1.029 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.05 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.531 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes avg x 2 workers, 41K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.458 ms
(12 rows)
:explain select * from r2 a left join d2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.01..2.12 rows=4 width=24) (actual time=1.007..1.008 rows=0 loops=1)
-> Hash Left Join (cost=1.01..2.06 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.00..1.00 rows=1 width=16) (never executed)
-> Seq Scan on d2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.561 ms
(slice0) Executor memory: 42K bytes.
(slice1) Executor memory: 41K bytes avg x 2 workers, 41K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 2.402 ms
(12 rows)
:explain select * from r2 a left join r2 b using (c1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.16 rows=4 width=28) (actual time=3.310..3.311 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.09 rows=2 width=28) (never executed)
Hash Cond: (a.c1 = b.c1)
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.489 ms
(slice0) Executor memory: 43K bytes.
(slice1) Executor memory: 4119K bytes avg x 2 workers, 4119K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.016 ms
(19 rows)
:explain select * from r2 a left join r2 b using (c1, c2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=1.03..2.16 rows=4 width=24) (actual time=4.152..4.152 rows=0 loops=1)
-> Hash Left Join (cost=1.03..2.10 rows=2 width=24) (never executed)
Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2))
-> Redistribute Motion 2:2 (slice2; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: a.c1, a.c2
-> Seq Scan on r2 a (cost=0.00..1.00 rows=1 width=16) (never executed)
-> Hash (cost=1.02..1.02 rows=1 width=16) (never executed)
Buckets: 524288 Batches: 1 Memory Usage: 4096kB
-> Redistribute Motion 2:2 (slice3; segments: 2) (cost=0.00..1.02 rows=1 width=16) (never executed)
Hash Key: b.c1, b.c2
-> Seq Scan on r2 b (cost=0.00..1.00 rows=1 width=16) (never executed)
Planning Time: 1.644 ms
(slice0) Executor memory: 47K bytes.
(slice1) Executor memory: 4122K bytes avg x 2 workers, 4122K bytes max (seg0). Work_mem: 4096K bytes max.
(slice2) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
(slice3) Executor memory: 37K bytes avg x 2 workers, 37K bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 5.858 ms
(19 rows)
--
-- insert
--
insert into t1 (c1) values (1), (2), (3), (4), (5), (6)
returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into t2 (c1) values (1), (2), (3), (4), (5), (6)
returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
6 |
4 |
5 |
(6 rows)
insert into d1 (c1) values (1), (2), (3), (4), (5), (6)
returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into d2 (c1) values (1), (2), (3), (4), (5), (6)
returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into r1 (c1) values (1), (2), (3), (4), (5), (6)
returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into r2 (c1) values (1), (2), (3), (4), (5), (6)
returning c1, c2;
c1 | c2
----+----
1 |
3 |
4 |
5 |
6 |
2 |
(6 rows)
begin;
insert into t1 (c1) values (1) returning c1, c2;
c1 | c2
----+----
1 |
(1 row)
insert into d1 (c1) values (1) returning c1, c2;
c1 | c2
----+----
1 |
(1 row)
insert into r1 (c1) values (1) returning c1, c2;
c1 | c2
----+----
1 |
(1 row)
insert into t2 (c1) values (1) returning c1, c2;
c1 | c2
----+----
1 |
(1 row)
insert into d2 (c1) values (1) returning c1, c2;
c1 | c2
----+----
1 |
(1 row)
insert into r2 (c1) values (1) returning c1, c2;
c1 | c2
----+----
1 |
(1 row)
rollback;
begin;
insert into t1 (c1) select i from generate_series(1, 20) i
returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
18 |
19 |
20 |
(20 rows)
insert into d1 (c1) select i from generate_series(1, 20) i
returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
18 |
19 |
20 |
(20 rows)
insert into r1 (c1) select i from generate_series(1, 20) i
returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
18 |
19 |
20 |
(20 rows)
insert into t2 (c1) select i from generate_series(1, 20) i
returning c1, c2;
c1 | c2
----+----
4 |
5 |
11 |
12 |
13 |
14 |
19 |
1 |
2 |
3 |
6 |
7 |
8 |
9 |
10 |
15 |
16 |
17 |
18 |
20 |
(20 rows)
insert into d2 (c1) select i from generate_series(1, 20) i
returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
18 |
19 |
20 |
(20 rows)
insert into r2 (c1) select i from generate_series(1, 20) i
returning c1, c2;
c1 | c2
----+----
4 |
6 |
14 |
15 |
16 |
18 |
19 |
1 |
2 |
3 |
5 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
17 |
20 |
(20 rows)
rollback;
begin;
insert into t1 (c1, c2) select c1, c2 from t1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into t1 (c1, c2) select c2, c1 from t1 returning c1, c2;
c1 | c2
----+----
| 1
| 2
| 3
| 4
| 5
| 6
| 1
| 2
| 3
| 4
| 5
| 6
(12 rows)
insert into t1 (c1, c2) select c1, c2 from t2 returning c1, c2;
c1 | c2
----+----
4 |
5 |
1 |
2 |
3 |
6 |
(6 rows)
insert into t1 (c1, c2) select c2, c1 from t2 returning c1, c2;
c1 | c2
----+----
| 1
| 2
| 3
| 6
| 4
| 5
(6 rows)
insert into t1 (c1, c2) select c1, c2 from d1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into t1 (c1, c2) select c1, c2 from d2 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into t1 (c1, c2) select c1, c2 from r1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into t1 (c1, c2) select c1, c2 from r2 returning c1, c2;
c1 | c2
----+----
2 |
1 |
3 |
4 |
5 |
6 |
(6 rows)
rollback;
begin;
insert into t2 (c1, c2) select c1, c2 from t1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
6 |
4 |
5 |
(6 rows)
insert into t2 (c1, c2) select c2, c1 from t1 returning c1, c2;
c1 | c2
----+----
| 2
| 4
| 6
| 1
| 3
| 5
(6 rows)
insert into t2 (c1, c2) select c1, c2 from d1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
6 |
4 |
5 |
(6 rows)
insert into t2 (c1, c2) select c1, c2 from d2 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
6 |
4 |
5 |
(6 rows)
insert into t2 (c1, c2) select c1, c2 from r1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
6 |
4 |
5 |
(6 rows)
insert into t2 (c1, c2) select c1, c2 from r2 returning c1, c2;
c1 | c2
----+----
4 |
5 |
2 |
1 |
3 |
6 |
(6 rows)
rollback;
begin;
insert into d1 (c1, c2) select c1, c2 from t1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into d1 (c1, c2) select c2, c1 from t1 returning c1, c2;
c1 | c2
----+----
| 1
| 2
| 3
| 4
| 5
| 6
(6 rows)
insert into d1 (c1, c2) select c1, c2 from t2 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
6 |
4 |
5 |
(6 rows)
insert into d1 (c1, c2) select c2, c1 from t2 returning c1, c2;
c1 | c2
----+----
| 1
| 2
| 3
| 6
| 4
| 5
(6 rows)
insert into d1 (c1, c2) select c1, c2 from d1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
1 |
2 |
3 |
4 |
5 |
6 |
| 1
| 2
| 3
| 4
| 5
| 6
1 |
2 |
3 |
6 |
4 |
5 |
| 1
| 2
| 3
| 6
| 4
| 5
(30 rows)
insert into d1 (c1, c2) select c1, c2 from d2 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into d1 (c1, c2) select c1, c2 from r1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into d1 (c1, c2) select c1, c2 from r2 returning c1, c2;
c1 | c2
----+----
2 |
1 |
3 |
4 |
5 |
6 |
(6 rows)
rollback;
begin;
insert into d2 (c1, c2) select c1, c2 from t1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into d2 (c1, c2) select c2, c1 from t1 returning c1, c2;
c1 | c2
----+----
| 1
| 2
| 3
| 4
| 5
| 6
(6 rows)
insert into d2 (c1, c2) select c1, c2 from d1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into d2 (c1, c2) select c1, c2 from d2 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
1 |
2 |
3 |
4 |
5 |
6 |
| 1
| 2
| 3
| 4
| 5
| 6
1 |
2 |
3 |
4 |
5 |
6 |
(24 rows)
insert into d2 (c1, c2) select c1, c2 from r1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into d2 (c1, c2) select c1, c2 from r2 returning c1, c2;
c1 | c2
----+----
2 |
1 |
3 |
4 |
5 |
6 |
(6 rows)
rollback;
begin;
insert into r1 (c1, c2) select c1, c2 from t1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into r1 (c1, c2) select c2, c1 from t1 returning c1, c2;
c1 | c2
----+----
| 1
| 2
| 3
| 4
| 5
| 6
(6 rows)
insert into r1 (c1, c2) select c1, c2 from t2 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
6 |
4 |
5 |
(6 rows)
insert into r1 (c1, c2) select c2, c1 from t2 returning c1, c2;
c1 | c2
----+----
| 1
| 2
| 3
| 6
| 4
| 5
(6 rows)
insert into r1 (c1, c2) select c1, c2 from d1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into r1 (c1, c2) select c1, c2 from d2 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into r1 (c1, c2) select c1, c2 from r1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
1 |
2 |
3 |
4 |
5 |
6 |
| 1
| 2
| 3
| 4
| 5
| 6
1 |
2 |
3 |
6 |
4 |
5 |
| 1
| 2
| 3
| 6
| 4
| 5
1 |
2 |
3 |
4 |
5 |
6 |
1 |
2 |
3 |
4 |
5 |
6 |
(42 rows)
insert into r1 (c1, c2) select c1, c2 from r2 returning c1, c2;
c1 | c2
----+----
2 |
1 |
3 |
4 |
5 |
6 |
(6 rows)
rollback;
begin;
insert into r2 (c1, c2) select c1, c2 from t1 returning c1, c2;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
(6 rows)
insert into r2 (c1, c2) select c2, c1 from t1 returning c1, c2;
c1 | c2
----+----
| 1
| 2
| 4
| 5
| 6
| 3
(6 rows)
insert into r2 (c1, c2) select c1, c2 from d1 returning c1, c2;
c1 | c2
----+----
4 |
5 |
1 |
2 |
3 |
6 |
(6 rows)
insert into r2 (c1, c2) select c1, c2 from d2 returning c1, c2;
c1 | c2
----+----
4 |
5 |
1 |
2 |
3 |
6 |
(6 rows)
insert into r2 (c1, c2) select c1, c2 from r1 returning c1, c2;
c1 | c2
----+----
3 |
4 |
6 |
1 |
2 |
5 |
(6 rows)
insert into r2 (c1, c2) select c1, c2 from r2 returning c1, c2;
c1 | c2
----+----
2 |
1 |
6 |
| 5
5 |
4 |
5 |
4 |
6 |
1 |
2 |
3 |
6 |
1 |
2 |
3 |
6 |
3 |
4 |
2 |
3 |
4 |
5 |
| 1
| 2
| 4
| 6
4 |
5 |
1 |
2 |
1 |
3 |
5 |
| 3
6 |
(36 rows)
rollback;
--
-- pg_relation_size() dispatches an internal query, to fetch the relation's
-- size on each segment. The internal query doesn't need to be part of the
-- distributed transactin. Test that we correctly issue two-phase commit in
-- those segments that are affected by the INSERT, and that we don't try
-- to perform distributed commit on the other segments.
--
insert into r1 (c4) values (pg_relation_size('r2'));
--
-- copy to a partial replicated table from file should work
--
select gp_debug_set_create_table_default_numsegments(2);
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
2
(1 row)
create table partial_rpt_from (c1 int, c2 int) distributed replicated;
select gp_debug_reset_create_table_default_numsegments();
gp_debug_reset_create_table_default_numsegments
-------------------------------------------------
(1 row)
copy partial_rpt_from (c1, c2) from stdin with delimiter ',';
select * from gp_dist_random('partial_rpt_from');
c1 | c2
----+----
1 | 2
1 | 2
(2 rows)
--
-- copy from a partial replicated table to file should work
--
select gp_debug_set_create_table_default_numsegments(2);
gp_debug_set_create_table_default_numsegments
-----------------------------------------------
2
(1 row)
create table partial_rpt_to (c1 int, c2 int) distributed replicated;
select gp_debug_reset_create_table_default_numsegments();
gp_debug_reset_create_table_default_numsegments
-------------------------------------------------
(1 row)
insert into partial_rpt_to values (1,1);
copy partial_rpt_to to stdout;
1 1
-- change a replica to provide data
\c
set search_path=test_partial_table,public;
copy partial_rpt_to to stdout;
1 1
-- change to another replica to provide data
\c
set search_path=test_partial_table,public;
copy partial_rpt_to to stdout;
1 1
-- start_ignore
-- We need to do a cluster expansion which will check if there are partial
-- tables, we need to drop the partial tables to keep the cluster expansion
-- run correctly.
reset search_path;
drop schema test_partial_table cascade;
NOTICE: drop cascades to 9 other objects
DETAIL: drop cascades to table test_partial_table.t1
drop cascades to table test_partial_table.d1
drop cascades to table test_partial_table.r1
drop cascades to table test_partial_table.t2
drop cascades to table test_partial_table.d2
drop cascades to table test_partial_table.r2
drop cascades to table test_partial_table.size_sanity_check
drop cascades to table test_partial_table.partial_rpt_from
drop cascades to table test_partial_table.partial_rpt_to
-- end_ignore