Re: atrocious update performance

while you weren't looking, Tom Lane wrote:

> I hate to break it to you, but that most definitely means you are
> running with BLCKSZ = 32K. Whatever you thought you were rebuilding
> didn't take effect.

I saw that and thought so. The other day, I was rooting around in
$PGDATA, and saw a lot of 32K files and wondered for a moment, too.
If that's the case, though, that's ... weird.

> I agree that the larger blocksize is of dubious value. People used to
> do that back when the blocksize limited your row width, but these days
> I think you're probably best off with the standard 8K.

I'd been experimenting with larger blocksizes after we started seeing
a lot of seqscans in query plans. 32K proved quickly that it hurts
index scan performance, so I was--I thought--trying 16.

> If the big EXPLAIN ANALYZE is still running, would you get a dump of its
> open files (see "lsof -p") and correlate those with the tables being
> used in the query? I'm trying to figure out what the different writes
> and reads represent.

It looks rather like it's hitting the foreign keys; one of the files
that shows is the account.note table, which has an fk to the pk of the
table being updated. The file's zero size, but it's open. The only
reason it should be open is if foreign keys are being checked, yes?

You'd said that the foreign keys were only checked if last-change is
after current-query, as of 7.3.4, yes? `rpm -qa postgresql` comes up
with 7.3.2-3, which makes no sense, 'cos I know I removed it before
installing current; I remember making sure no-one was using pg on this
machine, and remember saying rpm -e.

Regardless, something thinks it's still there. Is there any way that
it is, and that I've somehow been running 7.3.2 all along? `which
psql`, &c show the bindir from my configure, but I'm not sure that's
sufficient.

How would I tell? I don't remember any of the binaries having a
--version argument.

/rls

--
Rosser Schwarz
Total Card, Inc.