Hello 2011/12/13 Aleksej Trofimov <aleksej.trofimov@xxxxxxxxxx>: > 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@xxxxxxxxxx>: >>> >>> 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@xxxxxxxxxxxxxx) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-performance > > It is strange - on my comp FOREACH is about 2x faster postgres=# select input_value(array(select generate_series(1,1000000)::numeric), 100000); input_value ------------- (1 row) Time: 495.426 ms postgres=# select input_value_fe(array(select generate_series(1,1000000)::numeric), 100000); input_value_fe ---------------- (1 row) Time: 248.980 ms Regards Pavel > > -- > Best regards > > Aleksej Trofimov > > UAB "Ruptela" > > Phone: +370 657 80475 > > E-Mail: aleksej.trofimov@xxxxxxxxxx > 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 > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
