2

I have a table with common word values to match against brands - so when someone types in "coke" I want to match any possible brand names associated with it as well as the original term.

CREATE TABLE word_association ( commonterm TEXT, assocterm TEXT);

INSERT INTO word_association ('coke', 'coca-cola'), ('coke', 'cocacola'), ('coke', 'coca-cola');

I have a function to create a list of these values in a pipe-delim string for pattern matching:

CREATE OR REPLACE FUNCTION usp_get_search_terms(userterm text)
  RETURNS text AS
$BODY$DECLARE
    returnstr TEXT DEFAULT '';

BEGIN
    SET DATESTYLE TO DMY;

    returnstr := userterm;

    IF EXISTS (SELECT 1 FROM word_association WHERE LOWER(commonterm) = LOWER(userterm)) THEN
        SELECT  returnstr || '|' || string_agg(assocterm, '|') INTO returnstr
        FROM    word_association
        WHERE   commonterm = userterm;

    END IF;

    RETURN returnstr;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION usp_get_search_terms(text)
  OWNER TO customer_role;

If you call SELECT * FROM usp_get_search_terms('coke') you end up with 

coke|coca-cola|cocacola|coca cola

EDIT: this function runs <100ms so it works fine.

I want to run a query with this text inserted e.g.

SELECT X.article_number, X.online_description
FROM articles X
WHERE LOWER(X.online_description) % usp_get_search_terms ('coke');

This takes approx 56s to run against my table of ~500K records.

If I get the raw text and use it in the query it takes ~300ms e.g.

SELECT X.article_number, X.online_description
FROM articles X
WHERE X.online_description % '(coke|coca-cola|cocacola|coca cola)';

The result sets are identical.

I've tried modifying what the output string from the function to e.g. enclose it in quotes and parentheses but it doesn't seem to make a difference.

Can someone please advise why there is a difference here? Is it the data type or something about calling functions inside queries? Thanks.

Improve this question

4 Answers 4

Reset to default
5

Your function might take 100ms, but it's not calling your function once; it's calling it 500,000 times.

It's because your function is declared VOLATILE. This tells Postgres that either the function returns different values when called multiple times within a query (like clock_timestamp() or random()), or that it alters the state of the database in some way (for example, by inserting records).

If your function contains only SELECTs, with no INSERTs, calls to other VOLATILE functions, or other side-effects, then you can declare it STABLE instead. This tells the planner that it can call the function just once and reuse the result without affecting the outcome of the query.

But your function does have side-effects, due to the SET DATESTYLE statement, which takes effect for the rest of the session. I doubt this was the intention, however. You may be able to remove it, as it doesn't look like date formatting is relevant to anything in there. But if it is necessary, the correct approach is to use the SET clause of the CREATE FUNCTION statement to change it only for the duration of the function call:

...
$BODY$
  LANGUAGE plpgsql STABLE
  SET DATESTYLE TO DMY
  COST 100;

The other issue with the slow version of the query is the call to LOWER(X.online_description), which will prevent the query from utilising the index (since online_description is indexed, but LOWER(online_description) is not).

With these changes, the performance of both queries is the same; see this SQLFiddle.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

That explanation makes sense - I have changed it (dropped the SET DATESTYLE altogether as I don't need it, just out of habit) and it improved the query, but only down to around 48s. So it must be a factor of a function call repeated thousands of times as you say. I'll have to alter the NodeJS that calls it to use a static string. Thankyou for the help though - useful
P.S. I tried even changing it to IMMUTABLE but that cut it to 7 seconds. Obviously not highly useful in this scenario.
Ok, a few more questions then... Is there an index on online_description? I don't think the % operator is built-in, what is it? Only one of your example queries calls LOWER(), is that intentional? What is the EXPLAIN output for the slow and fast versions?
Yes the online_description field has GIN (online_description COLLATE pg_catalog."default" gin_trgm_ops). The EXPLAIN versions were radically different - lot of rubbish going on with the function call and the raw one was quite simple.
% operator = not sure. Was there when I got here so I'm going to research that. Its "modulus" in most languages. Effect on text I'll have to find out.
|
1

So the answer came to me about dawn this morning - CTEs to the rescue!

Particularly as this is the "simple" version of a very large query, it helps to get this defined once in isolation, then do the matching against it. The alternative (given I'm calling this from a NodeJS platform) is to have one request retrieve the string of terms, then make another request to pass the string back. Not elegant.

WITH matches AS
    (   SELECT * FROM usp_get_search_terms('coke')  )
, main AS 
    (   SELECT X.article_number, X.online_description
        FROM articles X
        JOIN matches M ON X.online_description % M.usp_get_search_terms )
SELECT * FROM main

Execution time is somewhere around 300-500ms depending on term searched and articles returned.

Thanks for all your input guys - I've learned a few things about PostGres that my MS-SQL background didn't necessarily prepare me for :)

Improve this answer

Comments

0

Have you tried removing the IF EXISTS() and simply using:

SELECT  returnstr || '|' || string_agg(assocterm, '|') INTO returnstr
FROM    word_association
WHERE   LOWER(commonterm) = LOWER(userterm)
Improve this answer

1 Comment

I need the original search term in the string. The function doesn't appear to be an issue - it executes in under 100ms to produce the output. Executing it writing the query is what I'm not understanding.
0

In instead of calling the function for each row call it once:

select x.article_number, x.online_description
from
    woolworths.articles x
    cross join
    woolworths.usp_get_search_terms ('coke') c (s)
where lower(x.online_description) % s
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.