Tom Lane-2 wrote
> paulcc <
> paulcc.two@
> > 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.
>
> 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.
I'm trying to answer this with a bit more detail but cannot because the OP
provided too little information which is then causing Tom to make
assumptions. I'm not sure to what degree the ORM is being stupid here since
I do not know why it thinks LATERAL is more appropriate than a select-list
function call for a non-SRF function (which I have to presume this is, but
it is not stated).
With respect to "the function will never return NULL": this is not the
issue. The issue is that the function could return nothing (i.e., zero
records) in which case the CROSS JOIN would suppress the corresponding
correlated row from the result.
Non-SRF functions are more easily used within the select-list of the query
instead of attached to a LATERAL clause; the only issue there is when the
function returns a composite and you try to immediately explode it into its
constituent parts - the function will be evaluated multiple times. I'm not
sure if that is what Tom is saying above but the combination of that
limitation and limited optimizations if the function is in LATERAL seems to
be in conflict here.
There has been a recent uptick in interest in making PostgreSQL more ORM
friendly (i.e., more able to simply ignore stuff that is added to the query
even though a particular call doesn't actually need it) but I haven't seen
anyone looking into LATERAL. More detailed reports may at least bring
exposure to what is being used in the wild and garner interest from other
parties in improving things. Unfortunately this report is too limited to
really make a dent; lacking even the name of the ORM that is being used and
the entire queries that are being generated - and why.
David J.
--
View this message in context:
http://postgresql.nabble.com/query-laziness-of-lateral-join-with-function-tp5837706p5837735.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
