Optimizing Query Performance in PostgreSQL 16 with the Advanced auto_explain Extension

To use the advanced auto_explain extension in PostgreSQL 16, you should first load it into the server. This can be done by adding auto_explain to either session_preload_libraries or shared_preload_libraries in the postgresql.conf file. This setup allows you to track slow queries as they occur. The auto_explain module has several configurable parameters:
  • auto_explain.log_min_duration: Sets the minimum execution time for a statement to have its plan logged.
  • auto_explain.log_analyze: Enables the logging of EXPLAIN ANALYZE output.
  • auto_explain.log_buffers and auto_explain.log_wal: Control the logging of buffer and WAL usage statistics.
  • auto_explain.log_timing: Toggles the logging of per-node timing information.
  • auto_explain.log_triggers: Includes trigger execution statistics in logs.
  • auto_explain.log_verbose: Enables verbose output.
  • auto_explain.log_settings: Logs information about modified configuration options.
  • auto_explain.log_format: Sets the output format (text, xml, json, yaml).
  • auto_explain.log_level: Determines the log level for the query plan.
  • auto_explain.log_nested_statements: Controls whether nested statements are logged.
  • auto_explain.sample_rate: Sets the fraction of statements to explain in each session.
For example, to log every query with its execution plan, you can set auto_explain.log_min_duration to 0 and enable auto_explain.log_analyze. Remember, enabling some of these features, especially auto_explain.log_analyze, can impact performance due to the overhead of collecting detailed statistics. For detailed information and examples, please refer to the PostgreSQL 16 documentation on auto_explain.
About Shiv Iyer 455 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.