CODEWITHSUNDEEP
sqlperformancepostgresqlstring-formattingplpgsql
Lin Du
Lin Du

Reputation: 43

Performance when calling a function in SELECT statement

I have a query where I need to call a SQL function to format a particular column in the query. The formatting needed is very similar to formatting a phone number, ie. changing 1234567890 into (123)456-7890.

I've read that calling a function from a select statement could be a performance killer, and it was kind of reflected in my situation, the time the query took more than tripled and I did not think the function would take this much longer. The function runs in linear time but does use SQL loops. To give an idea of the size of the database this particular query returns about 220,000 rows. The run time of the query went from < 3s to > 9s when running without calling the function vs. running calling the function. The column that needs formatting isn't indexed or used in a join condition or where clause.

Is the performance drop here expected or is there something I can do to improve it?

This is the function in question:

CREATE OR REPLACE FUNCTION fn(bigint)
  RETURNS character varying LANGUAGE plpgsql AS
$BODY$
DECLARE
   v_chars varchar[];
   v_ret   varchar;
   v_length int4;
   v_count  int4;
BEGIN
   if ($1 isnull or $1 = 0) then
      return null;
   end if;

   v_chars  := regexp_split_to_array($1::varchar,'');
   v_ret    := '';
   v_length := array_upper (v_chars,1);
   v_count  := 0;

   for v_index in 1..11   loop
      v_count := v_count + 1;

      if (v_index <= v_length) then
         v_ret := v_chars[v_length - (v_index - 1)] || v_ret;
      else
         v_ret := '0' || v_ret;
      end if;

      if (v_count <= 6 and (v_count % 2) = 0) then
         v_ret := '.' || v_ret;
      end if;
   end loop;

   return v_ret;
END
$BODY$

Upvotes: 3

Views: 5226

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658947

It depends on the specifics of the function. To find out how much a bare function call will cost, create dummy functions like:

CREATE FUNCTION f_bare_plpgsql(text)
  RETURNS text LANGUAGE plpgsql IMMUTABLE AS
$BODY$
BEGIN
   RETURN $1;
END
$BODY$;

CREATE FUNCTION f_bare_sql(text)
  RETURNS text LANGUAGE sql IMMUTABLE AS
$BODY$
   SELECT $1;
$BODY$;

And try your query again.
If then you wonder why your function is slow, add it to your question.

Solution for updated question

Your function could be improved in many places, but there is a more radical solution:

SELECT to_char(12345678901, '00000"."00"."00"."00')

Many times faster, obviously. More about to_char() in the manual.
Consider the following demo:

WITH x(n) AS (
   VALUES  (1::bigint), (12), (123), (1234), (12345), (123456), (1234567)
          ,(12345678), (123456789), (1234567890), (12345678901), (123456789012)
   )
SELECT n, x.fn(n), to_char(n, '00000"."00"."00"."00')
FROM x

      n       |       fn       |     to_char
--------------+----------------+-----------------
            1 | 00000.00.00.01 |  00000.00.00.01
           12 | 00000.00.00.12 |  00000.00.00.12
          123 | 00000.00.01.23 |  00000.00.01.23
         1234 | 00000.00.12.34 |  00000.00.12.34
        12345 | 00000.01.23.45 |  00000.01.23.45
       123456 | 00000.12.34.56 |  00000.12.34.56
      1234567 | 00001.23.45.67 |  00001.23.45.67
     12345678 | 00012.34.56.78 |  00012.34.56.78
    123456789 | 00123.45.67.89 |  00123.45.67.89
   1234567890 | 01234.56.78.90 |  01234.56.78.90
  12345678901 | 12345.67.89.01 |  12345.67.89.01
 123456789012 | 23456.78.90.12 |  #####.##.##.##

to_char() is only prepared for up to 11 decimal digits, as you can see.
Can easily be extended, if need should be.

Upvotes: 1

Tony
Tony

Reputation: 10357

If you really must perform the formatting in the database then modify your table to include a field to store the formatted number.

A trigger can call your function to generate the formatted number when the value changes, then you only (slightly) increase the time taken to INSERT or UPDATE a few rows at a time, rather than all of them.

Your query returning all 220k rows then becomes a simple SELECT of the formatted value and should be nice and quick.

Upvotes: 1

Related Questions