From: Aleksej Trofimov <aleksej.trofimov@ruptela.lt>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres array parser
Date: Tue, 13 Dec 2011 16:28:32 +0200
Message-ID: <4EE76110.8060802@ruptela.lt> (raw)
In-Reply-To: <CAFj8pRBFQMboepy8MTPp4YT_L-qqGkpWgtuAD4bguf9ojfkwXQ@mail.gmail.com>
References: <4EE75936.8040806@ruptela.lt>
<CAFj8pRBFQMboepy8MTPp4YT_L-qqGkpWgtuAD4bguf9ojfkwXQ@mail.gmail.com>
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_-_konkurencin...http://www.ruptela.lt/news/37/121/Ruptela-sekmingiausia-jauna-aukstuju-technologiju-imone-Lietuvoje
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: aleksej.trofimov@ruptela.lt, pavel.stehule@gmail.com
Subject: Re: Postgres array parser
In-Reply-To: <4EE76110.8060802@ruptela.lt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox