calling plpy.execute in PL/Python much slower than calling Execute in PL/pgsql

Viewed 24

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

0 Answers
Related