Querying large partitioned tables with sequential scans becomes a bottleneck as data grows. The Elastic Parallel Query (EPQ) feature in PolarDB for PostgreSQL (Compatible with Oracle) distributes partition scans across multiple workers in parallel, reducing query latency for large-scale partitioned workloads.
Supported operations
EPQ supports the following operations on partitioned tables:
| Operation | Supported |
|---|---|
| Parallel query on range partitions | Yes |
| Parallel query on list partitions | Yes |
| Parallel query on hash partitions | Yes (single-column partition key only) |
| Partition pruning | Yes |
| Parallel query on indexed partitioned tables | Yes |
| JOIN queries on partitioned tables | Yes |
| Parallel query on multi-level partitions | No |
Hash partitions with a composite (multi-column) partition key are not supported for parallel query. If your hash partition is defined on more than one column, parallel execution does not apply.
Enable Elastic Parallel Query for partitioned tables
EPQ uses three parameters in a hierarchy: a global switch, a partition-specific switch, and an optional multi-level partition switch. Enable them in order.
| Parameter | Purpose |
|---|---|
polar_enable_px | Global switch. Must be on before other EPQ parameters take effect. Disabled by default. |
polar_px_enable_partition | Enables EPQ for partitioned tables. |
polar_px_optimizer_multilevel_partitioning | Enables EPQ for multi-level partitioned tables. Only needed for multi-level partitions. |
Enable EPQ
Turn on the global EPQ switch:
SET polar_enable_px = on;Enable parallel query for partitioned tables:
SET polar_px_enable_partition = true;(Optional) To run parallel queries on multi-level partitioned tables, enable:
SET polar_px_optimizer_multilevel_partitioning = true;
What's next
For commands to create, modify, and query partitioned tables, see Command list for partitioned tables.