This is fast (49ms):
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;
This is slow (15 seconds):
select cd_cliente into v_cd_cliente
from central.cliente where nr_cpf_cnpj = ext.uf_converte_numerico(new.nr_cpf);
Function:
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;
I am using PostgreSQL 12.
Why is the second variant slow?
language sql
function that is defined asimmutable
– a_horse_with_no_name Oct 22 at 16:47