Re: slow update but have an index - Mailing list pgsql-general

My experience with bulk inserts/updates is that indexes cause a major
performance hit, because they have to be rebuilt after every insert/update.
Also, each insert/update is wrapped in a transaction by default, so there is
transaction overhead for each one too.
Wrapping all of the statements in a BEGIN; .... COMMIT; would probably solve
both of these problems.  Dropping and recreating the index might help too.
Results will vary according to the situation.

----- Original Message -----
From: "Feite Brekeveld" <feite.brekeveld@osiris-it.nl>
To: "Martijn van Oosterhout" <kleptog@svana.org>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, August 17, 2001 7:18 AM
Subject: Re: [GENERAL] slow update but have an index


> Martijn van Oosterhout wrote:
>
> > On Fri, Aug 17, 2001 at 01:40:32PM +0200, Feite Brekeveld wrote:
> > > > Well, an index speeds it up, but that times 80,000 will still take a
while.
> > > > Is there any trickery or will this work?
> > > >
> > > > update accounting set status = 'C';
> > > >
> > > > If so, that will be much faster.
> > >
> > > No that will not work, because they other 6000 need not to be changed.
Of
> > > course I could update the this way and change the other 6000 back to
their
> > > original status, but the query I issued is so slow that I think
something is
> > > wrong.
> >
> > Well, there's a bit of an issue here. Each time you do an insert, the
table
> > gets larger, the index gets larger, etc. Disk accesses everywhere. If
you
> > can do it one query then the sequential is much friendlier to disk
caches
> > and the performance will be much more consistant.
> >
> > Can you codify in an SQL query how you decide which records to change.
I've
> > found the best way to improve performance is to minimise the number of
> > queries, letting the database do the maximum optimisation possible.
> >
>
> hacked it with perl into several
>
> update ... where seqno between x and y statements.
>
> That went smoothly.
>
>
>
> >
> > --
> > Martijn van Oosterhout <kleptog@svana.org>
> > http://svana.org/kleptog/
> > > It would be nice if someone came up with a certification system that
> > > actually separated those who can barely regurgitate what they crammed
over
> > > the last few weeks from those who command secret ninja networking
powers.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> --
> Feite Brekeveld
> feite.brekeveld@osiris-it.nl
> http://www.osiris-it.nl
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>