Thread: update inside function does not use the index
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; 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
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 > -- Adrian Klaver adrian.klaver@aklaver.com
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
On 11/16/2015 08:03 AM, Johannes wrote: Ccing list > 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; So what do you see if you do?: EXPLAIN ANALYZE select ... your_function(...); > > 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 >>> >> >> > -- Adrian Klaver adrian.klaver@aklaver.com
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
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 > > > > >
Attachment
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
A function seams to be atomic for the analyze command (or?) EXPLAIN ANALYZE select my_function(); returns no inner query plan, just the costs, rows and width.... Am 16.11.2015 um 17:57 schrieb Adrian Klaver: > EXPLAIN ANALYZE select ... your_function(...);
Attachment
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 > >