Bad perfomance of pl/pgsql-function on new server - Mailing list pgsql-performance
From | Wil Peters |
---|---|
Subject | Bad perfomance of pl/pgsql-function on new server |
Date | |
Msg-id | 3E860D66.9030209@itaudit.demon.nl Whole thread Raw |
Responses |
Re: Bad perfomance of pl/pgsql-function on new server
|
List | pgsql-performance |
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';
pgsql-performance by date: