create table t1 (id int primary key);
create table t2 (id int primary key);

insert into t1 select x.x from generate_series(1,1000000) x(x);
insert into t2 select x.x from generate_series(1,1000000) x(x);

vacuum analyze;


select count(t2.id) from t1 left outer join t2 on t1.id=t2.id;

** Patched:
D:\Postgres\install\bin>pgbench -f d:\unijoin.sql -n -T 60 postgres
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 77
latency average: 779.221 ms
tps = 1.269234 (including connections establishing)
tps = 1.269637 (excluding connections establishing)


** Unpatched
D:\Postgres\install\bin>pgbench -f d:\unijoin.sql -n -T 60 postgres
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 70
latency average: 857.143 ms
tps = 1.152724 (including connections establishing)
tps = 1.153080 (excluding connections establishing)

** 10 min run: Patched:
D:\Postgres\install\bin>pgbench -f d:\unijoin.sql -n -T 600 postgres
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 600 s
number of transactions actually processed: 778
latency average: 771.208 ms
tps = 1.295764 (including connections establishing)
tps = 1.295800 (excluding connections establishing)

** 10 min run: Unpatched:
D:\Postgres\install\bin>pgbench -f d:\unijoin.sql -n -T 600 postgres
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 600 s
number of transactions actually processed: 705
latency average: 851.064 ms
tps = 1.173946 (including connections establishing)
tps = 1.173984 (excluding connections establishing)


SET enable_hashjoin = off;

** 1 min run (Patched) using merge join
D:\Postgres\install\bin>pgbench -f d:\unijoin.sql -n -T 60 postgres
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 108
latency average: 555.556 ms
tps = 1.785677 (including connections establishing)
tps = 1.786248 (excluding connections establishing)

** 1 min run (Unpatched) using merge join
D:\Postgres\install\bin>pgbench -f d:\unijoin.sql -n -T 60 postgres
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 107
latency average: 560.748 ms
tps = 1.776052 (including connections establishing)
tps = 1.776605 (excluding connections establishing)

SET enable_mergejoin = off;

** 1 min run (Patched) nested loop join
D:\Postgres\install\bin>pgbench -f d:\unijoin.sql -n -T 60 postgres
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 28
latency average: 2142.857 ms
tps = 0.465196 (including connections establishing)
tps = 0.465356 (excluding connections establishing)

** 1 min run (Unpatched) nested loop join
D:\Postgres\install\bin>pgbench -f d:\unijoin.sql -n -T 60 postgres
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 27
latency average: 2222.222 ms
tps = 0.442878 (including connections establishing)
tps = 0.443015 (excluding connections establishing)


New Query:

create table s1 (id varchar(32) primary key);
insert into s1 select x.x::varchar from generate_series(1,1000000) x(x);
create table s2 (id varchar(32) primary key);
insert into s2 select x.x::varchar from generate_series(1,1000000) x(x);
vacuum analyze;

select count(s2.id) from s1 left outer join s2 on s1.id=s2.id;

** 1 min run (Patched) hash join
D:\Postgres\install\bin>pgbench -f d:\unijoin2.sql -n -T 60 postgres
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 73
latency average: 821.918 ms
tps = 1.198489 (including connections establishing)
tps = 1.198821 (excluding connections establishing)

** 1 min run (Unpatched) Hash join
D:\Postgres\install\bin>pgbench -f d:\unijoin2.sql -n -T 60 postgres
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 67
latency average: 895.522 ms
tps = 1.101832 (including connections establishing)
tps = 1.102183 (excluding connections establishing)