Skip site navigation (1) Skip section navigation (2)

Re: Performance of update

From: sam <sam(dot)mahindrakar(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Performance of update
Date: 2008-03-28 20:31:58
Message-ID: ccbb8991-c56f-45b9-89f7-61d52f77bd7f@8g2000hsu.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Terry Lee TuckerDate: 2008-03-28 21:06:10
Subject: Escaping \n
Previous:From: Scott MarloweDate: 2008-03-28 20:31:01
Subject: Re: Out of memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group