I have written the same function in PL/PgSQL and PL/Python:
create function pythontest2()
returns TABLE(id bigint, timestamp_ timestamp without time zone, acknowledgedtimestamp timestamp without time zone, inactivetimestamp timestamp without time zone)
language plpython3u
as
$$
tablename = "dimension_alarms"
columns = ["id", "timestamp as TimeStamp_", "acknowledgedtimestamp", "inactivetimestamp"]
query_string = "select {0} from {1}".format(", ".join(columns), tablename)
return plpy.execute(query_string)
$$;
create function pythontest3() returns text
language plpython3u
as
$$
tablename = "dimension_alarms"
columns = ["id", "timestamp as TimeStamp_", "acknowledgedtimestamp", "inactivetimestamp"]
return "select {0} from {1}".format(", ".join(columns), tablename)
$$;
create function pythontest3execute()
returns TABLE(id bigint, timestamp_ timestamp without time zone, acknowledgedtimestamp timestamp without time zone, inactivetimestamp timestamp without time zone)
language plpgsql
as
$$
BEGIN
RETURN QUERY EXECUTE pythonTest3();
RETURN;
END
$$;
When I call select pythontest3execute();
and select pythonTest2();
, the first statement executes in half the time of the second.
The first function calls "Execute" from a PL/PqSQL function, the second function calls plpy.execute(...). Both functions use the python interpreter to execute, so why is plpy.execute() in particular so slow?
Edit: The average time it took to execute pythonTest2() was around 200ms, and the average time it took pythonTest3Execute() was about 80ms. The query returns around 150,000 results