Horrible/never returning performance using stable function on WHERE clause

[Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx>]

Hello list,
I have written some functions to extract some data from our DB, from an hierarchical structure, the problem is that if a function doing lookups is defined as STABLE in the WHERE clause the performance 
is horrible. What I am trying to achieve is given a specific node in an hierarchical structure (a machine definition) to find all its equivalent sister nodes and then for a specific instance of this 
hierarchy to find the max RH (running hours) among all sister nodes.I am using some functions/opers from intarray. Here are the functions :

Compares two nodes for sister property:

CREATE OR REPLACE FUNCTION public.is_defid_sister_node(vdefid1 integer, vdefid2 integer)
 RETURNS boolean
 LANGUAGE plpgsql
 STABLE
AS $function$DECLARE
vparents1 INTEGER[];
vparents2 INTEGER[];
descr1 TEXT;
descr2 TEXT;
i INTEGER;
BEGIN

                SELECT COALESCE(partid,0)||'__'||regexp_replace(coalesce(description,''),'[nN][oO]([0-9]+)',''),parents into descr1,vparents1 FROM machdefs where defid=vdefid1;
                SELECT COALESCE(partid,0)||'__'||regexp_replace(coalesce(description,''),'[nN][oO]([0-9]+)',''),parents into descr2,vparents2 FROM machdefs where defid=vdefid2;

        IF (level(vparents1) = 0 AND level(vparents2) = 0) THEN
                RETURN vdefid1=vdefid2;
        ELSIF (level(vparents1) <> level(vparents2)) THEN
                RETURN false;
        ELSE
                RETURN ((descr1=descr2) AND is_defid_sister_node(first(vparents1),first(vparents2)));
        END IF;

END;$function$

Finds the set of sister nodes for a given node:

CREATE OR REPLACE FUNCTION public.get_machdef_sister_defids(vdefid integer)
 RETURNS INTEGER[]
 LANGUAGE plpgsql
 STABLE
AS $function$
DECLARE
tmp INTEGER[];
BEGIN

        select (select array_agg(mdsis.defid) FROM machdefs mdsis WHERE mdsis.machtypeid=md.machtypeid AND level(mdsis.parents)=level(md.parents) AND last(mdsis.parents)=last(md.parents) AND 
is_defid_sister_node(mdsis.defid,md.defid)  ) INTO tmp from machdefs md where md.defid=vdefid;

        IF (tmp IS NULL) THEN
                tmp := '{}';
        END IF;
        RETURN tmp;

END;
$function$

Finds max RH for a given tree instance among all sister nodes of a given node :

CREATE OR REPLACE FUNCTION public.get_machdef_sister_defids_maxrh(vvslid INTEGER,vdefid INTEGER)
 RETURNS INTEGER
 LANGUAGE plpgsql
 STABLE
AS $function$
DECLARE
tmp INTEGER;
BEGIN
        select max(rh) into tmp from items where vslwhid=vvslid and itoar(defid) ~ get_machdef_sister_defids(vdefid);
        RETURN tmp;
END;
$function$


Query :
select get_machdef_sister_defids_maxrh(479,319435);

never ends (I have waited till 2-3 minutes), however, *doing the wrong thing* and declaring get_machdef_sister_defids as IMMUTABLE makes the above call return fast :

# select get_machdef_sister_defids_maxrh(479,319435);
 get_machdef_sister_defids_maxrh
---------------------------------
                           10320
(1 row)

Time: 110.211 ms

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 ??

Defining get_machdef_sister_defids back to STABLE and forcing get_machdef_sister_defids_maxrh to only call get_machdef_sister_defids once makes things work again :

CREATE OR REPLACE FUNCTION public.get_machdef_sister_defids_maxrh(vvslid integer, vdefid integer)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE
AS $function$
DECLARE
tmp INTEGER;
tmppars INTEGER[];
BEGIN
        tmppars := get_machdef_sister_defids(vdefid);
        select max(rh) into tmp from items where vslwhid=vvslid and itoar(defid) ~ tmppars;
        RETURN tmp;
END;
$function$

# select get_machdef_sister_defids_maxrh(479,319435);
 get_machdef_sister_defids_maxrh
---------------------------------
                           10320
(1 row)

Time: 111.318 ms

Is this expected ?

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



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general