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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
