Обсуждение: updates way slower than selects?

Hi!

I've got a question: has anybody noticed in your production
tables, that updates on existing rows take longer than inserts
into those same tables?

I work on an Internet auction system. We have 'items' put to
auction with 'autorestore' option. When such an auction is ended
without solution (I mean: nobody bought the item either because
of not achieving min. price or because of no offers at all)
it can be reentered automatically. It is done by a special
script running on the server.

I mark those auctions to be autorestored by a flag auto_restore int4
auto_restore = 1 means 'autorestore on' (option active)
auto_restore = -1 means 'autorestore pending'

when in autorestore loop I simply select all the auctions with
auto_restore = -1, reinsert those items into the database and update
the column to auto_restore = 1

The timings are very discouraging though - I have timings of insert
vs update like 1:5. It means I insert new auction in 1ms and the
update of the flag takes 5ms. So I have 500% waste of time because of
the method!

This is not a big issue, because I can use additional table
autorestore_pending (consisting of only the IDs of auctions to be
restored) and skip the 'insert / update' routine (changing it into
insert / delete one), but it is not very good news to me
(I have other procedures where I have to massively update the auctions
table and some of them are quite time-critical for the system)



Can someone give me some hint? Do you experience the same effect?
Is it 'normal'? If the answer is 'yes' I will have to look for
solutions avoiding updates on the database.


I use PostgreSQL 7.0.3 with Python 2.0 and PoPy combination
on RH 7.0 (Debian on production machine) on 2.4.x kernel
The mentioned table has a few triggers attached and 16 indices
(it is 30-column table of items in auction - to be sorted multiple
ways and selected on multiple conditions). 3 triggers attached to
'INSERT' and 'UPDATE' are used to provide statistical information
(for the application as well as for the users). But I think
that triggers are not the problem here, because the same ones
are used for 'insert' and for 'update'.


regards and best wishes

--
Marek Pętlicki
marpet@linuxpl.org

Marek P�tlicki <marpet@linuxpl.org> writes:

> Hi!
>
> I've got a question: has anybody noticed in your production
> tables, that updates on existing rows take longer than inserts
> into those same tables?

Standard first question: are you vacuuming?

-Doug

On Saturday, April, 2001-04-07 at 23:04:23, Doug McNaught wrote:
> Marek Pętlicki <marpet@linuxpl.org> writes:
>
> > Hi!
> >
> > I've got a question: has anybody noticed in your production
> > tables, that updates on existing rows take longer than inserts
> > into those same tables?
>
> Standard first question: are you vacuuming?

this is a productio system running over half a year - yes it is
vacuumed every night. Additional question: is update speed on single
int4 value really dependant on vacuuming or are you just asking a
'standard question'?

regards and thanks

--
Marek Pętlicki <marpet@buy.pl>

> I've got a question: has anybody noticed in your production
> tables, that updates on existing rows take longer than inserts
> into those same tables?

Updates naturally take longer than inserts.  Especially if you haven't
provided an index that allows the row(s) to be updated to be found
easily.  Have you checked the EXPLAIN results for your problem queries?

            regards, tom lane

On Friday, April, 2001-04-13 at 18:34:06, Tom Lane wrote:
> > I've got a question: has anybody noticed in your production
> > tables, that updates on existing rows take longer than inserts
> > into those same tables?
>
> Updates naturally take longer than inserts.  Especially if you haven't
> provided an index that allows the row(s) to be updated to be found
> easily.  Have you checked the EXPLAIN results for your problem queries?

OK, I take that for granted Tom, but this database has 16 indexes (most
of them on 2-3 columns) and the updated column is just an int4 with no
index defined. No constraints attached. The update is on a single row
selected by serial primary key field ('where field=value'). I change the
field from -1 into 1 to be exact (it is kinda flag field indicating
a state of the record which is being reverted on the update).
'Explain' off course shows index scan that is why I am amazed by the lack
of speed comparing to insert (which is faster, disregarding the need to
update 16 indexes and going through couple of triggers).

(one side-note: I don't argue that the table is well-designed - observe
the number of indexes - I am just puzzled by lack of consistency in the
experience I gained by this - and I would like to learn more about why
it happened or, more likely, to learn that I've messed up badly and
this in not a normal situation).

I have reorganized the app so it doesn't use the 'flag field' anymore
(instead it uses one column table of ints to store the 'marked'
records keys). The process of 'delete from tb1 where id=value' doesn't
compare to 'update tb2 set field1=1 where id=value' in measure of speed
(or slowness). The whole operation (a few inserts/deletes on a single
transaction) takes 20% of the previous time which is much more
satisfactory to me (and my employers ;-)

Any further ideas will be more than appreciated, for the sake of my future
attempts.

thanks and best regards.

--
Marek Pętlicki <marpet@buy.pl>