Help Center/ GaussDB/ Distributed_3.x/ Partitioned Table/ Introduction to Partitioned Tables/ Partitioning Policy/ Impact of Partitioned Tables on Import Performance
Updated on 2024-05-20 GMT+08:00
View PDF

Impact of Partitioned Tables on Import Performance

In the GaussDB kernel implementation, compared with the non-partitioned table, the partitioned table has partition routing overheads during data insertion. The overall data insertion overheads include: (1) heap base table insertion and (2) partition routing. The heap base table insertion solves the problem of importing tuples to the corresponding heap table and is shared by ordinary tables and partitioned tables. The partition routing solves the problem that the tuple is inserted into the corresponding partRel.

Therefore, data insertion optimization focuses on the following aspects:
  1. Heap base table insertion in a partitioned table:
    1. The operator noise floor is optimized.
    2. Heap data insertion is optimized.
    3. Index insertion build (with indexes) is optimized.
  2. Partition routing in a partitioned table:
    1. The logic of the routing search algorithm is optimized.
    2. The routing noise floor is optimized, including enabling the partRel handle of the partitioned table and adding the logic overhead of function calling.

    The performance of partition routing is reflected by a single INSERT statement with a large amount of data. In the UPDATE scenario, the system searches for the tuple to be updated, deletes the tuple, and then inserts new tuple. Therefore, the performance is not as good as that of a single INSERT statement.

Table 1 shows the routing algorithm logic of different partitioning types.
Table 1 Routing algorithm logic

Partitioning Type

Routing Algorithm Complexity

Implementation Description

Range partitioning

O(logN)

Implemented based on binary search

Interval partitioning

O(logN)

Implemented based on binary search

Hash partitioning

O(1)

Implemented based on the key-partOid hash table

List partitioning

O(1)

Implemented based on the key-partOid hash table
The main processing logic of routing is to calculate the partition where the imported data tuple is located based on the partition key. Compared with a non-partitioned table, this part is an extra overhead. The performance loss caused by this overhead in the final data import is related to the CPU processing capability of the server, table width, and actual disk/memory capacity. Generally, it can be roughly considered that:
  • In the x86 server scenario, the import performance of a partitioned table is 10% lower than that of an ordinary table.
  • In the Arm server scenario, the performance decreases by 20%. The main reason is that routing is performed in the in-memory computing enhancement scenario. The single-core instruction processing capability of mainstream x86 CPUs is slightly better than that of Arm CPUs.
Parent topic: Partitioning Policy