postgresql - 从大型jsonb字段中检索多个值的速度更快(PostgreSQL 9.4)

tl;博士

使用PSQL 9.4，有一种方法可以从jsonb字段中检索多个值，例如使用虚函数的方法:

jsonb_extract_path(x, ARRAY['a_dictionary_key', 'a_second_dictionary_key', 'a_third_dictionary_key'])

希望加快选择多个值所需的几乎线性的时间(1个值= 300ms，2个值= 450ms，3个值= 600ms)

背景

我有以下jsonb表:

CREATE TABLE "public"."analysis" (
  "date" date NOT NULL,
  "name" character varying (10) NOT NULL,
  "country" character (3) NOT NULL,
  "x" jsonb,
  PRIMARY KEY(date,name)
);

大约有10万行，其中每行都有一个jsonb字典，其中包含90多个键和相应的值。我正在尝试编写一个SQL查询以相当快的方式(
索引和查询:190ms

我首先添加一个索引:

CREATE INDEX ON analysis USING GIN (x);

这样可以快速基于“x”字典中的值进行查询，例如:

SELECT date, name, country FROM analysis where date > '2014-01-01' and date < '2014-05-01' and cast(x#>> '{a_dictionary_key}' as float) > 100;

这大约需要190毫秒(我们可以接受)

检索字典值

但是，一旦我开始添加要在SELECT部分中返回的键，执行时间就会几乎成线性增长:

1个值:300ms

select jsonb_extract_path(x, 'a_dictionary_key') from analysis where date > '2014-01-01' and date < '2014-05-01' and cast(x#>> '{a_dictionary_key}' as float) > 100;

耗时366毫秒(+175毫秒)

select x#>'{a_dictionary_key}' as gear_down_altitude from analysis where date > '2014-01-01' and date < '2014-05-01' and cast(x#>> '{a_dictionary_key}' as float) > 100 ;

花费300毫秒(+110毫秒)

3个值:600ms

select jsonb_extract_path(x, 'a_dictionary_key'), jsonb_extract_path(x, 'a_second_dictionary_key'), jsonb_extract_path(x, 'a_third_dictionary_key') from analysis where date > '2014-01-01' and date < '2014-05-01' and cast(x#>> '{a_dictionary_key}' as float) > 100;

耗时600毫秒(对于每个选定值，为+410或+100)

select x#>'{a_dictionary_key}' as a_dictionary_key, x#>'{a_second_dictionary_key}' as a_second_dictionary_key, x#>'{a_third_dictionary_key}' as a_third_dictionary_key from analysis where date > '2014-01-01' and date < '2014-05-01' and cast(x#>> '{a_dictionary_key}' as float) > 100 ;

耗时600毫秒(对于每个选定值，为+410或+100)

更快检索更多值

有没有一种方法可以从jsonb字段中检索多个值，例如使用虚函数的方法:

jsonb_extract_path(x, ARRAY['a_dictionary_key', 'a_second_dictionary_key', 'a_third_dictionary_key'])

这可能会加快这些查找的速度。它可以将它们作为列，列表/数组甚至json对象返回。

使用PL/Python检索数组

我只是使用PL/Python创建了一个自定义函数，但这要慢得多(5s +)，这可能是由于json.loads造成的:

CREATE OR REPLACE FUNCTION retrieve_objects(data jsonb, k VARCHAR[])
RETURNS TEXT[] AS $$
  if not data:
    return []

  import simplejson as json
  j = json.loads(data)

  l = []
  for i in k:
    l.append(j[i])

  return l

$$ LANGUAGE plpython2u;

# Usage:
# select retrieve_objects(x, ARRAY['a_dictionary_key', 'a_second_dictionary_key', 'a_third_dictionary_key']) from analysis  where date > '2014-01-01' and date < '2014-05-01'

更新2015-05-21

我使用带有GIN索引的hstore重新实现了该表，其性能几乎与使用jsonb相同，即对我而言没有帮助。

最佳答案

您正在使用 #> operator ，它看起来像是执行路径搜索。您是否尝试过正常的->查找？喜欢:

select  json_column->'json_field1'
,       json_column->'json_field2'

看看使用临时表会发生什么会很有趣。喜欢:

create temporary table tmp_doclist (doc jsonb)
;
insert  tmp_doclist
        (doc)
select  x
from    analysis
where   ... your conditions here ...
;
select  doc->'col1'
,       doc->'col2'
,       doc->'col3'
from    tmp_doclist
;

关于postgresql - 从大型jsonb字段中检索多个值的速度更快(PostgreSQL 9.4)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30327140/