Re: Concatenate performance question

"Michael Guyver" <kenevel(at)googlemail(dot)com> writes:
> CREATE OR REPLACE FUNCTION test_v_01() RETURNS VARCHAR AS $$
> DECLARE
> buffer varchar;
> i int4;
> BEGIN
> buffer := 'the quick brown fox jumps over the lazy dog';
> FOR i IN 1..1000 LOOP
> buffer := buffer || 'the quick brown fox jumps over the lazy dog';
> END LOOP;
> RETURN buffer;
> END;
> $$
> LANGUAGE plpgsql;

I looked into this a bit with gprof/oprofile, and found that there are
two issues skewing the results of this test:

* the function ought to be declared STABLE or even IMMUTABLE. The fact
that it is not creates significant snapshot/command-counter overhead.
(This cost is about the same for both variants, though.)

* the buffer variable, and probably the function result too, ought to be
declared TEXT not VARCHAR. That's because the result of the concat
operator is always TEXT, and plpgsql is not bright about the fact that
the conversion from text to varchar could be optimized away. There
isn't any comparable cost for the array_append approach because of the
way that the result type of array_append() is determined --- it comes
out varchar[].

After correcting these issues I find that the concat approach is about
20% faster than the array_append approach, which is more in line with
what I expected.

regards, tom lane