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
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 |