Re: UPDATE slow

On Wed, 5 Feb 2003, Nigel J. Andrews wrote:

>
> On Tue, 4 Feb 2003, Stephan Szabo wrote:
>
> >
> > On Wed, 5 Feb 2003, Nigel J. Andrews wrote:
> >
> > > On Tue, 4 Feb 2003, Stephan Szabo wrote:
> > >
> > > > On Tue, 4 Feb 2003, John Smith wrote:
> > > >
> > > > > That works - updates on foo take about 1.4 seconds. I dropped all the
> > > > > indexes and fk's on stats and updates there take about 2.8 seconds.
> > > > > These are on the cygwin machine.
> > > >
> > > > The 2.8 seconds is on stats after dropping the fks and indexes?  But
> > > > it didn't help on the linux box?
> >
> >...
> >
> > Yeah, but I thought he'd said that on the linux box, even after dropping
> > indexes and fks it was taking 5-10 seconds.
>
> You miss remembered :)

In any case that's 3x slower than my development box which is not terribly
powerful and not set up as a database server for real using pretty much
all default configuration settings with the database on the same partition
as / (/usr, etc...).

> >
> > I'm also a bit confused because I'm not sure he's getting 2.8 seconds to
> > update all the records or just a single record.
>
> I'm pretty sure the command originally quoted was an unconstrained update
> setting a constant value, i.e. all the rows. I had to change my test because
> I'd setup unique indexes so couldn't do the constant value bit.

That's what the first message was, but his first followup with an explain
analyze output used:

explain analyze update stats set clicks = 3344 where link_id=1;