postgres-list-query-stats

About

The postgres-list-query-stats tool retrieves query statistics from the pg_stat_statements extension in a PostgreSQL database. It provides detailed performance metrics for executed queries. It’s compatible with any of the following sources:

• alloydb-postgres
• cloud-sql-postgres
• postgres

postgres-list-query-stats lists detailed query statistics as JSON, ordered by total execution time in descending order. The tool takes the following input parameters:

• database_name (optional): The database name to filter query stats for. The input is used within a LIKE clause. Default: "" (all databases).
• limit (optional): The maximum number of results to return. Default: 50.

Example

tools:  list_query_stats:  kind: postgres-list-query-stats  source: postgres-source  description: List query statistics from pg_stat_statements, showing performance metrics for queries including execution counts, timing information, and resource usage. Results are ordered by total execution time descending. 

The response is a json array with the following elements:

[  {  "datname": "database name",  "query": "the SQL query text",  "calls": "number of times the query was executed",  "total_exec_time": "total execution time in milliseconds",  "min_exec_time": "minimum execution time in milliseconds",  "max_exec_time": "maximum execution time in milliseconds",  "mean_exec_time": "mean execution time in milliseconds",  "rows": "total number of rows retrieved or affected",  "shared_blks_hit": "number of shared block cache hits",  "shared_blks_read": "number of shared block disk reads"  } ] 

Notes

This tool requires the pg_stat_statements extension to be installed and enabled on the PostgreSQL database. The pg_stat_statements extension tracks execution statistics for all SQL statements executed by the server, which is useful for identifying slow queries and understanding query performance patterns.

Reference