Joining tables by UUID field - very slow

From: Cherio <cherio(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Joining tables by UUID field - very slow
Date: 2012-02-24 22:46:23
Message-ID: CAKHqFk+BOgDe=3B9P0mvtviTN+bcO1XvL0k50BVQqiVWRjF7eg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2012-02-24 23:54:00 Re: Re: [PERFORM] Disable-spinlocks while compiling postgres 9.1 for ARM Cortex A8
Previous Message Jayashankar K B 2012-02-24 22:42:29 Re: [PERFORM] Disable-spinlocks while compiling postgres 9.1 for ARM Cortex A8