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 09:54:02 -0400
Message-ID: <25200.1176213242@sss.pgh.pa.us> (raw)
In-Reply-To: <D14BB823-DA04-453C-AD70-248B34F1307B@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>
Drew Wilson <drewmwilson@gmail.com> writes:
> The SELECT is not slow, so its a side effect of the update... Looking
> at the table definition, there is a "BEFORE ON DELETE" trigger
> defined, two CHECK constraints for this table, and three foreign
> keys. Nothing looks suspicious to me.
Since this is an update we can ignore the before-delete trigger, and
the check constraints don't look expensive to test. Outgoing foreign
key references are normally not a problem either, since there must
be an index on the other end. But *incoming* foreign key references
might be an issue --- are there any linking to this table?
Also, the seven indexes seem a bit excessive. I'm not sure if that's
where the update time is going, but they sure aren't helping, and
some of them seem redundant anyway. In particular I think that the
partial index WHERE obsolete IS NOT TRUE is probably a waste (do you
have any queries you know use it? what do they look like?) and you
probably don't need all three combinations of source_id and
translation_id --- see discussion here:
http://www.postgresql.org/docs/8.2/static/indexes-bitmap-scans.html
BTW, I don't think you ever mentioned what PG version this is exactly?
If it's 8.1 or later it would be worth slogging through EXPLAIN ANALYZE
on the update, or maybe an update of 10% or so of the rows if you're
impatient. That would break out the time spent in the triggers, which
would let us eliminate them (or not) as the cause of the problem.
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: <25200.1176213242@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