主页 / dba / 问题 / 227397
Accepted
Metropolis
Asked: 2019-01-18 08:07:51 +0800 CST

Postgres jsonb 与复合类型的性能差异

  • 772

在 jsonb 列和相同结构的复合类型列之间进行选择涉及哪些注意事项？

例如，考虑 Postgres 文档中使用的列：

CREATE TYPE inventory_item AS (
name            text,
supplier_id     integer,
price           numeric
);

这种方法与反映这种结构的 jsonb 列之间的权衡是什么？

例如，我怀疑复合类型不需要存储每条记录的键名，而 jsonb 类型需要这样做。

database-design postgresql

3 个回答

  1. 真的不是性能问题

    • 复合类型是强类型的，这意味着您必须定义它们的形状。
    • JSON 是弱类型的。

    此外，JSON 有一个用例，但是对于复合类型，您几乎不想使用它们，因为它几乎总是更好地将它们规范化为它们自己的关系。但是有一个例外——当你要围绕一组数据创建一个完整的库时，复合类型可以让一切变得更好。例如PostGIS 中的stdaddr就是这样一个例子。该类型代表一个物理地址，并且多个事物可以采用该类型（例如 PostGIS 地理编码器）。

    所以你可以做

    CREATE TABLE foo ( foo_id int, address postgis.stdaddr );

    现在您可以相对轻松地获取地理编码信息，您只需传递一个值而不是 15。

    • 4

  2. 使用复合类型

    • 更少的磁盘空间
    • 更快的选择/插入/更新

    使用复合类型

    • 它是结构化的（即不灵活）。你不能在飞行中弥补领域。
    • 更长的编码时间（尤其是当你使用 ORM 时）

    还有一个用于存储使用另一个表的结构化数据的选项:)。您可以通过创建 2 个表来测试自己。

    • 2
  3. Best Answer

    TYPEinventory_item是在问题中定义的（与指南中相同），因此我们只需要定义具有复合（ROW）类型的表tc和具有 JSONb 类型的tj 。

    插入时间

    -- drop table tc; drop table tj;
CREATE TABLE tc (id serial, x inventory_item);
CREATE TABLE tj (id serial, x JSONb);

EXPLAIN ANALYSE 
  INSERT INTO tc(x) VALUES 
    (ROW('fuzzy dice', 42, 1.99)),
    (ROW('test pi', 3, 3.1415))
; -- Execution Time: try1 0.386 ms; try2 0.559 ms; try3 0.102 ms; ... 
EXPLAIN ANALYSE 
  INSERT INTO tj(x) VALUES 
    ('{"name":"fuzzy dice", "supplier_id":42, "price":1.99}'::jsonb),
    ('{"name":"test pi", "supplier_id":3, "price":3.1415}'::jsonb)
; -- Execution Time: try1 0.343; try2 0.355 ms; try3 0.112 ms; ...

    当然，我们需要循环等复杂的东西来测试......但似乎“可比”的时间，没有太大的区别。

    选择本地时间

    仅检索原始数据类型。需要好的基准，但让我们想象一些简单的东西，只检查大的差异。

    EXPLAIN ANALYSE   SELECT x, i FROM tc, generate_series(1,999999) g(i); 
EXPLAIN ANALYSE   SELECT x, i FROM tj, generate_series(1,999999) g(i);

    再次没有区别。两者都具有“执行时间：~460”。

    爆炸时间

    EXPLAIN ANALYSE
  SELECT i, id, (x).name, (x).supplier_id, (x).price
  FROM tc, generate_series(1,999999) g(i)
; -- Execution Time: ~490 ms 
EXPLAIN ANALYSE
  SELECT i, tj.id, t.* 
  FROM tj, generate_series(1,999999) g(i),
       LATERAL jsonb_populate_record(null::inventory_item, tj.x) t  
; -- Execution Time: ~650 ms

    似乎将 JSONb-object 转换为 SQL-row 非常快！似乎是二进制转换：我们可以假设该函数jsonb_populate_record使用inventory_item内部定义将 JSONb 类型映射到 SQL。

    它比复合表更快。

    爆炸和计算一些东西

    EXPLAIN ANALYSE
  SELECT i, (x).supplier_id+i, (x).price+0.01
  FROM tc, generate_series(1,999999) g(i)
; -- Execution Time: ~800 ms

EXPLAIN ANALYSE
  SELECT i,  t.supplier_id+i, t.price+0.01
  FROM tj, generate_series(1,999999) g(i),
       LATERAL jsonb_populate_record(null::inventory_item, tj.x) t  
; -- Execution Time: ~620 ms

    大约 150 毫秒的计算，所以预期的时间相同......上面的例子有一些错误，需要更好的基准来检查真正的差异。

    检查从文本中投射的比较时间。

    EXPLAIN ANALYSE -- (supposed to) cast from binary
  SELECT i, id, x->>'name' as name, 
         (x->'supplier_id')::int as supplier_id, (x->'price')::float as price
  FROM tj, generate_series(1,999999) g(i)
; -- Execution Time: ~1600 ms 

EXPLAIN ANALYSE -- cast from text
  SELECT i, id, x->>'name' as name, 
         (x->>'supplier_id')::int as supplier_id, (x->>'price')::float as price
  FROM tj, generate_series(1,999999) g(i)
; -- Execution Time: ~1600 ms

    时间长且相同。似乎(x->'supplier_id')::int它只是(x->>'supplier_id')::int or的一种糖语法(x->'supplier_id')::text::int。

    PS：这个答案也是另一个问题的补充，关于“使用 JSONb 进行二进制到二进制转换”。

    • 2

相关问题