在 WHERE 子句中使用函式時查詢慢

這很快（49 毫秒）：

v_cpf_numerico := ext.uf_converte_numerico(new.nr_cpf);

select cd_cliente into v_cd_cliente
from central.cliente where nr_cpf_cnpj = v_cpf_numerico;

這很慢（15 秒）：

select cd_cliente into v_cd_cliente
from central.cliente where nr_cpf_cnpj = ext.uf_converte_numerico(new.nr_cpf);

功能：

create or replace function ext.uf_converte_numerico(_input varchar(30)) returns bigint
as
$$
begin
   _input := regexp_replace(_input, '[^0-9]+', '', 'g');

   if _input = '' then
       return null;
   end if;

   return cast(_input as bigint);
end
$$ language plpgsql;

我使用的是 PostgreSQL 12。

為什麼第二個變種很慢？

考慮這個簡化的等價物：

CREATE OR REPLACE FUNCTION ext.uf_converte_numerico(_input varchar(30))
 RETURNS bigint LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT NULLIF(regexp_replace(_input, '[^0-9]+', '', 'g'), '')::bigint;
$func$;

• IMMUTABLE，因為它是，並且出於Laurenz 解釋的原因。
• PARALLEL SAFE在 Postgres 10 或更高版本中，因為它是. 如果沒有標籤，函式預設為PARALLEL RESTRICTED，這會禁用並行查詢。這可能會或可能不會影響顯示的查詢。但是你報告的 15 秒錶明你在大桌子上操作。所以它可以對其他查詢產生巨大的影響。
• LANGUAGE SQL啟用函式內聯，這對於顯示的查詢（在標記後IMMUTABLE）並不重要，但會簡化查詢計劃並提高整體性能。
• NULLIF作為小的簡化。

另外：您的輸入是varchar(30)，它仍然允許超出範圍的錯誤bigint。要麼考慮varchar(18)確定。或者只是讓它text消除無效的限制。

引用自：https://dba.stackexchange.com/questions/251709