0

Is there a way to log queries which use sequential scans in PostgreSQL? Or just log everything and then later grep for seqscans?

3

I don't know a way to log sequential scans only. Just a small remark: seqscans are not evil, you will get them (especially for smaller tables) all the time.

On the other hand, you can definitely log everything, if you want, and there are multiple ways.

The easiest is probably

[ALTER DATABASE yourdb ] SET log_statement TO 'all';

This will log every query that passes the parser:

Note: Statements that contain simple syntax errors are not logged even by the log_statement = all setting, because the log message is emitted only after basic parsing has been done to determine the statement type. In the case of extended query protocol, this setting likewise does not log statements that fail before the Execute phase (i.e., during parse analysis or planning). Set log_min_error_statement to ERROR (or lower) to log such statements.

Another insteresting way of achieving it is setting log_min_duration_statement to a (very) low value:

[ALTER DATABASE yourdb ] SET log_min_duration_statement TO 0; -- or somewhat higher

The difference between the two is that the latter will also register the actual parameters passed to functions.

Notes:

  • you need superuser rights for changing either of these settings
  • you can use both at the same time. For details, please read the documentation linked above.
1

The answer appears to be to log everything (or at least long-running queries) with the auto_explain module (https://www.postgresql.org/docs/current/static/auto-explain.html) turned on, then parse the results.

  • That's also a solution, yes. In my experience, however, it produces so much output that the normal usage of the logs is next to impossible - I'd use it in a targeted fashion instead of explaining everything. – dezso Aug 11 '16 at 10:53

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.