Re: Horrible/never returning performance using stable function on WHERE clause

Hello David

On 29/03/2016 14:04, David Rowley wrote:
> On 29 March 2016 at 20:01, Achilleas Mantzios
> <achill(at)matrix(dot)gatewaynet(dot)com> wrote:
>> We are using PostgreSQL 9.3. Shouldn't the optimizer use a single call to
>> get_machdef_sister_defids in get_machdef_sister_defids_maxrh ??
>>
>> It shouldn't be up to the optimizer to evaluate a STABLE function.
>> Only IMMUTABLE functions will be evaluated during planning.
>> What's not that clear to me is if the planner might be able to work a
>> bit harder to create an "Initplan" for stable functions with Const
>> arguments. Right now I can't quite see a reason why that couldn't be
>> improved upon, after all, the documentation does claim that a STABLE
>> function during a "single table scan it will consistently return the
>> same result for the same argument values".
And to add here the docs (http://www.postgresql.org/docs/9.3/static/xfunc-volatility.html) also say :
"A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. *This category allows the optimizer to
optimize multiple calls of the function to a single call*. In particular, it is safe to use an expression containing such a function in an index scan condition."
>> However it would be quite simple just for you to force the STABLE
>> function to be evaluated once, instead of once per row, just by
>> modifying your query to become:
>>
>> select max(rh) into tmp from items where vslwhid=vvslid and
>> itoar(defid) ~ (select get_machdef_sister_defids(vdefid));
>>
>> Viewing the EXPLAIN of this, you'll notice the InitPlan, which will
>> evaluate the function and allow the use the output value as a
>> parameter in the main query.
That's true, this worked indeed. But still cannot understand why the distinction between ~ get_machdef_sister_defids(...) and ~ (SELECT get_machdef_sister_defids(...)).
Why is the planner forced in the second case and not in the first, since clearly the input argument is not dependent on any query result? (judging by the docs).

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt