Postgres not using indexes when ORing across join I'm doing a query with a join on two tables, where I need to do an ILIKE filter on a field in each of the tables with wildcards at both ends, and it should return a row if either of them match.

Approx:

SELECT * FROM "tableA" LEFT OUTER JOIN "tableB" ON 
"tableA"."tableB_id" = "tableB"."id" 
WHERE "tableA"."name" ILIKE '%rich%' OR "tableB"."name" ILIKE '%rich%';


As both of these tables are quite large I decided to try using trigram indexes to speed things up. However, this didn't appear to help, and having done an EXPLAIN ANALYZE I discovered that the indexes are not used. They are used if the filters are combined with AND instead of OR, and they are used if I do an OR between two indexed columns in the same table, just not with an OR between indexed columns in different tables.

Anyone have any ideas why?
It's sometimes useful to rewrite a query with OR conditions as a UNION (ALL) query. This usually leads to a different execution plan which may be more efficient. Testing will identify which of the two methods/plans is more efficient in each case (eg. if the trigram indexes are used for both parts of the union).

The rewrite:

SELECT * 
FROM "tableA" AS a 
    LEFT OUTER JOIN "tableB" AS b
    ON a."tableB_id" = b."id" 
WHERE a."name" ILIKE '%rich%'

UNION ALL

SELECT * 
FROM "tableA" AS a 
    INNER JOIN "tableB" AS b
    ON a."tableB_id" = b."id" 
WHERE b."name" ILIKE '%rich%'
  AND (a."name" IS NULL OR a."name" NOT ILIKE '%rich%')
 ;