Re: Optimze usage of immutable functions as relation

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