Postgres Monitor Logo
Alpha Release

Demo Account

demo@postgresmonitor.com

Slow Queries

Page Outline

Overview

Slow queries are often the root of many performance problems for a PostgreSQL database. Postgres Monitor collects query statistics and EXPLAIN plans so you can troubleshoot these problematic queries.

Query statistics are collected from the pg_stat_statements view every minute by the agent.

EXPLAIN plans are collected automatically when a slow query is logged to the PostgreSQL logs. Plans are redacted so that query parameters are removed. Slow query logging is controlled by the log_min_duration_statement setting which can be adjusted.

Slowest Queries

The slowest queries view sorts queries by slowest mean execution time.

Slow queries are often caused by missing indexes or reading too much data from a table.

Most Time Consuming Queries

The most time consuming queries view sorts queries by their total execution time.

This will show queries that are both executed often and are slower than other queries. Speeding up these queries will often have the largest impact for your users.

Most Called Queries

The most called queries view sorts queries by their total call count.

These queries may be quite fast but are executed far more often than other queries. Look for opportunities to cache query results or even remove these query calls if they are unnecessary.

Slowest IO Queries

The slowest IO queries view sorts queries by their total block IO time.

These queries are not hitting the database cache and are instead hitting disk which is much slower. They are often caused by missing indexes. If the correct indexes already exist, you should evaluate if upgrading your database's instance size to a larger memory and cache size would fix your slow IO queries.

EXPLAIN Plans

EXPLAIN plans show how a PostgreSQL server executes a given query. Additional information on EXPLAIN plans is available.