Use window functions to accelerate parallel queries - PolarDB

Window function is a feature introduced in MySQL Community Edition 8.0 to improve query and analysis capabilities. PolarDB for MySQL 8.0 fully supports window functions and executes them in parallel across multiple workers, reducing query latency without requiring any changes to your SQL.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for MySQL Cluster Edition 8.0 cluster with revision version 8.0.2.2.0 or later. To check your version, see Query the engine version.
A window function query that includes a PARTITION BY clause. Only window functions with PARTITION BY support parallel execution.

How parallel window function execution works

PolarDB parallelizes window function execution in three stages:

Parallel scan — Multiple workers scan the table simultaneously, each processing a subset of rows.
Repartition — Data is redistributed across workers using hash partitioning on the PARTITION BY column. This ensures all rows in the same partition go to the same worker, so each worker can compute the window function independently and correctly.
Gather — The leader node collects and merges the results from all workers.

Verify parallel execution

Use EXPLAIN FORMAT=TREE to confirm that PolarDB is executing a window function in parallel. This is the only supported syntax for inspecting window function execution plans in PolarDB.

Set up a sample table

Create an employee_salaries table and insert sample data:

CREATE TABLE `employee_salaries` (
  `dept` varchar(20) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `salary` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `employee_salaries` VALUES
('Engineering','Dharma',3500),
('Engineering','Bình',3000),
('Engineering','Adalynn',2800),
('Engineering','Samuel',2500),
('Engineering','Cveta',2200),
('Engineering','eve',2000),
('Engineering','Dharma',3500),
('Sales','Carbry',500),
('Sales','Clytemnestra',400),
('Sales','Juraj',300),
('Sales','Kalpana',300),
('Sales','Svantepolk',250),
('Sales','Angelo',200);

Run EXPLAIN FORMAT=TREE

explain format=tree select ROW_NUMBER() OVER(partition by dept order by salary desc) AS 'row_number' from employee_salaries\G

A parallel execution plan looks similar to this:

*************************** 1. row ***************************
EXPLAIN:
-> Gather (slice: 1; workers: 4)  (cost=26.42 rows=12)
    -> Window aggregate  (cost=15.67 rows=3)
        -> Repartition (hash keys: employee_salaries.dept; slice: 2; workers: 4)  (cost=15.33 rows=3)
            -> Sort: employee_salaries.dept, employee_salaries.salary DESC  (cost=1.55 rows=13)
                -> Parallel table scan on employee_salaries, with parallel partitions: 4

Read the execution plan

Each node in the plan corresponds to one stage of the parallel pipeline. The nodes run in bottom-up order — the deepest node executes first.

Node	Description
`Parallel table scan on employee_salaries, with parallel partitions: 4`	The table is scanned by 4 workers simultaneously. Each worker handles a disjoint subset of rows.
`Sort: employee_salaries.dept, employee_salaries.salary DESC`	Each worker sorts its local data by `dept` and `salary DESC` to prepare for windowing.
`Repartition (hash keys: employee_salaries.dept; slice: 2; workers: 4)`	Data is redistributed across the 4 workers by hashing on `dept` (the `PARTITION BY` column). All rows with the same `dept` go to the same worker. `slice: 2` identifies this as the second pipeline stage.
`Window aggregate`	Each worker independently computes `ROW_NUMBER()` over its assigned department partitions.
`Gather (slice: 1; workers: 4)`	The leader collects results from all 4 workers and merges them. `slice: 1` marks the final stage.

workers is the number of parallel threads participating in that stage. slice is the pipeline stage identifier.

Limitations

Limitation	Details
`PARTITION BY` required	Only window functions that include a `PARTITION BY` clause can run in parallel. Window functions without `PARTITION BY` run serially.