CODEWITHSUNDEEP
sqlregexpostgresqljoin
alex
alex

Reputation: 227

Inner join on regexes

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

Upvotes: 0

Views: 1090

Answers (2)

Jeff Maass
Jeff Maass

Reputation: 3742

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.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 3

Related Questions