Postgres not using indexes when ORing across join

Question

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?

Answer 1

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%')
 ;

Answer 2

It would be quite complicated to implement that type of optimization, and no one has done so yet. You couldn't do either just a nested loop from A to B, nor just one from B to A. You would have to have a nested loop in both directions with some mechanism to eliminate duplicates rows in the cases where both branches of the or were true. That mechanism would need to allow for arbitrarily large sets in need of this deduplication.