Skip site navigation (1) Skip section navigation (2)

Re: Update join performance issues

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Kevin Kempter <cs_dba(at)consistentstate(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Update join performance issues
Date: 2012-04-03 20:43:34
Message-ID: CAHyXU0wZcyAChqEBAbYe5+hwUhLHGZvoCU6LCqOX5OOZJLyz5g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: David KerrDate: 2012-04-04 01:36:33
Subject: pg_autovacuum in PG9.x
Previous:From: Merlin MoncureDate: 2012-04-03 18:44:51
Subject: Re: H800 + md1200 Performance problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group