4

Basically I want to log those query which is taking more than 300ms to execute. I don't want to log those query which is less than 300ms. But at PostgreSQL showing all type of log whether it is below 300ms or above 300ms.

I configured below parameter:

  • log_directory = 'pg_log'
  • log_filename = 'postgresql-%a.log'
  • log_statement = 'all'
  • logging_collector = on
  • log_min_duration_statement = 300

Why PostgreSQL log all type of statement, though I set parameter log_min_duration_statement=300? It is confusing to me, Is there any better configuration to log only 300ms SQL statement?

3
  • 2
    "log_statement = 'all'" will log all statements. Turn that off if you don't want that to happen.
    – jjanes
    Jul 20, 2022 at 20:06
  • Yes I have tried with others but issue is that I want all statement which is above 300ms whatever it is? Then how I can do it? Jul 21, 2022 at 3:19
  • 1
    What specifically happened (or didn't happen) when you tried turning "log_statement = 'none'" while keeping you other settings? Provide concrete examples. Just saying that it is not what you want doesn't tell us anything.
    – jjanes
    Jul 21, 2022 at 17:07

1 Answer 1

2

log_statement being set to all is telling PostgreSQL to log the text of all statements. When used together with log_min_duration_statement, statement text is not repeated in logs, forcing manual correlations by process/session identifier for analysis. If you truly want log_statement set to all (you want the text of all statements to be logged), be sure to be logging the process and/or session identifier (see log_line_prefix and its %p and %c "escape sequences"). Otherwise, consider setting log_statement to none. Note that log_duration will also lead to logging for all statements.

On a loosely related note, consider checking out pg_stat_statements, a PostgreSQL-supplied module, function, and view that provides planning and execution statistics for executed queries. This module has proven itself very useful in the past. If interested, check out PostgreSQL: pg_stat_statements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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