Postgresql GIN index on jsonb strange behaviour

Viewed 19

I'm using postgres (PostgreSQL) 14.5 (Homebrew) on Mac OS X 12.6.

Here is my table:

CREATE TABLE test1 (
  id SERIAL PRIMARY KEY,
  data JSONB,
  column1 VARCHAR
);

CREATE INDEX ON test1 USING GIN (data);

Now if I filter the records using this query

EXPLAIN SELECT * FROM test1 WHERE data @> '"foo"';

I can see that the GIN index has been used:

                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Bitmap Heap Scan on test1  (cost=8.07..18.22 rows=8 width=68)
   Recheck Cond: (data @> '"foo"'::jsonb)
   ->  Bitmap Index Scan on test1_data_idx  (cost=0.00..8.06 rows=8 width=0)
         Index Cond: (data @> '"foo"'::jsonb)

If now I create the same table but with some more columns like this:

CREATE TABLE test2 (
  id SERIAL PRIMARY KEY,
  data JSONB,
  column1 VARCHAR,
  column2 VARCHAR,
  column3 VARCHAR
);

CREATE INDEX ON test2 USING GIN (data);

and perform the same query

EXPLAIN SELECT * FROM test2 WHERE data @> '"foo"';

I see that the GIN index is no more used:

                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on test2  (cost=0.00..16.38 rows=5 width=132)
   Filter: (data @> '"foo"'::jsonb)

Why does this happen? How can I get around this problem?

0 Answers
Related