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@xxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
