Performance issue with castings args of the function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello there!

I have an performance issue with functions and args type.

Table and data:
create table t1 (id serial, str char(32));
insert into t1 (str) select md5(s::text) from generate_series(1, 1000000) as s;

And simple functions:
create function f1(line text) returns void as $$
begin
  perform * from t1 where str = line;
end;
$$ language plpgsql;

create function f2(line char) returns void as $$
begin
  perform * from t1 where str = line;
end;
$$ language plpgsql;

​Query:
test=> explain analyze select f2('2b00042f7481c7b056c4b410d28f33cf');
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=0) (actual time=189.008..189.010 rows=1 loops=1)
 Planning time: 0.039 ms
 Execution time: 189.039 ms
(3 rows)

Time: 189,524 ms
test=> explain analyze select f1('2b00042f7481c7b056c4b410d28f33cf');
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=0) (actual time=513.734..513.735 rows=1 loops=1)
 Planning time: 0.024 ms
 Execution time: 513.757 ms
(3 rows)

Time: 514,125 ms
test=> explain analyze select f1('2b00042f7481c7b056c4b410d28f33cf'::char(32));
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=0) (actual time=513.507..513.509 rows=1 loops=1)
 Planning time: 0.074 ms
 Execution time: 513.535 ms
(3 rows)

Time: 514,104 ms
test=> 
​Seems that casting param from text to char(32) needs to be done only once and​ f1 and f2 must be identical on performance. But function f2 with text param significantly slower, even with casting arg while pass it to function.

Tested postgresql versions 9.5.5 and 9.6.1 on Ubuntu 16.04. It's normal behavior or it's can be fixed?

--
​Andrey Khozov

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux