postgresql - Postgres 在查询中使用函数

Question

我有一个表格，其中包含要与品牌匹配的常用词值 - 所以当有人输入“可乐”时，我想匹配与其关联的任何可能的品牌名称以及原始术语。

CREATE TABLE word_association ( commonterm TEXT, assocterm TEXT);

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

我有一个函数可以在用于模式匹配的管道分隔符字符串中创建这些值的列表:

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;

如果你调用 SELECT * FROM usp_get_search_terms('coke') 你最终会得到

coke|coca-cola|cocacola|coca cola

编辑:此函数运行 <100 毫秒，因此工作正常。

我想使用插入的文本运行查询，例如

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

这需要大约 56 秒来运行我的 ~500K 记录表。

如果我得到原始文本并在查询中使用它，则需要大约 300 毫秒，例如

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

结果集完全相同。

我尝试将函数的输出字符串修改为例如将其括在引号和括号中，但似乎没有什么不同。

有人能告诉我为什么这里有区别吗？是数据类型还是有关在查询中调用函数的内容？谢谢。

Answer 1

您的函数可能需要 100 毫秒，但它不会调用您的函数一次；它调用了 500,000 次。

这是因为你的函数被声明为VOLATILE .这告诉 Postgres 该函数在查询中多次调用时返回不同的值(如 clock_timestamp() 或 random())，或者它改变了数据库以某种方式(例如，通过插入记录)。

如果您的函数只包含 SELECT，没有 INSERT，调用其他 VOLATILE 函数，或其他副作用，那么您可以声明它为 STABLE。这告诉规划器它可以只调用一次函数并重用结果而不影响查询的结果。

但由于 SET DATESTYLE 语句，您的函数确实有副作用，该语句在 session 的其余部分生效。然而，我怀疑这是故意的。您可以删除它，因为它看起来不像日期格式与其中的任何内容相关。但如果有必要，正确的做法是使用 SET clause of the CREATE FUNCTION statement仅在函数调用期间更改它:

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

查询的慢版本的另一个问题是对 LOWER(X.online_description) 的调用，这将阻止查询利用索引(因为 online_description已编入索引，但 LOWER(online_description) 未编入索引。

有了这些变化，两个查询的性能是一样的；看这个SQLFiddle .