Re: Updates are slow..

it might help to see the explain output of the update query, with and without
the indexes in question...




"Tom Burke" <lists%spamex.com@interlock.lexmark.com> on 06/13/2002 11:47:12 AM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  Re: [GENERAL] Updates are slow..


I didn't receive any response to my last post, but I've got a little
more info and have 2 questions.

I dropped the index on email.
I then populated a smallint field with random value from 0 - 2000
and created an in index on that field.

Now an update on fk_dept_id takes 7 minutes, as opposed to 2 minutes
without any additional indexes and 25 minutes with an index on email.

The index on the smallint has these stats:
NOTICE:  Index emp_ix04: Pages 4959; Tuples 1230703: Deleted 597153.
     CPU 1.05s/9.84u sec elapsed 29.49 sec.

Which is 1/3 of the pages that the index on email had.

My two questions:
1) Does the 25 minute update of 600K rows with an index on the
   email column still seem out of line or is there not much we can do?
2) Do people think it's a good idea to get a PostgreSQL performance
   tuning consultant to review our setup? If so, any recommendations
   for who could help?

Many thanks in advance!

Tom Burke

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster