How to find the system impact of a query in postgres

Question

When an query is executed in postgres - pgadmin4 . Can i know the query to check the performance of the query using any system tables ?

example :

1. Time taken
2. Query run by 3.CPU impact
3. any blocks etc..

Answer 1

Add pg_stat_statements in shared preload libraries Create extension pg_stat_statements. Then query on pg_stat_statements to get time related stats of the query.

Query run by can be tracked from pg_stat_activity for the currently running statements.

For blocks you can query as below SELECT activity.pid, activity.usename, activity.query, blocking.pid AS blocking_id, blocking.query AS blocking_query FROM pg_stat_activity AS activity JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));