0

I have a PostgreSQL RDS instance hosted in AWS. I have set the log_min_duration_statement setting to 1 second. When checking my log for slow queries I found the following six entries which don't contain any query/statement:

slow query log

Does anyone have any idea why this happened? How can I find out which slow queries caused these log entries?

UPDATE Thanks to jjanes for pointing me in the right direction. I found that the "log_statement" option causes duration and statement to be logged separately. Here it is in Postgre docs: https://www.postgresql.org/docs/9.1/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT

  • Is the statement text being hidden by your applied filter? – jjanes Mar 27 at 14:41
  • @jjanes no the filter is just looking for "duration:" text, it wouldn't block any text from appearing in individual events – blizz Mar 27 at 14:45
  • @jjanes i'll check but that's not how Postgres slow query logging works. It always includes the duration and statement on the same line. – blizz Mar 27 at 15:15
  • 1
    Not when log_statements='all'. Or when set above none, but less than all, for the cases where it does kick in. – jjanes Mar 27 at 15:16
  • 1
    With that setting, the text of the query is logged when the query begins, and the duration (only) is logged when it ends. You can use the PID or maybe the client IP and port number to pair those up in the log file. I don't know if RDS provides a specific way to do that, I've always done it in vim or less. But really, I just auto_explain now. I think that this is available in RDS although I've never used it there. – jjanes Mar 27 at 19:19
0

With log_statements='all', the text of the query is logged when the query begins, and the duration (only) is logged when it ends. You can use the PID or maybe the client IP and port number to pair those up to each other in the log file. I don't know if RDS provides a specific way to do that, I've always done it in 'vim' or 'less'.

But really, I just auto_explain now. I think that this is available in RDS although I've never used it there.

| improve this answer | |
0

You may have log_duration set to on.
If so, changingit to off should allow you to get statements with your log_min_duration_statement set to 1.

| improve this answer | |
  • No it's not on. These are the only log entries showing a duration. – blizz Mar 27 at 15:14
  • 1
    Unless RDS did some weird hacking here, setting log_duration = on does not suppress the output of log_min_duration_statement, rather the opposite. (But setting log_statements='all' does cause the statement to be logged on a different line than the duration, which might cause it to be overlooked) – jjanes Mar 27 at 15:15
  • 1
    @jjanes, what you wrote is totally true, depending on the way RDS is performing log parsing would have an effect on what is shown on the log output. My point was to disable the log_duration to remove duration only output and see what was going on. If both settings are turned on, there might be some double duration output that might confuse (maybe) the AWS parser. – JC Arnu Mar 27 at 15:39
  • Good point. Thanks for the clarification. – jjanes Mar 27 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.