Re: update inside function does not use the index

That solves my problem. Thanks!!

Best regards Johannes

Am 16.11.2015 um 18:19 schrieb Tom Lane:
> 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
>
>