Parallel Query Optimization

Availability

This feature is available since MogDB 5.0.0.

Introduction

MogDB version 5.0 introduces the parallel sort merge join feature, which is mainly used to optimize the execution speed of join operations. When executing join operations, when the system configuration allows parallel execution and the execution plan is suitable for parallel execution, multiple threads can be used to scan and match the associated columns at the same time, thus improving the execution efficiency of the join operation.

Benefits

With the parallel sort merge join feature, MogDB version 5.0 is able to better cope with the demands of large-scale data join operations, providing higher query performance and faster execution speed. The introduction of this feature provides users with more powerful data processing capabilities and a more optimized query experience.

Description

Parallel sort merge join is an optimization method to improve efficiency when performing join operations. A traditional sort merge join scans and matches data row by row in sequential order after sorting each of the associated columns of a related table. Although ordering is guaranteed, it is less efficient. Parallel query optimization, on the other hand, provides the ability to execute sort merge join in parallel. By utilizing multiple worker threads to match with the associated table data at the same time, resource utilization can be improved, thus enhancing the query performance of sort merge join.

Specifically, when the system configuration allows parallel execution and the execution plan is suitable for parallel execution, MogDB can utilize multiple threads to scan and match data from the associated columns in parallel. This can speed up the execution of join operations and improve query efficiency.

Enhancements

None

Constraints

Requires the parallel switch to be turned on, i.e., set the query_dop parameter to a value greater than 1.
Multi-table query operations in JOIN_UNIQUE_OUTER, JOIN_FULL and JOIN_RIGHT scenarios are not supported.

Dependencies

None

Example

Create a table and insert data.

CREATE TABLE MogDB_parallel_merge_join(id int );
INSERT INTO MogDB_parallel_merge_join VALUES ( generate_series ( 1, 1000000));

Create index.

CREATE INDEX index_parallel_merge_join ON MogDB_parallel_merge_join(id);

Collect statistical information.
```
analyze MogDB_parallel_merge_join;
```
Turn on the parallel query switch and set the query_dop parameter value greater than 1.
```
set query_dop = 4;
```

View merge join as a parallel query via explain.

EXPLAIN (COSTS OFF ) SELECT * FROM MogDB_parallel_merge_join t1, MogDB_parallel_merge_join t2 WHERE t1.id = t2.id order by t1.id limit 10;

A message similar to the following is displayed:

QUERY PLAN                                                                                                        
------------------------------------------------------------------------------------------------------------------
Limit                                                                                                             
  - >  Sort                                                                                                        
        Sort Key: t1.id                                                                                           
        - >  Streaming(type: LOCAL GATHER dop: 1 / 4)                                                                
              - >  Limit                                                                                           
                    - >  Merge Join                                                                                
                          Merge Cond: (t1.id = t2.id)                                                             
                          - >  Index Only Scan using index_parallel_merge_join on mogdb_parallel_merge_join t1     
                          - >  Full Index Only Scan using index_parallel_merge_join on mogdb_parallel_merge_join t2

query_dop