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