LEFT JOIN ON string match Very slow

I have to perform this LEFT JOIN but it’s very slow (30.000ms) because of the match on text string. How can I make it faster?

SELECT
    *
FROM table_a
LEFT JOIN table_b ON (
    table_b.state    = 'enabled'         AND
    table_b.group_id =  5  AND 
    ( 
    (                      
        LOWER(table_b.text) = LOWER(table_a.query)
    ) 
        OR
    (
        table_b.match_type = 'phrase' AND 
        table_a.query ILIKE '%'  || table_b.text || '%'
    )   
    )
)

Answer

Your best bet is probably to break this into two queries, one that does a left join with LOWER(table_b.text) = LOWER(table_a.query), supported by index on table_b (lower(text),group_id,state). And one that does a inner join with table_b.match_type = 'phrase' AND table_a.query ILIKE '%' || table_b.text || '%', supported by an index on table_a using gin (query gin_trgm_ops). And then have the results from the 2nd query overwrite the results from the first one for the relevant rows.

This could be implemented either on the client side, or by combining the queries with UNION ALL and then wrapping that in a DISTINCT ON to keep the correct rows. With the info you provided, we can’t tell you how to implement the “correct rows”. Does table_a have a primary or unique key?