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

From: Tom Lane
Subject: Re: how to efficiently update tuple in many-to-many relationship?
Date: ,
Msg-id: 25200.1176213242@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson)
Responses: Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson)
List: pgsql-performance

Tree view

how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
 Re: how to efficiently update tuple in many-to-many relationship?  ("Merlin Moncure", )
 Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
  Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
   Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
    Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
     Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
      Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
       Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
        Question about memory allocations  (Steve, )
         Re: Question about memory allocations  (Tom Lane, )
          Re: Question about memory allocations  (Steve, )
         Re: Question about memory allocations  (Greg Smith, )
          Re: Question about memory allocations  (Steve, )
         Re: Question about memory allocations  (Andrew McMillan, )
          Re: Question about memory allocations  (Steve, )
           Re: Question about memory allocations  (Ron, )
            Re: Question about memory allocations  (Tom Lane, )
           Re: Question about memory allocations  (Carlos Moreno, )
            Re: Question about memory allocations  ("Jan de Visser", )

Drew Wilson <> 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


pgsql-performance by date:

From: "Michael Dengler"
Date:
Subject: Do I need to rebuild php-pgsql for 8.2.3
From: "Guillaume Smet"
Date:
Subject: Re: Do I need to rebuild php-pgsql for 8.2.3