Postgres Query uses Bitmap index scan on GIN indexed item. Does not GIN use B-Tree internally?

Question

From postgres docs on GIN -

Internally, a GIN index contains a B-tree index constructed over keys.

But in my use case, I see Bitmap indexes instead -

My Schema and Indexes are created as below.

CREATE TABLE customer (
    jdata jsonb NOT NULL,
    comments text NULL
);

CREATE INDEX idx_jdata ON customer USING gin (jdata jsonb_path_ops);

Say, inserted a 10K records. sample prepared data/ alternate link

explain analyze select jdata from  customer where jdata @> '{"supplier":{"id":"7f5644ca-f0d3-4f50-947b-9e3e38f7b796"}}'

Outpt -

Bitmap Heap Scan on file_set  (cost=2139.27..36722.32 rows=10744 width=18) (actual time=1.438..267.122 rows=4048 loops=1)
  Recheck Cond: (jdata @> '{"supplier": {"id": "7f5644ca-f0d3-4f50-947b-9e3e38f7b796"}}'::jsonb)
  Heap Blocks: exact=1197
  ->  Bitmap Index Scan on idx_jdata  (cost=0.00..2136.58 rows=10744 width=0) (actual time=1.214..1.214 rows=4048 loops=1)
        Index Cond: (jdata @> '{"supplier": {"id": "7f5644ca-f0d3-4f50-947b-9e3e38f7b796"}}'::jsonb)
Planning Time: 0.065 ms
Execution Time: 267.456 ms

(the query plan above is based on real output form postgres - i had to change table/column names)

Why is Bitmap index created when a GIN index is created

My Postgres DB version is 13.5.

After looking at comment from @a-horse-with-no-name -> I tried below

SET enable_seqscan = OFF;
explain analyze
select * from pg_opclass where "oid"=10003;
SET enable_seqscan = ON;

and the output -

QUERY PLAN                                                                                                                      |
--------------------------------------------------------------------------------------------------------------------------------+
Index Scan using pg_opclass_oid_index on pg_opclass  (cost=0.14..8.16 rows=1 width=93) (actual time=0.015..0.016 rows=1 loops=1)|
  Index Cond: (oid = '10003'::oid)                                                                                              |
Planning Time: 0.060 ms                                                                                                         |
Execution Time: 0.027 ms                                                                                                        |

I see difference - "Bitmap Index Scan on idx_jdata" vs "Index Scan using pg_opclass_oid_index on pg_opclass"

Does this mean anything important. Can some one add more details about the using vs on and the on pg_opsclass.

There is no bitmap index "created", what you are seeing is a "bitmap scan" which reads data from the index and keeps a bitmap of the data in memory. — user330315
– user330315, Commented Dec 28, 2022 at 19:55
@a_horse_with_no_name - is the explain output bit messed.. should not it also include the index scan details. How can I figure out if its a in memory build up from the index. — samshers
– samshers, Commented Dec 28, 2022 at 20:02
@a_horse_with_no_name - plz see my further addition to the question. Any thing you want to elaborate over on vs using. — samshers
– samshers, Commented Dec 28, 2022 at 20:15

jjanes · Accepted Answer · 2022-12-29 03:15:37Z

2

A GIN index can contain the same tuple pointer multiple times, listed under different tokens which the field is broken down into. Those need to be de-duplicated to make sure it doesn't return duplicate rows where it should not, and the way GIN chooses to do this is by forcing it go through a bitmap index scan; which inherently deduplicates the pointers. It is not the index which is the bitmap, it is the scan which uses bitmaps. Any index can be used in a bitmap index scan, but GIN indexes can only be used in bitmap scans, due to the need to deduplicate.

answered Dec 29, 2022 at 3:15

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Query uses Bitmap index scan on GIN indexed item. Does not GIN use B-Tree internally?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related