On Apr 10, 2007, at 6:54 AM, Tom Lane wrote:
Drew Wilson <drewmwilson@xxxxxxxxx> 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?
There is only one incoming foreign key - the one coming in from the
many-to-many join table ('instance').
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
99% of our queries use obsolete IS NOT TRUE, so we have an index on
this.
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.
Sorry. I'm using 8.2.3 on Mac OS X 10.4.9, w/ 2.Ghz Intel Core Duo,
and 2G RAM.
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. Is there something else I
can look for in the logs?
Here is the explain analyze output:
MatchBox=# EXPLAIN ANALYZE UPDATE translation_pair_data SET is_public
= true
WHERE translation_pair_id IN
(SELECT translation_pair_id FROM translation_pair_data
JOIN instance i using(translation_pair_id)
JOIN loc_submission ls using(loc_submission_id)
WHERE ls.is_public = true);
QUERY PLAN
------------------------------------------------------------------------
---------------------------------------------
Hash IN Join (cost=324546.91..457218.64 rows=1698329 width=90)
(actual time=12891.309..33621.801 rows=637712 loops=1)
Hash Cond: (public.translation_pair_data.translation_pair_id =
public.translation_pair_data.translation_pair_id)
-> Seq Scan on translation_pair_data (cost=0.00..38494.29
rows=1698329 width=90) (actual time=0.045..19352.184 rows=1690272
loops=1)
-> Hash (cost=290643.93..290643.93 rows=2006718 width=8)
(actual time=10510.411..10510.411 rows=1207161 loops=1)
-> Hash Join (cost=66710.78..290643.93 rows=2006718
width=8) (actual time=1810.299..9821.862 rows=1207161 loops=1)
Hash Cond: (i.translation_pair_id =
public.translation_pair_data.translation_pair_id)
-> Hash Join (cost=352.38..169363.36 rows=2006718
width=4) (actual time=11.369..6273.439 rows=1207161 loops=1)
Hash Cond: (i.loc_submission_id =
ls.loc_submission_id)
-> Seq Scan on instance i
(cost=0.00..99016.16 rows=5706016 width=8) (actual
time=0.029..3774.705 rows=5705932 loops=1)
-> Hash (cost=283.23..283.23 rows=5532
width=4) (actual time=11.277..11.277 rows=5563 loops=1)
-> Index Scan using
loc_submission_is_public on loc_submission ls (cost=0.00..283.23
rows=5532 width=4) (actual time=0.110..7.717 rows=5563 loops=1)
Index Cond: (is_public = true)
Filter: is_public
-> Hash (cost=38494.29..38494.29 rows=1698329
width=4) (actual time=1796.574..1796.574 rows=1690272 loops=1)
-> Seq Scan on translation_pair_data
(cost=0.00..38494.29 rows=1698329 width=4) (actual
time=0.012..917.006 rows=1690272 loops=1)
Total runtime: 1008985.005 ms
Thanks for your help,
Drew
