Fold Stable Functions in Sublinks

[24nishant]

trafficstars

Enhancement

Currently in ORCA, query is checked for 'constant folding' (using fold_constants()) before invoking ORCA in optimize_query(). Using Folding, function calls are tried to be inlined or computed to a constant.

Before going for Folding, one of the checks also finds whether 'stable' functions in the query can also be evaluated. The checks involves

root->query_level > 1 or
RTE count > 0

Thus, if we don't have a RTE in the outer query, then none of the stable functions are folded. To solve this, following 2 approaches were tried but couldn't be concluded. Approach 1 - https://github.com/greenplum-db/gpdb/pull/17073 Approach 2 - https://github.com/greenplum-db/gpdb/pull/17141 It was decided to work more on the Approach1, for time being both the above PRs were closed.

Performing this, optimization can lead to performance gain, specially in a scenario where the function needs to be evaluated multiple time. Eg

CREATE OR REPLACE FUNCTION test_func_im(a int, b int)
 RETURNS int
 LANGUAGE sql
 IMMUTABLE
 AS $$
 SELECT a + b + 10;
 $$;

 CREATE OR REPLACE FUNCTION test_func_stable(a int)
 RETURNS int
 LANGUAGE sql
 STABLE
 AS $$
 SELECT (ceil(a + 10 + random())::int);
 $$;

 drop table if exists bar_pt;
 create table bar_pt (a int, b int) distributed by (a) partition by range(a) (start (1) inclusive end (12) every (2), default partition other);
 insert into bar_pt select i,i from generate_series(1,11)i;
 analyze bar_pt;

Current Main output -

 explain select test_func_im(10, (select min(a) from bar_pt where a < test_func_stable(6)));
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..882688.28 rows=2 width=4)
   Join Filter: true
   ->  Result  (cost=0.00..0.00 rows=1 width=1)
   ->  Materialize  (cost=0.00..431.00 rows=1 width=4)
         ->  Finalize Aggregate  (cost=0.00..431.00 rows=1 width=4)
               ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=4)
                     ->  Partial Aggregate  (cost=0.00..431.00 rows=1 width=4)
                           ->  Dynamic Seq Scan on bar_pt  (cost=0.00..431.00 rows=2 width=4)
                                 Number of partitions to scan: 7 (out of 7)
                                 Filter: (a < test_func_stable(6))
 Optimizer: GPORCA
(11 rows)

After the optimization, the function call test_func_stable(6) can be update to a constant 17.