Re: atrocious update performance

"Rosser Schwarz" <rschwarz(at)totalcardinc(dot)com> writes:
> But if I'm not touching the column referenced from account.acct, why
> would it be looking there at all? I've got an explain analyze of the
> update running now, but until it finishes, I can't say for certain
> what it's doing. explain, alone, says:

EXPLAIN won't tell you anything about triggers that might get fired
during the UPDATE, so it's not much help for investigating possible
FK performance problems. EXPLAIN ANALYZE will give you some indirect
evidence: the difference between the total query time and the total time
reported for the topmost plan node represents the time spent running
triggers and physically updating the tuples. I suspect we are going
to see a big difference.

> which shows it not hitting account.acct at all. (And why did it take
> the planner 20-some seconds to come up with that query plan?)

It took 20 seconds to EXPLAIN? That's pretty darn odd in itself. I'm
starting to think there must be something quite whacked-out about your
installation, but I haven't got any real good ideas about what.

(I'm assuming of course that there weren't a ton of other jobs eating
CPU while you tried to do the EXPLAIN.)

[ thinks for awhile... ] The only theory that comes to mind for making
the planner so slow is oodles of dead tuples in pg_statistic. Could I
trouble you to run
vacuum full verbose pg_statistic;
and send along the output?

regards, tom lane