Re: Optimze usage of immutable functions as relation

On Tue, 10 Jul 2018 17:34:20 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>> 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?
>
>Perhaps. You could only do it for non-set-returning functions, which
>isn't the typical use of function RTEs, which is probably why we've not
>thought hard about it before. I'm not sure what would need to happen
>for lateral functions. Also to be considered, if it's not foldable to
>a constant, is whether we're risking evaluating it more times than
>before.
>
> regards, tom lane

I reworked the patch and implemented processing of FuncScan in
pull_up_subqueries() in a way similar to VALUES processing. In order to
prevent folding of non-foldable functions it checks provolatile of the
function and are arguments const or not and return type to prevent
folding of SRF.

--
Aleksandr Parfenov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company