Postgresql 9.6 中有一个表,与关系表相比,对 jsonb 列的查询速度较慢,并且在其上添加 GIN 索引并不会使其更快。

表:

-- create table
create table dummy_jsonb (
    id serial8,
    data jsonb,
    primary key (id)
);

-- create index
CREATE INDEX dummy_jsonb_data_index ON dummy_jsonb USING gin (data);
-- CREATE INDEX dummy_jsonb_data_index ON dummy_jsonb USING gin (data jsonb_path_ops);

生成数据:
-- generate data,
CREATE OR REPLACE FUNCTION dummy_jsonb_gen_data(n integer) RETURNS integer AS $$
DECLARE
    i integer:=1;
    name varchar;
    create_at varchar;
    json_str varchar;
BEGIN
    WHILE i<=n LOOP
        name:='dummy_' || i::text;
        create_at:=EXTRACT(EPOCH FROM date_trunc('milliseconds', now())) * 1000;
        json_str:='{
                 "name": "' || name || '",
                 "size": ' || i || ',
                 "create_at": ' || create_at || '
               }';

        insert into dummy_jsonb(data) values
        (json_str::jsonb
        );
        i:= i + 1;
    END LOOP;

    return n;
END;
$$ LANGUAGE plpgsql;

-- call function,
select dummy_jsonb_gen_data(1000000);

-- drop function,
DROP FUNCTION IF EXISTS dummy_jsonb_gen_data(integer);

查询:
select * from dummy_jsonb
where data->>'name' like 'dummy_%' and data->>'size' >= '500000'
order by data->>'size' desc
offset 50000 limit 10;

测试结果:
  • 在慢速虚拟机上查询需要 1.8 秒。
  • 添加或删除索引,没有区别。
  • 使用 jsonb_path_ops 更改为索引 gin ,也没有区别。

  • 问题:
  • 是否可以使查询更快,无论是改进索引还是 sql?
  • 如果不是,这是否意味着,在这种情况下,在 pg 中关系表更合适?
  • 而且,在我的测试中,mongodb 表现更好,这是否意味着 mongodb 更适合这种存储和查询?
  • 最佳答案

    Quote from the manual



    您的查询使用 LIKE 和字符串与 > 进行比较(开始时可能不正确),GIN 索引不支持这两者。

    但即使是 (data ->> 'name') 上的索引也不会用于条件 data->>'name' like 'dummy_%' ,因为对于 所有 行都是如此,因为每个名称都以 dummy 开头。

    您可以在名称上创建常规 btree 索引:

    CREATE INDEX ON dummy_jsonb ( (data ->> 'name') varchar_pattern_ops);
    

    如果条件足够严格,将使用哪个,例如:
    where data->>'name' like 'dummy_9549%'
    

    如果需要查询大小,可以在 ((data ->> 'size')::int) 上创建一个索引,然后使用如下内容:
    where (data->>'size')::int >= 500000
    

    但是,您使用 limitoffset 将始终强制数据库读取所有行,对它们进行排序并限制结果。这永远不会很快。您可能想阅读 this article 以了解更多信息,为什么限制/偏移不是很有效。

    JSON 是关系世界的一个很好的补充,但前提是您使用得当。如果您不需要行的动态属性,则使用标准列和数据类型。尽管 JSON 支持 Postgres 非常好,但这并不意味着人们应该将它用于所有事情,仅仅因为它是当前的炒作。 Postgres 仍然是一个关系数据库,应该这样使用。

    无关,但是:您生成测试数据的函数可以简化为单个 SQL 语句。您可能不知道 generate_series() 函数用于类似的事情:
    insert into dummy_jsonb(data)
    select jsonb_build_object('name', 'dummy_'||i,
                              'size', i::text,
                              'created_at', (EXTRACT(EPOCH FROM date_trunc('milliseconds', clock_timestamp())) * 1000)::text)
    from generate_series(1,1000000) as t(i);
    

    关于Postgresql:对 jsonb 列的查询 - 索引不会使其更快,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/48092484/

    10-15 19:47
    查看更多