On Tue, Apr 3, 2012 at 12:29 PM, Kevin Kempter <cs_dba@xxxxxxxxxxxxxxxxxxx> wrote: > Hi All; > > I have a query that wants to update a table based on a join like this: > > update test_one > set f_key = t.f_key > from > upd_temp1 t, > test_one t2 > where > t.id_number = t2.id_number > > upd_temp1 has 248,762 rows > test_one has 248,762 rows > > test_one has an index on f_key and an index on id_number > upd_temp1 has an index on id_number > > > The explain plan looks like this: > Update (cost=0.00..3212284472.90 rows=256978208226 width=121) > -> Nested Loop (cost=0.00..3212284472.90 rows=256978208226 width=121) > -> Merge Join (cost=0.00..51952.68 rows=1033028 width=20) > Merge Cond: ((t.id_number)::text = (t2.id_number)::text) > -> Index Scan using idx_tmp_001a on upd_temp1 t > (cost=0.00..12642.71 rows=248762 width= > 52) > -> Materialize (cost=0.00..23814.54 rows=248762 width=17) > -> Index Scan using index_idx1 on test_one t2 > (cost=0.00..23192.64 rows > =248762 width=17) > -> Materialize (cost=0.00..6750.43 rows=248762 width=101) > -> Seq Scan on test_one (cost=0.00..5506.62 rows=248762 > width=101) > (9 rows) > > > The update never finishes, we always stop it after about 30min to an hour. > > Anyone have any thoughts per boosting performance? to add: reading explain output is an art form all onto itself but the following is a giant screaming red flag: rows=256978208226 unless of course you're trying to update that many rows, this is telling you that there is an unconstrained join in there somewhere as others have noted. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
