Updated on 2024-07-04 GMT+08:00

Dynamic PBE Pruning

Dynamic PBE pruning is supported in the following scenarios:
  1. Supported partitioning types: range partitioning, hash partitioning, and list partitioning.
  2. Supported expression types: comparison expression (<, <=, =, >=, >), logical expression, and array expression.
  3. Supported conversions and functions: some implicit type conversions and the IMMUTABLE function.
  • Dynamic PBE pruning supports expressions, implicit conversions, the IMMUTABLE function, and the STABLE function, but does not support subquery expressions or VOLATILE function. For the STABLE function, type conversion functions such as to_timestamp may be affected by GUC parameters and lead to different pruning results. To ensure performance optimization, you can analyze table to regenerate a gplan.
  • Dynamic PBE pruning is based on the generic plan. Therefore, when determining whether a statement can be dynamically pruned, you need to set plan_cache_mode to 'force_generic_plan' to eliminate the interference of the custom plan.
  • Typical scenarios where dynamic PBE pruning is supported are as follows:
    1. Comparison expressions
      -- Create a partitioned table.
      gaussdb=# CREATE TABLE t1 (c1 int, c2 int)
      PARTITION BY RANGE (c1)
      (
          PARTITION p1 VALUES LESS THAN(10),
          PARTITION p2 VALUES LESS THAN(20),
          PARTITION p3 VALUES LESS THAN(MAXVALUE)
      );
      
      gaussdb=# PREPARE p1(int) AS SELECT * FROM t1 WHERE c1 = $1;
      PREPARE
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p1(1);
                               QUERY PLAN                         
      ------------------------------------------------------------
       Data Node Scan
         Output: t1.c1, t1.c2
         Node/s: datanode1
         Node expr: $1
         Remote query: SELECT c1, c2 FROM public.t1 WHERE c1 = $1
      
       Remote SQL: SELECT c1, c2 FROM public.t1 WHERE c1 = $1
       Datanode Name: datanode1
         Partition Iterator
           Output: c1, c2
           Iterations: PART
           ->  Partitioned Seq Scan on public.t1
                 Output: c1, c2
                 Filter: (t1.c1 = $1)
                 Selected Partitions:  1 (pbe-pruning)
      
      (16 rows)
    2. Logical expressions
      gaussdb=# PREPARE p2(INT, INT) AS SELECT * FROM t1 WHERE c1 = $1 AND c2 = $2;
      PREPARE
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p2(1, 2);
                                     QUERY PLAN                               
      ------------------------------------------------------------------------
       Data Node Scan
         Output: t1.c1, t1.c2
         Node/s: datanode1
         Node expr: $1
         Remote query: SELECT c1, c2 FROM public.t1 WHERE c1 = $1 AND c2 = $2
      
       Remote SQL: SELECT c1, c2 FROM public.t1 WHERE c1 = $1 AND c2 = $2
       Datanode Name: datanode1
         Partition Iterator
           Output: c1, c2
           Iterations: PART
           ->  Partitioned Seq Scan on public.t1
                 Output: c1, c2
                 Filter: ((t1.c1 = $1) AND (t1.c2 = $2))
                 Selected Partitions:  1 (pbe-pruning)
      
      (16 rows)
    3. Implicit type conversion
      gaussdb=# set plan_cache_mode = 'force_generic_plan';
      gaussdb=# PREPARE p3(TEXT) AS SELECT * FROM t1 WHERE c1 = $1;
      PREPARE
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p3('12');
                                   QUERY PLAN                             
      --------------------------------------------------------------------
       Data Node Scan
         Output: t1.c1, t1.c2
         Node/s: datanode1
         Node expr: $1
         Remote query: SELECT c1, c2 FROM public.t1 WHERE c1 = $1::bigint
      
       Remote SQL: SELECT c1, c2 FROM public.t1 WHERE c1 = $1::bigint
       Datanode Name: datanode1
         Partition Iterator
           Output: c1, c2
           Iterations: PART
           ->  Partitioned Seq Scan on public.t1
                 Output: c1, c2
                 Filter: (t1.c1 = ($1)::bigint)
                 Selected Partitions:  2 (pbe-pruning)
      
      (16 rows)
  • Typical scenarios where dynamic PBE pruning is not supported are as follows:
    1. Subquery expressions
      gaussdb=# PREPARE p4(INT) AS SELECT * FROM t1 WHERE c1 = ALL(SELECT c2 FROM t1 WHERE c1 > $1);
      PREPARE
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p4(1);
                                     QUERY PLAN                                
      -------------------------------------------------------------------------
       Streaming (type: GATHER)
         Output: public.t1.c1, public.t1.c2
         Node/s: All datanodes
         ->  Partition Iterator
               Output: public.t1.c1, public.t1.c2
               Iterations: 3
               ->  Partitioned Seq Scan on public.t1
                     Output: public.t1.c1, public.t1.c2
                     Distribute Key: public.t1.c1
                     Filter: (SubPlan 1)
                     Selected Partitions:  1..3
                     SubPlan 1
                       ->  Materialize
                             Output: public.t1.c2
                             ->  Streaming(type: BROADCAST)
                                   Output: public.t1.c2
                                   Spawn on: All datanodes
                                   Consumer Nodes: All datanodes
                                   ->  Partition Iterator
                                         Output: public.t1.c2
                                         Iterations: 3
                                         ->  Partitioned Seq Scan on public.t1
                                               Output: public.t1.c2
                                               Distribute Key: public.t1.c1
                                               Filter: (public.t1.c1 > 1)
                                               Selected Partitions:  1..3
      (26 rows)
    2. Implicit type conversion failure
      gaussdb=# PREPARE p5(name) AS SELECT * FROM t1 WHERE c1 = $1;
      PREPARE
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p5('12');
                                      QUERY PLAN                                
      --------------------------------------------------------------------------
       Data Node Scan
         Output: t1.c1, t1.c2
         Node/s: All datanodes
         Remote query: SELECT c1, c2 FROM public.t1 WHERE c1::text = '12'::text
      
       Remote SQL: SELECT c1, c2 FROM public.t1 WHERE c1::text = '12'::text
       Datanode Name: datanode1
         Partition Iterator
           Output: c1, c2
           Iterations: 3
           ->  Partitioned Seq Scan on public.t1
                 Output: c1, c2
                 Filter: ((t1.c1)::text = '12'::text)
                 Selected Partitions:  1..3
      
      (15 rows)
    3. STABLE and VOLATILE functions
      gaussdb=# create sequence seq;
      gaussdb=# PREPARE p6(TEXT) AS SELECT * FROM t1 WHERE c1 = currval($1);-- The VOLATILE function does not support pruning.
      PREPARE
      gaussdb=# EXPLAIN (VERBOSE ON, COSTS OFF) EXECUTE p6('seq');
                                       QUERY PLAN                                 
      ----------------------------------------------------------------------------
       Data Node Scan
         Output: t1.c1, t1.c2
         Node/s: All datanodes
         Remote query: SELECT c1, c2 FROM ONLY public.t1 WHERE true
         Coordinator quals: ((t1.c1)::numeric = currval(('seq'::text)::regclass))
      
       Remote SQL: SELECT c1, c2 FROM ONLY public.t1 WHERE true
       Datanode Name: datanode1
         Partition Iterator
           Output: c1, c2
           Iterations: 3
           ->  Partitioned Seq Scan on public.t1
                 Output: c1, c2
                 Selected Partitions:  1..3
      
      (15 rows)
      
      -- Clean up the environment.
      gaussdb=# DROP TABLE t1;