set dolphin.b_compatibility_mode to off;
drop database if exists b_char_blank_test_2;
NOTICE: database "b_char_blank_test_2" does not exist, skipping
create database b_char_blank_test_2 encoding 'UTF-8' lc_collate 'C' lc_ctype 'C' dbcompatibility 'B';
\c b_char_blank_test_2
set b_format_behavior_compat_options = '';
---------------------------
----- column table
--------------------------
create table t_char1(a char(10), b varchar(10), c text) with (orientation=column);
insert into t_char1 values('1','1','1');
insert into t_char1 values(' 1 ',' 1 ',' 1 ');
insert into t_char1 values('1 ','1 ','1 ');
insert into t_char1 values('1 ','1 ','1 ');
insert into t_char1 values(' 1',' 1',' 1');
create table t_char2(a char(10), b varchar(10), c text) with (orientation=column);
insert into t_char2 values('1','1','1');
insert into t_char2 values(' 1 ',' 1 ',' 1 ');
insert into t_char2 values('1 ','1 ','1 ');
insert into t_char2 values('1 ','1 ','1 ');
insert into t_char2 values(' 1',' 1',' 1');
select * from t_char1 where a='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where a='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where a=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where a=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where a='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where b='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where b='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where b=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where b=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where b='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where c='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where c='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where c=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where c=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where c='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where a!='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where a!='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where a!=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where a!=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where a!='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where b!='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where b!='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where b!=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where b!=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where b!='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where c!='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where c!='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where c!=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where c!=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where c!='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where a>='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where a>='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where a>=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where a>=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where a>='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where b>='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where b>='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where b>=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where b>=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where b>='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where c>='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where c>='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where c>=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where c>=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where c>='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where a<='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where a<='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where a<=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where a<=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where a<='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where b<='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where b<='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where b<=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where b<=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where b<='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where c<='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where c<='1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where c<=' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where c<=' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where c<='1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(5 rows)
select * from t_char1 where a<'1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where a<'1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where a<' 1 ';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where a<' 1';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where a<'1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where b<'1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where b<'1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where b<' 1 ';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where b<' 1';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where b<'1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where c<'1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where c<'1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where c<' 1 ';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where c<' 1';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where c<'1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
(2 rows)
select * from t_char1 where a>'1 ';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where a>'1 ';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where a>' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where a>' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where a>'1';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where b>'1 ';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where b>'1 ';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where b>' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where b>' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where b>'1';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where c>'1 ';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where c>'1 ';
a | b | c
---+---+---
(0 rows)
select * from t_char1 where c>' 1 ';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where c>' 1';
a | b | c
------------+-----+-----
1 | 1 | 1
1 | 1 | 1
1 | 1 | 1
(3 rows)
select * from t_char1 where c>'1';
a | b | c
---+---+---
(0 rows)
--only when string_hash_compatible is on, then hash join will ignore tail blank
show string_hash_compatible;
string_hash_compatible
------------------------
on
(1 row)
set enable_hashjoin to on;
set enable_mergejoin to off;
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Hash Left Join
Hash Cond: (a.a = b.a)
-> CStore Scan on t_char1 a
-> CStore Scan on t_char2 b
(7 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Hash Left Join
Hash Cond: (a.a = (b.b)::bpchar)
-> CStore Scan on t_char1 a
-> CStore Scan on t_char2 b
(7 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Hash Left Join
Hash Cond: ((a.a)::text = b.c)
-> CStore Scan on t_char1 a
-> CStore Scan on t_char2 b
(7 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Hash Left Join
Hash Cond: ((a.b)::bpchar = b.a)
-> CStore Scan on t_char1 a
-> CStore Scan on t_char2 b
(7 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Hash Left Join
Hash Cond: ((a.b)::text = (b.b)::text)
-> CStore Scan on t_char1 a
-> CStore Scan on t_char2 b
(7 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Hash Left Join
Hash Cond: ((a.b)::text = b.c)
-> CStore Scan on t_char1 a
-> CStore Scan on t_char2 b
(7 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Hash Left Join
Hash Cond: (a.c = (b.a)::text)
-> CStore Scan on t_char1 a
-> CStore Scan on t_char2 b
(7 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Hash Left Join
Hash Cond: (a.c = (b.b)::text)
-> CStore Scan on t_char1 a
-> CStore Scan on t_char2 b
(7 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Hash Left Join
Hash Cond: (a.c = b.c)
-> CStore Scan on t_char1 a
-> CStore Scan on t_char2 b
(7 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
set enable_hashjoin to off;
set enable_mergejoin to on;
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Merge Left Join
Merge Cond: (a.a = b.a)
-> Vector Sort
Sort Key: a.a
-> CStore Scan on t_char1 a
-> Vector Sort
Sort Key: b.a
-> CStore Scan on t_char2 b
(11 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Merge Left Join
Merge Cond: (a.a = (b.b)::bpchar)
-> Vector Sort
Sort Key: a.a
-> CStore Scan on t_char1 a
-> Vector Sort
Sort Key: b.b USING <
-> CStore Scan on t_char2 b
(11 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Merge Left Join
Merge Cond: (((a.a)::text) = b.c)
-> Vector Sort
Sort Key: ((a.a)::text)
-> CStore Scan on t_char1 a
-> Vector Sort
Sort Key: b.c
-> CStore Scan on t_char2 b
(11 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Merge Left Join
Merge Cond: ((a.b)::bpchar = b.a)
-> Vector Sort
Sort Key: a.b USING <
-> CStore Scan on t_char1 a
-> Vector Sort
Sort Key: b.a
-> CStore Scan on t_char2 b
(11 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Merge Left Join
Merge Cond: ((a.b)::text = (b.b)::text)
-> Vector Sort
Sort Key: a.b
-> CStore Scan on t_char1 a
-> Vector Sort
Sort Key: b.b
-> CStore Scan on t_char2 b
(11 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Merge Left Join
Merge Cond: ((a.b)::text = b.c)
-> Vector Sort
Sort Key: a.b
-> CStore Scan on t_char1 a
-> Vector Sort
Sort Key: b.c
-> CStore Scan on t_char2 b
(11 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Merge Left Join
Merge Cond: (a.c = (b.a)::text)
-> Vector Sort
Sort Key: a.c
-> CStore Scan on t_char1 a
-> Vector Sort
Sort Key: ((b.a)::text)
-> CStore Scan on t_char2 b
(11 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Merge Left Join
Merge Cond: (a.c = (b.b)::text)
-> Vector Sort
Sort Key: a.c
-> CStore Scan on t_char1 a
-> Vector Sort
Sort Key: b.b
-> CStore Scan on t_char2 b
(11 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Merge Left Join
Merge Cond: (a.c = b.c)
-> Vector Sort
Sort Key: a.c
-> CStore Scan on t_char1 a
-> Vector Sort
Sort Key: b.c
-> CStore Scan on t_char2 b
(11 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
set enable_mergejoin to off;
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Nest Loop Left Join
Join Filter: (a.a = b.a)
-> CStore Scan on t_char1 a
-> Vector Materialize
-> CStore Scan on t_char2 b
(8 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Nest Loop Left Join
Join Filter: (a.a = (b.b)::bpchar)
-> CStore Scan on t_char1 a
-> Vector Materialize
-> CStore Scan on t_char2 b
(8 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Nest Loop Left Join
Join Filter: ((a.a)::text = b.c)
-> CStore Scan on t_char1 a
-> Vector Materialize
-> CStore Scan on t_char2 b
(8 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.a=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Nest Loop Left Join
Join Filter: ((a.b)::bpchar = b.a)
-> CStore Scan on t_char1 a
-> Vector Materialize
-> CStore Scan on t_char2 b
(8 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Nest Loop Left Join
Join Filter: ((a.b)::text = (b.b)::text)
-> CStore Scan on t_char1 a
-> Vector Materialize
-> CStore Scan on t_char2 b
(8 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Nest Loop Left Join
Join Filter: ((a.b)::text = b.c)
-> CStore Scan on t_char1 a
-> Vector Materialize
-> CStore Scan on t_char2 b
(8 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.b=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Nest Loop Left Join
Join Filter: (a.c = (b.a)::text)
-> CStore Scan on t_char1 a
-> Vector Materialize
-> CStore Scan on t_char2 b
(8 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.a order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Nest Loop Left Join
Join Filter: (a.c = (b.b)::text)
-> CStore Scan on t_char1 a
-> Vector Materialize
-> CStore Scan on t_char2 b
(8 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.b order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
explain(costs off) select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Row Adapter
-> Vector Sort
Sort Key: a.a NULLS FIRST, (length(a.a)) NULLS FIRST, a.b NULLS FIRST, (length((a.b)::text)) NULLS FIRST, a.c NULLS FIRST, (length(a.c)) NULLS FIRST, b.a NULLS FIRST, (length(b.a)) NULLS FIRST, b.b NULLS FIRST, (length((b.b)::text)) NULLS FIRST, b.c NULLS FIRST, (length(b.c)) NULLS FIRST
-> Vector Nest Loop Left Join
Join Filter: (a.c = b.c)
-> CStore Scan on t_char1 a
-> Vector Materialize
-> CStore Scan on t_char2 b
(8 rows)
select a.a,length(a.a),a.b,length(a.b),a.c,length(a.c),b.a,length(b.a),b.b,length(b.b),b.c,length(b.c) from t_char1 a left join t_char2 b on a.c=b.c order by 1,2,3,4,5,6,7,8,9,10,11,12;
a | length | b | length | c | length | a | length | b | length | c | length
------------+--------+-----+--------+-----+--------+------------+--------+-----+--------+-----+--------
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 1 | 1 | 1 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 2 | 1 | 2 | 1 | 10 | 1 | 3 | 1 | 3
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 1 | 1 | 1
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 2 | 1 | 2
1 | 10 | 1 | 3 | 1 | 3 | 1 | 10 | 1 | 3 | 1 | 3
(13 rows)
drop table t_char1;
drop table t_char2;
\c postgres
drop database b_char_blank_test_2;