0

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

Adam Van Oijen
  • 435
  • 3
  • 8
  • 17
  • No idea; you'd have to profile. My stance is: if you need some glue between SQL statements, use PL/pgSQL. If you want to do extensive non-SQL processing or access something outside the database, use a different procedural language. If it has to be as fast as possible ot touch PostgreSQL internals, use C. – Laurenz Albe Sep 09 '22 at 05:32

0 Answers0