Re: Update command too slow

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(at)gmail(dot)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(at)yahoo(dot)com> wrote:
> > Hi,
> >
> > There aren't any foreign keys and we are currently
> > using Postgres version 7.4...
> >
> > --- Venkatesh Babu <venkatbabukr(at)yahoo(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > >
> > > > Venkatesh Babu <venkatbabukr(at)yahoo(dot)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(at)postgresql(dot)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