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