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)

  • "I already tried UNION" so what happened?
    – jjanes
    Mar 17, 2022 at 2:16
  • UNION with pg_trgm indexes works fine for me.
    – jjanes
    Mar 17, 2022 at 16:17


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.