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

On 29 March 2016 at 20:01, Achilleas Mantzios
<achill@matrix.gatewaynet.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