pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Bad perfomance of pl/pgsql-function on new server
2+ messages / 2 participants
[nested] [flat]

* Bad perfomance of pl/pgsql-function on new server
@ 2003-03-29 21:17 Wil Peters <info@itaudit.demon.nl>
  2003-03-29 23:25 ` Re: Bad perfomance of pl/pgsql-function on new server Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Wil Peters @ 2003-03-29 21:17 UTC (permalink / raw)
  To: 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';




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Bad perfomance of pl/pgsql-function on new server
  2003-03-29 21:17 Bad perfomance of pl/pgsql-function on new server Wil Peters <info@itaudit.demon.nl>
@ 2003-03-29 23:25 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2003-03-29 23:25 UTC (permalink / raw)
  To: Wil Peters <info@itaudit.demon.nl>; +Cc: pgsql-performance

Wil Peters <info@itaudit.demon.nl> writes:
> 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.

Is the new installation really equivalent to the old?  I'd wonder about
differences in multibyte compilation option, database locale and
encoding, etc.  Any of these could result in a huge hit in text-pushing
performance.

Another traditional post-upgrade problem is forgetting to VACUUM
ANALYZE; but that probably shouldn't affect this function, since it's
not issuing any database queries.

(Personally I'd have written this sort of function in plperl or pltcl,
either of which are far more appropriate for text-string-mashing than
plpgsql.  But that's not really answering your question.)

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2003-03-29 23:25 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-29 21:17 Bad perfomance of pl/pgsql-function on new server Wil Peters <info@itaudit.demon.nl>
2003-03-29 23:25 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox