Re: update inside function does not use the index

This helps me to understand for these common table expressions better.
Thanks. This looks more elegant than the cursor variant.

Limiting the cte to 10 records the update query needs 1.8 seconds. But
the cursor variant ( 10 records ) was finished in 0.7 seconds. I guess
it is faster, because behind the scenes no join is needed.

Best regards Johannes

Am 16.11.2015 um 15:22 schrieb Thomas Kellerer:
> Johannes schrieb am 16.11.2015 um 14:56:
>> 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;
>> 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 ).
>
>
> Doing row-by-row processing (also referred to as "slow-by-slow")  is usually not a good idea.
>
> I think your statement can be re-written to avoid the loop completely:
>
>     with path_levels as (
>       SELECT id,
>              level_ids,
>              path_names||'%' as path_names
>       from x
>     )
>     update x
>       set path_ids[i.level] = id
>     from path_levels i
>     where x.path_names like i.path_names
>
>
>
>
>