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?
Look at the auto_explain setting. Taking a random interval snapshot of running queries likely will not teach you anything useful.
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?
You can see locking contention in the pg_locks table. In my experience I rarely ever saw anything in there even when I was cranking tens of millions of inserts and updates per day. I don't think there's anything for historical queries or to probe anything more about a currently running query's progress.
Take some time to think about your queries and how you can reduce any locking they need. If you do need some locking, consider using the FOR UPDATE clause in SELECT to limit what you do lock.
