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