From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rosser Schwarz <rschwarz@totalcardinc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: atrocious update performance
Date: Tue, 16 Mar 2004 15:14:46 -0500
Message-ID: <27129.1079468086@sss.pgh.pa.us> (raw)
In-Reply-To: <001e01c40b91$181d66c0$2500fa0a@CardServices.TCI.com>
References: <001e01c40b91$181d66c0$2500fa0a@CardServices.TCI.com>
"Rosser Schwarz" <rschwarz@totalcardinc.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, rschwarz@totalcardinc.com
Subject: Re: atrocious update performance
In-Reply-To: <27129.1079468086@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox