Hi
I'm using cross join lateral with a non-trivial function in
an attempt to limit calculation of that function, and am
wondering about some aspects of how lateral is currently
implemented.
NB these queries are generated by a certain ORM, and are
usually embedded in much more complex queries...
Case one: counting
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.
Case two: pagination
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. Again, EXPLAIN shows
otherwise.
So: am I expecting too much for LATERAL, or have I missed a
trick somewhere?
Many thanks in advance!
Paul
--
View this message in context: http://postgresql.nabble.com/query-laziness-of-lateral-join-with-function-tp5837706.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
