pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedJoining tables by UUID field - very slow
3+ messages / 3 participants
[nested] [flat]
* Joining tables by UUID field - very slow
@ 2012-02-24 22:46 Cherio <cherio@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Cherio @ 2012-02-24 22:46 UTC (permalink / raw)
To: pgsql-performance
We are experiencing an unusual slowdown when using UUID field in JOIN when
updating a table. SQL looks like this:
UPDATE dst
SET data_field = src.data_field
FROM src
WHERE dst.uuid_field = src.uuid_field;
This statement takes over 6 times longer than a similar statement against
the same table except the join is done by a integer field, e.g.
UPDATE dst
SET data_field = src.data_field
FROM src
WHERE dst.integer_field = src.integer_field;
I can't get rid of UUID in the "src" table since it comes from another
database that we can't change. The table has around 1 mil rows. I tried
vacuuming it. Tried creating indexes on src table (it ignores them and
builds hash join anyway). It takes faster to rebuild the whole table than
to update it while joining by UUID. Has anyone experienced this before and
what was the solution for you?
Help is greatly appreciated.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Joining tables by UUID field - very slow
@ 2012-02-27 14:53 Merlin Moncure <mmoncure@gmail.com>
parent: Cherio <cherio@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Merlin Moncure @ 2012-02-27 14:53 UTC (permalink / raw)
To: Cherio <cherio@gmail.com>; +Cc: pgsql-performance
On Fri, Feb 24, 2012 at 4:46 PM, Cherio <cherio@gmail.com> wrote:
> We are experiencing an unusual slowdown when using UUID field in JOIN when
> updating a table. SQL looks like this:
>
> UPDATE dst
> SET data_field = src.data_field
> FROM src
> WHERE dst.uuid_field = src.uuid_field;
>
> This statement takes over 6 times longer than a similar statement against
> the same table except the join is done by a integer field, e.g.
>
> UPDATE dst
> SET data_field = src.data_field
> FROM src
> WHERE dst.integer_field = src.integer_field;
>
> I can't get rid of UUID in the "src" table since it comes from another
> database that we can't change. The table has around 1 mil rows. I tried
> vacuuming it. Tried creating indexes on src table (it ignores them and
> builds hash join anyway). It takes faster to rebuild the whole table than to
> update it while joining by UUID. Has anyone experienced this before and what
> was the solution for you?
If you're updating every field in the table, you're basically
rebuilding the whole table anyways. Also, both the heap and the
indexes have to track both row versions. HOT helps for non indexed
field updates, but the HOT optimization tends to only really shine
when the updates are small and frequent. In postgres it's good to try
and avoid large updates when reasonable to do so.
The UUID is slower because it adds lots of bytes to both the heap and
the index although 6 times slower does seem like a lot. Can you
simulate a similar update with a text column to see if the performance
differences is related to row/key size?
merlin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Joining tables by UUID field - very slow
@ 2012-02-27 15:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Cherio <cherio@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2012-02-27 15:25 UTC (permalink / raw)
To: Cherio <cherio@gmail.com>; +Cc: pgsql-performance
Cherio <cherio@gmail.com> writes:
> This statement takes over 6 times longer than a similar statement against
> the same table except the join is done by a integer field, e.g.
Could we see EXPLAIN ANALYZE data for both cases?
How are you representing the UUIDs, exactly (ie what's the column data
type)?
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-02-27 15:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-24 22:46 Joining tables by UUID field - very slow Cherio <cherio@gmail.com>
2012-02-27 14:53 ` Merlin Moncure <mmoncure@gmail.com>
2012-02-27 15:25 ` Tom Lane <tgl@sss.pgh.pa.us>
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