MemSQL Not using merage Join

Help
1

Hello,

I have two tables:

  1. 100 rows on rowstore.
  2. 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

Please advise,

Thanks,
Sasi

2

For various reasons I won’t get into (mainly that our hash join performs better), MemSQL mostly uses hash join and only uses merge join in limited circumstances. Merge join can be used for joining two columnar tables, but you will also need the columnstore keys to match the join.

Is the use of hash join causing a problem?

3

Thanks for the reply.
of course not. but, my concerns are, when the table will growth, I will expect from the optimizer, to use merge Join in order to get better performance. (e.g. join between 40M X 60M)

Does join between two columnar tables (same key), guarantee that the join will be Merge Join ? because I tested this scenario as well and still got HashJoin

Thanks,
Sasi

4

Merge join can only be used when joining two columnstore table on their columnstore key. However, this does not guarantee the join type will be merge join since join type selection is cost based. For example, when joining a large table to a much smaller table (your example above), hash join will have better performance and so would be selected.

If you have queries you think are choosing the wrong join type, please share the explain/profile for the query.