Re: Optimze usage of immutable functions as relation

On 16/05/18 13:47, Aleksandr Parfenov wrote:
> Hello,
> 
> I reworked a patch to make more stable in different cases. I decided to
> use simplify_function instead of eval_const_expression to prevent
> inlining of the function. The only possible outputs of the
> simplify_function are Const node and NULL.

Hmm. That's still a bit inefficient, we'll try to simplify the function 
on every reference to it.

We already simplify functions in function RTEs, but we currently do it 
after preprocessing all other expressions in the query. See 
subquery_planner(), around comment "/* Also need to preprocess 
expressions within RTEs */". If we moved that so that we simplify 
expressions in the range table first, then in the code that you're 
adding to eval_const_expression_mutator(), you could just check if the 
function expression is already a Const.


But stepping back a bit, it's a bit weird that we're handling this 
differently from VALUES and other subqueries. The planner knows how to 
do this trick for simple subqueries:

postgres=# explain select * from tenk1, (select abs(100)) as a (a) where 
unique1 < a;
                         QUERY PLAN
-----------------------------------------------------------
  Seq Scan on tenk1  (cost=0.00..483.00 rows=100 width=248)
    Filter: (unique1 < 100)
(2 rows)

Note that it not only evaluated the function into a constant, but also 
got rid of the join. For a function RTE, however, it can't do that:

postgres=# explain select * from tenk1, abs(100) as a (a) where unique1 < a;
                             QUERY PLAN
-------------------------------------------------------------------
  Nested Loop  (cost=0.00..583.01 rows=3333 width=248)
    Join Filter: (tenk1.unique1 < a.a)
    ->  Function Scan on a  (cost=0.00..0.01 rows=1 width=4)
    ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)
(4 rows)

Could we handle this in pull_up_subqueries(), similar to the 
RTE_SUBQUERY and RTE_VALUES cases?

- Heikki