Performance impact UUID vs VARCHAR(36)

DerekWDerekW Registered User

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

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file