3
\$\begingroup\$

I am using PostgreSQL 11.0 and I wrote a PL/pgSQL function to test if a word or phrase is contained within another phrase. This function is called several times within another function to extract some words from a paragraph. The execution of the calling function is slow, and I suspect it is because of this function:

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

COST 100
immutable 
AS $BODY$
declare 

v_res integer := 0;

BEGIN

p_text2:= replace(p_text2, '(','\(');
p_text2:= replace(p_text2, ')','\)');

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$;

Words (or phrase) boundaries could be spaces (' word '), dashes ('-word-'), parentheses ('(word)') or nothing (like when the word is at the beginning or the end of the phrase).

For example:

select public."Contains"('domain-specific ontology' , 'domain'); -- true
select public."Contains"('domain-specific ontology' , 'ontology'); -- true
select public."Contains"('domain-specific ontology' , 'domain specific'); -- true
select public."Contains"('domain-specific ontology' , 'domain-specific'); -- true
select public."Contains"('information retrieval (IR)' , 'IR'); -- true
select public."Contains"('information retrieval (IR)' , 'information retrieval IR'); -- false
select public."Contains"('domain-specific ontology' , 'domain ontology'); -- false

This function is called in the outer function this way:

perform 1
         from some_table
         where public."Contains"(col1, v_word) = true;

some_table has no index on col1.

So, is there a faster alternative to the SIMILAR TO operator to perform the same regular expression matching?

\$\endgroup\$
  • \$\begingroup\$ The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles. \$\endgroup\$ – Toby Speight Jun 6 at 14:49
  • \$\begingroup\$ can you suggest another title, please? \$\endgroup\$ – Nina Jun 6 at 16:27
  • 1
    \$\begingroup\$ Well, I have only a limited understanding of what you're doing, but perhaps just from the first sentence, "a PL/pgSQL function to test if a word or phrase is contained within another phrase" could be a possibility? (Any question with performance is obviously interested in faster alternatives, so it's redundant to mention that in the title, as well as being against site standards) \$\endgroup\$ – Toby Speight Jun 6 at 16:31

Your Answer

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

Browse other questions tagged or ask your own question.