Re: impact of auto explain on overall performance

You will have to measure it yourself and see.  It depends on your hardware, OS, and OS version, and PostgreSQL version.  And the nature of your queries.  If you have auto_explain.log_timing=on, then I find that large sorts are the worst impacted.  So if you have a lot of those, you should be careful.

On older kernels, I would run with auto_explain.log_timing=off.  On newer kernels where you can read the clock from user-space, I run with auto_explain.log_timing=on.  I find the slowdown noticeable with careful investigation (around 3%, last time I carefully investigated it), but usually well worth paying to have actual data to work with when I find slow queries in the log.  I made a special role with auto_explain disabled for use with a few reporting queries with large sorts, both to circumvent the overhead and to avoid spamming the log with slow queries I already know about.

Cheers,

Jeff