PolarDB:Best Practices

Overview

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 of ePQ coordinates the execution of the plan on multiple compute nodes, and uses 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).

Usage notes

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

Enable ePQ

The following statements enable the ePQ feature for a table that is named t1. If PolarDB PX Optimizer is returned in the plan, ePQ is enabled.

SET polar_enable_px = 1;
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)

SELECT * FROM t1;

GUC parameters

=> CREATE EXTENSION polar_monitor;
CREATE EXTENSION

=> SELECT name,slot_name,type FROM polar_cluster_info WHERE type = 'Replica';
 name  | slot_name |  type
-------+-----------+---------
 node2 | replica1  | Replica
 node3 | replica2  | Replica
(2 rows)

=> SET polar_px_nodes = 'node2,node3';
=> SHOW polar_px_nodes;
 polar_px_nodes
----------------
 node2,node3
(1 row)

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

Best practices

Allow specific tables to use ePQ

If you only want to perform ePQ on a specific table, you can set polar_px_enable_check_workers to on. You also need to explicitly set the px_workers option for the table on which you need to perform ePQ.

ALTER TABLE t1 SET (px_workers = 1);

Enable the ePQ feature

Global level

In the PolarDB console, set polar_enable_px to on to enable ePQ globally. By default, ePQ is used for both transactional and analytical queries.

Use the following sample SQL statement to view the execution plan. If PolarDB PX Optimizer is returned in the plan, ePQ is enabled.

=> 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)

Session level

Set polar_enable_px to ON within a session to enable ePQ for all queries executed within the session.

SET polar_enable_px = ON;

Database level and user level

After ePQ is enabled at the global or session level, the system automatically uses ePQ for all queries that qualify for parallel execution before using other execution methods. From a best practice perspective, ePQ is more suitable for long queries with heavy analytical workloads than for short transactional queries. For short queries, the overhead associated with establishing connections, exchanging data, and destroying connections between compute nodes during the use of ePQ may result in decreased query performance.

If ePQ is required for specific business needs, analytical SQL queries that require ePQ can be extracted from the main database and executed on a separate database configured for ePQ queries.

ALTER DATABASE ap_database SET polar_enable_px = ON;

Alternatively, you can use a specific account to execute analytical SQL queries that require ePQ.

ALTER ROLE ap_role SET polar_enable_px = ON;

Query level

If you want to use ePQ only for specific queries within a session, such as for nightly report tasks, you can use the pg_hint_plan plug-in and SQL hints to enable ePQ for those queries. To make SQL hints work, make sure that the pg_hint_plan plug-in has been added to the GUC parameter shared_preload_libraries.

Prepend /*+ PX() */ to a query to enable ePQ for the 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)

Prepend /*+ NoPX() */ to a query to disable ePQ for the query.

=> /*+ NoPX() */ EXPLAIN SELECT * FROM t1;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on t1  (cost=0.00..15.00 rows=1000 width=8)
(1 row)

Prepend /*+ PX(N) */ to a query to enable ePQ with a single-node parallelism of N. In the following example, the value of N is 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)