From: Kevin Kempter <cs_dba@consistentstate.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Update join performance issues
Date: Tue, 03 Apr 2012 11:29:56 -0600
Message-ID: <4F7B3394.3030703@consistentstate.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cs_dba@consistentstate.com
Subject: Re: Update join performance issues
In-Reply-To: <4F7B3394.3030703@consistentstate.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox