Auto Explain in PostgreSQL

Link - https://www.postgresql.org/docs/9.4/static/auto-explain.html
Reference Link - https://www.endpoint.com/blog/2012/10/30/postgresql-autoexplain-module

Some Key Points for Auto Explain:

1. Auto Explain helps analyzing the queries automatically in log file based on the configuration we set up.
2. Auto Explain can be set up in session level(preferred) and also in postgresql.conf file(which needs a reboot).
3. There are performance impact if we set up auto explain for all the queries as it takes up additional time to explain the queries and log in log file before executing it actually. 
4. Auto Explain supports from PG version 9.3.
5. If we add Auto Explain Plan in postgresql.conf file, it would need a reboot of the postgres service. 
6. Auto Explain gets logged in default log file of the postgres instance.

Steps to set up Auto Explain in session( Once we disconnect from session it wont be logging auto explain plan anymore in log file)

1. We need to issue the below commands so that auto explain starts getting logged in log file.
2.  Command 1 - LOAD 'auto_explain';
3. Command 2 - SET auto_explain.log_min_duration = 5; ( It is in seconds)
4. Command 3 - SET auto_explain.log_analyze = true; ( It logs the explain plan in more detailed way)
5. This process does not needs any reboot or reload.

Steps to configure Auto Explain Plan in postgresql.conf file:

1. Make the below parameter changes in postgresql.conf file.
2. Parameter 1 - shared_preload_libraries = ‘auto_explain’;
3. Parameter 2 - auto_explain.log_min_duration = '5s'
4. Parameter 3 - auto_explain.log_analyze = true
5. Restart postgres service.