How to see auto_explain output in TEXT format in Azure Database for PostgreSQL flexible server

Question

How to see auto_explain output in TEXT format in Azure Database for PostgreSQL flexible server?

Answer 1

Hi Naveen Kumar Kakarla (Quadrant Resource),

Thanks for reaching out to Microsoft Q&A.

1.     Enable auto_explain Extension:

First, ensure that you have enabled the auto_explain extension on your Azure Database for PostgreSQL flexible server instance. You can do this by adding the extension to the shared preload libraries. Here’s how:

Add auto_explain extension to the shared preload libraries from the server parameters page on the Azure Database for PostgreSQL flexible server portal. Note that making this change will require a server restart.

2.     Configure auto_explain Parameters:

After enabling the extension, configure the following parameters:

auto_explain.log_min_duration: Set the minimum statement execution time (in milliseconds) that will cause the statement’s plan to be logged. Ex., setting it to 250ms will log all statements running 250ms or longer.

auto_explain.log_format: Choose the output format for the EXPLAIN logs. The allowed values are text, xml, json and yaml. The default is text.

auto_explain.log_analyze: Enable this parameter to print EXPLAIN ANALYZE output instead of just EXPLAIN output when an execution plan is logged.

Once configured, you can retrieve the execution plans for slow queries:

·        Connect to the azure_sys database and execute the following query to retrieve the actual query text using the provided script

select the “Logs” section from the Monitoring tab on the Azure Database for PostgreSQL flexible server portal overview page. Choose the time range where high CPU utilization was found.

Execute the following query to retrieve the EXPLAIN ANALYZE output of the identified query

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.