Message-ID: <4F7B3394.3030703@consistentstate.com>
Date: Tue, 03 Apr 2012 11:29:56 -0600
From: Kevin Kempter <cs_dba@consistentstate.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:11.0) Gecko/20120316 Thunderbird/11.0
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Update join performance issues
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

Thanks in advance