里使用Postgres 9.5 GIN索引和JSONB

Question

我试图创建一个Postgres GIN索引来加速下面的查询：

CREATE TABLE foo (
    id serial primary key, 
    a jsonb not null 
); 

insert into foo(a) values 
    ('[{"b": "aaa"}, {"b": "ddd"}]'::jsonb), 
    ('[{"b": "aaa"}, {"b": "aaa"}]'::jsonb), 
    ('[{"b": "aaa"}]'::jsonb), 
    ('[{"b": "aaa"}]'::jsonb), 
    ('[{"b": "aaa"}]'::jsonb), 
    ('[{"b": "bbb"}]'::jsonb), 
    ('[{"b": "bbb"}]'::jsonb), 
    ('[{"b": "bbb"}]'::jsonb), 
    ('[{"b": "ccc"}]'::jsonb), 
    ('[]'::jsonb); 

select distinct id from (
    select id, jsonb_array_elements(a)->>'b' as b from foo 
) t where t.b = 'aaa' 

是在Postgres的可能这样的事情？我也愿意接受其他选择。不幸的是，我无法规格化表格，所以我需要使用我已有的表格结构。

Answer 1

是的，你可以在这里申请一个GIN索引，但它可能不是特别有用：

CREATE INDEX find_fast_jsonb_value 
ON foo USING GIN (a jsonb_path_ops); 

现在你仍然必须通过阵列匹配键/值对进行搜索。您的查询就变成了：

SELECT DISTINCT id 
FROM foo, jsonb_array_elements(a) AS t(b) -- Implicit LATERAL join 
WHERE b @> '{"b": "aaa"}';     -- Comparing json key/values here 

这也放置set-returning-functionjsonb_array_elements()的FROM条款，它属于英寸