0

Already asked the question here but it was falsely marked as closed.

I use psqlODBC and PowerQuery to load data from a PostgreSQL (12.4) to Microsoft Excel. Recently I am having performance issues I cannot explain. Some of the functions take extremely long (15 minutes and longer) to pull the data via ODBC. Some of the functions take only seconds. When running the same functions using pgAdmin, Beekeeper or psql from command line none of them takes more than 500ms for execution.

Is there anything I can do to improve the performance of psqlODBC?

I found this answer stating that it might be possible that the aggregations are done client side. That would be really bad as the tables contain several million rows and I would only want to send the aggregated columns back. Can someone give more information to that? I wasn't able to find anything about that.

What I've tried so far

  1. I replaced a function by a view, which seems to solve the problem. However, this is not a general solution as there are some functions that contain logic that cannot be done in a view.
  2. I used pg_stat_statements and auto_explain to find out if there is a problem in the functions. I did not find anything suspicious. As mentioned before, the problem only occurs when using psqlODBC, so I expect the problem to be anywhere there.
  3. Modifying the psqlODBC config's parameters. No success so far.

Details

The functions in postgreSQL are usually defined like this:

CREATE FUNCTION func(parameter integer) RETURNS TABLE(<col1> <type1>, ...) 
    LANGUAGE 'plpgsql' COST 100 STABLE PARALLEL UNSAFE ROWS 500
AS $BODY$
BEGIN
    RETURN QUERY
        <SELECT ...>
END

I use functions instead of views because some of them contain logic that cannot be done in views.

The server is running PostgreSQL 12.4, client uses psqlODBC 12.02, Excel 2016 (latest).

I am thankful for every piece of advice here how I could solve my problem.

Thank you.

Andi
  • 124
  • 2
  • 12
  • The question won't become better by repeating it. You will have to analyze where the time is spent. We have no crystal ball to see that. – Laurenz Albe Feb 26 '21 at 07:47
  • Can you give me a hint on how to do that? I used `pg_stat_statements` and `auto_explain` but I couldn't find out any reason why the execution of the function takes so much longer when ODBC is used. Everything looks perfect when the query is executed from pgAdmin. – Andi Feb 26 '21 at 07:54
  • 1
    The `EXPLAIN (ANALYZE, BUFFERS)` output from the SQL statements in the function that you captured using `auto_explain` is the same as when you run the queries manually? I don't think so. Find the SQL statements with the problem and ask a question that contains the `EXPLAIN (ANALYZE, BUFFERS)` output for the fast and the slow execution. – Laurenz Albe Feb 26 '21 at 09:00

0 Answers0