pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Wil Peters <info@itaudit.demon.nl>
To: pgsql-performance@postgresql.org
Subject: Bad perfomance of pl/pgsql-function on new server
Date: Sat, 29 Mar 2003 22:17:26 +0100
Message-ID: <3E860D66.9030209@itaudit.demon.nl> (raw)

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




view thread (2+ messages)  latest in thread

Message-ID: <3E860D66.9030209@itaudit.demon.nl>
Permalink:  ../3E860D66.9030209@itaudit.demon.nl/
Also on:    postgresql.org/message-id/3E860D66.9030209@itaudit.demon.nl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: info@itaudit.demon.nl
  Subject: Re: Bad perfomance of pl/pgsql-function on new server
  In-Reply-To: <3E860D66.9030209@itaudit.demon.nl>
  References: <3E860D66.9030209@itaudit.demon.nl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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