Bad perfomance of pl/pgsql-function on new server

Hi,

I've written a pl/pgsql-function called 'f_matchstr' to support a
search-module on several websites. In short, the function scans the
content of a field and counts the occurances of a given search-string.

The complete function is listed below.

On a database-server that runs SuSE-linux 7.1 and PostgreSQL 7.2 the
function perfoms fine. Even when text-fields are accessed with large
volumes of text inside the response is OK. This is also very important,
because the search-module is used to scan articles that are stored in a
databasetable.

Recently the database-server is upgraded. It now runs SuSE 8.1 and
PostgreSQL 7.2. I copied the databases to the new server using
pg_dumpall etc.

On the new server - although this server has far better specs! - the
function does NOT perfom as well as on the old server. Searches take
several minutes, where on the old server a few SECONDS where needed.

As far as I can see the settings of PostgreSQL on both servers are the same.

Can someone help me with this problem??

Thanx,

Wil Peters
www.ldits.nl




-- Name: "f_matchstr" (text,text,integer,integer)
-- Type: FUNCTION
-- Owner: postgres

CREATE FUNCTION "f_matchstr" (text,text,integer,integer) RETURNS integer
AS 'DECLARE
    fld       text;         -- Field
    sstr      text;         -- Searchstring
    scptn     ALIAS FOR $3;    -- Case-sensitivity
    sxmtch    integer;      -- Exact-matching
    match     integer;      -- Number of matches
    i         integer;
    lenfld    integer;
    lensstr   integer;
    srchstr   text;
    middle    text;
    lenmiddle integer;
BEGIN
    fld     := $1;
    sstr    := $2;
    sxmtch  := $4;
    lenfld  := length(fld);
    lensstr := length(sstr);
    i       := 1;
    match    := 0;

    -- Work case insensitive
    IF scptn = 0 THEN
        fld  := lower(fld);  -- Set fieldcontent to lowercase
        sstr := lower(sstr); -- Set searchstring to lowercase
    END IF;

    IF lenfld = lensstr THEN
        sxmtch := 0;         -- Setting of sxmtch does not matter
    END IF;

    -- Set searchstring
    srchstr := '''' || sstr || '''';

    IF fld ~ srchstr THEN
        IF lensstr <= lenfld AND sxmtch = 0 THEN
        -- Walk trough fieldcontent
        WHILE i <= lenfld LOOP
            IF substring(fld,i,lensstr) = sstr THEN
            match := match + 1;
            END IF;
            i := i + 1;
            -- Escape from loop if 10 matches are reached
            IF match >= 10 THEN
            i := lenfld + 1;
            END IF;
        END LOOP;
        ELSIF lensstr < lenfld AND sxmtch = 1 THEN
        -- Set searchstring for begin of fieldcontent
        srchstr := ''^'' || sstr || ''[ ,:?!]+'';
        IF substring(fld,1,lensstr+1) ~ srchstr THEN
            match := match + 1;
        END IF;
        -- Set searchstring for end of fieldcontent
        srchstr := '' '' || sstr || ''[.?!]?$'';
        IF substring(fld,lenfld-lensstr-1,lensstr+2) ~ srchstr       THEN
            match := match + 1;
        END IF;
        -- Extract middle part of fieldcontent
        middle    := substring(fld,lensstr+1,lenfld-(2*lensstr));
        -- Store length of middle part
        lenmiddle := length(middle);
        -- Set searchstring for end of fieldcontent
        -- See below for regular expression thas is needed
        srchstr := ''[ >("\\'' || '''''' || '']+'' || sstr || ''[ ,.:?!)<"\\''
|| '''''' || '']+'';
        -- Walk trough middle part of fieldcontent
        WHILE i <= lenmiddle LOOP
            IF substring(middle,i,lensstr+2) ~ srchstr THEN
            match := match + 1;
            END IF;
            i := i + 1;
            -- Escape from loop if 10 matches are reached
            IF match >= 10 THEN
            i := lenmiddle + 1;
            END IF;
        END LOOP;
        END IF;
    END IF;
    RETURN match;
END;' LANGUAGE 'plpgsql';