Skip to content
This documentation is sourced from a third-party project and is not maintained by pgEdge.

Monitoring

Monitor performance with pg_stat_statements (be sure to add it to shared_preload_libraries).

CREATE EXTENSION pg_stat_statements;

Get the most time-consuming queries with:

SELECT query, calls, ROUND((total_plan_time + total_exec_time) / calls) AS avg_time_ms,
    ROUND((total_plan_time + total_exec_time) / 60000) AS total_time_min
    FROM pg_stat_statements ORDER BY total_plan_time + total_exec_time DESC LIMIT 20;

Note: Replace total_plan_time + total_exec_time with total_time for Postgres < 13

Monitor recall by comparing results from approximate search with exact search.

BEGIN;
SET LOCAL enable_indexscan = off; -- use exact search
SELECT ...
COMMIT;