Hi All,
As i understand we have pg_stats_activity which shows the real time activity of sessions currently running in the database. And the pg_stats_statement provides the aggregated information of the historical execution of all the queries in the database. But I don't see any sampling or timing information in those views. For example at a certain point in time in the past , what queries were getting executed in the database and overall wait events etc.
So is there any other view which provides such information to dig into the past to diagnose any historical performance issues ? or should we create our own table and flush the information from the pg_stats_activity view to that with the current timestamp and that would be helpful in analyzing performance issues or any extension available for such?
Also even the explain analyze can only provide the exact run time stats of a completed query. If we want to see what's going on for a long running query and at what step in the execution path the query is spending most resources and time when it keeps running in the database, is there any available option in postgres database? for e.g. in a SELECT query index access path if taking most of the time OR in an INSERT query INDEX block is causing contention while inserting data into the table , how would we be able to find that for a currently running query or a for a historical query?
As i understand we have pg_stats_activity which shows the real time activity of sessions currently running in the database. And the pg_stats_statement provides the aggregated information of the historical execution of all the queries in the database. But I don't see any sampling or timing information in those views. For example at a certain point in time in the past , what queries were getting executed in the database and overall wait events etc.
So is there any other view which provides such information to dig into the past to diagnose any historical performance issues ? or should we create our own table and flush the information from the pg_stats_activity view to that with the current timestamp and that would be helpful in analyzing performance issues or any extension available for such?
Also even the explain analyze can only provide the exact run time stats of a completed query. If we want to see what's going on for a long running query and at what step in the execution path the query is spending most resources and time when it keeps running in the database, is there any available option in postgres database? for e.g. in a SELECT query index access path if taking most of the time OR in an INSERT query INDEX block is causing contention while inserting data into the table , how would we be able to find that for a currently running query or a for a historical query?
Regards
Veem
