paulcc <paulcc.two@gmail.com> writes:
> select count(alpha.id)
> from alpha
> cross join lateral some_function(alpha.id) as some_val
> where alpha.test
> Here the function is strict, and moreover its argument will never
> be null - hence there should always be a non-null value returned.
> I would expect that since the function doesn't impact on the
> number of rows (always one value returned for each row in alpha),
> then I'd hope the function is never called. EXPLAIN shows it being
> called for each row in the main table.
You're out of luck on that one at the moment, although testing it on
HEAD suggests that commit 55d5b3c08279b487cfa44d4b6e6eea67a0af89e4
might have fixed it for you in future releases.
> select alpha.*, some_val
> from alpha
> cross join lateral some_function(alpha.id) as some_val
> where alpha.test
> order by alpha.name asc
> limit 100 offset 100
> Same setup as above, and I'd expect that the ordering and
> selection of rows can be done first and the function only
> called on the rows that get selected.
The planner might produce such a result if there's an opportunity
to perform the sorting via an index on "alpha" (ie, the ORDER BY
matches some index). If it has to do an explicit sort it's gonna
do the join first.
(If you have such an index, and it's not going for the plan you want,
you might need to crank up the COST property of some_function to
persuade the planner that it should try to minimize the number of calls
even if that means a slower scan choice.)
In both cases though, I rather wonder why you're using LATERAL at all, as
opposed to just calling the function in the main query when you want its
result. The query planner can't be expected to make up for arbitrary
amounts of stupidity in the formulation of the submitted query.
regards, tom lane
