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';