2

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
  • 47,252
  • 12
  • 78
  • 118
dewastator
  • 213
  • 4
  • 13

1 Answers1

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

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Do you have any tip how to replace `ILIKE %asd%`? What is the solution? – dewastator Nov 28 '17 at 19:16
  • You can use [**TEXT SEARCH**](https://www.postgresql.org/docs/current/static/textsearch.html) But I haven't use it with a JSON field yet. – Juan Carlos Oropeza Nov 28 '17 at 19:21
  • 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 – dewastator Nov 28 '17 at 19:33
  • GIN index is based on trigrams and it works with `ILIKE %asd%` queries. See [this](https://www.cybertec-postgresql.com/en/postgresql-more-performance-for-like-and-ilike-statements/) article. – Monsignor Jul 26 '23 at 12:38