Postgres array parser

From: Aleksej Trofimov <aleksej(dot)trofimov(at)ruptela(dot)lt>
To: pgsql-performance(at)postgresql(dot)org
Subject: Postgres array parser
Date: 2011-12-13 13:55:02
Message-ID: 4EE75936.8040806@ruptela.lt
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Pavel Stehule 2011-12-13 14:02:29 Re: Postgres array parser
Previous Message Merlin Moncure 2011-12-12 16:38:04 Re: copy vs. C function