pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance issue with castings args of the function
4+ messages / 3 participants
[nested] [flat]
* Performance issue with castings args of the function
@ 2017-01-02 14:34 Андрей Хозов <avkhozov@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Андрей Хозов @ 2017-01-02 14:34 UTC (permalink / raw)
To: pgsql-performance
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('2b00042f7481c7b056c4b410d28f33
cf'::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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance issue with castings args of the function
@ 2017-01-02 15:17 Pavel Stehule <pavel.stehule@gmail.com>
parent: Андрей Хозов <avkhozov@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2017-01-02 15:17 UTC (permalink / raw)
To: Андрей Хозов <avkhozov@gmail.com>; +Cc: pgsql-performance
Hi
2017-01-02 15:34 GMT+01:00 Андрей Хозов <avkhozov@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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance issue with castings args of the function
@ 2017-01-02 16:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Андрей Хозов <avkhozov@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2017-01-02 16:36 UTC (permalink / raw)
To: Андрей Хозов <avkhozov@gmail.com>; +Cc: pgsql-performance
=?UTF-8?B?0JDQvdC00YDQtdC5INCl0L7Qt9C+0LI=?= <avkhozov@gmail.com> writes:
> create table t1 (id serial, str char(32));
> create function f1(line text) returns void as $$
> begin
> perform * from t1 where str = line;
> end;
> $$ language plpgsql;
This query is specifying a text comparison (text = text operator).
Since the table column isn't text, a char-to-text conversion must
happen at each line.
> create function f2(line char) returns void as $$
> begin
> perform * from t1 where str = line;
> end;
> $$ language plpgsql;
This query is specifying a char(n) comparison (char = char operator).
No type conversion step needed, so it's faster.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance issue with castings args of the function
@ 2017-01-02 16:52 Андрей Хозов <avkhozov@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Андрей Хозов @ 2017-01-02 16:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Thanks all for explain!
On Mon, Jan 2, 2017 at 9:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?UTF-8?B?0JDQvdC00YDQtdC5INCl0L7Qt9C+0LI=?= <avkhozov@gmail.com> writes:
> > create table t1 (id serial, str char(32));
>
> > create function f1(line text) returns void as $$
> > begin
> > perform * from t1 where str = line;
> > end;
> > $$ language plpgsql;
>
> This query is specifying a text comparison (text = text operator).
> Since the table column isn't text, a char-to-text conversion must
> happen at each line.
>
> > create function f2(line char) returns void as $$
> > begin
> > perform * from t1 where str = line;
> > end;
> > $$ language plpgsql;
>
> This query is specifying a char(n) comparison (char = char operator).
> No type conversion step needed, so it's faster.
>
> regards, tom lane
>
--
Andrey Khozov
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-01-02 16:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-01-02 14:34 Performance issue with castings args of the function Андрей Хозов <avkhozov@gmail.com>
2017-01-02 15:17 ` Pavel Stehule <pavel.stehule@gmail.com>
2017-01-02 16:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-01-02 16:52 ` Андрей Хозов <avkhozov@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox