PostgreSQL UUID 类型性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29880083/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL UUID type performance
提问by adamek
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.
我不是要重新开始 UUID 与串行整数键的辩论。我知道任何一方都有有效的观点。我在我的几个表中使用 UUID 作为主键。
- 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");
- 列类型：
"uuidKey" text NOT NULL - 指数：
CREATE UNIQUE INDEX grand_pkey ON grand USING btree ("uuidKey") - 主键约束：
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?
这是我的第一个问题；使用 PostgreSQL 9.4 将列类型设置为 UUID 是否有任何性能优势？
The documentation http://www.postgresql.org/docs/9.4/static/datatype-uuid.htmldescribes UUID's, but is there any benefit aside from type safety for using this type instead of texttype? In the character types documentation it indicates that char(n)would not have any advantage over textin PostgreSQL.
文档 http://www.postgresql.org/docs/9.4/static/datatype-uuid.html描述了 UUID，但是除了类型安全之外，使用这种类型而不是text类型还有什么好处吗？在字符类型文档中，它表明在 PostgreSQL 中char(n)没有任何优势text。
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.
提示：这三种类型之间没有性能差异，除了使用空白填充类型时增加了存储空间，以及在存储到长度受限列时需要额外的一些 CPU 周期来检查长度。虽然 character(n) 在其他一些数据库系统中具有性能优势，但在 PostgreSQL 中没有这样的优势；事实上 character(n) 通常是三个中最慢的，因为它有额外的存储成本。在大多数情况下，应改用文本或字符变化。
I'm not worried about disk space, I'm just wondering if it's worth my time benchmarking UUID vs text column types?
我不担心磁盘空间，我只是想知道是否值得花时间对 UUID 与文本列类型进行基准测试？
Second question, hash vs b-tree indexes. No sense in sorting UUID keys so would b-tree have any other advantages over hash index?
第二个问题，hash vs b-tree 索引。对 UUID 键进行排序没有意义，那么 b-tree 与哈希索引相比还有其他优势吗？
回答by Clodoaldo Neto
A UUIDis a 16 bytes value. The same as textis a 32 bytes value. The storage sizes are:
AUUID是一个 16 字节的值。与text32 字节值相同。存储大小为：
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.
较小的表导致更快的操作。
回答by Damien Sawyer
We had a table with about 30k rows that (for a specific unrelated architectural reason) had UUIDs stored in a text field and indexed. I noticed that the query perf was slower than I'd have expected. I created a new UUID column, copied in the text uuid primary key and compared below. 2.652ms vs 0.029ms. Quite a difference!
我们有一个包含大约 30k 行的表，它（出于特定的不相关的架构原因）将 UUID 存储在文本字段中并编入索引。我注意到查询性能比我预期的要慢。我创建了一个新的 UUID 列，复制到文本 uuid 主键中并在下面进行比较。2.652 毫秒与 0.029 毫秒。差别很大！
-- With text index
QUERY PLAN
Index Scan using tmptable_pkey on tmptable (cost=0.41..1024.34 rows=1 width=1797) (actual time=0.183..2.632 rows=1 loops=1)
Index Cond: (primarykey = '755ad490-9a34-4c9f-8027-45fa37632b04'::text)
Planning time: 0.121 ms
Execution time: 2.652 ms
-- With a uuid index
QUERY PLAN
Index Scan using idx_tmptable on tmptable (cost=0.29..2.51 rows=1 width=1797) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (uuidkey = '755ad490-9a34-4c9f-8027-45fa37632b04'::uuid)
Planning time: 0.109 ms
Execution time: 0.029 ms
