I have two tables:
- 100 rows on rowstore.
- 20M rows on columnar (both with same shard key)
I performed the following Join:
select * from table_100
join table_20M using(id)
when watching the plain, I see that the optimizer not using MergJoin instead it’s use a HashJoin .
I checked the join with more then 100 rows (50,000, 10,000,000, 20,000,000)
and I still not bring the optimizer to use MergeJoin.
BTW, I tested these scenarios when both of the tables are columnar and still I got the same results.
I’m using memsql 7.0.2