Hash Join In Couchbase

Couchbase Server
1

Hi All,

What is the major difference of USE HASH (PROBE) and USE HASH (BUILD) while using HASH join in couchbase. Please share if there is document reference link for more detailed info.

Thanks,
Debasis

2

They are the different sides of a hash join. The BUILD side is the side used to build the hash table. The PROBE is the side used to probe the hash table to effect the join.

Ref: https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/keyspace-hints.html#use_hash

HTH.

3

Thanks .Just want to know that one of query using hint USE HASH (PROBE) is taking 12 s but same query when I changed to USE HASH (BUILD) it execution time changes to 1.2 s and as I understand these are two phases of hash join one is create the hash table and other used to search the data in hash table on basis of join condition.
Please correct me if I am wrong.

Thanks,
Debasis

4

It indicates which keyspace is used for which operation. You typically supply just the one and the other is implied by the join condition.

Say you have a 1 million “orders” documents and 10 “salesperson” documents in their respective collections. You’d want to BUILD on “salesperson” and PROBE on “orders”.

If you reverse these, the time and resources taken to build the hash table will be a lot more and will likely not perform well.

To get the desired order, you’d only need to specify USE HASH(BUILD) on “salesperson” or USE HASH(PROBE) on “orders” (you could specify both - if using the comment block hints - but one will be largely redundant; using the legacy JOIN hint you can specify only the one of course and it applies to the right hand side of the join).

HTH.

(I should add: If you have up to date statistics and are using the cost based optimiser, the correct selection will be made for you and you shouldn’t typically need any hints.)

5

Example 9
You must use smaller results BUILD side and larger results on probe side so that it can stream. cc @bingjie.miao