Re: in Pl/PgSQL, do commit every 5000 records

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