Additional setup in the PostgreSQL instance is needed to allow SQL Monitor to sample query plans.

Once done, SQL Monitor will capture query plans produced by auto_explain and allow you to explore them alongside top queries. We recommend tuning auto_explain's configuration options to capture the right number of plans for your situation. In many cases, we would recommend starting with some caution; perhaps logging plans where queries ran for over a minute. Logging too many plans may affect server performance and will increase instance log file size. SQL Monitor can be configured to store query plans for a shorter duration if you find that the number of plans being logged results in too much data being stored: see Setting SQL Monitor data retention policy.

Update postgresql.conf

The auto_explain extension must be added to shared_preload_libraries (alongside pg_stat_statements, which was added in the main Preparing PostgreSQL for monitoring page).

shared_preload_libraries = 'pg_stat_statements, auto_explain'

There are several further configuration options for auto_explain.

• auto_explain.log_format must be set to 'json'.
• auto_explain.log_level must be set to 'LOG' to support sampling query plans.
• auto_explain.log_verbose can be set to 'true' to get more detailed information on query plans (e.g. output column list).
• auto_explain.log_analyze can be set to 'true' to get more detailed information on query plans (e.g. per-plan-node timing information), at the expense of instance performance. If set to true, these options are also available:
  
  • auto_explain.log_buffers (buffer usage statistics).
  • auto_explain.log_wal (write-ahead log usage statistics).
  • auto_explain.log_timing (timing statistics - this can be set to 'false' to reduce the performance cost of enabling log_analyze).
  • auto_explain.log_triggers (trigger execution statistics).
• auto_explain.sample_rate can be set to different values to control the probability of logging a query plan.
  • Setting to 1 logs all query plans. 0.01 will log 1% of plans.
• auto_explain.log_min_duration can be set to different values to control how long a query must run for before being considered for plan logging.
  • If set too high, frequent but fast queries may not be sampled. Setting to 0 will log all plans, but significantly increase the amount of logging the instance will perform. We recommend setting this threshold high initially (e.g. 30 seconds) and progressively reduce it over time to gain a proper understanding of the volume of data to be processed.

auto_explain.log_format = json             # this must be set to json as shown
auto_explain.log_level = LOG               # this must be set to LOG as shown
auto_explain.log_verbose = true            # records more detailed query plan information
auto_explain.log_analyze = true            # causes timing for each node to be recorded
auto_explain.log_buffers = true            # record buffer usage statistics
auto_explain.log_wal = true                # record WAL performance statistics (PostgreSQL >= 13 only)
auto_explain.log_timing = true             # record per-node timing
auto_explain.log_triggers = true           # record trigger statistics
auto_explain.sample_rate = 0.01            # record plans for only 1% of queries
auto_explain.log_min_duration = 30000      # 30000 ms = 30 seconds  
auto_explain.log_nested_statements = true  # records the plan for any nested statement invoked by a function call

You will need to restart the PostgreSQL instance for configuration changes to take effect.