0

I have the following PostgreSQL function which checks if p_text1 contains the word/phrase p_text2 within it:

CREATE OR REPLACE FUNCTION public."Contains"(
p_text1 character varying,
p_text2 character varying)
RETURNS boolean
LANGUAGE 'plpgsql'

COST 100
IMMUTABLE 
AS $BODY$

BEGIN

 perform 1 
 where   ( p_text1 similar to '((% )|(%-)|(%\())?'||p_text2||'(( %)|(-%)|(\)%))?' ) or 
    ( replace(p_text1,'-',' ') similar to '((% )|(%\())?'||replace(p_text2,'-',' ')||'(( %)|(\)%))?' ) or 
    ( replace(p_text1,'-','') similar to '((% )|(%\())?'||replace(p_text2,'-','')||'(( %)|(\)%))?' );      

 return found;

END; 
$BODY$;

p_text2 is considered a word/phrase if it is preceded/followed by a dash, space, parenthesis or nothing.

Examples:

select public."Contains"('data mining' , 'mining') --> true

select public."Contains"('information retrieval (ir) system' , 'ir') --> true

select public."Contains"('semantic (information retrieval)' , 'semantic information') --> false

select public."Contains"('ontology-based queries' , 'ontology') --> true

select public."Contains"('ontology-based queries' , 'ontology based') --> true

The function will be called like this:

select * from my_table
where public."Contains"( text_column , some_text_variable) = true;

Given that my_table contains about 15,000 rows.

I read many advises to avoid using SIMILAR TO and to replace it with simple LIKE for performance issues... I just don't know how to rewrite such a query with LIKE, and I don't know if that would yield better performance. Any help is appreciated. Thanks in advance

2
  • Can you provide your input and output data? – Luan Huynh Mar 10 '20 at 9:11
  • @LuanHuynh: I have edited my question – Nina Mar 10 '20 at 17:59
0

LIKE is much less flexible than SIMILAR TO. In general there is going to be no formulaic way to downgrade from SIMILAR TO to LIKE. Based on your example, it might take like 34 (4*4 + 3*3 + 3*3) different ORed together LIKE to accomplish this. For example, your first expression in the OR branch gets to pair 3 alternatives plus the empty possibility (so 4 total) at the beginning with another 4 at the end, so that would expand to 16.

I read many advises to avoid using SIMILAR TO and to replace it with simple LIKE for performance issues

This advice would only apply if there is a simple like which does the same job. But in general, tuning should be based on evidence, not on rumors. So to start with, do you actually have a performance problem?

2
  • The operation is going to be done "offline", so It will not affect online users even with slow performance. If the alternative to my current formula is that complicated, I think I will stick to the current formula with SIMILAR TO. – Nina Mar 10 '20 at 20:43
  • Can you add to the answer an example about the first SIMILAR TO expression after replacing it with LIKE? just to make things clear – Nina Mar 10 '20 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.