Hive: Join Tables with Big and Small Table.

[blank-manash]

Context

We have two tables, A and B. The size of A is very large (over 500 GB) and the size of the other table B is small (about 44 rows)
We are executing a select query in three different ways

• Select Query on A
• Select Query on B
• Select Query on A LEFT JOIN B

Issue

It is observed that the first and second queries execute very fast. But however, the third query takes much more time than expected. I was curious to discuss how the architecture works to allocate resources to such kinds of queries. And if there are any ways to speed it up.

[findepi]

cc @dain @arhimondr

[arhimondr]

@blank-manash By default the engine would execute an INNER join. Is it possible that the join mentioned is highly selective with a lot of pruning involved? A LEFT join must retain all the rows from table A no matter what.