“Problematic queries”

This section displays suspicious queries in various cross-sections, which are defined in the server configuration or can be overridden through ALTER SYSTEM.

On the home page, the host is selected, and the analytics of problematic queries is displayed for the selected host in the corresponding tabs.

  1. Problematic queries:

    1. will display a summary of the hosts:

      • total problematic queries,

      • total problematic query patterns,

      • timeline.

    2. will transfer to the analytics of the selected host:

      • Resource costs for queries aggregated at patterns level,

      • Resource costs for queries aggregated at models level

      • resource costs for queries aggregated by applications,

      • Etc.

The main page of the section displays a summary of hosts with problematic queries, the main of which are the total number of problematic queries and the number of patterns to which they can be reduced.

For each host, a graph of the intensity of problematic queries is displayed, divided into 24 hours (Timeline).

To go to the detailed analytics of problematic queries, click on the quantity of patterns.

For the selected host, the service provides analytics of problematic queries. The resource requirements for queries will be displayed as pivot tables:

  • aggregated at patterns level;

  • aggregated at models level;

  • aggregated by applications;

  • and others.

Enable reset analysis of running queries directly from the active session

To enable resetting the analysis of running queries directly from an active session, you can use the following construct:

SET log_min_duration_statement = '1ms'; -- log all queries longer than 1ms LOAD 'auto_explain'; -- load the module auto_explain SET auto_explain.log_min_duration = '1ms'; -- remove the plan if longer than 1ms SET auto_explain.log_analyze = 'on'; -- write the plan as EXPLAIN (ANALYZE, BUFFERS) SET auto_explain.log_buffers = 'on'; SET auto_explain.log_timing = 'on'; SET auto_explain.log_triggers = 'on'; -- and the triggers are also SET track_io_timing = 'on'; -- plan the time of IO operations

You can also (recommended) set the required auto_explain settings in the server config.