Optimize select query with OR and ILIKE
1
vote
0
answers
671
views
I have the following query for my PostgreSQL 13 database:
SELECT * FROM "user"
INNER JOIN "comment" on "comment"."user_id" = "user"."id"
WHERE "user"."biography" ILIKE '%SomeString%'
OR "comment"."text" ILIKE '%SomeString%';
I have a GIN index defined on
user
biography
and another one on comment
text
CREATE INDEX user_bio_idx ON "user" USING gin ("biography" gin_trgm_ops);
CREATE INDEX comment_txt_idx ON "comment" USING gin ("text" gin_trgm_ops);
Unfortunately, running EXPLAIN ANALYZE ...
on my select query reveals that the indexes are not being used and the query is taking 4 to 5 seconds.
How can I improve its performance? (I already tried UNION
and even concatenating the two columns)
Asked by Kamal Karain
(11 rep)
Mar 16, 2022, 10:18 PM
Last activity: Sep 29, 2022, 11:07 PM
Last activity: Sep 29, 2022, 11:07 PM