From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Tom Burke" <lists(at)spamex(dot)com> |
Cc: | ffabrizio(at)mmrd(dot)com, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Updates are slow.. |
Date: | 2002-06-11 21:37:56 |
Message-ID: | 4480.1023831476@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Tom Burke" <lists(at)spamex(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ray Hunter | 2002-06-11 22:17:12 | PostGIS |
Previous Message | Chris Gamache | 2002-06-11 21:27:52 | Re: "ERROR:" Messages |