Re: how to efficiently update tuple in many-to-many relationship?

Drew Wilson <drewmwilson(at)gmail(dot)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