Re: update inside function does not use the index

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 11/16/2015 08:03 AM, Johannes wrote:
>>> 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;

Probably the problem is that the planner is unable to fold i.path_names
to a constant, so it can't derive an indexscan condition from the LIKE
clause.

A little bit of experimentation says that that will work if "i" is
declared with a named rowtype, but not if it's declared RECORD.  This
might or might not be something we could fix, but in the meantime I'd
try

DECLARE i x%rowtype;

FOR i IN SELECT * FROM x LOOP
update x set path_ids[i.level] = id where path_names like (i.path_names || '%');

which while it might look less "constant" is actually more so from the
planner's perspective, because there is no question of whether "i" has
got a field of that name.

            regards, tom lane