0

I have an application executing a simple SQL command in PostgreSQL. When the application executes the request, the postgres log files show a duration time of 44 seconds (as does the application). However, if I use the PSQL CLI to execute that very same command, the query is executed in less than a second.

Are there any common reasons as to why this might be the case? Thanks.

Jordan
  • 904
  • 2
  • 12
  • 32
  • You should add more information, programming language, db driver, connection string, ... – McNets Mar 06 '17 at 14:00
  • Obvious but necessary question: Are those connected to the same postgresql server? – Jorge Campos Mar 06 '17 at 14:00
  • @McNets I don't manage the application and so don't have the connection string or code used. I'm relying on the postgresql logs. – Jordan Mar 06 '17 at 14:02
  • @JorgeCampos - Yep – Jordan Mar 06 '17 at 14:02
  • 1
    Well a wild guess here is that the application is doing some sort of transaction control that MAYBE is causing the delay. Just a guess. – Jorge Campos Mar 06 '17 at 14:04
  • @JorgeCampos - My fear also. The SQL statement itself is exactly the same. I suspect the application may be trying to be clever. Is there any way to log out the transactions in PostgreSQL? – Jordan Mar 06 '17 at 14:05
  • 1
    Also, it is well known that logging all sql statements is a performance killer (I don't think that is the problem, though) I would suggest you to use the `explain analyze` feature to see if there are differences between the execution in the application and in the client. – Jorge Campos Mar 06 '17 at 14:13
  • @JorgeCampos - You're right. I've enabled logging only temporarily for diagnostic purposes. I've executed EXPLAIN ANALYSE on the command line execution, i'll try tricking the application into doing the same and see what comes back! Thanks. – Jordan Mar 06 '17 at 14:20
  • 2
    The execution time on the server should not depend on the client, unless different configuration parameters are used. Set `log_min_duration_statement` to 0 and see if both queries logged are identical. Check if the parameters are different (run `SHOW ALL` on both). You can get `EXPLAIN` output in the log with [auto_explain](https://www.postgresql.org/docs/current/static/auto-explain.html) and setting `auto_explain.log_analyze` to `on` and `auto_explain.log_min_duration` to 0. – Laurenz Albe Mar 06 '17 at 14:23

0 Answers0