PolarDB:Control whether to use ePQ based on table size or execution plan cost

Applicability

This feature is supported on PolarDB for PostgreSQL clusters that run PostgreSQL 14 with minor engine version 2.0.14.9.15.0 or later.

Background information

ePQ excels at processing complex online analytical processing (OLAP) queries that have long running times. It is not suitable for simple online transactional processing (TP) queries with short running times. For short queries, the overhead of establishing connections, exchanging data, and closing connections between compute nodes can decrease performance. PolarDB for PostgreSQL lets you control whether to use ePQ or single-node execution based on table size or execution plan cost. This helps you select the more performant execution mode for your queries.

Precautions

The execution plan cost threshold and table size threshold apply only to the following objects or operations:

• Standard tables

• Partitioned tables

• Materialized views

• Queries without the ePQ hint /*+ PX() */

Parameters

Usage guide

1. Prepare data.

   CREATE TABLE test (id INT);
   INSERT INTO test SELECT generate_series(1, 1000);
   ANALYZE test;

2. View the execution plan cost.

   Disable ePQ and view the single-node execution plan.

   SET polar_enable_px TO OFF;
   EXPLAIN SELECT COUNT(*) FROM test;

   The following result is returned:

                             QUERY PLAN
   --------------------------------------------------------------
    Aggregate  (cost=17.50..17.51 rows=1 width=8)
      ->  Seq Scan on test  (cost=0.00..15.00 rows=1000 width=0)
   (2 rows)

   In the execution plan, cost=xxx..xxx in each line indicates the estimated execution cost of the current operator. The value before .. is the startup cost, and the value after is the total cost. The total cost of the top-level operator is the total execution cost of the query. For example, the total execution cost in the preceding plan is 17.51. Because the total cost of the single-node execution plan is lower than the minimum execution plan cost specified by the polar_px_min_pg_plan_cost parameter, this query does not use ePQ even when ePQ is enabled.

   SET polar_enable_px TO ON;
   EXPLAIN SELECT COUNT(*) FROM test;
                             QUERY PLAN
   --------------------------------------------------------------
    Aggregate  (cost=17.50..17.51 rows=1 width=8)
      ->  Seq Scan on test  (cost=0.00..15.00 rows=1000 width=0)
   (2 rows)

3. View the table size.

   Use the pg_relation_size function to view the table size.

   SELECT pg_size_pretty(pg_relation_size('test'));

   The result is as follows:

    pg_size_pretty
   ----------------
    40 kB
   (1 row)

   The result shows that the test table is 40 kB, which is smaller than the minimum table size specified by the polar_px_min_table_scan_size parameter. Therefore, queries that reference only the test table do not use ePQ, even if ePQ is enabled.

   SET polar_enable_px TO ON;
   EXPLAIN SELECT COUNT(*) FROM test AS a, test AS b, test AS c WHERE a.id > b.id AND b.id > c.id;
                                       QUERY PLAN
   ----------------------------------------------------------------------------------
    Aggregate  (cost=5292822.50..5292822.51 rows=1 width=8)
      ->  Nested Loop  (cost=0.00..5015045.00 rows=111111000 width=0)
            Join Filter: (b.id > c.id)
            ->  Nested Loop  (cost=0.00..15032.50 rows=333333 width=4)
                  Join Filter: (a.id > b.id)
                  ->  Seq Scan on test a  (cost=0.00..15.00 rows=1000 width=4)
                  ->  Materialize  (cost=0.00..20.00 rows=1000 width=4)
                        ->  Seq Scan on test b  (cost=0.00..15.00 rows=1000 width=4)
            ->  Materialize  (cost=0.00..20.00 rows=1000 width=4)
                  ->  Seq Scan on test c  (cost=0.00..15.00 rows=1000 width=4)
   (10 rows)

4. Clear the threshold.

   To ignore the execution cost and table size thresholds and force a query to use ePQ, set the polar_px_force_use parameter.

   SET polar_enable_px TO ON;
   SET polar_px_force_use TO ON;
   EXPLAIN SELECT COUNT(*) FROM test;

   The following result is returned:

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

5. Re-execution cost of the execution plan after a data insert.

   Insert more data into the table to meet the conditions for using ePQ.

   INSERT INTO test SELECT generate_series(1, 10000000);
   ANALYZE test;
   SELECT pg_size_pretty(pg_relation_size('test'));

   The following result is returned:

    pg_size_pretty
   ----------------
    348 MB
   (1 row)

   At this point, the size of the test table exceeds the minimum table size specified by the polar_px_min_table_scan_size parameter.

   The single-node execution plan cost for the following query also exceeds the minimum execution plan cost specified by the polar_px_min_pg_plan_cost parameter.

   SET polar_enable_px TO OFF;
   EXPLAIN SELECT COUNT(*) FROM test;

   The following output is returned.

                                          QUERY PLAN
   -----------------------------------------------------------------------------------------
    Finalize Aggregate  (cost=97621.42..97621.43 rows=1 width=8)
      ->  Gather  (cost=97621.21..97621.42 rows=2 width=8)
            Workers Planned: 2
            ->  Partial Aggregate  (cost=96621.21..96621.22 rows=1 width=8)
                  ->  Parallel Seq Scan on test  (cost=0.00..86205.77 rows=4166177 width=0)
   (5 rows)

   If ePQ is enabled, ePQ is used for the preceding query.

   SET polar_enable_px TO ON;
   SET polar_px_force_use TO OFF;
   EXPLAIN SELECT COUNT(*) FROM test;
                                         QUERY PLAN
   --------------------------------------------------------------------------------------
    Finalize Aggregate  (cost=0.00..470.76 rows=1 width=8)
      ->  PX Coordinator 6:1  (slice1; segments: 6)  (cost=0.00..470.76 rows=1 width=8)
            ->  Partial Aggregate  (cost=0.00..470.76 rows=1 width=8)
                  ->  Partial Seq Scan on test  (cost=0.00..467.66 rows=1666471 width=1)
    Optimizer: PolarDB PX Optimizer
   (5 rows)