We were planning to have the auto_explain extension added and set the log_min_duration to ~5 seconds and log_analyze to true. So that all the queries going above that time period will be logged and provide detailed information on the exact point of bottleneck. Will it be a good idea to set it on production DB which is a highly active database? or should we only have the extension added but only set the parameters while we debug some performance issue and then reset it back after we are done.
I would not use log_analyze on a highly active production db. Even on a dev system, use it carefully as it has some downsides. The log_min_duration of 5s should be fine everywhere, however.