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