Query slow when using function in the WHERE clause

Consider this simplified equivalent:

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, because it is, and for the reasons Laurenz explained.

• PARALLEL SAFE in Postgres 10 or later, because it is. Without the label, functions default to PARALLEL RESTRICTED, which disables parallel queries. This may or may not affect the query on display. But the 15 seconds you reported indicate you are operating on big tables. So it can make a huge difference in other queries.

• LANGUAGE SQL to enable function inlining, which won't matter much for the query on display (after you labelled it IMMUTABLE), but will simplify query plans and improve overall performance.

• NULLIF as minor simplification.

Aside: your input is varchar(30), which still allows out of range errors for bigint. Either consider varchar(18) to be sure. Or just make it text to remove the ineffective restriction.

Since the function is VOLATILE (by default), PostgreSQL doesn't know that it will return the same value for every row in central.cliente, so it is evaluated repeatedly.

Set the volatility to IMMUTABLE and PostgreSQL knows that it has to be evaluated only once:

ALTER FUNCTION ext.uf_converte_numerico(varchar(30)) IMMUTABLE;