Sample Header Ad - 728x90

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