Re: Postgres array parser

We have tried foreach syntax, but we have noticed performance degradation:
Function with for: 203ms
Function with foreach: ~250ms:

there is functions code:
CREATE OR REPLACE FUNCTION input_value_fe(in_inputs numeric[],
in_input_nr numeric)
RETURNS numeric AS
$BODY$
declare i numeric[];
BEGIN
FOREACH i SLICE 1 IN ARRAY in_inputs
LOOP
if i[1] = in_input_nr then
return i[2];
end if;
END LOOP;

return null;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE OR REPLACE FUNCTION input_value(in_inputs numeric[], in_input_nr
numeric)
RETURNS numeric AS
$BODY$
declare
size int;
BEGIN
size = array_upper(in_inputs, 1);
IF size IS NOT NULL THEN
FOR i IN 1 .. size LOOP
if in_inputs[i][1] = in_input_nr then
return in_inputs[i][2];
end if;
END LOOP;
END IF;

return null;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

On 12/13/2011 04:02 PM, Pavel Stehule wrote:
> Hello
>
> do you know FOREACH IN ARRAY statement in 9.1
>
> this significantly accelerate iteration over array
>
> http://www.depesz.com/index.php/2011/03/07/waiting-for-9-1-foreach-in-array/
>
>
>
> 2011/12/13 Aleksej Trofimov<aleksej(dot)trofimov(at)ruptela(dot)lt>:
>> Hello, I wanted to ask according such a problem which we had faced with.
>> We are widely using postgres arrays like key->value array by doing like
>> this:
>>
>> {{1,5},{2,6},{3,7}}
>>
>> where 1,2,3 are keys, and 5,6,7 are values. In our pgSql functions we are
>> using self written array_input(array::numeric[], key::numeric) function
>> which makes a loop on whole array and searches for key like
>> FOR i IN 1 .. size LOOP
>> if array[i][1] = key then
>> return array[i][2];
>> end if;
>> END LOOP;
>>
>> But this was a good solution until our arrays and database had grown. So now
>> FOR loop takes a lot of time to find value of an array.
>>
>> And my question is, how this problem of performance could be solved? We had
>> tried pgperl for string parsing, but it takes much more time than our
>> current solution. Also we are thinking about self-written C++ function, may
>> be someone had implemented this algorithm before?
>>
> you can use indexes or you can use hstore
>
> Regards
>
> Pavel Stehule
>
>> --
>> Best regards
>>
>> Aleksej Trofimov
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance

--
Best regards

Aleksej Trofimov

UAB "Ruptela"

Phone: +370 657 80475

E-Mail: aleksej(dot)trofimov(at)ruptela(dot)lt
Web: http://www.ruptela.lt

Ruptela - the most successful IT company in Lithuania 2011
Ruptela - sekmingiausia Lietuvos aukštųjų technologijų įmonė 2011
http://www.prezidentas.lt/lt/spaudos_centras_392/pranesimai_spaudai/inovatyvus_verslas_-_konkurencingos_lietuvos_pagrindas.html
http://www.ruptela.lt/news/37/121/Ruptela-sekmingiausia-jauna-aukstuju-technologiju-imone-Lietuvoje