Joe Conway <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
