Inner join on regexes

Question

I have an inner join on regular expressions - it is very slow. Is there any easy way to speed this up? I am using postgres.

FROM A
inner join B ON trim(lower(replace(replace(replace(B.enginequery,',',' '),'"',' '),'+',' '))) = trim(lower(A.keyphrase))
             OR trim(lower(replace(replace(replace(B.enginequery,',',' '),'"',' '),'+',' '))) ~ (trim(lower(A.keyphrase)) || '$')
             OR trim(lower(replace(replace(replace(B.enginequery,',',' '),'"',' '),'+',' '))) ~ (trim(lower(A.keyphrase)) || ' ')

Answer 1

Is there any easy way to speed this up?

The reason performance suffers is all the operations, let alone the regex, that have to be performed just to make a match. You need to simplify the relationship so these don't need to be performed.

Answer 2

I would start by placing the results of :

 trim(lower(replace(replace(replace(B.enginequery,',',' '),'"',' '),'+',' ')))

into a column in your table. At least then one wouldn't have to repeatedly calculate it. How you implement that in postgres I don't know. In Ms sql server, I would try a calculated column so that my apps wouldn't have to know about updating B.enginequery and its cleaned version.

And then, I would probably end up attempting an index on that cleaned up column.