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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 JohannesAm 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: 5649FE39.8000501@posteo.de
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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
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:
Show quoted text
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
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:
Show quoted text
EXPLAIN ANALYZE select ... your_function(...);
That solves my problem. Thanks!!
Best regards Johannes
Am 16.11.2015 um 18:19 schrieb Tom Lane:
Show quoted text
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
tryDECLARE 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