Hi 2017-01-02 15:34 GMT+01:00 Андрей Хозов <avkho...@gmail.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 >