How can I log SQL execution plans?

Overview

You may be requested to log SQL execution plans by CAST Support as part of the process of identifying performance issues. Follow the instructions below to do so. CAST does not recommend doing this for normal every-day usage since the logging itself can impact performance and will generate a large volume of logs.

The instructions below use the PostgreSQL module called auto_explain documented here: https://www.postgresql.org/docs/current/auto-explain.html .

Configure auto_explain

To log execution plans, you need to modify the postgresql.conf file, which can be found in the following locations, although this may differ for installations in Linux environments:

CAST Storage Service
%PROGRAMFILES%\CAST\CastStorageService<version>\db_data\postgresql.conf

Linux
/etc/postgresql/<version>/main/postgresql.conf

In a Linux environment, use find / -name "postgresql.conf" to recursively search all folders to find this file.

Edit this file and add in the following parameters at the end of the file: (these parameters are not provided out of the box):

auto_explain.log_min_duration = '0s'        # setting this to zero logs all plan
auto_explain.log_nested_statements = on     # log statements executed inside a function
auto_explain.log_verbose = on               # it's equivalent to the VERBOSE option of EXPLAIN
auto_explain.log_buffers = on               # it's equivalent to the BUFFERS option of EXPLAIN
auto_explain.log_analyze = on               # causes EXPLAIN ANALYZE output; when this parameter is on, per-plan-node timing occurs for all statements executed, whether or not they run long enough to actually get logged. This can have an extremely negative impact on performance. Turning off "auto_explain.log_timing" ameliorates the performance cost, at the price of obtaining less information.
shared_preload_libraries = 'auto_explain'   # change requires restart
auto_explain.log_timing = off               # Leave this set to "off" (this will log row counts) unless you need exact log times when you should set it to"on" (this can negatively impact performance). Note that this parameter has no effect unless "auto_explain.log_analyze" is enabled