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