PostgreSQL: Run EXPLAIN on all queries in pg_stat_activity

After further diagnostics and some Googling, we came across this article that described many of the same symtoms we were experiencing. The root cause of their problem (and from what we can tell, ours too) was related to the Transparent Huge Pages implementation.

After disabling Transparent Huge Pages with this command:

echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled

The problem appears to have been resolved. We've been running under an increased workload for the last two weeks and the issue has not resurfaced. The system's contexts and interrupts are consistently 1/10th of what they had been and average system time has decreased as well.

Not sure if it is the solution for everyone, but I post it here as a possible cause in case it can help anyone else resolve a similar issue.

It also says that xact_start can be different than query_start, but doesn't explain why

It differs when the transaction encapsulates several queries, for example:

BEGIN;  -- starts at t0, beginning of transaction
query1; -- starts at t1
query2; -- starts at t2
COMMIT; -- starts at t4, end of transaction

If we look at the pg_stat_activity entry for this session while query2 is running, xact_starts will be t0 and query_start will be t2, with t2>t0, the difference being essentially the duration of query1.

xact_start helps discovering long running transactions, which is useful when troubleshooting or tuning a live database.