Hello,
We have a table with raw jsonb data in a field "data".
We have a jsonb_ops GIN index on this field, because we query the following "freeform" match:
SELECT COUNT(id)
FROM records
WHERE data @@ '$.**.label == "person"';
When analysing this plan, it seems the BitmapIndexScan to do this is fairly fast (as it uses the GIN index, which is quite powerful), but the BitmapHeapScan that follows, is slow, when the amount of matching records span a lot of heap blocks.
I can understand why this happens: the BitmapIndexScan is supposed to be somewhat optimistic, and rows need to be discarded by properly going through the data in a BitmapHeapScan. This optimism can (for other types of indices) be somewhat tamed by "vacuum"ing the indices, which would allow IndexOnly queries that include availability of the records with the index.
However, in the case of jsonb queries above, there is no need to do the BitmapHeapScan really: the BitmapIndexScan has the correct row count, always.
So, I would like for us to just skip it. If we need the data, we can do LIMIT/OFFSET windows to tame the BitmapHeapScan, but having a full count would be nice.
Can we skip this in some way? Can I find the right spot in the code where this could be skipped?
Thanks so much for any help,
--
Maarten