Message-ID: <4EE75936.8040806@ruptela.lt>
Date: Tue, 13 Dec 2011 15:55:02 +0200
From: Aleksej Trofimov <aleksej.trofimov@ruptela.lt>
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:8.0) Gecko/20111124 Thunderbird/8.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Postgres array parser
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

-- 
Best regards

Aleksej Trofimov