PolarDB:Elastic parallel query (ePQ)

Usage notes

ePQ is unavailable for PolarDB for PostgreSQL (Compatible with Oracle 2.0).

How it works

When ePQ is used for queries, PolarDB for PostgreSQL (Compatible with Oracle) uses the ePQ optimizer to generate an execution plan that can be executed by multiple compute nodes in parallel. The execution engine coordinates the plan across multiple compute nodes, using the CPU, memory, and I/O bandwidth of multiple nodes to scan and compute data.

To achieve serverless elastic scaling, you can use Grand Unified Configuration (GUC) parameters to dynamically adjust the number of compute nodes that participate in ePQ (scale-out) and the degree of parallelism for each node (scale-up).

Enable ePQ

Run the following SQL statements against a table named t1 to enable ePQ and verify the configuration:

SET polar_enable_px = 1;
EXPLAIN SELECT * FROM t1;

Expected output:

                                  QUERY PLAN
-------------------------------------------------------------------------------
 PX Coordinator 6:1  (slice1; segments: 6)  (cost=0.00..431.00 rows=1 width=8)
   ->  Partial Seq Scan on t1  (cost=0.00..431.00 rows=1 width=8)
 Optimizer: PolarDB PX Optimizer
(3 rows)

If PolarDB PX Optimizer appears in the execution plan, ePQ is enabled. Run the query:

SELECT * FROM t1;

Control ePQ scope

When ePQ is enabled globally or at the session level, the system automatically routes qualifying queries to the ePQ optimizer. However, ePQ is better suited for long-running analytical queries than short transactional queries. For short queries, the overhead of establishing connections, exchanging data, and tearing down connections between compute nodes may degrade performance.

Session level

Enable ePQ for all queries within the current session:

SET polar_enable_px = ON;

Global level

In the PolarDB console, set polar_enable_px to ON to enable ePQ for all queries globally. Verify the configuration:

EXPLAIN SELECT * FROM t1;

                                  QUERY PLAN
-------------------------------------------------------------------------------
 PX Coordinator 6:1  (slice1; segments: 6)  (cost=0.00..431.00 rows=1 width=8)
   ->  Partial Seq Scan on t1  (cost=0.00..431.00 rows=1 width=8)
 Optimizer: PolarDB PX Optimizer
(3 rows)

Database and user level

To isolate ePQ workloads, enable ePQ on a dedicated analytical database:

ALTER DATABASE ap_database SET polar_enable_px = ON;

Alternatively, enable ePQ for a specific user account:

ALTER ROLE ap_role SET polar_enable_px = ON;

Query-level hints

Use the pg_hint_plan extension with SQL hints to enable or disable ePQ for individual queries. Before using hints, verify that pg_hint_plan is included in the shared_preload_libraries GUC parameter.

Enable ePQ for a single query:

/*+ PX() */ EXPLAIN SELECT * FROM t1;

                                    QUERY PLAN
----------------------------------------------------------------------------------
 PX Coordinator 6:1  (slice1; segments: 6)  (cost=0.00..431.03 rows=1000 width=8)
   ->  Partial Seq Scan on t1  (cost=0.00..431.00 rows=167 width=8)
 Optimizer: PolarDB PX Optimizer
(3 rows)

Disable ePQ for a single query:

/*+ NoPX() */ EXPLAIN SELECT * FROM t1;

                      QUERY PLAN
------------------------------------------------------
 Seq Scan on t1  (cost=0.00..15.00 rows=1000 width=8)
(1 row)

Enable ePQ with a specific degree of parallelism per node. The following example sets parallelism to 6:

/*+ PX(6) */ EXPLAIN SELECT * FROM t1;

                                     QUERY PLAN
------------------------------------------------------------------------------------
 PX Coordinator 12:1  (slice1; segments: 12)  (cost=0.00..431.02 rows=1000 width=8)
   ->  Partial Seq Scan on t1  (cost=0.00..431.00 rows=84 width=8)
 Optimizer: PolarDB PX Optimizer
(3 rows)

Per-table control

To limit ePQ to specific tables, set polar_px_enable_check_workers to ON, then configure the px_workers option on each table:

ALTER TABLE t1 SET (px_workers = 1);

GUC parameters

Grand Unified Configuration (GUC) parameters control ePQ behavior. All ePQ-related GUC parameters take effect when polar_enable_px is set to ON.

Identify read-only nodes

To list the read-only nodes available for ePQ, create the polar_monitor extension and query the cluster information:

CREATE EXTENSION polar_monitor;

SELECT name,slot_name,type FROM polar_cluster_info WHERE type = 'Replica';

Example output:

 name  | slot_name |  type
-------+-----------+---------
 node2 | replica1  | Replica
 node3 | replica2  | Replica
(2 rows)

To restrict ePQ to specific nodes, set polar_px_nodes:

SET polar_px_nodes = 'node2,node3';
SHOW polar_px_nodes;

 polar_px_nodes
----------------
 node2,node3
(1 row)

Performance tuning

Adjust parallelism per node

Set polar_px_dop_per_node to half the number of CPU cores on the node. If CPU load is high, decrease the value until CPU utilization stays at or below 80%. If query performance is low, increase the value, but keep CPU utilization below 80% to avoid slowing down other background processes.

Set a query timeout

ePQ processes acquire shared locks on queried tables. DDL operations that require exclusive locks may be blocked by these ePQ processes. After the time specified by polar_px_wait_lock_timeout elapses, ePQ queries are cancelled to free resources for the DDL operations.

For long-running analytical queries, set statement_timeout to allow a reasonable response time. For example, set it to 10800000 milliseconds (3 hours) for large, complex queries. Avoid leaving statement_timeout at the default value of 0 (unlimited), which can cause database connections to remain held for extended periods.

Troubleshooting

Maximum workers exceeded

If the number of ePQ worker processes on a read-only node exceeds the polar_px_max_workers_number limit, the following error occurs:

ERROR:  over px max workers, already xxx workers, max 30 workers

To resolve this issue, increase the value of polar_px_max_workers_number. Avoid setting the value excessively high, as too many processes on a single node increases the risk of out-of-memory (OOM) errors.