X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1810C475A8D for ; Sat, 29 Mar 2003 16:16:06 -0500 (EST) Received: from post-20.mail.nl.demon.net (post-20.mail.nl.demon.net [194.159.73.1]) by postgresql.org (Postfix) with ESMTP id B002E474E42 for ; Sat, 29 Mar 2003 16:16:04 -0500 (EST) Received: from [212.238.27.227] (helo=itaudit.demon.nl) by post-20.mail.nl.demon.net with esmtp (Exim 3.36 #1) id 18zNgK-000O4w-00 for pgsql-performance@postgresql.org; Sat, 29 Mar 2003 21:16:05 +0000 Message-ID: <3E860D66.9030209@itaudit.demon.nl> Date: Sat, 29 Mar 2003 22:17:26 +0100 From: Wil Peters User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Bad perfomance of pl/pgsql-function on new server Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-12.1 required=5.0 tests=BAYES_10,USER_AGENT_MOZILLA_UA autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/196 X-Sequence-Number: 1494 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';