如何访问JSONB的内部表示

Question

在大数据中查询中间人"铸造给文本"是一个性能瓶颈...好的二进制信息在jsonb datatype上：如何营救它？

典型的"选择"示例：

 with t(x,j) as (select 'hello','{"flag1":true,"flag2":false}'::jsonb) 
 SELECT x FROM t 
 WHERE (j->>'flag1')::boolean AND NOT((j->>'flag2')::boolean)

"铸造文本"是巨大的性能丧失。理想是从JSONB到布尔值的一种直接机制，例如

 WHERE (j->'flag1')::magic_boolean AND NOT((j->'flag2')::magic_boolean)

---

ps：可以使用C ？是否可以解决此问题的CREATE CAST C 实现？

Answer 1

该功能在Postgres 11：

中实现

e.4.3.4。数据类型

[...]

将JSONB标量的铸件添加到数字和布尔数据类型(Anastasia lubennikova(

db＆lt;＆gt; fiddle。

Answer 2

tl; dr

性能，最好将#>与适当的索引一起使用，涵盖所有JSON属性(包括类型转换((访问索引时避免使用类型转换(：https：//dbfiddle.uk/?rdbms = postgres_11＆amp_11& amp.amp; pamp.amp; amp.amp; amp.amp；/p>

CREATE INDEX idx_flags_btree_jsonb ON t ((j#>'{flag1}'), (j#>'{flag2}'));

次(全部选择相同的5,195行1,000,000行(：

->>::boolean | ~75 ms
->::boolean  | ~55 ms
@>           | ~80 ms
#>           | ~40 ms

可伸缩性：

有趣的是，每个查询的10行(最佳(数字(最佳(数字(不包括第一次和最后一个运行(中的以下数字(最佳(数字：

->>::boolean |  222.333 ms
->::boolean  |  268.002 ms
@>           | 1644.605 ms
#>           |  207.230 ms

因此，实际上， new 演员表似乎在较大的数据集上放慢了速度(我怀疑这是由于它在转换为boolean之前仍然转换为text，但在包装器中，不是直接(。

我们还可以看到，使用GIN索引的@>操作员在这里的扩展不是很好，这是可以预期的，因为它比其他特殊用途索引更通用，因此需要在下面做更多的事情。 - hood。

但是，如果这些特殊用途btree索引无法放置，或者I/O成为瓶颈，那么GIN索引将是优越的，因为它仅消耗磁盘上的一小部分(以及内存(，以及增加索引缓冲区命中的机会。

，但这取决于许多因素，需要确定所有访问的应用程序。

详细信息：

最好使用单个GIN索引的@>固定操作员，因为它节省了许多特殊用途索引：

with t(x,j) as (select 'hello','{"flag1":true,"flag2":false}'::jsonb) 
 SELECT x FROM t 
 WHERE j @> '{"flag1":true, "flag2":false}'::jsonb;

...给出类似的计划：

                        QUERY PLAN                         
-----------------------------------------------------------
 CTE Scan on t  (cost=0.01..0.03 rows=1 width=32)
   Filter: (j @> '{"flag1": true, "flag2": false}'::jsonb)
   CTE t
     ->  Result  (cost=0.00..0.01 rows=1 width=64)
(4 rows)

作为替代方案(如果您能够负担得起特殊用途索引和由此产生的写入惩罚(使用#>操作员代替->或->>，然后通过该跳过任何性能式式转换，例如

with t(x,j) as (select 'hello','{"flag1":true,"flag2":false}'::jsonb) 
 SELECT x FROM t 
 WHERE j#>'{flag1}' = 'true'::jsonb AND j#>'{flag2}' = 'false'::jsonb;

...产生类似的计划：

                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 CTE Scan on t  (cost=0.01..0.04 rows=1 width=32)
   Filter: (((j #> '{flag1}'::text[]) = 'true'::jsonb) AND ((j #> '{flag2}'::text[]) = 'false'::jsonb))
   CTE t
     ->  Result  (cost=0.00..0.01 rows=1 width=64)
(4 rows)

因此，这里不再有隐式类型的转换(仅适用于给定常数，但这是一次性操作，而不是每行(。