Page Outline
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.
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.
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.
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.
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 show how a PostgreSQL server executes a given query. Additional information on EXPLAIN
plans is available.