Creating GIN index on ILIKE postgres query

Question

I am using postgres 9.6. I store JSONB data in one column and I would improve performance of one query. In my json data I have name column which is text. I am querying this data using following query.

SELECT "id", "body", "created_at", "updated_at" 
FROM "read_models" 
WHERE ((body ->> 'name') ILIKE '%asd%') 
LIMIT 40 OFFSET 0;

Here is the analyzed result of this query:

Limit  (cost=0.00..33.58 rows=40 width=72) (actual 
time=112.428..4071.757 rows=11 loops=1)                                                      
|
|   ->  Seq Scan on read_models  
(cost=0.00..2636.90 rows=3141 width=72) (actual time=112.416..4071.646 
rows=11 loops=1) |
|         Filter: ((body ->> 'name'::text) ~~* '%asd%'::text)                                                                                     
|
|         Rows Removed by Filter: 78516                                                                                                           
|
| Planning time: 1.658 ms                                                                                                                         
|
| Execution time: 4071.847 ms

I created following index for this property:

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name ON read_models USING gin ((body ->> 'name') gin_trgm_ops);

I created different index types but it is always the same result. The query time is the same like without any index. I see that PG not uses this index when I query that data. I saw many informations how to index text data in postgres and I don't understand why It doesn't work in my case. Thanks for any help.

Juan Carlos Oropeza · Accepted Answer · 2017-11-28 19:10:39Z

1

The problem is your where condition isnt SARGABLE

What makes a SQL statement sargable?

You can use index for LIKE 'ABC%' but not for LIKE '%ABC%'

Check those tips, even for MySQL the majority also apply for Postgres MySQL index TIPS

answered Nov 28, 2017 at 19:10

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dewastator Over a year ago

Do you have any tip how to replace ILIKE %asd%? What is the solution?

Juan Carlos Oropeza Over a year ago

You can use TEXT SEARCH But I haven't use it with a JSON field yet.

dewastator Over a year ago

Ok I see. Now I need to figure out how to replace my LIKE queries with text search operators. If you have some tutorials (which will be easier to get into rather than docs) please post here. Thx

Monsignor Over a year ago

GIN index is based on trigrams and it works with ILIKE %asd% queries. See this article.

Collectives™ on Stack Overflow

Creating GIN index on ILIKE postgres query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related