Re: Postgresql array parser

On Tue, Dec 13, 2011 at 3:16 AM, Aleksej Trofimov
<aleksej(dot)trofimov(at)ruptela(dot)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