pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate join performance issues
6+ messages / 5 participants
[nested] [flat]
* Update join performance issues
@ 2012-04-03 17:29 Kevin Kempter <cs_dba@consistentstate.com>
0 siblings, 4 replies; 6+ messages in thread
From: Kevin Kempter @ 2012-04-03 17:29 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Update join performance issues
@ 2012-04-03 17:37 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Kevin Kempter <cs_dba@consistentstate.com>
3 siblings, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2012-04-03 17:37 UTC (permalink / raw)
To: Kevin Kempter <cs_dba@consistentstate.com>; pgsql-performance
Kevin Kempter <cs_dba@consistentstate.com> wrote:
> update test_one
> set f_key = t.f_key
> from
> upd_temp1 t,
> test_one t2
> where
> t.id_number = t2.id_number
As written above, it is joining the two table references in the FROM
clause and updating every row in test_one with every row in the JOIN
-- which is probably not what you want. Having a FROM clause on an
UPDATE statement is not something which is covered by the standard,
and different products have implemented different semantics for
that. For example, under MS SQL Server, the first reference in the
FROM clause to the target of the UPDATE is considered to be the same
reference; so the above statement would be accepted, but do
something very different.
You probably want this:
update test_one t2
set f_key = t.f_key
from
upd_temp1 t
where
t.id_number = t2.id_number
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Update join performance issues
@ 2012-04-03 17:39 Andrew Dunstan <andrew@dunslane.net>
parent: Kevin Kempter <cs_dba@consistentstate.com>
3 siblings, 1 reply; 6+ messages in thread
From: Andrew Dunstan @ 2012-04-03 17:39 UTC (permalink / raw)
To: Kevin Kempter <cs_dba@consistentstate.com>; +Cc: pgsql-performance
On 04/03/2012 01:29 PM, Kevin Kempter 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
Why is test_one in the from clause? update joins whatever is in the from
clause to the table being updated. You almost never need it repeated in
the from clause.
cheers
andrew
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Update join performance issues
@ 2012-04-03 17:49 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2012-04-03 17:49 UTC (permalink / raw)
To: Kevin Kempter <cs_dba@consistentstate.com>; Andrew Dunstan <andrew@dunslane.net>; +Cc: pgsql-performance
Andrew Dunstan <andrew@dunslane.net> wrote:
> Why is test_one in the from clause? update joins whatever is in
> the from clause to the table being updated. You almost never need
> it repeated in the from clause.
This is actually one of the nastier "gotchas" in converting from
Sybase ASE or MS SQL Server to PostgreSQL -- there are syntactically
identical UPDATE statements with very different semantics when a
FROM clause is used in an UPDATE statement. You need to do what the
OP was showing to use an alias with the target table under those
other products.
I suppose it might be possible to generate a warning when it appears
that someone is making this mistake, but it wouldn't be easy and
would probably not be worth the carrying cost. The test would need
to be something like:
(1) The relation which is the target of the UPDATE has no alias.
(2) There is a FROM clause which included the target relation (with
an alias).
(3) There aren't any joining references between the UPDATE target
and the relation(s) in the FROM clause.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Update join performance issues
@ 2012-04-03 17:51 Thomas Kellerer <spam_eater@gmx.net>
parent: Kevin Kempter <cs_dba@consistentstate.com>
3 siblings, 0 replies; 6+ messages in thread
From: Thomas Kellerer @ 2012-04-03 17:51 UTC (permalink / raw)
To: pgsql-performance
Kevin Kempter wrote on 03.04.2012 19:29:
> 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
>
To extend on what Kevin has already answere:
Quote from the manual:
"Note that the target table must not appear in the from_list, unless you intend a self-join"
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Update join performance issues
@ 2012-04-03 20:43 Merlin Moncure <mmoncure@gmail.com>
parent: Kevin Kempter <cs_dba@consistentstate.com>
3 siblings, 0 replies; 6+ messages in thread
From: Merlin Moncure @ 2012-04-03 20:43 UTC (permalink / raw)
To: Kevin Kempter <cs_dba@consistentstate.com>; +Cc: pgsql-performance
On Tue, Apr 3, 2012 at 12:29 PM, Kevin Kempter
<cs_dba@consistentstate.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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-04-03 20:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-04-03 17:29 Update join performance issues Kevin Kempter <cs_dba@consistentstate.com>
2012-04-03 17:37 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-04-03 17:39 ` Andrew Dunstan <andrew@dunslane.net>
2012-04-03 17:49 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-04-03 17:51 ` Thomas Kellerer <spam_eater@gmx.net>
2012-04-03 20:43 ` Merlin Moncure <mmoncure@gmail.com>
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