My apologies. That function call was some test code to verify that my
subselect was only being called once.
Let me try again, please.
Here's the query plan for a SELECT statement that returns 1,207,161
rows in 6 seconds.
MatchBox=# explain select count(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
------------------------------------------------------------------------
-----------------------------------
Aggregate (cost=299276.72..299276.73 rows=1 width=4)
-> Hash Join (cost=59962.72..294036.83 rows=2095954 width=4)
Hash Cond: (i.translation_pair_id =
translation_pair_data.translation_pair_id)
-> Hash Join (cost=369.15..177405.01 rows=2095954 width=4)
Hash Cond: (i.loc_submission_id = ls.loc_submission_id)
-> Seq Scan on instance i (cost=0.00..99016.16
rows=5706016 width=8)
-> Hash (cost=296.92..296.92 rows=5778 width=4)
-> Index Scan using loc_submission_is_public
on loc_submission ls (cost=0.00..296.92 rows=5778 width=4)
Index Cond: (is_public = true)
Filter: is_public
-> Hash (cost=31861.92..31861.92 rows=1690292 width=4)
-> Seq Scan on translation_pair_data
(cost=0.00..31861.92 rows=1690292 width=4)
And here's the query plan for the UPDATE query that seems to never
complete. (Execution time > 30 minutes.)
MatchBox=# explain 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=328000.49..453415.65 rows=1690282 width=90)
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..31861.82
rows=1690282 width=90)
-> Hash (cost=293067.74..293067.74 rows=2067660 width=8)
-> Hash Join (cost=59958.35..293067.74 rows=2067660 width=8)
Hash Cond: (i.translation_pair_id =
public.translation_pair_data.translation_pair_id)
-> Hash Join (cost=365.00..177117.92 rows=2067660
width=4)
Hash Cond: (i.loc_submission_id =
ls.loc_submission_id)
-> Seq Scan on instance i
(cost=0.00..99016.16 rows=5706016 width=8)
-> Hash (cost=293.75..293.75 rows=5700 width=4)
-> Index Scan using
loc_submission_is_public on loc_submission ls (cost=0.00..293.75
rows=5700 width=4)
Index Cond: (is_public = true)
Filter: is_public
-> Hash (cost=31861.82..31861.82 rows=1690282 width=4)
-> Seq Scan on translation_pair_data
(cost=0.00..31861.82 rows=1690282 width=4)
I figure I must be doing something wrong here. Thanks for the help,
Drew
On Apr 9, 2007, at 2:43 PM, Tom Lane wrote:
Drew Wilson <drewmwilson@xxxxxxxxx> writes:
I have 2 tables (A,B) joined in a many-to-many relationship via a
join table ("membership"), where updating table A based on table B
takes a very long time.
...
-> Function Scan on a (cost=0.00..12.50 rows=1000 width=4)
I think you've left out some relevant details ... there's nothing
in what you said about a set-returning function ...
regards, tom lane
