Posgresql auto_explain extended referenc

Auto_explain can define the sql execution time to record in the postgres log, including the direct plan of sql, which can be said to be a very useful tool for online optimization.

In addition, the combination of pg_stat_statements, log_min_duration_statement and log_statement = all can be said to be a sharp tool for analyzing online slow sql. Auto_explain is included in the installation software and can be installed and used directly:

[root@my1 ~] # cd postgresql-10.0/contrib/auto_explain

[root@my1 auto_explain] # make & & make install

Configure the postgresql.conf file:

Shared_preload_libraries = 'auto_explain'

# auto_explain

# auto_explain.log_min_duration ='1s'# record the execution of sql for more than 1 second

Auto_explain.log_min_duration = 100 # record execution of sql that exceeds 100ms

# auto_explain.log_min_duration = 0 # record all executed sql

Auto_explain.log_timing = on

Auto_explain.log_verbose = on

Restart the database:

Pg_ctl restart-m fast

Note:

Auto_explain.log_min_duration can be modified at any time, and then reload can be modified. Online adjustment can be made as needed.

Modification requires restarting the database shared_preload_libraries = 'auto_explain'