Re: patch: fix performance problems with repated decomprimation of varlena values in plpgsql

2010/11/22 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> On 11/22/2010 07:46 AM, Pavel Stehule wrote:
>>
>> Hello
>>
>> this patch remove a multiple detoasting of varlena values in plpgsql.
>>
>> It is usable mainly for iteration over longer array directly loaded
>> from relation.
>>
>> It's doesn't have a impact on semantic or behave - it's just eliminate
>> some performance trap.
>>
>> sample: table 10000 rows one column with array with 1000 string fields:
>>
>> patched pl time: 6 sec
>> unpatched pl time: 170 sec
>>
>
> Since you haven't told us exactly how you tested this it's hard to gauge the
> test results.

sorry - it is related to tests from FOR-IN-ARRAY thread

create table t1000(x text[]);

CREATE OR REPLACE FUNCTION rndstr() RETURNS text AS $$select
array_to_string(array(select substring('ABCDEFGHIJKLMNOPQ' FROM
(random()*16)::int FOR 1) from generate_series(1,10)),'')$$ LANGUAGE
sql;

create or replace function rndarray(int) returns text[] as
$$select array(select rndstr() from generate_series(1,$1)) $$ language sql;

insert into t1000 select rndarray(1000) from generate_series(1,10000);

CREATE OR REPLACE FUNCTION public.filter02(text[], text, integer)
RETURNS text[]
LANGUAGE plpgsql
AS $function$
DECLARE
s text[] := '{}';
l int := 0; i int;
v text;
BEGIN
FOR i IN array_lower($1,1)..array_upper($1,1)
LOOP
EXIT WHEN l = $3;
IF $1[i] LIKE $2 THEN
s := s || $1[i];
l := l + 1;
END IF;
END LOOP;
RETURN s;
END;$function$

test query: select avg(array_upper(filter02(x,'%AA%', 10),1)) from t1000;

Regards

Pavel Stehule

>
> cheers
>
> andrew
>