PostgreSQL UUID类型的性能 [英] PostgreSQL UUID type performance

问题描述

我不想重启UUID与串行整数密钥辩论。我知道任何一方都有有效的观点。我在几个表中使用UUID作为主键。

• 列类型：uuidKey文本NOT NULL


• 索引： CREATE UNIQUE INDEX grand_pkey ON grand USING btree（uuidKey）


• 主键约束： ADD CONSTRAINT grand_pkey PRIMARY KEY（uuidKey）;

这是我的第一个问题;与PostgreSQL 9.4是否有任何性能优势设置列类型UUID？

文档 http://www.postgresql.org/docs/9.4/static/datatype-uuid.html 描述了UUID，但除此之外还有什么好处从使用这种类型的类型安全而不是 text type？在字符类型文档中，它指出在PostgreSQL中 char（n）在 text 中没有任何优势。

提示：这三种类型之间没有性能差异，除了使用空白填充类型时增加存储空间的
之外，以及
几个额外的CPU周期来检查存储到
长度受限列中的长度。虽然character（n）在某些其他数据库系统中的性能
有优势，但PostgreSQL中没有这样的优势
;实际上字符（n）通常是
三种中最慢的，因为它有额外的存储成本。在大多数情况下，应该使用文本
或character varying。

我不担心磁盘空间， m只是想知道是否值得我的时间基准UUID vs文本列的类型？

第二个问题，散列与b-tree索引。没有意义排序UUID键，那么b-tree会比hash索引有什么其他的优势吗？

解决方案 UUID 是一个16字节的值。与 text 相同的是一个32字节的值。存储大小为：

  select 
 pg_column_size（'a0eebc999c0b4ef8bb6d6bb9bd380a11':: text）as text_size，
 pg_column_size（'a0eebc999c0b4ef8bb6d6bb9bd380a11':: uuid）as uuid_size; 
 text_size | uuid_size 
 ----------- + ----------- 
 36 | 16 
  

更小的表格可带来更快的操作。

I'm not trying to restart the UUID vs serial integer key debate. I know there are valid points to either side. I'm using UUID's as the primary key in several of my tables.

• Column type: "uuidKey" text NOT NULL
• Index: CREATE UNIQUE INDEX grand_pkey ON grand USING btree ("uuidKey")
• Primary Key Constraint: ADD CONSTRAINT grand_pkey PRIMARY KEY ("uuidKey");

Here is my first question; with PostgreSQL 9.4 is there any performance benefit to setting the column type to UUID?

The documentation http://www.postgresql.org/docs/9.4/static/datatype-uuid.html describes UUID's, but is there any benefit aside from type safety for using this type instead of text type? In the character types documentation it indicates that char(n) would not have any advantage over text in PostgreSQL.

Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

I'm not worried about disk space, I'm just wondering if it's worth my time benchmarking UUID vs text column types?

Second question, hash vs b-tree indexes. No sense in sorting UUID keys so would b-tree have any other advantages over hash index?

解决方案

A UUID is a 16 bytes value. The same as text is a 32 bytes value. The storage sizes are:

select
    pg_column_size('a0eebc999c0b4ef8bb6d6bb9bd380a11'::text) as text_size,
    pg_column_size('a0eebc999c0b4ef8bb6d6bb9bd380a11'::uuid) as uuid_size;
 text_size | uuid_size 
-----------+-----------
        36 |        16

Smaller tables lead to faster operations.

这篇关于PostgreSQL UUID类型的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！