Skip to main content

Slow Query

On the slow query screen, you can monitor and view slow SQL that exceeds the threshold time set in DB parameters. PostgreSQL records slow queries to a log file, but to check them, you need to connect to the DB server via terminal and read the text log directly. This screen collects slow query logs in real time and provides them as visual charts and lists.

  • In the Slow Query chart, you can visually identify when slow queries occurred and their execution time.

  • In Slow Query List, you can check detailed information for individual queries and view execution plans (Plan).

  • Both real-time (Live) mode and historical time queries are supported.

Note

An additional agent is required to collect slow queries. In on-premises environments, install the XOS agent, and in cloud (AWS RDS, etc.) environments, install the DBXC agent.

Tip

The slow query chart is similar to the SQL Elapse Map chart on the dashboard, but with the following differences:

Table | Differences between Slow Query chart and SQL Elapse Map chart
CategorySlow QuerySQL Elapse Map
Data SourceSlow query log recorded by DBActive sessions collected by the agent at 5-second intervals
Execution Time AccuracyAccurate — actual execution time recorded by DBUp to 5 seconds of error possible due to 5-second interval collection
Collection TargetOnly queries exceeding the threshold time (log_min_duration_statement)All active sessions running at the time of collection

Prerequisites​

To display data on this screen, DB configuration, DBX Agent configuration, and either XOS Agent or DBXC Agent configuration depending on your environment are required.

DB configuration​

Set the slow query threshold time in postgresql.conf. PostgreSQL restart or running pg_reload_conf() is required after changes.

postgresql.conf
log_min_duration_statement = 500
ParameterDescription
log_min_duration_statementRecords SQL that exceeds the set value (milliseconds) in the log. For example, if set to 500, SQL taking 500ms or more is recorded.

DBX Agent configuration​

Add the following setting to whatap.conf.

whatap.conf
slow_query_log=ON
OptionTypeDefaultDescription
slow_query_logStringOFFEnable slow query log collection

OS agent configuration​

An agent matching the DB operating environment is required to collect slow query logs.

EnvironmentRequired AgentKey ConfigurationSetup Guide
On-premises (Linux, Windows)XOS agentNone (if auto-detection fails, specify log file path directly in slow_query=)XOS Agent Configuration
Cloud (AWS RDS, NCP, etc.)DBXC agentslow_query: true (per-instance setting in config.yaml)DBXC Agent Configuration

Slow Query​

Basic options​

In the top filter area of the slow query screen, you can select the time period and target DB instance to query.

  • Time: Select real-time (Live) mode or a past time. Live mode automatically refreshes every 10 seconds.

  • Instance: Select the target DB instance to query. You can select all or individual instances.

Slow Query chart​

Displays the time when slow queries occurred as a scatter chart. The Y-axis is execution time (seconds) and the X-axis is time.

  • Each dot represents one slow query; dots higher up indicate queries that took longer.
  • Mouse drag on the chart area displays only the queries from that interval in the list below.
  • The ∧ / ∨ buttons in the upper right adjust the Y-axis range.

Slow Query List​

Displays the list of slow queries in the selected interval from the chart.

ColumnDescription
InstanceName of the instance where the query was executed
DBDatabase name
UserExecuting user name
Log TimeTime when the slow query log was recorded
TypeLog type (e.g., statement)
ColtypeCollection type
Duration (s)Query execution time (seconds)
SQLExecuted SQL statement

SQL details​

Click the query in the SQL column to open the details window.

Data interpretation guide​

Key checkpoints​

  • Queries with long Duration: Queries with long individual execution time → SQL tuning, index check

  • When the same SQL appears repeatedly: A specific query is consistently slow → Fundamental tuning required

  • Concentrated in a specific time period: Check for external factors such as DB load, lock contention, batch jobs during that time

Practical usage scenarios​

Scenario 1. Real-time monitoring of slow queries​

  1. Observe the Slow Query chart in Live mode

  2. When new dots appear, drag and select that area

  3. Check queries in the Slow Query List → Click SQL to view details and execution plan

Scenario 2. Analyzing slow queries in a specific time period​

  1. Select the problematic time period in the time filter

  2. Drag the interval where queries are concentrated in the chart

  3. Check queries with the longest Duration first to analyze the cause

Scenario 3. Adjusting the slow query threshold​

  1. If too many slow queries are being collected, raise the log_min_duration_statement value to loosen the threshold

  2. If no slow queries are being collected, lower the log_min_duration_statement value or check the slow_query_log=ON setting

Analyzing with AI​

Click the WhaTap AI button on the screen to open the WhaTap AI chat window, where the queried screen data is summarized and the analysis result is shown as a conversation. After reading the result, you can continue with follow-up questions in the same conversation.

  • The data used for the analysis is the top 20 items with the longest elapsed time among the items selected on the scatter plot.

  • The analysis target is the data queried on the screen. If you change the query conditions and analyze again, the analysis is based on the changed data.

  • The conversation history is recorded in the AI Analysis - {screen name} format.

Last updated on