Message-ID: <4DDD358E.20307@lerner.co.il>
Date: Wed, 25 May 2011 19:59:58 +0300
From: "Reuven M. Lerner" <reuven@lerner.co.il>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Speeding up loops in pl/pgsql function
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi, everyone.  I'm working on a project that's using PostgreSQL 8.3, 
that requires me to translate strings of octal digits into strings of 
characters -- so '141142143' should become 'abc', although the database 
column containing this data (both before and after) is a bytea.


While the function I've written is accurate, it turns out that it's also 
ridiculously slow.  I've managed to speed it up a fair amount, to twice 
what it was previously doing, by folding a helper function into a main 
one, and appending to an array (which I then join into a string at the 
end of the function) instead of concatenating a string onto itself time 
after time.


I realize that pl/pgsql is not a good choice for doing this sort of 
task, and that another language -- say, one with direct support for 
octal digits, or with built-in, speedy array functions such as pop() and 
push()  -- would be a better choice.  But that's not an option at this 
point.


I should also note that I'm not manipulating a huge amount of data 
here.  We're talking about 300 or so rows, each of which contains about 
250 KB of data.  (Hmm, could the problem be that I'm constantly forcing 
the system to compress and uncompress the data in TOAST?  I hadn't 
thought of that until just now...)


I thus have two basic questions:


(1) Are there any good guidelines for what operations in pl/pgsql are 
optimized for which data structures?  For example, it turns out that a 
great deal of time is being spent in the substring() function, which 
surprised me.  I thought that by switching to an array, it might be 
faster, but that wasn't the case, at least in my tests.  Having a sense 
of what I should and shouldn't be trying, and which built-in functions 
are particularly fast or slow, would be useful to know.


(2) Is there any configuration setting that would (perhaps) speed things 
up a bit?  I thought that maybe work_mem would help, but the 
documentation didn't indicate this at all, and sure enough, nothing 
really changed when I increased it.


Of course, any suggestions for how to deal with octal digits in 
PostgreSQL 8.3, such as an octal equivalent to the x'ff' syntax, would 
be more than welcome.


Thanks in advance,


Reuven

-- 
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner