Hackers,
In src/pl/plpgsql/src/pl_exec.c: exec_run_select intentionally does not
allow a parallel plan if a portal will be returned. This has the practical
consequence that a common coding practice (at least for me) of doing
something like:
create function myfunc(arg1 text, arg2 text) returns setof myfunctype as $$
declare
sql text;
result myfunctype;
begin
-- unsafe interpolation, but this is just a code example
sql := 'select foo from bar where a = ' || arg1 || ' and b = ' || arg2;
for result in execute sql loop
return next result;
end loop;
return;
end;
$$ language plpgsql volatile;
can't run the generated 'sql' in parallel. I think this is understandable, but
the documentation of this limitation in the sgml docs is thin. Perhaps
someone who understands this limitation better than I do can document it?
Changing myfunc to create a temporary table, to execute the sql to populate
that temporary table, and to then loop through the temporary table's rows
fixes the problem. For the real-world example where I hit this, that single
change decreases the runtime from 13.5 seconds to 2.5 seconds. This
makes sense to me, because doing it that way means pl_exec knows that
it won't be returning a portal for the executed sql, so the parallel plan is
still allowed.
Sorry to be a nag. I'm only trying to help the next person who might
otherwise trip over this limitation. Perhaps this belongs in section 15.3.4
or 42.5.4.
mark
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
