"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