Обсуждение: Postgresql array parser

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?

--

On Tue, Dec 13, 2011 at 3:16 AM, Aleksej Trofimov
<aleksej.trofimov@ruptela.lt> wrote:
> 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?

A better way to do a brute force loop of arrays came along in 9.1:

http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY.

A slightly smarter lookup could be done if you were willing to make a
key, value composite type like so:
postgres=# create type pair as (key text, value text);
CREATE TYPE

postgres=# select array[row('a', 'b'), row('c', 'd')]::pair[];
       array
-------------------
 {"(a,b)","(c,d)"}
(1 row)

postgres=# select * from (select unnest( array[row('a', 'b'), row('c',
'd')]::pair[]) as pair) q where (pair).key = 'a';
 pair
-------
 (a,b)
(1 row)

postgres=# select (pair).* from (select unnest( array[row('a', 'b'),
row('c', 'd')]::pair[]) as pair) q where (pair).key = 'a';
 key | value
-----+-------
 a   | b
(1 row)

However, if you are managing large key-value lists though you really
should either A. looking at hstore (hstore is a generalization of
key/value storage in a single column and supports GIST/GIN for
indexing):

http://www.postgresql.org/docs/9.1/interactive/hstore.html

or B. normalizing your structure (why have you not already done this?).

merlin