PostgreSQL现有jsonb结构下子键存在性查询如何命中索引

方案1：固定查询用户ID场景（仅查用户2的role2权限） #

直接针对查询用到的表达式创建GIN索引，和你的原查询完全匹配，可直接命中：

CREATE INDEX idx_items_acl_users_2 ON items USING GIN ((acl->'users'->'2') jsonb_ops);

创建完成后你原有的查询语句SELECT COUNT(*) FROM items WHERE ( acl->'users'->'2' ? 'role2')就可以直接命中索引，不需要修改任何查询逻辑。
如果需要进一步缩小索引体积提升查询效率，也可以加条件创建部分索引：

CREATE INDEX idx_items_acl_users_2 ON items USING GIN ((acl->'users'->'2') jsonb_ops)
WHERE acl ? 'users' AND acl->'users' ? '2';

方案2：动态查询用户ID场景（需要查不同用户的权限） #

如果你的查询用户ID不是固定值，而是会动态传入不同用户ID做查询，可以创建jsonb_path_ops类型的GIN索引，同时调整查询逻辑兼顾索引命中和查询效率：

1. 先创建更轻量的jsonb_path_ops索引：

CREATE INDEX idx_items_acl_users ON items USING GIN ((acl->'users') jsonb_path_ops);

2. 调整查询写法，先用@>运算符走索引过滤大部分不符合的行，再用?做精准校验：

SELECT COUNT(*) FROM items 
WHERE acl->'users' @> '{"2": {"role2": {}}}'::jsonb
AND acl->'users'->'2' ? 'role2';

jsonb_path_ops索引的体积只有默认jsonb_ops的1/4左右，查询效率高很多，上面的写法先通过索引过滤掉99%以上不匹配的行，再对少量剩余行做精准校验，整体速度可以达到你的性能要求。

内容的提问来源于stack exchange，提问作者NathanVss