PostgreSQL 10 High Performance - Third Edition

auto_explain

One of the great features added in PostgreSQL 8.4 is auto_explain, documented at http://www.postgresql.org/docs/current/static/auto-explain.html.

This is a contrib module not necessarily installed by default. See the discussion of contrib modules in Chapter 1, PostgreSQL Versions.

To enable the feature, edit your postgresql.conf to add parameters such as this:

shared_preload_libraries = 'auto_explain' 
custom_variable_classes = 'auto_explain' 
auto_explain.log_min_duration = '1s' 

That will trigger auto_explain on any query longer than a second. You'll need to completely restart the server after making this change before it goes into effect.

Once that's done, any query that takes longer than this will be logged with a full explain ...