Обсуждение: 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>