Re: Update command too slow - Mailing list pgsql-general

From Venkatesh Babu
Subject Re: Update command too slow
Date
Msg-id 20050208130902.27908.qmail@web50407.mail.yahoo.com
Whole thread Raw
In response to Re: Update command too slow  (guegue <guegue@gmail.com>)
List pgsql-general
Hi,

The where clause is used in update statements and the
column present in the where clause is indexed...

but still updates are slow.

Thanks,
Venkatesh

--- guegue <guegue@gmail.com> wrote:

> you mention you use one update statement by record,
> this may be to
> basic but anyway, it just happened to me...
>
> do you use the WHERE clause in your UPDATE
> statement, and if so is the
> column you use to filter indexed?
>
> javier
>
>
> On Sat, 5 Feb 2005 03:14:52 -0800 (PST), Venkatesh
> Babu
> <venkatbabukr@yahoo.com> wrote:
> > Hi,
> >
> > There aren't any foreign keys and we are currently
> > using Postgres version 7.4...
> >
> > --- Venkatesh Babu <venkatbabukr@yahoo.com> wrote:
> >
> > > Hi,
> > >
> > > There aren't any triggers but there are 75262
> update
> > > statements. The problem is that we have a
> datatype
> > > called as "Collection" and we are fetching the
> data
> > > rows into it, modifying the data and call
> > > Collection.save(). This save method generates
> one
> > > update satement per record present in it.
> > >
> > > Thanks,
> > > Venkatesh
> > >
> > > --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > >
> > > > Venkatesh Babu <venkatbabukr@yahoo.com>
> writes:
> > > > > We have a table cm_quotastates which has
> exactly
> > > > > 4624564 rows and 25 columns and 9 indexes...
> Out
> > > > of
> > > > > these, our code retrieves 75262 rows and
> > > modifies
> > > > just
> > > > > one column in each row... but updating these
> to
> > > > > database is taking some significant time
> (around
> > > > 20
> > > > > minutes)... Tried the following with the
> update
> > > >
> > > > Any triggers or foreign keys on that table?
> Also,
> > > > what PG version is
> > > > this?  Are you doing this in a single UPDATE
> > > > command, or 75262 separate
> > > > commands?
> > > >
> > > >                     regards, tom lane
> > > >
> > >
> > >
> > >
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > Yahoo! Mail - Find what you need with new
> enhanced
> > > search.
> > > http://info.mail.yahoo.com/mail_250
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > >                http://archives.postgresql.org
> > >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Mail - You care about security. So do we.
> > http://promotions.yahoo.com/new_mail
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
>       subscribe-nomail command to
> majordomo@postgresql.org so that your
>       message can get through to the mailing list
> cleanly
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail

pgsql-general by date:

Previous
From: Chris Green
Date:
Subject: Re: Confused by to_char
Next
From: sid tow
Date:
Subject: Performance tuning using copy