> big_jsonb @> '[{"x": 1, "filler": "cfcd208495d565ef66e7dff9f98764da"}]';
> I wonder why bitmap heap scan adds such a big amount of time on top of the
> plain bitmap index scan.
> It seems to me, that the recheck is active although all blocks are exact
> [1] and that pg is loading the jsonb for the recheck.
>
> Is this an expected behavior?
>
Yes, this is expected. The gin index is lossy. It knows that all the
elements are present (except when it doesn't--large elements might get
hashed down and suffer hash collisions), but it doesn't know what the
recursive structure between them is, and has to do a recheck.
For example, if you change your example where clause to:
big_jsonb @> '[{"filler": 1, "x": "cfcd208495d565ef66e7dff9f98764da"}]';
You will see that the index still returns 50,000 rows, but now all of them
get rejected upon the recheck.
You could try changing the type of index to jsonb_path_ops. In your given
example, it won't make a difference, because you are actually counting half
the table and so half the table needs to be rechecked. But in my example,
jsonb_path_ops successfully rejects all the rows at the index stage.
Cheers,
Jeff
