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

Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> · Tue, 29 Mar 2016 14:55:30 +0300

    Hello David

      On 29/03/2016 14:04, David Rowley wrote:

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