Hey,
thanks, now we have good information:
the python package are really loaded once per connection, so no optimization is needed.
Unlike plperl or plR there is no easy way to preload packages.
There may be some solutions to make this import at connection start but it would involve C modification (found no trace of python file or hackable sql script in postgres source and install directory)
After that,
further optimization is possible by avoiding the useless 'import' (because it is already loaded) (see the trick
here)
,however benefits are not proven.
My use case is simple geometry manipulation functions. It is easier to use plpython rather than plpgsql thanks to numpy for vector manipulation. Usually the functions are called inside complex query with many CTE, and execute over 100k of rows. Total execution time is in the order of minutes. (exemple of querry at the end)
Thanks everybody,
Rémi
Example of querry
CREATE TABLE holding_result AS
WITH the_geom AS (
SELECT gid, geom
FROM my_big_table --200k rows
)
SELECT gid, my_python_function(geom) AS result
FROM the_geom;
