gpdb icon indicating copy to clipboard operation
gpdb copied to clipboard

[ORCA] Merge Filter in PdxlnFromFilter.

Open fishtree1161 opened this issue 1 year ago • 5 comments

In some cases, ORCA will generate a chain of CPhysicalFilter Node in plan, and they will be translated to a chain of Result Node, especially in CTE cases. For example:

EXPLAIN (costs off)
WITH cte1 AS (SELECT j, k FROM merge_filter_test1 WHERE i > 1),
cte2 AS (SELECT j, k FROM cte1 WHERE j < 40)
SELECT * FROM cte2 WHERE k > 101 ORDER BY 1,2;
                       QUERY PLAN
--------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   Merge Key: j, k
   ->  Sort
         Sort Key: j, k
         ->  Result
               Filter: (k > 101)
               ->  Result
                     Filter: (j < 40)
                     ->  Seq Scan on merge_filter_test1
                           Filter: (i > 1)
 Optimizer: GPORCA
(11 rows)

There are some reasons to merge these filters:

  1. The plan seems ugly.
  2. May lead to a deep function invocation stack in the executor.
  3. Scan can't get all filter, and may miss some storage optimization.

fishtree1161 avatar Jan 12 '24 07:01 fishtree1161