simple query on why a merge join plan got selected

Hey Guys,

I was just playing with exploring joins and plans i came across this

create table t1(a int);
create table t2(a int);
insert into t1 select (x % 10) from generate_series(1, 100000) x;
insert into t2 select (x % 100) from generate_series(1, 100000) x;

pgtesting=> analyze t1;
ANALYZE

pgtesting=> analyze t2;
ANALYZE

this is reproducible

the below query by default makes use of merge join (which takes way
longer to return rows as compared to when i explicitly disable merge
join it returns in half the time taken by merge join) but i am not
able to figure out why, although i have run analyze on the tables.

pgtesting=> explain (analyze, buffers) select * from t1 join t2 using (a);

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------

Merge Join (cost=19495.64..1039705.09 rows=97241600 width=4) (actual
time=124.153..22243.262 rows=100000000 loops=1)

Merge Cond: (t1.a = t2.a)

Buffers: shared hit=886, temp read=320384 written=616

-> Sort (cost=9747.82..9997.82 rows=100000 width=4) (actual
time=56.442..81.611 rows=100000 loops=1)

Sort Key: t1.a

Sort Method: external merge Disk: 1376kB

Buffers: shared hit=443, temp read=172 written=173

-> Seq Scan on t1 (cost=0.00..1443.00 rows=100000 width=4)
(actual time=0.030..10.003 rows=100000 loops=1)

Buffers: shared hit=443

-> Sort (cost=9747.82..9997.82 rows=100000 width=4) (actual
time=67.702..9469.366 rows=100000001 loops=1)

Sort Key: t2.a

Sort Method: external sort Disk: 1768kB

Buffers: shared hit=443, temp read=220222 written=443

-> Seq Scan on t2 (cost=0.00..1443.00 rows=100000 width=4)
(actual time=0.013..8.186 rows=100000 loops=1)

Buffers: shared hit=443

Planning time: 0.402 ms

Execution time: 26093.192 ms

(17 rows)

pgtesting=> set enable_mergejoin TO FALSE;

SET

pgtesting=> explain (analyze, buffers) select * from t1 join t2 using (a);

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------

Hash Join (cost=3084.00..1117491.00 rows=97241600 width=4) (actual
time=26.893..10229.924 rows=100000000 loops=1)

Hash Cond: (t1.a = t2.a)

Buffers: shared hit=889, temp read=273 written=271

-> Seq Scan on t1 (cost=0.00..1443.00 rows=100000 width=4)
(actual time=0.028..18.123 rows=100000 loops=1)

Buffers: shared hit=443

-> Hash (cost=1443.00..1443.00 rows=100000 width=4) (actual
time=26.255..26.255 rows=100000 loops=1)

Buckets: 131072 Batches: 2 Memory Usage: 2713kB

Buffers: shared hit=443, temp written=152

-> Seq Scan on t2 (cost=0.00..1443.00 rows=100000 width=4)
(actual time=0.017..9.163 rows=100000 loops=1)

Buffers: shared hit=443

Planning time: 0.099 ms

Execution time: 14095.975 ms

(12 rows)

pgtesting=> show work_mem;

work_mem

----------

4MB

(1 row)

pgtesting=> show shared_buffers;

shared_buffers

----------------

1GB

(1 row)

pgtesting=> select version();

version

---------------------------------------------------------------------------------------------------------------------------------------------

PostgreSQL 10.5 (Ubuntu 10.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609,
64-bit

(1 row)

May be i am missing something way obvious :) but my only concern being
high cardinality joins may not use the statistics correctly?

Regards,
Vijay