PostgreSQL Slow Query Analyzer

The Slow Query Analyzer tool helps you easily identify problem queries in your PostgreSQL hosting deployment at ScaleGrid.

Create a PostgreSQL Slow Query Report

1. Log into the ScaleGrid Console.
2. Go to your PostgreSQL Cluster Details page, and click the 'Slow Queries' tab at the top.
3. Select the server you're interested in analyzing.
4. Select the time range. By default you can pick last hour, 3 hours, 6 hours, 24 hours, or a custom time range.
5. Click profile to analyze your PostgreSQL slow queries.

Review a heatmap of all the queries in your system for your selected time range. All queries of the same type have the same color so you can get a good idea of the clustering of the different queries running in your system.

The X-axis is for time, and the Y-axis is the duration of the queries. The higher up the queries, the slower it is.

Hover or click over one of the bubble to get an idea of what sort of query is associated with it. You can also zoom into a particular time range to understand in detail what is going on over that period of time.

Top Read Load Generators & Top Write Load Generators

Review the top Read and Write Load Generator pie chart to easily identify the top queries causing the read and write load in your system. This is a quick one glance way to identify queries that are typically not indexed.

Slow Queries Detailed Table Report

If you scroll down, the data is presented in a tabular format:

• Type
• Users
• Query
• Database
• Count
• Plan Node Types
• Duration (ms)
• Rows Returned (avg)
• Rows Scanned
• Rows Modified

Customize a Filtered Query View

You can also control which queries show up in the heat map if you want a specific heat map for a particular query. Simply select the checkbox next to the particular query you’re interested in, and only those queries will show up on the heat map.

You can also filter your queries based on a particular string. For example, if you only want to analyze the 'SELECT' queries, enter this string into the search box above the table and this will filter the queries to only show those with SELECT.

Review the PostgreSQL Plan Visualizer

Click the link under the Plan Node Types column to see the PostgreSQL Plan Visualizer for a visual representation of the query plan that was selected for your query.

This will allow you to see all of the different stages in your Query Plan, and for a particular stage, you can select the different options to see the plan details and all of the different parameters.

Download Your Slow Query Analysis Report

Easily export all of your slow query analysis data.

• Download CSV: Export in a CSV format to open in excel for further analysis, and share with your team and do any sort of offline analysis that you want.
• Download PDF: You can also export as a report in PDF format to share with your team for a more detailed view of the slow query analysis.

On the top right, you can also view the previous slow query analysis that were run on your system and analyze through any of the above methods.

That’s it for our PostgreSQL Slow Query Analyzer tool. If you have any questions, reach out to us at [email protected].