Selecting Distribution Keys

Selecting a distribution key for a hash table is essential. Details are as follows:

1. Ensure that the column values are discrete so that data can be evenly distributed to each DN. You can select the primary key of the table as the distribution key. For example, for a person information table, choose the ID card number column as the distribution key.
2. Do not select the column that has a constant filter. If a constant constraint (for example, zqdh='000001') exists on the zqdh column in some queries on the dwcjk table, you are advised not to use zqdh as the distribution key.
3. Select the join condition as the distribution key, so that join tasks can be pushed down to DNs to execute, reducing the amount of data transferred between the DNs.

For a hash table, an improper distribution key may cause data skew or poor I/O performance on certain DNs. Therefore, you need to check the table to ensure that data is evenly distributed on each DN. You can run the following SQL statements to check for data skew:

1
2
3
4
5

select 
xc_node_id, count(1) 
from tablename 
group by xc_node_id 
order by xc_node_id desc;

xc_node_id corresponds to a DN. Generally, over 5% difference between the amount of data on different DNs is regarded as data skew. If the difference is over 10%, choose another distribution key.

Multiple distribution keys can be selected in GaussDB to evenly distribute data.

You can select the distribution key of the range or list distribution table as required. In addition to selecting a proper distribution key, pay attention to the impact of distribution rules on data distribution.