Re: Update join performance issues

On Tue, Apr 3, 2012 at 12:29 PM, Kevin Kempter
<cs_dba(at)consistentstate(dot)com> 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