Performance impact UUID vs VARCHAR(36)

DerekW

Hi,

I'm determining if it is a good idea to migrate my UUID columns, which are currently stored as varchar(36) to the new UUID data type.

I did a few tests, and what I see is that the new UUID data type indeed takes less storage. What I also see is that the cost of queries increases when the UUID column is used in a join. So apparently it is more expensive to join on a UUID data type than on a varchar(36), which really surprised me. Saving storage is important, but it should not result in query performance loss. Does anyone have experiences with this or know why this is the case?

Best,
Derek

Jim_Knicely

Hi,

I'm not seeing that in my super simple test.

dbadmin=> \dt uuid*
                        List of tables
 Schema |          Name           | Kind  |  Owner  | Comment
--------+-------------------------+-------+---------+---------
 public | uuid_as_uuid            | table | dbadmin |
 public | uuid_as_uuid_sub_set    | table | dbadmin |
 public | uuid_as_varchar         | table | dbadmin |
 public | uuid_as_varchar_sub_set | table | dbadmin |
(4 rows)

dbadmin=> select count(*) from uuid_as_uuid;
   count
-----------
 128000000
(1 row)

dbadmin=> select count(*) from uuid_as_uuid_sub_set;
  count
---------
 1000000
(1 row)

dbadmin=> select count(*) from uuid_as_varchar;
   count
-----------
 128000000
(1 row)

dbadmin=> select count(*) from uuid_as_varchar_sub_set;
  count
---------
 1000000
(1 row)

Here is the result of joining VARCHAR type PRIOR to running database designer:

dbadmin=> select count(*) from uuid_as_varchar u1 join uuid_as_varchar_sub_set u2 on u2.uuid = u1.uuid;
   count
-----------
 128000000
(1 row)

Time: First fetch (1 row): 12831.350 ms. All rows formatted: 12831.412

Here is the result of joining UUID type PRIOR to running database designer:

dbadmin=> select count(*) from uuid_as_uuid u1 join uuid_as_uuid_sub_set u2 on u2.uuid = u1.uuid;
   count
-----------
 128000000
(1 row)

Time: First fetch (1 row): 7397.609 ms. All rows formatted: 7397.644 ms

Here is the result of joining VARCHAR type AFTER running database designer:

dbadmin=> select count(*) from uuid_as_varchar u1 join uuid_as_varchar_sub_set u2 on u2.uuid = u1.uuid;
   count
-----------
 128000000
(1 row)

Time: First fetch (1 row): 2367.217 ms. All rows formatted: 2367.272 ms

Here is the result of joining UUID type AFTER running database designer:

dbadmin=> select count(*) from uuid_as_uuid u1 join uuid_as_uuid_sub_set u2 on u2.uuid = u1.uuid;
   count
-----------
 128000000
(1 row)

Time: First fetch (1 row): 1399.166 ms. All rows formatted: 1399.224 ms

The UUID join is faster in all cases.