加速JSONB对象字段的查询Postgres /索引JSONB字段

Question

我试图加快Postgres中对象字段的查询速度。

我正在搜索的表有以下结构：

varchar(2)

• images：
• page_id: integer
• lang：
• jsonb

图像JSONB字段包含这样的对象：

    {
      "-1": {
        "ns": 6,
        "known": "",
        "title": "File:Architrave nuraghe.jpg",
        "missing": "",
        "contentmodel": "wikitext",
        "pagelanguage": "it",
        "pagelanguagedir": "ltr",
        "pagelanguagehtmlcode": "it"
      },
      "-2": {
        "ns": 6,
        "known": "",
        "title": "File:Commons-logo.svg",
        "missing": "",
        "contentmodel": "wikitext",
        "pagelanguage": "it",
        "pagelanguagedir": "ltr",
        "pagelanguagehtmlcode": "it"
      },
    }

我需要检索使用某个文件的所有页面--我喜欢这样做：

select * from (
       select lang, page_id, img.b::jsonb->>'title' as file
       from (
            select *
        from pages where jsonb_typeof(images_jsonb) ='object') a,
             jsonb_each(images_jsonb) as img(a, b)
        ) as q
where file = 'File:Vigoleno castello2.jpg';

它工作正常，但它非常慢-查询计划如下所示：

                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..199113.78 rows=3998 width=39)
   ->  Seq Scan on pages  (cost=0.00..193066.80 rows=3998 width=39)
         Filter: (jsonb_typeof(images_jsonb) = 'object'::text)
   ->  Function Scan on jsonb_each img  (cost=0.00..1.50 rows=1 width=32)
         Filter: ((b ->> 'title'::text) = 'File:Vigoleno castello2.jpg'::text)
(5 rows)

我正在考虑在对象字段上建立索引，我已经阅读了一些关于对象索引的内容，但是在字段字段上找不到索引的内容。

我已经考虑过作为另一种策略来规范到另一张桌子上，但我想避免这种情况(保持同步等是一种负担)。

有什么想法吗？

Answer 1

您不能使用普通的jsonb索引方法来实现这一点，因为它们不允许在不指定指向对象值的键的情况下将其索引到对象值中。

您可以使用帮助函数将JSONB转换为一个值数组。

create function jsonb_to_array(jsonb) returns text[] immutable language sql as $$ 
    select array_agg(value->>'title') from jsonb_each ($1)
$$;

create index on pages using gin (jsonb_to_array(images)) 
    where jsonb_typeof(images) ='object'

select * from pages where 
    jsonb_typeof(images_jsonb) ='object' and
    jsonb_to_array(images_jsonb) && ARRAY['File:Vigoleno castello2.jpg']

请注意，我没有在查询中保留取消嵌套，因为我不知道您在多大程度上希望得到未嵌套的结果，而只是通过取消嵌套来提供一种获取正确行的方法。您可能需要在“File:VigolenoCastello2.jpg”上过滤两次，一次是通过索引获得正确的行，另一次是从行内获取正确的元素。

在这个主题上还有许多其他的变体。您可以使用助手函数返回带有标题数组的JSONB，而不是text[]。或者您可以让它返回对象数组的JSONB，而不是对象的对象。如果这样做，可以使用@>查询该对象数组。

create function jsonb_to_array2(jsonb) returns jsonb immutable language sql as $$ 
        select jsonb_agg(value||jsonb_build_object('key',key)) from jsonb_each ($1)
$$;

create index on pages using gin (jsonb_to_array2(images_jsonb)) 
    where jsonb_typeof(images_jsonb) ='object'

select * from pages where 
    jsonb_typeof(images_jsonb) ='object' and
    jsonb_to_array2(images_jsonb) @> '[{"title":"File:Architrave nuraghe.jpg"}]';