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: (view raw, whole thread or download thread mbox)
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 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:

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()
         for every row in the cursor
           //do some operations
            PERFORM update_data()
         end for
      END LOOP

function update_data()
    EXECUTE the update  statement

   EXCEPTION block

Any other suggestions how i can make this work faster.


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-2017 The PostgreSQL Global Development Group