Re: Postgres array parser - Mailing list pgsql-performance

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.trofimov@ruptela.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@postgresql.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.trofimov@ruptela.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