PolarDB:Massively parallel processing for columnar tables

Partition keys

Partition keys distribute data across multiple nodes. The core principle is to keep related data together for local computation.

How it works

For partitioned tables that use HASH or KEY partitioning at the first or second level, multi-node execution uses a share-nothing approach. Each partition is processed by a single node.

Key benefits

• Improved data caching: Each node processes only its assigned partitions, which enables more efficient use of local memory caches.

• Optimized query performance: JOIN and GROUP BY operations based on partition keys process data locally on each node, which significantly reduces cross-node data transfer.

Best practices

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

• Match partition counts: Tables involved in JOIN operations must have exactly the same number of HASH/KEY partitions. Mismatched partition counts prevent local computation.

• Use large prime numbers for partition counts: Use a large prime number such as 97 or 199 as the partition count. This ensures even data distribution across nodes and prevents data skew.

Sort keys

Sort keys organize data within each physical partition. The core principle is to skip irrelevant data during reads. You can filter large data volumes by using RANGE partitions or adding sort keys to columnar tables.

How it works

In columnar storage, data is organized into stripes. When you set a sort key, the data within each stripe is physically sorted by the specified column. During queries, the engine uses metadata in each stripe header, such as min/max values, to determine whether to read or skip the stripe.

Key benefits

• Efficient data pruning: When a WHERE clause filters on the sort key, the query engine skips entire stripes whose data ranges do not match the filter conditions. This reduces disk I/O.

Best practices

• Choose high-frequency filter columns: Set sort keys on columns frequently used in WHERE clauses, especially for range queries (>, <, BETWEEN) or high-cardinality equality queries.

• Combine with RANGE partitions: Use sort keys together with RANGE partitions for multi-level data filtering.

How do I check whether a SQL statement uses MPP?

You can analyze the EXPLAIN execution plan of a SQL statement to determine whether it uses MPP. The key indicator is the presence of the Exchange operator.

1. Step 1: Force an MPP plan to check feasibility

   First, verify whether the optimizer can generate an MPP plan for your SQL statement by using a specific hint.

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

   Example

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

   Result: If the execution plan contains the Exchange operator, the SQL statement can use multi-node parallel execution.

2. Step 2: Check the actual execution plan

   After confirming feasibility, check whether the optimizer actually selects MPP mode for your SQL statement without the hint.

   Method: Run EXPLAIN on your original SQL statement.

   Example

   EXPLAIN SELECT COUNT(*) FROM nation;

   Result:

   • If the execution plan contains the Exchange operator, the optimizer has enabled multi-node parallel execution for the query.

   • If the execution plan does not contain the Exchange operator, it may indicate that:

     • The query is too simple or involves too little data. The optimizer determines that single-node execution is faster.

     • The SQL syntax or table structure, such as partition key settings, limits MPP usage. Review Step 1 to optimize your SQL or table design.