JSONB ILIKE indexing

Question

I have a table people with body column as a jsonb type.

                                        Table "public.people"
     Column      |            Type             | Collation | Nullable |      Default       | Storage  | Stats target | Description
-----------------+-----------------------------+-----------+----------+--------------------+----------+--------------+-------------
 id              | uuid                        |           | not null | uuid_generate_v4() | plain    |              |
 body            | jsonb                       |           | not null |                    | extended |              |

Indexes:
    "people_pkey" PRIMARY KEY, btree (id)
    "idx_name" gin ((body ->> 'name'::text) gin_trgm_ops)

My index looks as follows:

CREATE INDEX idx_name ON people USING gin ((body ->> 'name') gin_trgm_ops);

However, when I do:

EXPLAIN ANALYZE SELECT * FROM "people" WHERE ((body ->> 'name') ILIKE '%asd%') LIMIT 40 OFFSET 0;

I see:

Limit  (cost=0.00..33.58 rows=40 width=104) (actual time=100.037..4066.964 rows=11 loops=1)                                                     
   ->  Seq Scan on people  (cost=0.00..2636.90 rows=3141 width=104) (actual time=99.980..4066.782 rows=11 loops=1) 
         Filter: ((body ->> 'name'::text) ~~* '%asd%'::text)                                                                                     
         Rows Removed by Filter: 78516                                                                                                           
 Planning time: 0.716 ms                                                                                                                         
 Execution time: 4067.038 ms

Why is the index not used there?

Did you ever find the answer? I'm facing the same problem: strings inside JSONB values fail to use the trigram index with ILIKE. — user124114, Jun 08 '19 at 13:04

Vao Tsun · Answer 1 · 2017-11-29T14:26:36.057

2

update

to avoid confusion with operators mentionned above I wll quote http://www.sai.msu.su/~megera/oddmuse/index.cgi/Gin

Gin comes with built-in support for one-dimensional arrays (eg. integer[], text[]), but no support for NULL elements. The following operations are available:

contains: value_array @> query_array

overlap: value_array && query_array

contained: value_array <@ query_array

if you want to use advantages of GIN, use @>, not LIKE operator

Also, please look at much better Erwins answer on close question

edited Nov 29 '17 at 14:26

answered Nov 29 '17 at 13:34

Vao Tsun

47,234
13
100
132

Besides, take a look on [this discussion](https://stackoverflow.com/a/799616/7925366) about non-sargable queries. – Michel Milezzi Nov 29 '17 at 13:38
The problem with FTS is that I cannot simulate `%query%` there. – Kamil Lelonek Nov 29 '17 at 13:52
you don't need to simulate it - `ilike '%val%'` will work fine, it just wont use FTS index. I mean you ask *Why is the index not used there?*, the short answer is *because you use `LIKE` operator* – Vao Tsun Nov 29 '17 at 13:54
We are not using arrays but searching within a string. – Kamil Lelonek Nov 29 '17 at 14:05
I put confusing quote - sorry. GIN itself is a key-pair store, that's why it is so effective for arrays or jsonb – Vao Tsun Nov 29 '17 at 14:22
1

I'm looking if there's a particular string matching `name` inside `body`, not if there's an exact JSON in this `body`. – Kamil Lelonek Nov 29 '17 at 14:35
This answer seems both *incoherent* and *irrelevant*. How does that information answer OP's question? – user124114 Jun 08 '19 at 13:07
OP asks - why LIKE doesnt use GIN on jsonb I answer - because GIN requires different operators - not `LIKE`, but `@>, && or <@`. obviously you can cast jsonb to text and use `LIKE` but then you will need different index - not GIN. – Vao Tsun Jun 10 '19 at 11:50

JSONB ILIKE indexing

1 Answers1