[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    GIN index operator ?(jsonb,text) not working?
From:       jian he <jian.universality () gmail ! com>
Date:       2022-06-28 8:54:33
Message-ID: CACJufxFcjss=F99XVA1o458XTVqfSWJcAJyTmp+E3UQcnx35bw () mail ! gmail ! com
[Download RAW message or body]

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

[Attachment #3 (text/html)]

<div dir="ltr"><div class="gmail_default" \
style="font-family:verdana,sans-serif">Hi,</div><div class="gmail_default" \
style="font-family:verdana,sans-serif"><br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif">dbfiddle: <a \
href="https://dbfiddle.uk/?rdbms=postgres_14&amp;fiddle=060af497bbb75ecddad9fd2744f802 \
2b">https://dbfiddle.uk/?rdbms=postgres_14&amp;fiddle=060af497bbb75ecddad9fd2744f8022b</a></div><div \
class="gmail_default" style="font-family:verdana,sans-serif">---<br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif">create   table test101 ( \
doc_id bigserial, document jsonb);<br>insert into test101(document) values \
(&#39;{&quot;user_removed&quot; :false}&#39;) returning *;<br>insert into   \
test101(document)   select &#39;{&quot;user_removed&quot; :false}&#39;::jsonb from \
generate_series(1,10000);<br>insert into   test101(document)   select \
(&#39;{&quot;user_remove&quot;   :false, &quot;test&quot;:&#39; || i \
||&#39;}&#39;::text)::jsonb<br>                                                from \
generate_series(1,400) i;</div><div class="gmail_default" \
style="font-family:verdana,sans-serif"><br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif">CREATE INDEX test101_gin_user_removed_na ON \
test101 USING GIN (document jsonb_ops)<br>      where (document ? \
&#39;user_removed&#39;) is false; <br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif">CREATE INDEX test101_gin_user_removed_na_b ON \
test101(document)<br>      where (document ? &#39;user_removed&#39;) is false; \
<br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif"><br></div><div class="gmail_default" \
style="font-family:verdana,sans-serif">Since 400 is very few percent compared to \
10000.  </div><div class="gmail_default" style="font-family:verdana,sans-serif">but \
the following query will not use any of the indexes.</div><div class="gmail_default" \
style="font-family:verdana,sans-serif">       explain (analyze)   select * from \
test101 where   document ? &#39;user_removed&#39; is false ;</div><div \
class="gmail_default" style="font-family:verdana,sans-serif"><br></div><div \
class="gmail_default" style="font-family:verdana,sans-serif"><br></div><br>-- \
<br><div dir="ltr" data-smartmail="gmail_signature"><div dir="ltr"><div><font \
size="2"><span style="color:rgb(153,153,153)">  I recommend David \
<span>Deutsch</span>&#39;s &lt;&lt;The Beginning of \
Infinity&gt;&gt;</span><br></font></div><div><font \
size="2"><br></font></div><div><font size="2"><span style="color:rgb(153,153,153)">   \
Jian</span><br></font></div><div><br></div><div><br></div></div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]