Hash Join

Updated on 2025-03-13 GMT+08:00

View PDF

Description

A hash join is an efficient join method that depends on the hash technology. During a hash join, GaussDB selects one of the two tables (usually small tables) and creates a hash table based on join conditions. The key of the hash table is the join column of the small table, and the other columns of the small table are the value. Then, the hash value of the join column is calculated for each row in the large table. After this, the hash table is searched for a matching row.

The time complexity of a hash join is O(n + m), where n and m indicate the numbers of rows in the two tables. However, the performance may degrade if the inner table is too large for the memory storage, as it will require additional disk I/O operations.

Typical Scenarios

The number of rows in two tables differ significantly and the join operation is performed.
The join columns of the two tables are equi-join (for example, using the = operator).

Examples

Example: The number of rows in two tables differ significantly and the join operation is performed.

-- Prepare data.
gaussdb=# DROP TABLE IF EXISTS t1;
gaussdb=# DROP TABLE IF EXISTS t2;
gaussdb=# CREATE TABLE t1(c1 number,c2 number,c3 number); 
CREATE TABLE 
gaussdb=# CREATE TABLE t2(c1 number,c2 number,c3 number); 
CREATE TABLE 
gaussdb=# INSERT INTO t1 VALUES(generate_series(1,100000), 2 ,3); 
INSERT 0 100000 
gaussdb=# INSERT INTO t2 VALUES(generate_series(1,100), 2, 3); 
INSERT 0 100

-- Collect statistics.
gaussdb=# ANALYZE t1;
gaussdb=# ANALYZE t2;

-- Execution result.
gaussdb=# EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c2; 
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=12.56..986.88 rows=100 width=31)
   Node/s: All datanodes
   ->  Hash Join  (cost=9.44..982.19 rows=100 width=31)
         Hash Cond: (t1.c1 = t2.c2)
         ->  Seq Scan on t1  (cost=0.00..816.00 rows=100000 width=16)
         ->  Hash  (cost=8.19..8.19 rows=100 width=15)
               ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..8.19 rows=100 width=15)
                     Spawn on: All datanodes
                     ->  Seq Scan on t2  (cost=0.00..2.50 rows=100 width=15)
(9 rows)

-- Drop the table.
gaussdb=# DROP TABLE t1,t2;

In the preceding example, the output of the hash join operator is as follows.

Item	Description
Hash Join	Operator name.
Hash Cond	Join predicate of the operator hash join. In the example, the condition is that t1.c1 is equal to t2.c2. During query execution, rows that meet these conditions are included in the final result set.
Hash	Operator for creating a hash table in an inner table.