Query Performance Insight

  • Article

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

Azure Database for PostgreSQL - Single Server is on the retirement path. We strongly recommend that you upgrade to Azure Database for PostgreSQL - Flexible Server. For more information about migrating to Azure Database for PostgreSQL - Flexible Server, see What's happening to Azure Database for PostgreSQL Single Server?.

Applies to: Azure Database for PostgreSQL - Single Server versions 9.6, 10, 11

Query Performance Insight helps you to quickly identify what your longest running queries are, how they change over time, and what waits are affecting them.

Prerequisites

For Query Performance Insight to function, data must exist in the Query Store.

Viewing performance insights

The Query Performance Insight view in the Azure portal will surface visualizations on key information from Query Store.

In the portal page of your Azure Database for PostgreSQL server, select Query performance Insight under the Intelligent Performance section of the menu bar. Query Text is no longer supported is shown. However, the query text can still be viewed by connecting to azure_sys and querying 'query_store.query_texts_view'.

Query Performance Insight long running queries

The Long running queries tab shows the top five queries by average duration per execution, aggregated in 15-minute intervals. You can view more queries by selecting from the Number of Queries drop down. The chart colors may change for a specific Query ID when you do this.

You can select and drag in the chart to narrow down to a specific time window. Alternatively, use the zoom in and out icons to view a smaller or larger period of time respectively.

The table below the chart gives more details about the long-running queries in that time window.

Select the Wait Statistics tab to view the corresponding visualizations on waits in the server.

Query Performance Insight waits statistics

Considerations

Next steps