Multi-node analysis

Partition key

A partition key distributes data across multiple nodes. The core idea is to keep related data together for local computation.

How it works

For partitioned tables in PolarDB that use HASH or KEY partitioning for primary or secondary partitions, the multi-node execution environment for columnar tables uses a Share-Nothing architecture. This means each partition is processed by a single, dedicated node.

Core advantages

• Improved data caching: Each node processes only its assigned partitions, allowing it to use local memory more effectively for data caching.

• Optimized query performance: For JOIN operations and GROUP BY queries based on the partition key, data is processed locally on each node. This significantly reduces data shuffle.

Best practices

• Choose core business association keys: Use the columns most frequently used in JOIN or GROUP BY clauses as the partition key, such as order_id or user_id.

• Keep the number of partitions consistent: The tables involved in a JOIN operation must have the same number of HASH/KEY partitions. Otherwise, local computation cannot be achieved.

• Use a large prime number for the partition count: Choose a sufficiently large prime number, such as 97 or 199, as the number of partitions. This helps ensure that data is distributed evenly across nodes and prevents data skew.

Sort key

A sort key organizes data within each physical partition. The core idea is to skip reading irrelevant data. For large datasets, you can filter data by using RANGE partitioning or by adding a sort key to a columnar table.

How it works

In columnar storage, data is organized into multiple data blocks. When you set a sort key, the data within each data block is physically sorted based on the specified column. When a query is run, the database uses metadata in the header of each data block, such as minimum and maximum values, to decide whether to read the block.

Core advantages

• Efficient data pruning: When a WHERE clause contains a filter on the sort key, the query engine can directly skip (or prune) entire data blocks whose data ranges do not match the filter conditions, reducing the required disk I/O.

Best practices

• Select frequently filtered columns: Use columns that are frequently filtered in WHERE clauses as the sort key. This is especially effective for columns used in range queries (>, <, BETWEEN) or high-cardinality equality queries.

• Combine with RANGE partitioning: Combine sort keys with RANGE partitioning to achieve multi-level filtering.

Determine if a SQL query uses MPP

The multi-node parallel execution (MPP) feature for PolarDB columnar tables can accelerate complex queries. You can determine if a query uses this feature by analyzing its EXPLAIN execution plan. The key indicator is the presence of an Exchange operator.

1. Step 1: Force an MPP plan

   First, use a specific hint to confirm if the optimizer can rewrite your SQL statement into an MPP plan.

   Procedure
   Add EXPLAIN before your SQL statement and insert the /*+ SET_VAR(imci_plan_use_mpp=forced) */ hint. This hint forces the optimizer to attempt to generate an MPP execution plan.

   Example

   EXPLAIN SELECT /*+ SET_VAR(imci_plan_use_mpp=forced) */ COUNT(*) FROM nation;

   Interpreting the result
   After you run the command, if the returned execution plan includes an Exchange operator, this indicates that your SQL query can use multi-node parallel execution.

2. Step 2: Check the actual execution plan

   After confirming the potential, check if the optimizer chooses to execute your SQL in MPP mode under normal conditions (without hints).

   Procedure
   Run EXPLAIN directly on your original SQL statement.

   Example

   EXPLAIN SELECT COUNT(*) FROM nation;

   Interpreting the result

   • If the execution plan includes an Exchange operator: This indicates that the optimizer, after a cost-based evaluation, chose MPP as the most efficient execution method for your query.

   • If the execution plan does not include an Exchange operator: This might mean one of the following:

     • The query is too simple or involves too little data, so the optimizer determined that single-node execution is faster.

     • The structure of the SQL query or the table, such as the partition key settings, prevents the use of MPP. You can return to Step 1 to check and optimize your SQL query or table design.