Re: Function call very slow from JDBC/java but super fast from DBear

On Tuesday, August 8, 2023, An, Hongguo (CORP) <Hongguo(dot)An(at)adp(dot)com> wrote:

> Hi:
>
> I have a function, if I call it from DBeaver, it returns within a minute.
>
> *call* commonhp.run_unified_profile_load_script_work_assignment_details(
> 'BACDHP', 'G3XPM6YE2JHMSQA2');
>
> but if I called it from spring jdbc template, it never comes back:
>

If you are passing in exactly the same text values and running this within
the same database then executing the call command via any method should
result in the same exact outcome in terms of execution. You can get shared
buffer cache variations but that should be it. I’d want to exhaust the
possibility that those preconditions are not met before investigating this
as some sort of bug. At least, a PostgreSQL bug. I suppose the relative
novelty of using call within the JBC driver leaves open the possibility of
an issue there. Removing the driver by using PREPARE may help to move
things forward (as part of a well written bug report). In any case this is
not looking to be on-topic for the performance list. Some more research
and proving out should be done then send it to either the jdbc list or
maybe -bugs, if not then -general.

You may want to install the auto-explain extension to get the inner query
explain plan(s) into the logs for examination.

David J.