CrateDB | Performance Optimisation in Left Joins

CrateDB SQL
1

Crate Version: 4.3.2

We have 2 tables Table_A and Table_B. Table_A consists of 40k records and Table_B consists of 400k records. Whenever we apply left join the crate DB kind of hangs and results coming in 140 secs… that’s too slow.

Note: I noticed that in left/right join nestableloop is getting applied instead of hash joins. Is there a way we can apply hash joins in left/right join cases?

Also Is there any other way we can achieve the same behaviour like left joins but which can respond quickly.

Any help would be much appreciated. @jayeff

2

Could you maybe share a query example?

from the docs

This optimisation cannot be applied unless the join is an INNER join and the join condition satisfies the following rules:

  • Contains at least one EQUAL operator
  • Contains no OR operator
  • Every argument of a EQUAL operator can only references fields from one relation