Indexes to support OR condition over a JOIN-CodePudding

I'm wondering if Postgres has any support optimizing for following fundamental problem.

I want to do a search a agains two columns on different tables joined via a foreign key. I have created an index for each column. If I do my join query and have a where condition for either one or the other column, the respective index is used to filter the result and the query performance is great. If use two where clause combined by an OR for one field on each table, the query gets very slow and no indexes are used. Presumably this is because the optimizer sees no other way than doing a full table join and scan to resolve. The query looks something like this:

select table1.id
from table1
left join table2 on table1.fk = table2.id
where table1.haystack ilike '%needle%' or table2.haystack ilike '%needle%'

The operation (ilike) isn't the issue and interchangeable, I have a working Trigram index setup. I just want to find out if there is any other way to make this type of query performant beside denormalizing all searched fields into one table.

I would be very greateful for any ideas.

CodePudding user response：

No, there is no special support in the database to optimize this. Do it yourself:

SELECT table1.id
FROM table1
   JOIN table2 ON table1.fk = table2.id
WHERE table1.haystack ILIKE '%needle%'
UNION
SELECT table1.id
FROM table1
   JOIN table2 ON table1.fk = table2.id
WHERE table2.haystack ILIKE '%needle%'

Provided both conditions are selective and indexed with a trigram index, and you have indexes on the join condition, that will be faster.