PostgreSQL – Get pg_stat_statements.calls per PID

postgresql

I am trying to collect information about a particular query, namely select 1 (or more globally, any select ?), and I need to know which application is issuing it the most. I know that I can get the total number of calls from pg_stat_statements, which is the total count, and I can also get which process is issuing it through pg_stat_activity, but I am not sure how I can combine this info to tell which process is issuing it the most. Any ideas?

I can't see a possibility for a join because the pg_stat_statments appears to coalesce all this data across all processes. If I can't use these tables, any others I can use before I can run some other form of profiling?

Best Answer

pg_stat_statement cannot produce this kind of information.

You'd have to set log_statement = all, then you get all these statements (and all others) in the log. Make sure that you have %p in your log_line_prefix.

Be careful: logging all statements can bring a busy server down. In PostgreSQL v12, you can set log_transaction_sample_rate to log only a certain sample of transactions, then you can estimate the total count.

Related Question