Greg Stark <gsstark@mit.edu> writes:
> On Sat, 22 Jan 2005 17:09:42 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I would imagine that the performance is spectacularly awful :-(.
>> Have you benchmarked it? A large sort on a unitext column,
>> for instance, would be revealing.
> Why do you persist in believing this? I sent timing results of doing a
> setlocale for every record here about a year ago. Sorting on the pg_strxfrm I
> posted (and Conway rewrote) was about twice as slow as sorting without using
> it. So it's slow but not spectacularly awful.
glibc is not the world. I tried Dawid's functions on Mac OS X, being a
random non-glibc platform that I happen to use. Using some text data
I had handy (44500 lines, 1.9MB) I made a single-column text table and
timedexplain analyze select * from foo order by f1;
The results wereIn C locale, SQL_ASCII encoding: 820 msIn C locale, UNICODE encoding: 825 msUsing Dawid's
functions: 62010 msStripped-down functions: 21010 ms
The "stripped down" functions were the same functions without the
locale overhead, eg
CREATE OR REPLACE FUNCTION unitext_le(unitext,unitext) RETURNS boolean AS $$ my $ret = ($_[0] le $_[1]) ? 't' :
'f'; return $ret;
$$ LANGUAGE plperlu STABLE;
so we may conclude that about one-third of the overhead is plperl's
fault and the other two-thirds is setlocale's fault. But it's still
a factor of 50 slowdown to do it this way (actually worse, since not
all of the EXPLAIN ANALYZE total runtime went into sorting).
I'm not sure what your threshold of "spectacularly awful" is, but that
meets mine.
regards, tom lane