On Wed, May 25, 2011 at 11:59 AM, Reuven M. Lerner <reuven@xxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
