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

2010/11/22 Andrew Dunstan <andrew@dunslane.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$
DECLAREs text[] := '{}';l int := 0; i int;v text;
BEGINFOR 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
>