Re: Performance issue with castings args of the function

Hi

2017-01-02 15:34 GMT+01:00 Андрей Хозов <avkhozov(at)gmail(dot)com>:

> 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('2b00042f7481c7b056c4b410d2
> 8f33cf'::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)
>

This explain shows nothing - you need to use nested explain

look on auto-explain
https://www.postgresql.org/docs/current/static/auto-explain.html

Maybe index was not used due different types.

Regards

Pavel

> 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
>