Re: Performance of update

On Mar 27, 8:28 am, laurenz(dot)a(dot)(dot)(dot)(at)wien(dot)gv(dot)at ("Albe Laurenz") wrote:
> sam wrote:
> > Iam trying to update a database table with approx 45000 rows. Iam not
> > updating all rows at a time. Iam updating 60 rows at a given time for
> > example. and this is happening in a FOR LOOP. A function that has the
> > update statements is called within the loop.
>
> > The updates take too long.....is postgres slow in doing updates on
> > large tables or is it because of the function call within the loop???
>
> Could you post the functions and the EXPLAIN output
> for the SQL statements in the functions?
>
> Yours,
> Laurenz Albe
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Ok....
The table is a partition of a main table.
Its schema is pretty straight forward with 10 columns.
There is no Fk consistency or UPDATE CASCADE involved not atleast on
the test table that iam using.
I created indexes for colums used in the WHERE clause. It made the
updating much faster.
The function goes something like this:

function getandsetimputedata()
BEGIN
LOOP
for every row in the cursor
//do some operations
PERFORM update_data()
end for
END LOOP
END

function update_data()
BEGIN
EXECUTE the update statement

EXCEPTION block
END

Any other suggestions how i can make this work faster.

Sam