Re: hexadecimal to decimal - Mailing list pgsql-general

Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>>I tried that after I posted, but only saw roughly 30% improvement (which
>>is consistent with my earlier tests IIRC). Not bad, but this still left
>>plperl initial call at ~40 msec versus plpgsql at ~4 msec.
>
> Hm.  And the first call to a plpgsql function does require opening a
> shared library.  Curious that libperl seems so much more heavyweight
> than plpgsql.
>

I found the problem (or arguably two). Hows this look 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=3.31..3.53 rows=3 loops=1)
  Total runtime: 3.69 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=2.38..2.39
rows=1 loops=1)
  Total runtime: 2.43 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.29..0.49 rows=3 loops=1)
  Total runtime: 0.54 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.15..0.15
rows=1 loops=1)
  Total runtime: 0.18 msec
(2 rows)


Now the first call to the perl function is quicker than plpgsql and 90+%
faster than without preloading :-)

The first problem is that the initialization function for plperl,
plperl_init_all() is declared static, hence it couldn't be loaded
externally at all. The second problem is that when I wrote
process_preload_libraries() I used this line to call the init function:

   initfunc = (func_ptr) load_external_function(filename, funcname,
                                                    false, NULL);

That false means that load_external_function() doesn't report errors if
the funcname cannot be found ;(

My reasoning at the time was that library preloading shouldn't prevent
the postmaster from starting, even if it is unsuccessful, but now I
wonder if that was a good idea.

What do you think:
1) should that call to load_external_function() use true for signalNotFound?

2) do you want a patch that exports plperl_init_all() (and I guess
similar init functions in pltcl and plpython)?

Joe