GIN index operator ?(jsonb,text) not working?

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: GIN index operator ?(jsonb,text) not working?
Date: 2022-06-28 08:55:54
Message-ID: CACJufxFcjss=F99XVA1o458XTVqfSWJcAJyTmp+E3UQcnx35bw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

dbfiddle:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=060af497bbb75ecddad9fd2744f8022b
---
create table test101 ( doc_id bigserial, document jsonb);
insert into test101(document) values ('{"user_removed" :false}') returning
*;
insert into test101(document) select '{"user_removed" :false}'::jsonb
from generate_series(1,10000);
insert into test101(document) select ('{"user_remove" :false, "test":'
|| i ||'}'::text)::jsonb
from generate_series(1,400) i;

CREATE INDEX test101_gin_user_removed_na ON test101 USING GIN (document
jsonb_ops)
where (document ? 'user_removed') is false;
CREATE INDEX test101_gin_user_removed_na_b ON test101(document)
where (document ? 'user_removed') is false;

Since 400 is very few percent compared to 10000.
but the following query will not use any of the indexes.
explain (analyze) select * from test101 where document ?
'user_removed' is false ;

--
I recommend David Deutsch's <<The Beginning of Infinity>>

Jian

Browse pgsql-general by date

  From Date Subject
Next Message Amit Kapila 2022-06-28 12:13:44 Re: Support logical replication of DDLs
Previous Message Kyotaro Horiguchi 2022-06-28 07:31:59 Re: help for pg_wal issue