gpdb
gpdb copied to clipboard
[ORCA] Merge Filter in PdxlnFromFilter.
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:
- The plan seems ugly.
- May lead to a deep function invocation stack in the executor.
- Scan can't get all filter, and may miss some storage optimization.