Re: Speeding up loops in pl/pgsql function

On Wed, May 25, 2011 at 11:59 AM, Reuven M. Lerner <reuven(at)lerner(dot)co(dot)il> wrote:
> 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.

let's see the source. I bet we can get this figured out.

merlin