Emi Lu wrote:
Florian G. Pflug wrote:
< snipped code of stored procedure >
Are you aware of the "insert into <table> (<field1>, ..., <fieldn>)
select <val1>, .., <valn> from ...."
command? It'd be much faster to use that it it's possible...
greetings, Florian Pflug
It did faster. Thank you Florian. Could you hint me why "insert into ..
select " is faster than a cursor transaction please?
Well, you're avoiding a lot of overhead. "insert into ... select from .."
is just one sql-statement. Of course, postgres internally does
something similar to your stored procedure, but it's all compiled
C code now (instead of interpreted plpgsql). Additionally, postgres
might be able to optimize this more than you could from plpgsql, because
you're restricted to the api that is exposed to plpgsql, while the backend-code
might be able to "pull a few more tricks".
In general, if you have the choice between looping over a large result
in a stored procedure (or, even worse, in a client app) and letting the
backend do the looping, then letting the backend handle it is nearly always
faster.
How about update?
Way1:
update tableA
set col1= X.col1, col2=X.col2, ... coln = X.coln
from table (select ... from ... where ..) AS X
where A.pk = X.pk ;
should be faster than
Way2:
open cursor:
fetch (select ... from ... where ... ) into xCol1, xCol2, ... xColn
update tableA
set col1 = xCol1, col2 =xCol2..., coln =xColn
where tableA.pkCols = xPkCols
right?
I'd say so, yes.
greetings, Florian Pflug
