pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBad 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>
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 23:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wil Peters <info@itaudit.demon.nl>
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