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>