updates way slower than selects?

From: Marek Pętlicki <marpet(at)linuxpl(dot)org>
To: PostgreSQL-general <pgsql-general(at)postgreSQL(dot)org>
Subject: updates way slower than selects?
Date: 2001-04-03 14:12:33
Message-ID: 20010403161232.A5976@marek.almaran.home
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)linuxpl(dot)org

Responses

Browse pgsql-general by date

  From Date Subject
Next Message ADBAAMD 2001-04-03 14:26:07 Re: Postgres startup
Previous Message Kris Van Hulle 2001-04-03 13:55:52 Virtual domains ?