CODEWITHSUNDEEP
postgresqlpostgresql-9.4
Nick Pfitzner
Nick Pfitzner

Reputation: 216

Postgres using functions inside queries

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.

Upvotes: 2

Views: 6199

Answers (4)

Nick Barnes
Nick Barnes

Reputation: 21356

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.

Upvotes: 5

Nick Pfitzner
Nick Pfitzner

Reputation: 216

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

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

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

Upvotes: 0

Eduardo
Eduardo

Reputation: 7851

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)

Upvotes: 0

Related Questions