我正在Postgres 9,6中进行一些查询，涉及一个jsonb列，一个int列，两个索引（每列一个）和onPause()运算符。查询的jsonb部分使用它的索引很好，但是int列却没有。
奇怪的是，将?|条件拆分为多个?|条件时，两个索引都能按预期工作。
以下是我正在使用的产品的详细信息。
-
int索引
-
or ?
-
-
jsonb索引
-
CREATE INDEX team_id_index ON revisions (team_id);
-
半工CREATE INDEX _group_ids_gin_index ON revisions USING GIN(_group_ids jsonb_ops);查询
?|select *
from revisions
where team_id = 1
and _group_ids ?| '{"0","91"}';
运作良好的Bitmap Heap Scan on revisions (cost=224.42..2214.66 rows=92 width=992) (actual time=7.783..40.178 rows=4454 loops=1)
Recheck Cond: (_group_ids ?| '{0,91}'::text[])
Filter: (team_id = 1)
Rows Removed by Filter: 63027
Heap Blocks: exact=5129
-> Bitmap Index Scan on _group_ids_gin_index (cost=0.00..224.40 rows=587 width=0) (actual time=7.086..7.086 rows=67481 loops=1)
Index Cond: (_group_ids ?| '{0,91}'::text[])
Planning time: 0.142 ms
Execution time: 40.401 ms
查询
?select *
from revisions
where team_id = 1
and (_group_ids ? '0' or _group_ids ? '91');
如果我只需要使用Bitmap Heap Scan on revisions (cost=2414.55..3091.44 rows=184 width=992) (actual time=12.965..16.162 rows=4454 loops=1)
Recheck Cond: (((_group_ids ? '0'::text) OR (_group_ids ? '91'::text)) AND (team_id = 1))
Heap Blocks: exact=818
-> BitmapAnd (cost=2414.55..2414.55 rows=184 width=0) (actual time=12.844..12.844 rows=0 loops=1)
-> BitmapOr (cost=424.89..424.89 rows=1173 width=0) (actual time=7.329..7.329 rows=0 loops=1)
-> Bitmap Index Scan on _group_ids_gin_index (cost=0.00..212.40 rows=587 width=0) (actual time=6.439..6.439 rows=67076 loops=1)
Index Cond: (_group_ids ? '0'::text)
-> Bitmap Index Scan on _group_ids_gin_index (cost=0.00..212.40 rows=587 width=0) (actual time=0.887..0.887 rows=405 loops=1)
Index Cond: (_group_ids ? '91'::text)
-> Bitmap Index Scan on team_id_index (cost=0.00..1989.36 rows=91858 width=0) (actual time=5.218..5.218 rows=90229 loops=1)
Index Cond: (team_id = 1)
Planning time: 0.154 ms
Execution time: 16.540 ms
而不是?重写所有查询，这没什么大不了的，但是为什么的奥秘在于，这让我发疯了。请帮我的理智！