Re: hexadecimal to decimal
От | Joe Conway |
---|---|
Тема | Re: hexadecimal to decimal |
Дата | |
Msg-id | 3F285CA7.2090403@joeconway.com обсуждение исходный текст |
Ответ на | Re: hexadecimal to decimal (Ron Johnson <ron.l.johnson@cox.net>) |
Ответы |
Re: hexadecimal to decimal
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
Ron Johnson wrote: > On Wed, 2003-07-30 at 16:49, Joe Conway wrote: >>I'm sure you could do this with plperl or one of the other PLs as well. > > They will probably be better optimized at it, also. > Your reply made me curious, so I tried a simple test: create or replace function hex_to_int_perl(char(2)) returns integer as ' return hex $_[0]; ' language 'plperl'; create or replace function hex_to_int(char(2)) returns integer as ' declare v_ret record; begin for v_ret in execute ''select x'''''' || $1 || ''''''::int as f'' loop return v_ret.f; end loop; end; ' language 'plpgsql'; create table foo(f1 char(2)); insert into foo values ('ff'); insert into foo values ('fe'); insert into foo values ('fd'); explain analyze select hex_to_int(f1) from foo; explain analyze select hex_to_int_perl('ff'); explain analyze select hex_to_int(f1) from foo; explain analyze select hex_to_int_perl('ff'); The results were enlightening. Starting from a fresh psql session: regression=# explain analyze select hex_to_int(f1) from foo; QUERY PLAN ----------------------------------------------------------------------------------------------- Seq Scan on foo (cost=0.00..22.50 rows=1000 width=6) (actual time=4.00..4.40 rows=3 loops=1) Total runtime: 4.66 msec (2 rows) regression=# explain analyze select hex_to_int_perl('ff'); QUERY PLAN ------------------------------------------------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (actual time=54.55..54.55 rows=1 loops=1) Total runtime: 54.63 msec (2 rows) regression=# explain analyze select hex_to_int(f1) from foo; QUERY PLAN ----------------------------------------------------------------------------------------------- Seq Scan on foo (cost=0.00..22.50 rows=1000 width=6) (actual time=0.51..0.86 rows=3 loops=1) Total runtime: 0.95 msec (2 rows) regression=# explain analyze select hex_to_int_perl('ff'); QUERY PLAN ---------------------------------------------------------------------------------- Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.22..0.23 rows=1 loops=1) Total runtime: 0.27 msec (2 rows) So based on this simple test, I'd say that if you only run the function once per connect, use plpgsql, but if you run the function many times during one backend session, use perl. Joe
В списке pgsql-general по дате отправления: