"Skipping" BitmapHeapScan for simple counts

From: Maarten Mortier <maarten(dot)mortier(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: "Skipping" BitmapHeapScan for simple counts
Date: 2021-04-30 07:38:28
Message-ID: CAHX=fuo4Zq0M-s+SX0x1fODbUM+9n+FghZYwkLeyxV1+biF63A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rajesh Madiwale 2021-04-30 07:51:35 client backwards compatible with older servers
Previous Message Ludovico Caldara 2021-04-29 22:25:07 Re: Oracle vs. PostgreSQL - a comment