Re: Performance of update

On Mar 27, 8:28 am, laurenz.a...@wien.gv.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...@postgresql.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