No, i did a mistake while simplifying it. It should be FOR i IN SELECT id, level, path_names||'%' as path_names from x LOOP update x set path_ids[i.level] = i.id where path_names like i.path_names; Sorry. Best regards Johannes Am 16.11.2015 um 15:10 schrieb Adrian Klaver: > On 11/16/2015 05:56 AM, Johannes wrote: >> Dear List, >> >> I have problems with a self written function, which does not use the >> index, which takes very long (500 ms per update). >> >> >> The pl/pgsql function iterates over a select resultset with a cursor. >> In every loop I execute an update with a where LIKE condition, which >> relates to my current cursor position: >> >> FOR i IN SELECT id, level_ids, path_names||'%' as path_names from x LOOP >> update x set path_ids[i.level] = id where path_names like i.path_names; > > Is this the actual UPDATE in the function? > > If so, where are i.level and id coming from? > > Or is that supposed to be?: > > update x set path_ids[i.level_ids] = i.id where path_names like > i.path_names; > >> RAISE NOTICE 'path_names : %', i.path_names; >> END LOOP; >> >> >> >> Calling the updates outside the function, they are very fast because >> like 'a.b%' uses the index of the path field ( ~ 15 ms ). >> >> Does anyone know how to fix that? >> Thanks, Johannes >> > >
Attachment:
signature.asc
Description: OpenPGP digital signature
