From: Tom Lane <tgl@sss.pgh.pa.us>
To: Drew Wilson <drewmwilson@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: how to efficiently update tuple in many-to-many relationship?
Date: Tue, 10 Apr 2007 12:08:14 -0400
Message-ID: <26925.1176221294@sss.pgh.pa.us> (raw)
In-Reply-To: <FCEF7DE2-913F-4C36-B117-8363EDE39BF3@gmail.com>
References: <233A9987-ABA5-4491-A304-FDA1F8FCD962@gmail.com>
<14019.1176155022@sss.pgh.pa.us>
<22512E5C-A60F-43E7-A370-257F7AF956FD@gmail.com>
<16374.1176171185@sss.pgh.pa.us>
<D14BB823-DA04-453C-AD70-248B34F1307B@gmail.com>
<25200.1176213242@sss.pgh.pa.us>
<FCEF7DE2-913F-4C36-B117-8363EDE39BF3@gmail.com>
Drew Wilson <drewmwilson@gmail.com> writes:
> If I understand the EXPLAIN ANALYZE results below, it looks like the
> time spent applying the "set is_public = true" is much much more than
> the fetch. I don't see any triggers firing.
Nope, there aren't any. 8.2 is smart enough to bypass firing FK
triggers on UPDATE if the key columns didn't change. Of course that
check takes a certain amount of time, but I don't think it's a large
amount. So basically we're looking at index update and WAL logging
as the major costs here, I think.
[ thinks for a bit... ] Part of the problem might be that the working
set for updating all the indexes is too large. What do you have
shared_buffers set to, and can you increase it?
Oh, and checkpoint_segments at 8 is probably still not nearly enough;
if you have disk space to spare, try something like 30 (which'd eat
about 1Gb of disk space). It might be educational to set
checkpoint_warning to 300 or so and watch the log to see how often
checkpoints happen during the update --- you want them at least a couple
minutes apart even during max load.
Also, bumping up wal_buffers a little might help some, for large updates
like this. I've heard people claim that values as high as 64 are helpful.
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, drewmwilson@gmail.com
Subject: Re: how to efficiently update tuple in many-to-many relationship?
In-Reply-To: <26925.1176221294@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