From: Reuven M. Lerner <reuven@lerner.co.il>
To: pgsql-performance@postgresql.org
Subject: Speeding up loops in pl/pgsql function
Date: Wed, 25 May 2011 19:59:58 +0300
Message-ID: <4DDD358E.20307@lerner.co.il> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: reuven@lerner.co.il
Subject: Re: Speeding up loops in pl/pgsql function
In-Reply-To: <4DDD358E.20307@lerner.co.il>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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