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?