Re: Updates are slow..

"Tom Burke" <lists@spamex.com> writes:
> Fran Fabrizio had an extremely helpful suggestion:
>> No idea if this would help but try REINDEX TABLE EMP; beforehand too.

> After trying this, the time was cut from 40 minutes to 20 minutes,
> which is a huge improvement but still quite slow.

> However, the real kicker is that I looked up what the emp_ix02 index
> was and it was (email, fk_dept_id). Therefore, there were actually
> two indexes on this column that had to be updated, and this index
> had a lot more pages. When I dropped this index and re-ran the updated
> - it took only 2 minutes!

Very interesting.  The emp_ix02 index was not all that much bigger than
the others, according to vacuum's numbers, so I don't see why it should
take so long to update.  Maybe we've got some kind of performance
problem with multicolumn indexes?

I'm wondering if the initial 20-minute savings from reindex was mostly
attributable to emp_ix02 as well.  Too bad we don't know how large the
indexes were before reindex.  Do you have enough of a log of your tests
so far to be able to recreate the pre-reindex situation?

It would be interesting to try making a third index on just email and
see where the update time goes to.  Also, could I trouble you for some
stats on the email column?  Min, max, and average length of the entries
would be useful to know.  And I assume it's unique, or nearly so?

            regards, tom lane